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


Can somebody point me in a direction please?

Thank you in advance,

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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