This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

postgres gis get the full road nodes query

I have OSM data using osm2pgsql (slim) with default style in PostgreSQL GIS DB. For my usage I'm trying to predict where the customer will go according to his current position on some way node.

In order to do so I want to get the all way (road) data by its current partial way.

I know how to do it by overpass using recursion up but can't find how to do it with SQL query (actually I done something but not so good by getting the way ref and then look for more ways with the same ref)

Tn'x for any help.

query gis postgres road

asked 30 May '18, 11:22

altopalo
53●8●10●13
accept rate: 0%

edited 30 May '18, 19:04

The answer is probably something with the relations between the data objects in the DB. So is there a good explanations of the data schema & relations of the osm2postgress schema? (can't find a decent documentation on that)

(31 May '18, 10:42) altopalo

One Answer:

I found the solution, so is someone will search for it.

Need to use planet_osm_rels table

Sample:

select osm_id, ref, name, way 
FROM planet_osm_roads where ST_DWithin(ST_Transform(ST_SetSrid(ST_Point(-1.021076,51.714512), 4326), 3857), way,20000) 
and osm_id in( 
    SELECT distinct(unnest(parts)) as part 
    FROM planet_osm_rels 
    WHERE parts @> ARRAY[147856382]::bigint[] and tags @> ARRAY['M40']::text[]
)

answered 13 Dec '18, 12:53

altopalo
53●8●10●13
accept rate: 0%

edited 13 Dec '18, 12:55