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.
WITH 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 SELECT 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 fullname, 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.