3

I have lines from an OpenStreetMap database, which I selected like this:

SELECT sq2.osm_id, sq2.type, sq2.simple_geom, sq2.z_order FROM (
    SELECT sq1.osm_id, sq1.type, sq1.z_order, ST_UnaryUnion(sq1.simple_geom) as simple_geom FROM (
        SELECT osm_id, type, z_order, ST_SimplifyPreserveTopology(geometry, 50.0) as simple_geom FROM osm_roads_gen0 WHERE "type" = 'primary') as sq1 
    GROUP BY osm_id, type, simple_geom, z_order) as sq2
    WHERE ST_Length2D(sq2.simple_geom) > 150.0
GROUP BY osm_id, type, simple_geom, z_order;

This gives me the result below - lines where I have a bunch of lines, but the highway departures are discarded (lines below 150m, I don't need them). Now what I wanted to do is to "stitch" these lines (by their ref). Meaning I want to either modify their endpoints or insert a line between and use ST_LineMerge.

Lines OpenStreetmap need stitching

How can I do this using PostGIS? Something like ST_Merge_Endpoints(ST_DWithin(geometry[i], geometry[i + 1], 150.0))

The second problem would be to move the line ending points (after the merge) to the nearest line (to "reconstruct" the simplified highway departures). I can get the ST_Endpoint, but then what? How can I move the endpoint to the nearest line?

  • Why do you need to eliminate the highway departures? What does your network image look like with them in place? It may be easier to be more selective about which highway departures you eliminate, rather than trying to reconstruct them afterwards. – amball Jul 26 '17 at 18:39
  • Well, it's OpenStreetMap data. The highways are simply inconsistently mapped. My goal was to seperate the junctions from the roads. For smaller scales, it doesn't make sense to have junctions at all, because you can't even see them. Yes, I tried things like ST_simplifypreservetopology, but it creates a horrible, triangulated mess instead of removing the lines. My current plan is to do it via a program, not SQL, calculating the midpoint of junctions via the centroid of the road connections. Then merge all lines within a radius to that center point. – BiggCrazy Jul 26 '17 at 19:15
  • What does your network image look like with the highway departures in place? – amball Jul 26 '17 at 22:02
  • Like this - other area but you get the idea. The lines are classified, however they often have breaks in the classification, it's not consistent. My goal is to seperate the junctions from the rest of the network and basically merge the junctions to a single point. – BiggCrazy Jul 27 '17 at 6:45
2

If you care about the network properties, then you'll need to use a routing solution. For example, rather than dropping the highway departure segments, you could iterate over all edges below a certain length, and drop the edge if pgrouting gives you an alternative route from the start node to the end node of the edge, within a certain tolerance. This is likely to be very slow.

If you don't care about the network properties, a geometric solution will be faster. Note that this may introduce network analysis problems, for example through creating new nodes where a road crosses over another road with no access between the two. Try the following query to connect your isolated line segments:

With a view set up like this:

CREATE TEMP VIEW streets AS 
SELECT sq2.osm_id as id, sq2.type, sq2.simple_geom as geom, sq2.z_order FROM (
    SELECT sq1.osm_id, sq1.type, sq1.z_order, ST_UnaryUnion(sq1.simple_geom) as simple_geom FROM (
        SELECT osm_id, type, z_order, ST_SimplifyPreserveTopology(geometry, 50.0) as simple_geom FROM osm_roads_gen0 WHERE "type" = 'primary') as sq1 
    GROUP BY osm_id, type, simple_geom, z_order) as sq2
    WHERE ST_Length2D(sq2.simple_geom) > 150.0
GROUP BY osm_id, type, simple_geom, z_order;

Use this query:

WITH ec as (SELECT s1.id, COUNT(s2.id) AS n 
    FROM streets s1, streets s2 
    WHERE ST_Intersects(ST_Endpoint(s1.geom),s2.geom) 
          AND s1.id != s2.id
    GROUP BY s1.id),
sc as (SELECT s1.id, COUNT(s2.id) AS n 
    FROM streets s1, streets s2 WHERE
    ST_Intersects(ST_Startpoint(s1.geom),s2.geom) 
          AND s1.id != s2.id
    GROUP BY s1.id),
deadends AS (SELECT id, ST_Startpoint(geom) geom 
    FROM streets LEFT join sc USING (id) WHERE sc.id is Null
  UNION  
    SELECT id, ST_Endpoint(geom) geom 
    FROM streets LEFT join ec USING (id) WHERE ec.id is Null)
SELECT ST_MakeLine(d1.geom, d2.geom) FROM deadends d1, deadends d2
   WHERE ST_DWithin(d1.geom,d2.geom,10) AND d1.id>d2.id

The first and second WITH queries return the counts of streets that intersect a line's endpoint and startpoint respectively. The third WITH query inverts this to return the geometries of the deadend points (i.e. streets that are not in the first or second queries).

The final query makes a line by joining up the deadend points within a certain radius. You'll have to adapt this to avoid multiple matches. The best thing to do is probably to take the first match within a search radius, but this will be the complicated part especially if you have sets of 3 points that you need to connect.

|improve this answer|||||
3

I think, the best way to do what you want is to use postgis_topology. You can apply this method: Use postgis topologies to clean up road networks

|improve this answer|||||
  • This does not work. PostGIS throws exceptions if the edges are overlapping. – BiggCrazy Jul 26 '17 at 11:22
  • More specifically: SQL/MM Spatial exception - geometry crosses edge 5632 CONTEXT: PL/pgSQL function totopogeom(geometry,topogeometry,double precision) line 111 at FOR over SELECT rows PL/pgSQL function totopogeom(geometry,character varying,integer,double precision) line 89 at assignment – BiggCrazy Jul 26 '17 at 11:25
  • I don't know what to make from this, it doesn't help. The article in question only concerns itself with already connected lines, not lines that have to be stitched. – BiggCrazy Jul 26 '17 at 11:27

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.