Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 please?

Thank you in advance,

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted
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 Open Street Map 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 Open Street Map data is called way, it might be different for you depending on the tool you used to import the data.

share|improve this answer
    
Thank you. ST_Intersects seems to be a good candidate, since ST_Contains perhaps gives all segments consisting of a street (say 1 segment = 1 block). However I get 0 rows. –  Martin0x777 Jun 18 '13 at 1:00
    
My bad... osm_places is a point layer. –  Martin0x777 Jun 19 '13 at 0:00
1  
ST_Intersects will return each segment as well. If you're just looking for a list of names change your select to SELECT DISTINCT r.osm_name. Change osm_name to whatever your name field is. –  HeyOverThere Jun 19 '13 at 12:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.