Generate source_id and target_ id for every segment in a multilinestring geometry (PostGIS|PgRouting)

Question

I am working on a project that requires finding the shortest path and to achieve this, I've used the PgRouting dijkstra function and it's all worked out perfectly.

Initially, the road network was imported using an osm file. The table created consists of multilinestring geometry types.

How can I transform the multilinestring into linestring and give each of them a source id and targer id so that dijkstra function gives the shortest path starting next to the point it has localised.

Thanks all !

Answer 1

Don't do that. You better use tools like osm2pgrouting or osm2po to import OSM data for use with pgRouting.

To find the nearest start and end point you need to use PostGIS functions like "ST_Line_Locate_Point". You can write pl/pgsql wrapper functions to do this or your application can handle it.