1

Im using OSM data as osm2pgsql-import (from extract.bbbike.org). And I want to draw all attractions ("tourism" = 'attraction').

The most of the attractions I found in the planet_osm_point table. But some are missing. I found them in the planet_osm_polygon table.

For example: A very important church and attraction is a building-shape in the polygon-table and is missing in the points table.

But I will draw ALL attractions as a point in a point-layer in my qgis. I’m sure that there is a elegant solution with postgis - but I’m very new to postgis and have no idea where to start.

To draw the points layer AND the polygon-centroids together is no solution for me.

2

Create a new table for attractions and collect features from both tables into it.

create table attractions as
select way as geometry, name 
from osm_point
where tourism='attraction';

insert into attractions
select ST_Centroid(way) as geometry, name
from osm_polygon
where tourism='attraction';

Once the new table is created it must be registered into the geometry_columns table so that programs like QGIS can easily find the table and get info about the projection and geometry type used in it. How to do this depends on the PostGIS version. For version 1.5 read this http://postgis.refractions.net/documentation/manual-1.5/ch04.html#Manual_Register_Spatial_Column. In PostGIS 2.x the procedure is quite different and documented in http://postgis.net/docs/manual-2.1/using_postgis_dbmanagement.html#Manual_Register_Spatial_Column.

2
  • What do you mean with " add a new line about the new table into the "geometry_columns" table." ?
    – MAP
    Jun 11 '14 at 9:08
  • I edited my answer. Spatial tables and views must be registered into a metadata table "geometry_columns" before programs like QGIS know that they contain spatial dala. I was remembering how the registering was done in PostGIS 1.5 but it has changed since that. Follow the links I added into my answer.
    – user30184
    Jun 11 '14 at 9:33
2

You can also use view to do this. There are few benefits of this solution:

  1. You're not duplicating data
  2. Your data is comming from tables created by osm2pgsql so it's always actual.

Pseudo-query:

Create or replace view attractions as
Select c1, c2, way from planet_osm_points where tourism='attraction'
Union
Select c1, c2, ST_Centroid(way) from planet_osm_polygon where tourism='attraction'

Selecting this view you'll get results from both tables

1
  • Thats a good solution for most cases but in my case, its good to have a table with all attractions (so I dont need the tables point and polygon anymore).
    – MAP
    Jun 11 '14 at 8:00

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.