User:Elektronisk/SSRtoPostGIS

This page contains notes for loading of the Norwegian Central Place Name Register into a PostGIS database.

Requirements:

• sosicon, from https://github.com/espena/sosicon (no other dependencies, generates .sql files)
• PostgreSQL with PostGIS extension available, install depends on OS and distro
• The data set in SOSI format, from http://data.kartverket.no/download/

It can be useful to use pgAdmin for queries, as well as QGIS for connecting to the database and showing the entries spatially.

Converting and loading data

Using psql shell (connect with psql -U postgres), create database and enable PostGIS.

postgres=# CREATE DATABASE kartverket WITH ENCODING='UTF8';
postgres=# \c kartverket
kartverket=# CREATE EXTENSION postgis;
kartverket=# \q

Use sosicon to convert SOSI files to SQL files. Linux shell:

$ sosicon -2psql -o 06_skrivemate.sql -t Skrivemåte -schema ssr -table skrivemate Stedsnavn_6_Buskerud_UTM33_SOSI.sos
$ sosicon -2psql -o 06_navneenhet.sql -t Navneenhet -schema ssr -table navneenhet Stedsnavn_6_Buskerud_UTM33_SOSI.sos
$ sosicon -2psql -o 06_forekomst.sql -t SSRForekomst -schema ssr -table forekomst Stedsnavn_6_Buskerud_UTM33_SOSI.sos

The last table (occurence table) might not be as interesting, as all it shows is which place names were used in Kartverkets map products. Not all names occur on maps, and not all occurences are spelled correctly. The commands produce 06_skrivemate.sql, 06_navneenhet.sql and 06_forekomst.sql. Verify that 06_skrivemate.sql to see if it has entries or not. If the "-t Skrivemåte" parameter is passed as UTF-8, it won't match the ISO-8859-10 used in the SOSI data.

If all your files contain INSERT statements, start a psql shell in the directory with the files, and load the .sql files into the database:

postgres=# \c karverket
kartverket=# \i 06_skrivemate.sql
kartverket=# \i 06_navneenhet.sql
kartverket=# \i 06_forekomst.sql

At this point you can connect to the database in QGIS, run queries, or load even more data. To add other .sos files, adjust the file names in the commands listed above and add the -insert option to sosicon. This way the .sql files for other counties won't try to create their own tables, but will be inserted into existing tables.

Since the SSR data set only contains points, the unused tables for polygons and linestrings created by sosicon can be deleted:

kartverket=# DROP TABLE ssr.skrivemate_linestring, ssr.skrivemate_polygon, ssr.navneenhet_linestring,
    ssr.navneenhet_polygon, ssr.forekomst_linestring, ssr.forekomst_polygon;