Get all streets of a city - Proximity search using mySQL

Hi there,

I’m pretty new to OSM and I’m just playing around with a dataset of my region generated by JOSM. I moved it into a mySQL DB with the 0.6 API scheme using Osmosis and now I’m desperately trying the following:

I want to get all streets of a city.
AFAIK there is no tag/relation to determine this, so I tried it using a proximity search to get all nodes in a radius around a node representing the city center. Most of the time I looked at the approaches here:

What I got is the following SQL code that should get me the closest 100 nodes around the node with id 36187002 and within a radius of 10km.

set @nodeid = 36187002;
set @dist = 10;
select longitude, latitude into @mylon, @mylat from nodes where id=@nodeid limit 1;

SELECT id, ( 6371 * acos( cos( radians(@mylon) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(@mylat) ) + sin( radians(@mylon) ) * sin( radians( latitude ) ) ) ) AS distance
FROM nodes HAVING distance < @dist ORDER BY distance LIMIT 0 , 100;

Well… it doesn’t work. :frowning: I guess the main problem is that OSM lats/lons are multiplied by 10.000.000 and I don’t know how I can correct this function to make it work.

Any ideas about this? All solutions/alternatives are very welcome!

Have a nice weekend,


btw: I also sent this issue to the mailing list, but it seems like it didn’t get submitted (yet).

Why are you not using PostGIS? It’s ideally suited to these kinds of spatial queries.

Thanks for your reply,

I have no experience with spatial queries, but I’ll give it a try.
Is there a good ressource/example that handles this kind of problems regarding OSM data?



The first thing you’ll have to do, is actually get your OSM data into PostGIS. Depending on what you want to be able to do with the data, there are a few possibilities.

  1. osm2pgsql. This sets up a PostGIS db that is primarily meant as a source for map rendering with mapnik. However, if you want to be able to find out all streets within a city, that’s also possible. Provided the city boundary is in OSM. If it’s not, you could still try your method (“all streets within x distance from a point”), but it’ll be much easier, and probably a lot faster.

  2. osmosis. Osmosis is the Swiss Army Knife of OSM data, and one of the applications is loading OSM data into mysql or postgresql. There are various parameters you can use for the postgresql import, again depending on what you want to do with the data. The default imports the data as you already have in mysql: nodes, nodes in ways, relations, tags. With further options, you can have osmosis build actual geometry objects in the db, which you can again use for your queries.

For both of these tools, it’s heavily recommended not to rely on packaged versions, since these are almost often too old. You can find both of these in the OSM SVN repository.

Edit: ah, you already used osmosis, but with mysql, not postgresql.

Hi LdP and thanks again! Feels like I’m finally going forward. :slight_smile:

Ok, I didn’t get osm2pgsql compiled on my mac,so I decided to keep going with osmosis and did the following:

  • installed postgre + postGIS
  • created a new DB using the “template_postgis” template
  • executed the pgsql_simple_schema_0.6, pgsql_simple_schema_0.6_linestring and pgsql_simple_schema_0.6_bbox SQL files on the DB
  • imported the OSM data using osmosis with the following options:

sh bin/osmosis --read-xml-0.6 file="../mytown.osm" --write-pgsql host="localhost" database="test_osm" user="myuser" password="mypassword" validateSchemaVersion=no enableBboxBuilder=yes enableLinestringBuilder=yes

@LdP: I think this was what you meant with your advise?

As far as I can see, everything worked out fine. So how can I now take advantage of the PostGIS interface to solve my inital problem (finding all streets of a city)?

I’m pretty new to GIS related DBs/calculation/etc, so it would be great if you could provide me some resources or examples how to handle this problem, esp. regarding PostGIS and OSM specific solutions.



My PostGIS “bible” :

It takes a bit of reading, but queries you can do with postGIS go far ahead from what you can achieve with mysql calculations.

It worth it :wink:

Ok, so this is my first try getting all nodes within a distance of 100 meters(?).

SELECT *, ST_AsText(geom), "public".distance(geom, GeomFromText('POINT(8.91103 49.936981)', 4326)) FROM nodes
WHERE ST_DWithin(geom, GeomFromText('POINT(8.91103 49.936981)', 4326), 100)
ORDER BY ST_Distance(geom, GeomFromText('POINT(8.91103 49.936981)', 4326))

Seems more right (more or less) - I’m happy for any kind of feedback. :slight_smile:

This looks good, except your “distance” won’t be 100 meters

ST_DWithin documentation : “The distance is specified in units defined by the spatial reference system of the geometries.”

In other words, you are performing a search of nodes within 100° of “distance” of your reference point

For those type of real distance computation, using a projected coordinates database might be a better choice. St_transform can help you to convert lat/long either “on the fly” (but it’s more CPU intensive) or to convert all your geometries to a better suited projection.

So is this even the right approach?

I’m curious about how to solve this problem: “Find all streets of a city”

  • Should I go with the more estimating option to make just a proximity search on the way nodes around a city center?
  • Or should I try to get the city limits of the OSM data (how can this be done?).

I’m especially interested in how the advised solutions can be realised. Is this possible with Osmosis?
How much can I rely on OSM data, that every city has a correct boundary? And how do I generate the polygons of the single ways?

@sly: Regarding the St_transform function in PostGIS:
I didn’t see an osmosis option for manipulating this.
As far as I can see (in the default osmosis SQL schemes) all OSM geo data is saved with the SRID 4326.
So to change this I guess I only have to change the scheme to use another SRID. But which one?
In another PostGIS tutorial they use 31467 (Gauss-Krueger Projection) - is this a possible option?

Sorry to pester you guys with so much questions, but I’m doing this for a study project and I really want to get this. :slight_smile:



I’m not well versed in the osmosis schema, but usually use the osm2pgsql schema with data in 900913.

When I want to find all streets within a city, I first find out if the city’s administrative boundary is known in OSM. If it is, I use that polygon[1] and use an ST_Intersects of that and the roads in the db.

[1] With the osmosis schema, I guess you’ll have to build that polygon from the administrative boundary relation, using something like ST_BuildArea on the relation members.

I’ll say that what you will get is just an approximation. Because not every city is of the same size :wink:
You tried 100 meters which might be plain good for a small village, but that will certainly fail for bigger city.

This is not going to be easy :wink: in the actual state of the database

You might be ending doing both unfortunetly.
The “problem” is that not all cities in osm have been tagged with their limits (well, this is rather rare in fact) and this varies a lot in methods between countries (some use the tag place on the city extents, some use a boundary=administrative around the city, but it might be much bigger that the actual city)

The proximity method, yes
osm2pgsql might be more suited for the “inside polygon” method as it will construct for you the POLYGON made of one or several ways

not too much :wink:

osm2pgsql is a good tool for that

You can do this after osmosis’s job


UPDATE set =st_transform(,2154)

2154 is the SRID of Lambert 93 wich I use in France and st_distance( ) will return meters

Unless I’m wrong (of course :wink: ) there are currently no way to ask postGIS a distance in meters wherever on the globe the coordinates are, you have somehow to semi-manually choose the projection.
I’ve heard that postgis 1.5 will/does provide a convenient way to automatically do that even on long distance (something called the geography type) but I haven’t played around with it

Some readings :–td16150222.html

Ok, I decided to go with the proximity search first.

I digged a little bit into the PostGIS wiki and I think the ST_distance_sphere function can be used for this without the need of modifying the SRID. Or am I getting something wrong here?

This is my current statement where I try to get all ways that are roads near a point with a max distance of 2000m:

ST_AsText(nodes.geom), --remove later for unique
ST_distance_sphere(nodes.geom, GeomFromText('POINT(8.88314 49.94497)', 4326))  as distance
FROM nodes, way_nodes, way_tags
    ST_distance_sphere(nodes.geom, GeomFromText('POINT(8.88314 49.94497)', 4326)) < 2000
    AND = way_nodes.node_id
    way_tags.way_id = way_nodes.way_id
    way_tags.k = 'highway'
ORDER BY ST_distance_sphere(nodes.geom, GeomFromText('POINT(8.88314 49.94497)', 4326))
LIMIT 1000

It looks like it works, but it’s really not the best option. :frowning:

I also have the linestrings and bounding boxes of the ways in my DB and I guess looking for them would be much faster then going through all the single nodes.

The problem is I really don’t understand how to modify/handle the SRID to query for linestrings in a special distance (in meters). I found this function here:

But to be honest, I still don’t have a clue how I can get this working using the linestrings of the ways…