Skip to content

full text search for swiss locations

Jump to bottom
Andrea Borghi edited this page Aug 26, 2021 · 1 revision

This page describes which data have been used for the addresses and administrative units search. The data have been taken from swisstopo OGD repository.

preliminary work on the dabase, if starting from scratch

Before starting, make sure that the database has the following requirements:

  • unaccent postgresql extension
  • there is a schema named swisstopo
  • the following dictionaries exist: de_de, fr_de and en_de

to boostrap it, run the following:

CREATE EXTENSION unaccent;

CREATE SCHEMA swisstopo;

CREATE TEXT SEARCH CONFIGURATION de_de (COPY = german);
ALTER TEXT SEARCH CONFIGURATION de_de ALTER MAPPING FOR hword, hword_part, word WITH unaccent, german_stem;
CREATE TEXT SEARCH CONFIGURATION fr_de (COPY = german);
ALTER TEXT SEARCH CONFIGURATION fr_de ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;
CREATE TEXT SEARCH CONFIGURATION en_de (COPY = german);
ALTER TEXT SEARCH CONFIGURATION en_de ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;

swiss addresses (GWR, Gebäude und Wohnungs Register, BFS)

  • data source: swisstopo
  • current version of the data: 2020
  • database schema: swisstopo
  • tablename: gwr_2020
  • main.tsearch.layername: GWR Addresses

download the data:

wget https://data.geo.admin.ch/ch.bfs.gebaeude_wohnungs_register/CSV/CH/CH.zip
unzip CH.zip

convert data from CSV to postgis, it is better to use the docker image from gdal so that it supports the latest versions of postgresql:

docker run --rm -v /home:/home osgeo/gdal:ubuntu-full-latest ogr2ogr -f "PostgreSQL" PG:"host=${PGHOST} port=${PGPORT} user=${PGUSER} dbname=${PGDATABASE} password=${PGPASSWORD} schemas=swisstopo" -nln gwr_2020 $PWD/CH.csv

login into postgres again with psql. We need to:

  • add a geometry column to the table
  • index these data into the tsearch table (do it for each instance of main.tsearch, i.e. int, prod, etc) if you have many.
/* add the geometry column */
ALTER TABLE swisstopo.gwr_2020 ADD COLUMN geom geometry(POINT,2056);
UPDATE swisstopo.gwr_2020 SET geom = ST_SetSRID(ST_MakePoint(dkode::double precision, dkodn::double precision),2056);

/* delete the old records to avoid duplicata */
DELETE FROM main.tsearch WHERE layer_name = 'GWR Addresses';

/* create the new records */
INSERT INTO main.tsearch (label, layer_name, the_geom, role_id, lang, ts)
 SELECT strname || ' ' || deinr || ', ' || dplz4 || ' ' || dplzname, 'GWR Addresses', geom, NULL, NULL, to_tsvector('de_de', strname || ' ' || deinr || ' ' || dplz4 || ' ' || dplzname) || to_tsvector('fr_de', strname || ' ' || dplzname) || to_tsvector('en_de', strname || ' ' || dplzname) || to_tsvector('simple', strname || ' ' || dplzname) FROM swisstopo.gwr_2020 ;

swiss administrative units

  • data source: swisstopo
  • current version of the data: 2021
  • database schema: swisstopo
  • tablename: swissboundaries3d_hoheitsgebiet_2021
  • main.tsearch.layername: Administrative Units

download the data:

wget https://data.geo.admin.ch/ch.swisstopo.swissboundaries3d-gemeinde-flaeche.fill/shp/2056/ch.swisstopo.swissboundaries3d-gemeinde-flaeche.fill.zip
unzip ch.swisstopo.swissboundaries3d-gemeinde-flaeche.fill.zip

convert data from shapefile to postgis, it is better to use the docker image from gdal so that it supports the latest versions of postgresql:

docker run --rm -v /home:/home osgeo/gdal:ubuntu-full-latest ogr2ogr -nlt PROMOTE_TO_MULTI -s_srs EPSG:2056 -t_srs EPSG:2056 -f "PostgreSQL" PG:"host=pg-gs.camptocamp.com port=30100 user=xenodochial_mclaren dbname=xenodochial_mclaren schemas=swisstopo" -nln swissboundaries3d_hoheitsgebiet_2021 $PWD/swissBOUNDARIES3D_1_3_TLM_HOHEITSGEBIET.shp

login into postgres with psql. We need to:

  • index these data into the tsearch table (do it for each instance of main.tsearch, i.e. int, prod, etc) if you have many.
/* delete the old records to avoid duplicata */
DELETE FROM main.tsearch WHERE layer_name = 'Administrative Units';

/* create the new records */
INSERT INTO main.tsearch (label, layer_name, the_geom, role_id, lang, ts)
 SELECT name, 'Administrative Units', ST_Force2D(wkb_geometry), NULL, NULL, to_tsvector('de_de', name) || to_tsvector('fr_de', name) || to_tsvector('en_de', name) || to_tsvector('simple', name) FROM swisstopo.swissboundaries3d_hoheitsgebiet_2021 ;
Clone this wiki locally