Re: [OSM-dev] PostGIS query "Crossing ways"

2016-09-12 Thread Paul Norman

On 9/12/2016 9:56 AM, Mike N wrote:
I did use the --slim option when importing, and see the node table, 
but I don't see topology in a direct table view.


The ways table has node membership information. Joining the table on 
w1.nodes && w2.nodes and differing way IDs will find ways that share 
nodes. You'll need to define your requirements more precisely than this 
and use something more sophisticated, but this should get you started.


  If I use something like the extension  postgis_topology , would I 
still need to write a tool to populate from OSM data? 


I'm not aware of any tools which import using postgis topology. You 
might be able to do what you want with pgrouting and osm2pgrouting, but 
I haven't used it myself.


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


Re: [OSM-dev] PostGIS query "Crossing ways"

2016-09-12 Thread Mike N

On 9/11/2016 3:52 PM, Paul Norman wrote:

PostGIS databases do not have topology so there is no notion of
connected linestrings. You can tell if two ways cross each other with
ST_Intersects, and you can tell if two share points by turning the
linestrings into points, but this doesn't tell you if the two share
nodes. If you want that you need either the osm2pgsql slim tables,
pgsnapshot, or some other schema with node information or topology.


I did use the --slim option when importing, and see the node table, but 
I don't see topology in a direct table view.


  If I use something like the extension  postgis_topology , would I 
still need to write a tool to populate from OSM data?



___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


Re: [OSM-dev] PostGIS query "Crossing ways"

2016-09-12 Thread Tomas Straupis
2016-09-11 18:20 GMT+03:00 Mike N :
> 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)

  The closest I could get to this was by splitting way
(planet_osm_lines) geometry into their segments. I was creating a new
table say „segments“ with identical attributes as planet_osm_lines in
which the same osm_id would be repeated as many times as many segments
that way has. (You could also do it without actually creating a new
table and letting postgresql process everything on the fly).

  If you have a way with three nodes, you would get two new lines (two
line geometries, two records in the new table).

  Then you can use PostGIS functions to find intersecting ways. But
you will not find intersection problems where two ways cross each
other on the same x/y, but they have two different points in OSM (two
different points with the same position one for way1, another for
way2).

-- 
Tomas

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


Re: [OSM-dev] PostGIS query "Crossing ways"

2016-09-11 Thread Ian Dees
On Sun, Sep 11, 2016 at 3:54 PM Paul Norman  wrote:

> On 9/11/2016 8:20 AM, Mike N wrote:
> > 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)
>

This isn't really an answer to your exact question, but I recently used
Mapbox's OSM QA Tiles + Tile Reduce to find all intersections between
residential/tertiary/primary/trunk/etc. roads. The code is here and can
probably be adapted to what you're looking for pretty easily:

https://github.com/iandees/on-every-corner
___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


Re: [OSM-dev] PostGIS query "Crossing ways"

2016-09-11 Thread Paul Norman

On 9/11/2016 8:20 AM, Mike N wrote:
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()? 


PostGIS databases do not have topology so there is no notion of 
connected linestrings. You can tell if two ways cross each other with 
ST_Intersects, and you can tell if two share points by turning the 
linestrings into points, but this doesn't tell you if the two share 
nodes. If you want that you need either the osm2pgsql slim tables, 
pgsnapshot, or some other schema with node information or topology.


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


Re: [OSM-dev] PostGIS query "Crossing ways"

2016-09-11 Thread Stefan Keller
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 :
> 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@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


[OSM-dev] PostGIS query "Crossing ways"

2016-09-11 Thread Mike N
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@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev