2

The use case is simple: extract all the buildings from OSM via osm2pgsql.

In the process, it seems other entities make it into the polygons list, such as "islands", only because they have the "building=NULL" tag available, such as:

ireland

I am reading this documentation: http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema

This table contains all polygons (closed ways) which were imported. For polygons which are members in one or more relations, multiple rows will be created: one with the polygon's own ID and tags, and one more for each relation membership. These additional rows contain the tags of the relation and the negative ID of the relation in the osm_id column (since a single relation may contain multiple polygons, negative IDs are not necessarily unique).


I've created a style file which allows me to extract all the building polygons, but it seems some areas, which have the tag 'building=NULL' are not actually buildings, but describing an island in the water, or the actual country itself (which also comes with the 'building=NULL' tag).

Would it be safe to simply discard all the polygons which have a negative ID?

At the moment, I collected all the OSM IDs of these "islands" and other elements tagged wrong, and I'm deleting them via PGSQL:

delete from planet_osm_polygon where osm_id in (-6040654, -6045552, -6045364, -6188599, -5763894 );

Is there a best practice in relation to this, should I go on the map and feed these fixes in, or is there a way to avoid this type of query where I want to look at building polygons without getting these "odd" entities?

  • There is no building=NULL tag. I guess this just means that there is no building tag present at all. – scai Jun 30 '16 at 14:27
  • Hi, as you can see in QGIS, that particular polygon defining the "island", has the following tags: building, office and shop, all with the value NULL, so this is not the case. – Mark Allen Jun 30 '16 at 14:30
  • No it doesn't. NULL is written in italics and thus just means something like "tag not available". – scai Jul 1 '16 at 6:27
2

The planet_osm_* tables are not normalized. You must create a table (or view) to store the data wich makes sense to your application.

Example:

DROP TABLE IF EXISTS "building";

CREATE TABLE "building" AS ( 
  SELECT way,building,aeroway
  FROM planet_osm_polygon
  WHERE ("building" IS NOT NULL AND "building" != 'no')
  OR "aeroway" IN ('terminal')
  ORDER BY z_order ASC 
);

CREATE INDEX "building_way_idx" ON "building" USING gist (way);

Other examples at https://github.com/boundlessgeo/OSM/blob/master/createDBobjects.sql

This will make easy to create some semantics to the data and to filter what you realy want (I prefer to create a table because indexes makes it faster).

I think all building=null is because that tuple is not concerning to buildings, but to some other information that fits into your style file selection and you've imported buildings too. So, the osm2pgsql created the building column because of the building tag in *.style file, but some other tag combination commands the importing of those lines with no building information. You must analyse the data lines to know what tag is causing this import.

You may look at the combination info page ( example: http://taginfo.openstreetmap.org/keys/amenity#combinations ) to know what tags is likely to combine with others to create a more normalized table ( or just follow my example and create a separated table by yourself ).

Avoid touching the planet_osm_* tables data directly.

  • many thanks for this, it's exactly what I was looking for! – Mark Allen Jun 30 '16 at 15:13
  • Glad to help you. In most cases I like to import all OSM data with the default.style and create separated tables. The import process is painful but I do it just once and have all data at my feet. – Magno C Jun 30 '16 at 15:19
  • I've updated the SQL example to be more close to your problem. – Magno C Jun 30 '16 at 16:07
  • There's a problem with the example, when I try to add the building table to the QGIS layers, I get this strange error: !error. Error text is <<The exact message is: Layer is not valid: The layer dbname='...' host=.... port=.... user='osmuser' password='...' sslmode=disable srid=.... type=Polygon table="public"."...table_name..." (way) sql= is not a valid layer and can not be added to the map.>> I looked up the problem, it seems there's a missing unique key in the new table, after adding it, still the same problem. Have you see this issue before? – Mark Allen Jun 30 '16 at 19:34
  • your example works, maybe it's something wrong that I'm doing. – Mark Allen Jul 1 '16 at 10:18
2

This is not a NULL tag, but simply an empty field, and the way QGIS displays it.

Negative osm_id have all polygons that are build from multipolygon relations, while polygons build from simple closed ways have their positive way osm_id.

See http://www.openstreetmap.org/relation/6188599 for the island you took as example. It has no building tag, neither have the members.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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