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:
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. 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,
Chris
btw: I also sent this issue to the mailing list, but it seems like it didn’t get submitted (yet).
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.
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.
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.
@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.
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.
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.
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
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 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
osm2pgsql is a good tool for that
You can do this after osmosis’s job
example
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 ) 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
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:
SELECT DISTINCT
ST_AsText(nodes.geom), --remove later for unique
way_nodes.way_id,
ST_distance_sphere(nodes.geom, GeomFromText('POINT(8.88314 49.94497)', 4326)) as distance
FROM nodes, way_nodes, way_tags
WHERE
ST_distance_sphere(nodes.geom, GeomFromText('POINT(8.88314 49.94497)', 4326)) < 2000
AND
nodes.id = way_nodes.node_id
AND
way_tags.way_id = way_nodes.way_id
AND
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.
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.
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.
