[OSM-dev] PostGIS query "Crossing ways"

Stefan Keller sfkeller at gmail.com
Sun Sep 11 18:11:10 UTC 2016


Mike,

See below a solution which runs standalone in a PostGIS db. This SQL
looks little bit scary - but I'm open for simplifications :-). It
would have been much easier to calculate in a topological structure
like OSM given nodes and list of nodes (=ways)).

The main query comes after the remark. The SQL WITH statement is just
to generate some test lines. The ST_SnapToGrid parameter indicates a
snap buffer in CRS units (mostly meter).

:Stefan

-- Requirements: PostGIS 2 db.
-- Get a table of baselines (e.g. roads) and table with checklines
(e.g. footways) to test:
with
baselines(id, geom) as (
  select 1, ST_GeomFromText('LINESTRING(2 10, 10 10)')::GEOMETRY(LINESTRING)
  union
  select 2, ST_GeomFromText('LINESTRING(2 5, 5 5, 10 5)')
),
checklines(id, geom) as (
  select 11, ST_GeomFromText('LINESTRING(5 8, 5 5, 5 2)')::GEOMETRY(LINESTRING)
  union
  select 12, ST_GeomFromText('LINESTRING(7 8, 7 2)')
  union
  select 13, ST_GeomFromText('LINESTRING(8 8, 9 1)')
)
-- Return all intersection points (e.g. cross roads) between baselines
and checklines
-- where checklines don't share a common point (node) with baselines.
-- Note the two occurrences of checklines and of the ST_SnapToGrid parameters.
select idc, idb, ST_AsText(geom)
from (
  select c.id idc, b.id idb, st_intersection(c.geom, b.geom) geom
  from baselines b
  inner join checklines c on ST_Intersects(c.geom, b.geom)
) intersections
where ST_SnapToGrid(intersections.geom,0.1) NOT IN (
  select ST_SnapToGrid((ST_DumpPoints(c.geom)).geom,0.1) from checklines c
  where c.id=intersections.idc
)
order by 1,2;

2016-09-11 17:20 GMT+02:00 Mike N <niceman at att.net>:
> Given a PostGIS database populated from OSM data by osm2pgsql, and 2 sets of
> lines  (such as the selection of all footways and the selection of all
> roads)   what function or series of functions will result in a list of
> locations where footways cross roads without any OSM connecting node?  (the
> equivalent of JOSM's "Crossing Ways" warning)
>
>   According to the PostGIS documentation, ST_Intersect() includes "Touches",
> which I assume would be sharing an OSM node.
>
>   ST_Crosses() might be what I want, but does it exclude the crossings that
> share a node?   If not, do I just remove all the ST_Intersect() results that
> are spatially close to ST_Crosses()?
>
>   Thanks,
>
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev



More information about the dev mailing list