sql/postgresql-entrypoint-initdb.d/11_postgis.sql

create schema carte;

set SEARCH_PATH to public,postgis;

create table carte.point (
  id serial primary key,
  nom text not null,
  altitude double precision,
  geom postgis.geometry(point, 4326) not null
);

create index idx_point_geom
  on carte.point using gist (geom);

create table carte.route (
  id serial primary key,
  depart int,
  arrivee int,
  geom postgis.geometry(linestring, 4326) not null,
  longueur double precision
);

create index idx_route_geom
  on carte.route using gist (geom);

create or replace function maj_longueur()
returns trigger as $$
begin
  new.longueur := postgis.st_length(postgis.st_transform(new.geom, 2154));
  return new;
end;
$$ language plpgsql;

create trigger trigger_longueur
  before insert or update on carte.route
  for each row
  execute function maj_longueur();

-- ----------------------------------------------------------------------

create table carte.route_cout (
  route_id int references carte.route(id),
  tag text,
  cout float,
  cout_inverse float,
  primary key (route_id, tag)
);

create table carte.zone (
  id int primary key,
  nom text,
  categorie text,
  geom postgis.geometry(multipolygon, 4326) -- on choisit srid 4326 (wgs84)
);

-- ----------------------------------------------------------------------
\COPY carte.point FROM '/tmp/point.csv' (FORMAT CSV, header, ENCODING 'UTF8');
\COPY carte.route(id, depart, arrivee, geom) FROM '/tmp/route.csv' (FORMAT CSV, header, ENCODING 'UTF8');
\COPY carte.route_cout FROM '/tmp/route_cout.csv' (FORMAT CSV, header, ENCODING 'UTF8');
\COPY carte.zone FROM '/tmp/zone.csv' (FORMAT CSV, header, ENCODING 'UTF8');
-- ----------------------------------------------------------------------

-- ----------------------------------------------------------------------
-- Mise à jour des coût suivant la pente et la nature de la route
-- cout = longueur × coef_surface × ( 1 + coef_pente)
-- Avec un minimum : cout = max⁡(cost, ϵ) pour éviter un coût négatif (< 0)  en descente

WITH calc AS (
  SELECT
    r.id,
    r.depart, r.arrivee,
    rc.cout, rc.cout_inverse,
    ST_Length(r.geom::geography) AS length_m,
    p1.altitude AS alt_depart,
    p2.altitude AS alt_arrivee,
    rc.tag,
    -- pente aller
    (p2.altitude - p1.altitude) / NULLIF(ST_Length(r.geom::geography), 0) AS slope_forward,
    -- pente retour
    (p1.altitude - p2.altitude) / NULLIF(ST_Length(r.geom::geography), 0) AS slope_reverse,
    CASE
      WHEN tag = 'rue' THEN 1.0
      WHEN tag = 'chemin' THEN 1.2
      WHEN tag = 'sentier' THEN 1.4
      ELSE 1.5
    END as coef_surface
  FROM carte.route r
  JOIN carte.point p1 ON r.depart = p1.id
  JOIN carte.point p2 ON r.arrivee = p2.id
  JOIN carte.route_cout rc ON rc.route_id = r.id
),
costs AS (
  SELECT *,
    length_m * coef_surface *
    CASE
      WHEN slope_forward >= 0.10 THEN (1 + slope_forward * 3)
      WHEN slope_forward >= 0.04 THEN (1 + slope_forward * 2)
      WHEN slope_forward >= 0 THEN (1 + slope_forward * 1)
      WHEN slope_forward >= -0.15 THEN (1 + slope_forward * 3)
      ELSE (1 + abs(slope_forward) * 1) -- pénalité en cas de forte descente
    end
    AS new_cost_forward,

    length_m * coef_surface *
    CASE
      WHEN slope_reverse >= 0.10 THEN (1 + slope_reverse * 3)
      WHEN slope_reverse >= 0.04 THEN (1 + slope_reverse * 2)
      WHEN slope_reverse >= 0 THEN (1 + slope_reverse * 1)
      WHEN slope_reverse >= -0.15 THEN (1 + slope_reverse * 3)
      ELSE (1 + abs(slope_reverse) * 1) -- pénalité en cas de forte descente
    END
    AS new_cost_reverse
  FROM calc
)
UPDATE carte.route_cout rc
SET
  cout = CASE WHEN rc.cout = -1 THEN -1
              ELSE GREATEST(c.new_cost_forward, 0.01) END,
  cout_inverse = CASE WHEN rc.cout_inverse = -1 THEN -1
                      ELSE GREATEST(c.new_cost_reverse, 0.01) END
FROM costs c
WHERE rc.route_id = c.id;

CREATE OR REPLACE VIEW carte.routes_a_star AS
SELECT
  r.id AS id,
  r.depart as source,
  r.arrivee as target,
  r.longueur,
  rc.cout AS cost,
  rc.cout_inverse AS reverse_cost,
  ST_X(p1.geom) AS x1, ST_Y(p1.geom) AS y1,
  ST_X(p2.geom) AS x2, ST_Y(p2.geom) AS y2,
  r.geom
FROM carte.route r
JOIN carte.route_cout rc ON rc.route_id = r.id
JOIN carte.point p1 ON r.depart = p1.id
JOIN carte.point p2 ON r.arrivee = p2.id;