Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

To moderators: If you think this question is too broad, please point me into right direction before closing, I'm desperately looking for help.

I'm stuck on my task and I don't know what to do. The job is:

  • extract data from Open Street Map
  • create database model containing following schema and data

Schema looks like this:

+---------+     +------------+      +----------+
| country |     | adm_unit   |      | city     |
+---------+     +------------+      +----------+
| id      |--\  | id         |--\   | id       |
| name    |   \-| country_id |   \--| adm_id   |
+---------+     | name       |      | name     |
                +------------+      | coord_x  |  //coordinates - longitude and latitude
                                    | coord_y  |
                                    +----------+

So far I managed to:

  • download map itself (only for Poland to test it)
  • import it to database using osm2pgsql tool
  • perform some selects and realize, I don't know how to do it.

The main problem is: I can't see any relations between different items of map. Taking Poland as example: I can find Warsaw (capitol city), as well as "masovian vovoidenship" performing few selects, but there is no relation between these two. There are lot of empty columns in my db instead. Also none of column names suggests that it should hold any relation to "parent".

I don't know how to find those relations. Is it something with imports that i'm missing? Should I change anything in my default.style (I haven't changed anything inside it)? Or maybe there's some kind of other approach to solve my problem? Maybe there are free databases that contain data I need and are less painful to work with? Geonames is no-go, their database is full of errors.

I'm really green when it comes to gis, I'm PHP programmer, that's completely different world for me. Unfortunately, even if I want to explore it, my deadlines won't allow that.

share|improve this question

migrated from stackoverflow.com Mar 3 at 18:23

This question came from our site for professional and enthusiast programmers.

    
i would ask on the well supported openstreet map forums. –  Dagon Feb 27 at 21:07
    
I downloaded my map from here: download.geofabrik.de/europe/poland.html . I think some of data is broken - I installed gui tool qgis and, after importing borders of Poland, I see only half of it. I asked my question on OSM forums. I will also check what kguest posted. Thanks! –  ex3v Feb 28 at 9:49

3 Answers 3

OpenStreetMap uses boundary relations for defining borders for areas/territories/jurisdictions and similar administrative things. So you have to look for relations tagged with boundary=administrative and certain admin_level=* values.

share|improve this answer

You may possibly want to look into querying against Nominatim.

https://github.com/kenguest/Services_Openstreetmap - a php based library for interacting with both the main OSM api and Nominatim api also might be of interest to you; specifically the getPlace method. (disclaimer/disclosure: I'm the author.)

As a very quick proof of concept, you could model what you need on something like the following:

<?php
require_once 'Services/OpenStreetMap.php';
$osm = new Services_OpenStreetMap();
$test = $osm->getPlace('Birdhill');
$attribs = $test[0]->attributes();
$display = (string) $attribs['display_name'];
$coords = (string) $attribs['lat'] . ", " . (string) $attribs['lon'];
var_dump(compact('display', 'coords'));
$test = $osm->getPlace('County Tipperary, Ireland');
$attribs = $test[0]->attributes();
$display = (string) $attribs['display_name'];
$coords = (string) $attribs['lat'] . ", " . (string) $attribs['lon'];
var_dump(compact('display', 'coords'));
$test = $osm->getPlace('Ireland');
$attribs = $test[0]->attributes();
$display = (string) $attribs['display_name'];
$coords = (string) $attribs['lat'] . ", " . (string) $attribs['lon'];
var_dump(compact('display', 'coords'));

As an aside, if you do find any bugs/missing functionality, please let me know some contact details are on http://about.me/kenguest or file them at http://pear.php.net/package/Services_OpenStreetMap

share|improve this answer
    
I have to admit, this is the first solution I have that works, but I still can't figure few things - how to select all countries? How to select all top level administrative units for each country? Thanks in advance. –  ex3v Feb 28 at 10:35
    
You might also want to look into using OverPass queries - wiki.openstreetmap.org/wiki/Overpass_API –  kguest Feb 28 at 10:42
up vote 0 down vote accepted

Found the solution.

Taking Poland as example: First I had to find vovoidenships. Some of vovoidenships were saved as closed polygons in planet_osm_polygon, some as set of records (lines) in planet_osm_line.

In case of polygon, thing was pretty simple, it was all about proper query:

INSERT INTO 
    out.city 
        (region_id, city_name, coordinates, place_type)
    (
        select 
            15,
            name, 
            ST_AsLatLonText(ST_Transform(way, 4326)), 
            place 
        FROM 
            planet_osm_point 
        WHERE 
            place IN ('city', 'town', 'village', 'hamlet')
            AND ST_Contains((SELECT way FROM planet_osm_polygon WHERE osm_id = -130919), way)
            AND name != ''
            AND name IS NOT NULL

    )

Things got worse with second case (set of linestring's). Most of them were unclosed. I struggled for hours trying to find working solution to join the lines, transform to shapes and search inside them. I gave up and got this brilliant idea - what about repairing this myself using some sort of GUI? Things ended up in

  • installing quantum gis,
  • drawing missing lines,
  • merging them together,
  • saving back to database (which qgis handles very well).

After that All I had was to perform something like this:

INSERT INTO 
    out.city 
        (region_id, city_name, coordinates, place_type)
    (
        select 
            17,
            name, 
            ST_AsLatLonText(ST_Transform(way, 4326)), 
            place 
        FROM 
            planet_osm_point 
        WHERE 
            place IN ('city', 'town', 'village', 'hamlet')
            AND ST_Contains((SELECT St_GeomFromText(St_AsText(St_MakePolygon(St_AsText(
            (
                SELECT 
                    string_agg(ST_AsText(way), '\n') 
                FROM
                    planet_osm_roads
                WHERE
                    osm_id = -223408
                )


            ))), 900913)), way)
            AND name != ''
            AND name IS NOT NULL
            ORDER BY name

    )

Worked like a charm! I hope this helps someone in the future. Thank you all for your help anyway :)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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