How can I get geometry of administrative boundaries from OSM data stored in postgreSQL/PostGIS database

Question

Hi, I have a PostGIS database which I filled with OSM data via osm2pgsql-Script. Now I have the tables planet_osm_nodes, planet_osm_line and so on. The geometry-data is stored in the way-column. Everything fine.

Is there a tutorial or something on how I can retrieve the geometry-data of a specific administrative boundary, like the national boundary of Germany or admin_level = 4 boundaries in the PostGIS database. I don't want to download them on a website, i need the data from the database, that I can do some quality analysis on this data and compare it to other geometry data.

When I try the following SQL-statement to get the geometry of a boundary-relation inside germany (relation id = 62611)

SELECT osm_id, way
  FROM planet_osm_polygon
    where osm_id = -62611

and view it in a GIS software or in C# (SharpMap), then it's not the boundary i expected. It's only some kind of rectangle and not the boundary of "Baden-Württemberg". Is that because I do something wrong or is my data incomplete?

Is there a proper way to get geometry data from PostGIS?

Thank you so far.

Thorsten

Answer 1

0

In principle what you are doing seems to be right. You should realize that in in the general case you will get multiple polygons back from your query (currently in the case of BW 4) that together represent the complete multi-polygon for the boundary.

You can generate a single object by for example doing:

select ST_Multi(ST_Collect(way)) from planet_osm_polygon where osm_id=-62611;

or on import request osm2pgsql to generate multi* geometries.

permanent link

answered 23 Apr '13, 12:18

SimonPoole ♦
40.0k●13●297●634
accept rate: 19%

Hi, thank you for the fast answer. I tried your statement on several geometries but the returned geometry was always NULL. Two of the four polygons for BW are NULL. When I try to suppress those emtpy polygons with

SELECT way
   FROM planet_osm_polygon
     WHERE osm_id = -62611 AND way IS NOT NULL

i still get all four lines.

Another statement was

 SELECT st_multi(st_collect(way)) 
    FROM planet_osm_roads
       WHERE admin_level = '2' AND "name:en" LIKE '%Germany%' 
           AND osm_id > 0;

but the return value for way column is again NULL

(23 Apr '13, 12:39) horstiborsti...

There are a number of possible issues, you may have invalid geometeries (which is not the same as the column being null) or empty geometries, you can for example test for valid geometries with ST_IsValid. Maybe something did go wrong with your import, I'm not aware of osm2pgsql generating NULL values for geometries.

(23 Apr '13, 12:48) SimonPoole ♦

How can I get geometry of administrative boundaries from OSM data stored in postgreSQL/PostGIS database

Related questions