This is a Two-way road, each line stands for one way, and the x means a split point in the way. That's to say, we will split each way to multiple segments before we save them to postgis. Each segment have a name same as the road name.

For example, the name of the above road is St1, so the 7 segments will all have the same name.

Now for a given name like St1, we want to combine all the matched segments as one (if one way) or two(if two way) linestrings.

After googling, it seems that the pg function ST_Union (https://postgis.net/docs/ST_Union.html) looks like what we want, after the test, it worked.

However there are some different roads(in different position) may own the same name.

enter image description here

As shown, suppose the red road in the top right have the same road name as "West Chester Pike", but they are two different roads in deed.

So if we may extract all the roads by a given name "West Chester Pike", ST_Union will merge all of them, so we wonder if it is possible to distinguish the roads for example by distance?

  • You should convert multi linestring to single linestring after ST_Union, see stackoverflow.com/questions/21719941/…. – Zoltan Dec 20 '18 at 6:04
  • @Zoltan: Sorry, but I am not exactly sure what you mentioned. After ST_Union I get the multiple-linestring which contains all the ways of all the matched roads by name, so converting them to single linestring can group them ? – giser Dec 20 '18 at 6:20
  • Sorry one plural missing from my comment. Multipart linestring to single part linestrings, no group at all. You will get several single linestrings. Did you check the link? Alternatively you can use QGIS multipart to single part function in Vector/Geometry tools menu. It needs no preparation comparing to ST_Union with group by clause as jaffres and EZMapDesing suggested. – Zoltan Dec 21 '18 at 7:32

In your situation (at least the one displayed on the picture) I would use additional information like road class and do the ST_Union separately for each road class. One of your Westchester Pikes seems to be a highway of some sorts whereas the other one is a simple street. Perhaps distinguishing them by class could help you.


I had a similar things to do with elevation lines and i used ST_union but before that i made a cluster of each subset you need to gather.

So you can add an id of touching segments and then go for your union but with name and cluster_id. you can look to function like https://postgis.net/docs/ST_ClusterDBSCAN.html or https://postgis.net/docs/manual-2.2/ST_ClusterWithin.html.

the idea is to rebrand your data in group of geometry you are sure to union, to avoid take road with the same name but no geometry link.

  • We have almost 5000,000 features, so add additional information for each feature means a lot more workload. That's why we want to find if pg can make some cluster automatically, for example by distance. – giser Dec 21 '18 at 4:13
  • then you need to use ST_Cluster_within and create an attribute, 5M features would not be something postgis can not handle. I don't know any better way to do what you want, sorry. – Maximilien jaffrès Dec 21 '18 at 11:09
  • Never mind, thank you anyway, I will have a try with the ST_Cluster_within. – giser Dec 23 '18 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.