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's gravatar image

Lengo
6111
accept rate: 0%


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:

SELECT name, highway, st_line_interpolate_point(way, 0.5) as geom
INTO my_new_table
FROM planet_osm_line
WHERE highway is not null;

The st_interpolate_point gives you a point halfway along the street which is what I suspect you want when you say "center of the street".

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:

ALTER TABLE my_new_table 
ADD COLUMN country VARCHAR(64);
UPDATE my_new_table 
SET country = (
   SELECT name from planet_osm_polygon 
   WHERE boundary='administrative'
   AND admin_level='2'
   AND st_contains(planet_osm_polygon.way, my_new_table.geom)
);

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.

CREATE INDEX my_country_index 
ON planet_osm_polygon 
USING gist(way) 
WHERE boundary='administrative'
AND admin_level='2';

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.

permanent link

answered 18 Feb '14, 19:11

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
70.9k836431106
accept rate: 24%

edited 23 Feb '14, 18:57

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×271
×146
×121
×113
×1

question asked: 16 Feb '14, 08:26

question was seen: 4,353 times

last updated: 11 Jun '14, 23:24

powered by OSQA