2
1

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

asked 23 Apr '13, 11:58

horstiborsti1337's gravatar image

horstiborsti...
41123
accept rate: 0%

Hey, thanks for your answers so far. I played a bit around with the geometry column of "Baden-Württemberg" (Relation ID 62611) and as I already said, there are two "empty" geometry columns. However in fact they are not really empty. I tried the following statement:

SELECT osm_id, way, ST_AsText(way), ST_IsValid(way), ST_IsEmpty(way)
   FROM planet_osm_polygon
      WHERE osm_id = -62611;

The result looked like

alt text

Is it possible to have a geometry column which is not empty but valid and has no WKT or WKB in the colums?

Thanks Thorsten

(25 Apr '13, 09:28) horstiborsti...
1

Hey Thorsten, This site is more an FAQ site rather than a discussing forum for deeper database issues.

Come to the german speaking subforum at http://forum.osm.org ...

(26 Apr '13, 13:43) stephan75

Have you managed to achieve your goal since then? if so, may you share your experience, please.

(01 Jul '17, 23:19) meglio

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

SimonPoole ♦
40.0k13297634
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 ♦

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:

×150
×126
×74
×34

question asked: 23 Apr '13, 11:58

question was seen: 7,069 times

last updated: 01 Jul '17, 23:19

powered by OSQA