I am trying to simplify road network with PostGIS. The original network and processed network are illustrated below.

enter image description here

I need to do the following manipulations:

  1. Split links which has intersection(s) with other link(s) at the middle (Examples: id4 in the original network which is connedted by id5, id15 which is connected by id20 and id14)
  2. Merge adjacent links which are connected at the edges, but not at the intersections (such as 1 and 2, 13 and 14, 3 and left side of 4 in the original network)

The desired output may be obtained from the following process:

  1. Find vertices which are connected to other links
  2. Split links using the vertices found in the process 1
  3. Merge links which are NOT connected to more than 3 links

However, as I just started to use PostGIS, I do not have sufficient knowledge for the implementation. I found a similar example at ST_LineMerge to simplify road network, but it uses join_id and street to merge links. My network does not have an identifier for merging links. Geometry information is only the way to identify links to be split/merged.

Here is the original network that I am using in pgAdmin4:

CREATE TABLE net (id integer, geom geometry(MultiLineString, 4326));
INSERT INTO net (id, geom)
VALUES (1, ST_GeomFromText('MULTILINESTRING((136.6196599601392 36.589639081378664,136.61989569531218 36.58965330446509))', 4326)),
       (2, ST_GeomFromText('MULTILINESTRING((136.61989569531218 36.58965330446509,136.62012398243598 36.589667078173306))', 4326)),
       (3, ST_GeomFromText('MULTILINESTRING((136.62012398243598 36.589667078173306,136.62022166003996 36.5896726469273))', 4326)),
       (4, ST_GeomFromText('MULTILINESTRING((136.62022166003996 36.5896726469273,136.6203506286787 36.58967999963244,136.62063360215916 36.58968753774985))', 4326)),
       (5, ST_GeomFromText('MULTILINESTRING((136.6203506286787 36.58967999963244,136.62036404026844 36.58954216863634))', 4326)),
       (6, ST_GeomFromText('MULTILINESTRING((136.62036404026844 36.58954216863634,136.620421707496 36.589543245124844))', 4326)),
       (7, ST_GeomFromText('MULTILINESTRING((136.620421707496 36.589543245124844,136.62049680988002 36.589587394642706))', 4326)),
       (8, ST_GeomFromText('MULTILINESTRING((136.62049680988002 36.589587394642706,136.62065237730553 36.589583086890116))', 4326)),
       (9, ST_GeomFromText('MULTILINESTRING((136.62063360215916 36.58968753774985,136.62065237730553 36.589583086890116,136.6206638954505 36.589480812069304))', 4326)),
       (10,ST_GeomFromText('MULTILINESTRING((136.6206638954505 36.589480812069304,136.62067517601872 36.589380646799555,136.62068188226328 36.589223432715414))', 4326)),
       (11,ST_GeomFromText('MULTILINESTRING((136.62068188226328 36.589223432715414,136.62020310748755 36.58920297313887))', 4326)),
       (12,ST_GeomFromText('MULTILINESTRING((136.62020310748755 36.58920297313887,136.62016019183955 36.58922666308018))', 4326)),
       (13,ST_GeomFromText('MULTILINESTRING((136.62016019183955 36.58922666308018,136.6200241103804 36.589219386818))', 4326)),
       (14,ST_GeomFromText('MULTILINESTRING((136.6200241103804 36.589219386818,136.6197417678685 36.589201896650366))', 4326)),
       (15,ST_GeomFromText('MULTILINESTRING((136.61954957107096 36.589770977368836,136.619626432514 36.58968753774985,136.6196599601392 36.589639081378664,136.6196867824193 36.58958524076644,136.6197323798457 36.58936126461083,136.6197417678685 36.589201896650366,136.6197447880784 36.58910164634096))', 4326)),
       (16,ST_GeomFromText('MULTILINESTRING((136.62013469499337 36.589528238518106,136.62012398243598 36.589667078173306,136.62015751006118 36.58969399847939))', 4326)),
       (17,ST_GeomFromText('MULTILINESTRING((136.62015751006118 36.58969399847939,136.6201497449703 36.58981549677306))', 4326)),
       (18,ST_GeomFromText('MULTILINESTRING((136.62013469499337 36.589528238518106,136.620149930439 36.58935147252844))', 4326)),
       (19,ST_GeomFromText('MULTILINESTRING((136.620149930439 36.58935147252844,136.62016019183955 36.58922666308018))', 4326)),
       (20,ST_GeomFromText('MULTILINESTRING((136.6196599601392 36.589639081378664,136.619642525882 36.58963800399084,136.61959358689234 36.58963701183535))', 4326)),
       (21,ST_GeomFromText('MULTILINESTRING((136.61959358689234 36.58963701183535,136.61952416180296 36.58963560435872))', 4326)),
       (22,ST_GeomFromText('MULTILINESTRING((136.62068188226328 36.589223432715414,136.62068456404154 36.589149132526586,136.62067785869635 36.58911575149091))', 4326)),
       (23,ST_GeomFromText('MULTILINESTRING((136.62063360215916 36.58968753774985,136.62062878485793 36.58976023194854))', 4326)),
       (24,ST_GeomFromText('MULTILINESTRING((136.62063360215916 36.58968753774985,136.6206888947573 36.589687030307246))', 4326)),
       (25,ST_GeomFromText('MULTILINESTRING((136.6206888947573 36.589687030307246,136.62076739174896 36.589686309908515))', 4326))

I received a suggestion to look into Unsplitting/merging lines that have coincident endpoints using PostGIS. It seems interesting but the suggested method couldn't be implemented in my network as my network does not contain source, target and cost columns which are mandatory to run gpRouting for contracting graph network.

The actual network that I am going to use is much larger than the one that I posted. It may be difficult to add the above information just to run the suggested query.

The suggested method in Unsplitting/merging lines that have coincident endpoints using PostGIS returned the following network.

enter image description here

It returns the network close to my desired result but it does not split links at intersections: i.e., id15 in the original network should be split at the intersections with id20 and id14. id19 and id21 also have the same issue.

I suppose this issue is not taken into account in the suggested method as all links are split at all intersections in their network.

Another issue is that id1-4, 20 and 21 are also merged after running the query. This may be possibly happen if large trellance is set in pgr_createTopology process.

  • It looks like you want to contract your network, have a look at this thread gis.stackexchange.com/questions/422316/… Commented Jun 8, 2022 at 5:12
  • @TimothyDalton Thank you, but the suggested method couldn't be implemented in my network as my network does not contain source, target and cost columns which are required to run the query in the suggested method. I tried to run the suggested query but it required those columns. I think cost can be calculated easily if assuming it as the length of segments. Others will be tricky. I will look for the way how to calculate it if there are no other solutions.
    – HSJ
    Commented Jun 8, 2022 at 5:54
  • 1
    You can add source and target information with docs.pgrouting.org/3.1/en/pgr_createTopology.html Commented Jun 8, 2022 at 7:21
  • Thank you. I ran a query SELECT pgr_createTopology('net', 0.00001, 'geom', 'id'); but it returned an empty table named net_vertices_pgr. I changd trellance and tried but the same result.
    – HSJ
    Commented Jun 8, 2022 at 8:55
  • 1
    1. You will have to split first stackoverflow.com/questions/25753348/… 2. then run create topology 3. then contract Commented Jun 8, 2022 at 10:22


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.