I have LineStrings that represent a road network/address centerline that are segmented at intersections, etc. I have determined how to query collections of LineStrings that are adjacent to each other and have the same street name. Now I am hoping to be able to list the sub-segment IDs sequentially based on adjacency, regardless of line direction of the sub segments since they do not all “flow” in the same direction. So that they are listed in order from one end to the other. How do I implement this? I know postgis_topology is an entire suite of topology related functions, but I’m hoping there is a simple way to compare coordinates.

     endpoints AS (SELECT ST_Collect(ST_StartPoint(shape), ST_EndPoint(shape)) AS geom 
                   FROM raod_segments),--convert segments down to start and end points
     clusters  AS (SELECT unnest(ST_ClusterWithin(geom, 1e-8)) AS geom 
                   FROM endpoints), --Unsplit/merge adjacent roads
     clusters_with_ids AS (SELECT row_number() OVER () AS cid, 
                                  ST_CollectionHomogenize(geom) AS geom 
                           FROM clusters) --add parent IDs to merged segements
    ST_Collect(raod_segments.shape) AS unsplit_geom,  ---create merged geometry as a collection of segments
    string_agg(raod_segments.objectid::text, ' | ' 
        --order by ST_YMin(ST_StartPoint(shape)) – HOW TO ORDER BY ADJACENCY STARTING FROM ONE END
        ) as child_segment_objectid,   -- list all child segments that make up the parent road
    COUNT(fullname) as split_count  
FROM raod_segments 
LEFT JOIN clusters_with_ids ON ST_Intersects(raod_segments.shape, clusters_with_ids.geom)
GROUP BY cid, fullname  --group the segments by parent id and unique street name.

Background: These are road segments that are missing address ranges. I am exploring methods to auto-populate theoretical address ranges for each segment in order to support geocoding by interpolation. What terms should I be googling? Pitfalls associated the proposed process.

  • Is there a chance that those multilinestrings can actually be merged into a linestring (since you say one end to the other)? Because I think this is not going to work much simpler if you have to work with multilinestrings; ordering within multigeoms is one hell of a tricky thing to automate since, for a multiline with branches or gaps, there is no way to determine where to start, where to end or where to go first when reaching an intersection or a gap between segments, except for a crude geographical localization like that 'ST_YMin' you mentioned. – ThingumaBob Oct 20 '17 at 12:56
  • Assume there are no self intersecting roads, branches, braids, gaps, etc. – Alison Wieckowicz Oct 25 '17 at 16:16

Your Answer

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

Browse other questions tagged or ask your own question.