From two layers in PostGIS (osm_places and osm_roads), I would need to have the roads belonging to a certain city.
It would be fair enough to get the street names of all streets in a radius of X kilometers from the coordinates of the point of osm_places.
The query I'm looking for is something like
SELECT OSM_ID FROM OSM_ROADS INNER JOIN OSM_PLACES WHERE DISTANCE_OF(LOCATION(OSM_ROADS), LOCATION(OSM_PLACES)) <= X AND OSM_PLACES.NAME = 'My City'
Can somebody point me in a direction?
SELECT r.osm_id
FROM osm_roads r, osm_places p
WHERE p.name = 'Hometown' AND ST_Intersects(p.way, r.way);
If you want only streets that are completely contained within the places polygon replace ST_Intersects with ST_Contains. Order matters to ST_Contains, it returns all geometries from the 2nd layer that are completely contained by the 1st.
A third way to get your answer is with ST_DWithin. It will return true if the two geometries are within a specified distance of each other, the distance will be in the units of the coordinate system the two layers use. Since most of the time OpenStreetMap data is imported in the "Google Mercator" projection (for me anyway) most likely your units will be meters.
Also, for me, the geometry field in my OpenStreetMap data is called way, it might be different for you depending on the tool you used to import the data.
