1

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.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
HSJ
  • 355
  • 1
  • 7
  • It looks like you want to contract your network, have a look at this thread https://gis.stackexchange.com/questions/422316/unsplitting-merging-lines-that-have-coincident-endpoints-using-postgis – Timothy Dalton Jun 08 '22 at 05: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 Jun 08 '22 at 05:54
  • 1
    You can add source and target information with https://docs.pgrouting.org/3.1/en/pgr_createTopology.html – Timothy Dalton Jun 08 '22 at 07: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 Jun 08 '22 at 08:55
  • I do not know why but I could make a table after adding two columns named source and target by running queries ALTER TABLE planet_osm_roads ADD COLUMN "source" integer; ALTER TABLE planet_osm_roads ADD COLUMN "target" integer; based on other source. But I am not sure whether the values, which seems to be random (but maybe not), in those columns are correct or not. Will check further. – HSJ Jun 08 '22 at 09:08
  • I could run the suggested query and obtained the network result. However, I found that this method won't solve the issue that I mentioned: splitting edges which are crossing. For instance, id15 in the original network should be splitted at the crossing points with id20 and id14. I suppose it is not taken into account in the suggested method. – HSJ Jun 08 '22 at 09:58
  • 1
  • You will have to split first https://stackoverflow.com/questions/25753348/how-do-i-divide-city-streets-by-intersection-using-postgis 2. then run create topology 3. then contract
    • – Timothy Dalton Jun 08 '22 at 10:22
  • As per the [help/behavior] please do not include chit chat like statements of appreciation within your posts. – PolyGeo Jun 08 '22 at 10:39