139 lines
4.2 KiB
PL/PgSQL
139 lines
4.2 KiB
PL/PgSQL
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;
|