CODEWITHSUNDEEP
postgresqlgeospatialpostgisspatialpgrouting
Forin
Forin

Reputation: 1619

Finding a route based on multilinestring table

I have a Postgis table containing a lot of MultiLineString features.

I would like to use that data to create a route between Point A and Point B. I assume there is no single feature, which allows to solve that problem. It means the route has to combined from two or more MultiLineString features.

How can I solve that problem?

Upvotes: 2

Views: 85

Answers (1)

Mariot Tsitoara
Mariot Tsitoara

Reputation: 21

It's not really possible to answer without the examples but I can point you to the direction of what I've done to solve a similar issue. I had a lot of MultiLineString that represented roads. I also had Points that represented a road node, where two roads merge.

You can find the shapefiles here: https://geoservices.ign.fr/route500#telechargement

I had to create a pgRouting table to calculate how to get from a road point to another.

CREATE TABLE routes (
  id INTEGER,
  source INTEGER,
  target INTEGER,
  cost FLOAT,
  reverse_cost FLOAT);

Then fill the table with infos from the road multilinestrings:

WITH troncon AS (
    SELECT ogc_fid                                                      AS id,
        CASE
            WHEN sens = 'Sens inverse' THEN ST_EndPoint(ST_LineMerge(wkb_geometry))
            ELSE ST_StartPoint(ST_LineMerge(wkb_geometry)) END          AS source,
        CASE
            WHEN sens = 'Sens inverse' THEN ST_StartPoint(ST_LineMerge(wkb_geometry))
            ELSE ST_EndPoint(ST_LineMerge(wkb_geometry)) END            AS target,
        longueur                                                        AS cost,
        CASE WHEN sens = 'Double sens' THEN longueur ELSE -longueur END AS reverse_cost
    FROM troncon_route
)
INSERT INTO routes (id, source, target, cost, reverse_cost)
SELECT id,
       (SELECT noeud_routier.ogc_fid FROM noeud_routier ORDER BY noeud_routier.wkb_geometry <-> source LIMIT 1) AS source,
       (SELECT noeud_routier.ogc_fid FROM noeud_routier ORDER BY noeud_routier.wkb_geometry <-> target LIMIT 1) AS target,
       cost,
       reverse_cost
FROM troncon;

You can then use dijstra to get the most optimal route:

WITH road_near_parcel AS (
    SELECT noeud_routier.ogc_fid AS id
    FROM noeud_routier
    ORDER BY noeud_routier.wkb_geometry <-> (SELECT parcels.centroid FROM parcels WHERE id = 250000054000010276) LIMIT 1
),
road_near_facility AS (
    SELECT noeud_routier.ogc_fid AS id
    FROM noeud_routier
    ORDER BY noeud_routier.wkb_geometry <-> (SELECT facilities.geometry FROM facilities WHERE id = 1946) LIMIT 1
)
SELECT troncon_route.num_route,
       SUM(di.cost) AS cost
FROM pgr_dijkstra(
             'SELECT id, source, target, cost, reverse_cost FROM routes',
             (SELECT road_near_parcel.id FROM road_near_parcel),
             (SELECT road_near_facility.id FROM road_near_facility),
             false
     ) AS di
JOIN troncon_route ON troncon_route.ogc_fid = di.edge
GROUP BY troncon_route.num_route;

I hope that helps! I think you can focus on how to transform a multilinestring to a routing table. You can use ST_EndPoint and ST_EndPoint to get the extremities

Upvotes: 2

Related Questions