Van Openstreetmap Nederland naar Postgresql/Postgis

Al eerder schreef ik een korte blogpost om van een OSM download te komen tot een Postgresql/Postgis database m.b.v. osm2pgsql. De laatste dagen ben ik verder gekomen met

  1. een Cmd file die een aantal conversies uitvoert en
  2. een PL/pgSQL script dat de nabewerking regelt.

Hieronder een korte uitleg.

Je wilt van de Openstreetmap data alles van Nederland hebben, … en iets ruimer zodat het kaartbeeld bij Vaals niet onmiddellijk wit wordt.

1 Voorbewerking en osm2pgsql
  • download van geofabrik de laatste PBF-versie van Europa.
  • download osm2pgsql en osmconvert.exe (even Googlen). Zet alles in één folder.
  • Open default.style in een tekstverwerker als Notepad++ en uncomment alle OSM tags die je als attributen wilt binnenhalen
  • Vervolgens laad ik een cmd file met de volgende regels:
d:
Cd D:\GIS software\osm2pgsql\x64
SET netBB="europe-latest.osm_01.pbf" 
osmconvert "europe-latest.osm.pbf" -b=1.902,50.662,7.465,53.665 --out-pbf -o=%netBB%

osm2pgsql -d "OSM-NetherlandsBB" -U postgres -C 6000 -s -S default.style -H localhost  -P 5432 %netBB%

shp2pgsql -I  -s 28992 -g way C:\Users\\Pim\Desktop\Nederlandgrenzen.shp public.grenzen | psql -d %netBB% -U postgres

pause

Zoiets als:

  1. navigeer naar de betreffende folder,
  2. snij met osmconvert en een boundingbox een stuk uit Europa, met daarin Nederland én iets daarbuiten en sla op in een nieuwe pbf.
  3. Met osm2pgsql wordt alles geconverteerd naar een postgresql database.
  4. Tenslotte wordt een shapefile van de Nederlandse grenzen geïmporteerd in dezelfde database. Iets wat ik nodig heb in fase 2.

Het hele proces – en dan met name de osm2pgsql conversie – duurt 24 uur, maar is natuurlijk afhankelijk van de betreffende computer.

2. Nabewerking

Daarna volgt een PL/pgSQL script met nabewerkingsacties.

DO $ do $ 
DECLARE osm_table_name TEXT;
BEGIN
   FOR osm_table_name IN 
   SELECT
      quote_ident(f_table_name),
      quote_ident(f_geometry_column) 
   FROM
      public.geometry_columns 
   WHERE
      f_geometry_column = 'way' 
      AND f_table_name LIKE 'planet%' LOOP 		
-- Indexen op primary keys worden automatisch aangemaakt en zijn ook niet te zien in PgAdmin
      Execute 'ALTER TABLE ' || osm_table_name || ' ADD COLUMN PKEY_ID BIGSERIAL PRIMARY KEY';
RAISE NOTICE 'Primary key created for %',osm_table_name;
--Hier worden de geometries gewijzigd van 900913 (resultaat van osm2pgsql) naar 28992
CASE
   WHEN
      osm_table_name like '%line' 
   THEN
      Execute 'ALTER TABLE ' || osm_table_name || ' ALTER COLUMN way TYPE geometry(LINESTRING,28992) USING ST_Transform(way,28992)';
WHEN
   osm_table_name like '%point' 
THEN
   Execute 'ALTER TABLE ' || osm_table_name || ' ALTER COLUMN way TYPE geometry(POINT,28992) USING ST_Transform(way,28992)';
WHEN
   osm_table_name like '%roads' 
THEN
   Execute 'ALTER TABLE ' || osm_table_name || ' ALTER COLUMN way TYPE geometry(LINESTRING,28992) USING ST_Transform(way,28992)';
WHEN
   osm_table_name like '%polygon' 
THEN
   Execute 'ALTER TABLE ' || osm_table_name || ' ALTER COLUMN way TYPE geometry(GEOMETRY,28992) USING ST_Transform(way,28992)';
END
CASE
;
RAISE NOTICE 'Geometry changed for %', osm_table_name;
END
LOOP;
-- Aparte tabel wordt aangemaakt voor de vlakken in het buitenland waar geen top10nl dekking is
CREATE table planet_osm_polygon_foreign_countries AS 
SELECT
   planet_osm_polygon.* 
FROM
   planet_osm_polygon,
   grenzen 
WHERE
   (
      planet_osm_polygon.landuse IS NOT Null 
      OR planet_osm_polygon.natural IS NOT NULL
   )
   AND NOT st_within(planet_osm_polygon.way, grenzen.way);
-- Aparte view voor plaatsnamen alleen in het buitenland
CREATE 
OR REPLACE VIEW osm_foreign_places_point AS 
SELECT
   planet_osm_point.osm_id,
   planet_osm_point.name,
   planet_osm_point.place,
   planet_osm_point.way 
FROM
   planet_osm_point,
   grenzen 
WHERE
   planet_osm_point.place IS NOT NULL 
   AND planet_osm_point.name IS NOT NULL 
   AND NOT st_within(planet_osm_point.way, grenzen.way);
END
$ do $ LANGUAGE plpgsql;

Hier gebeurt het volgende:

  • in de geometry_columns view staan verwijzingen naar 4 tabellen met hun geometrieën. Die 4 tabellen zijn belangrijk voor mijn kaarten waar OSM-data een rol speelt: planet_osm_polygon, planet_osm_line, planet_osm_point en planet_osm_roads.
  • Van elk van die tabellen wordt in een loop
    • een oude index verwijderd, die stond op de column osm_id, maar bevat geen unieke waardes, dus is als primary key ongeschikt.
    • een column ingevoerd die ook dienst doet als primary key-veld
    • een index wordt aangemaakt op dat laatste veld

Daarna worden alle data geherprojecteerd naar 28992, het Rijksdriehoekstelsel.

Vervolgens wordt een aparte tabel aangemaakt met buitenlandse terreinvlakken. Ik gebruik voor Nederland de top10nl vlakken, maar wil voor het naburige buitenland ook terreinvlakken hebben. Daarvoor dient deze tabel.

Tenslotte maak ik een view aan waarin alleen de plaatsnamen over de grens worden geselecteerd. Ook iets dat ik voor mijn kaarten nodig heb.