postgres gis get the full road nodes query

Question

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.

Answer 1

0

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[]
)

permanent link

answered 13 Dec '18, 12:53

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

edited 13 Dec '18, 12:55

postgres gis get the full road nodes query

Follow this question

Related questions