I'm kinda new to Django and OSM but after hours of googling I didn't find my answer. I have set up my own OSM tileserver which runs on a postgis database (psql). The tile server runs fine. Now I want to do the following. I want to add markers in the center of each street in a specific area. I want add all these markers in the database on forehand, because the data is already there (nodes,lines,way etc.) and I want to give streets additional values. This means that i have to add all the streets to my database. In my head these tables must at least contain: Name of street, City, Country, Lon, lat (of the center of the street) This way I can call-up the markers and values from the database. Is this the right way of thinking, and how do I get this street data from my postgis db to this new table with the right relations and coordinates? I hope someone could help me. asked 16 Feb '14, 08:26 Lengo |
There are several issues that make this difficult. One is that the database you are using does not have the concept of "a street" - it just has "ways" as they were mapped in OSM. You can easily create a database of all ways and their centre point but where a street consists of five ways in OSM, your database will then have five points. You'd create your basic table like this:
The The other non-triviality is that you would like to have city and country information which means you would have to run additional queries that use st_contains or similar functions to check which of the relevant boundary polygons from planet_osm_polygon actually contains your street, for example:
Similar coding can be added for cities. These queries can run for a long time, and it is a good idea to first run them on a subset (e.g. use a WHERE clause to only run them on every 100th my_new_table object or so) to get an idea of how long they take. Adding a specialized index to planet_osm_polygon can help, e.g.
Note that it is possible for streets to cross country or city borders but if you're just working on the centre point then the results should be clear. Updating this mapping (other than simply re-running the queries) could be achieved by adding some kind of dirty flag to the boundaries when they change, and re-run the matching only for those that have changed. But if you implement all that then you already have half a Nominatim installation, and it may be easier for you to look into how Nominatim solves these problems and base your solution on that. answered 18 Feb '14, 19:11 Frederik Ramm ♦ And what to do if I just want ALL road and street name list with ANY lon/lat point on it??? Could you please advice on the query?
(11 Jun '14, 23:24)
Gevork
|