Kadastrs data download and import into PostGIS

readme.md

Loading LV Cadastre into postgis

First, download all files, unzip and concatenate them into single shapefile. Takes around haf an hour.

#!/usr/bin/env bash

# Download all your data from https://data.gov.lv/dati/lv/dataset/kadastra-informacijas-sistemas-atverti-telpiskie-dati
# into data/kadastrs/ folder
# Script does the following:
# 1. Downloads all data
# 2. Creates a single shapefile with all the layers

# Download

echo "Getting file list"
FILES=$(curl -s https://data.gov.lv/dati/lv/dataset/b28f0eed-73b0-4e44-94e7-b04b11bf0b69.jsonld | jq -r '."@graph"[]."dcat:accessURL"."@id" | select(. != null)')

rm -rf data/kadastrs && mkdir -p data/kadastrs

FILES_ARR=( $FILES )
TOTAL_FILES=${#FILES_ARR[@]}
CURRENT_FILE=0

for FILE in $FILES
do
    CURRENT_FILE=$((CURRENT_FILE+1))
    echo -n "Downloading $CURRENT_FILE of $TOTAL_FILES file(s) - ${FILE##*/} ... "
    curl "$FILE" -s -o data/kadastrs/${FILE##*/}
    echo -n " unzipping ... "
    unzip -qq -o data/kadastrs/${FILE##*/} -d data/kadastrs/
    echo "ok"
done

# Concatenate
APPEND=0
LAYERS="KKCadastralGroup KKBuilding KKEngineeringStructurePoly KKParcel KKParcelBorderPoint KKParcelError KKParcelPart KKSurveyingStatus KKWayRestriction"
DB=vzd

for type in $LAYERS; do
    APPEND=0
    rm -f "data/kadastrs/$type*";
    target_file="data/kadastrs/$type.shp"
    target_layer=$(echo "$type" | tr '[:upper:]' '[:lower:]')

    for file in data/kadastrs/**/"$type".shp; do
        if [ "$APPEND" == 0 ]; then
            echo -n "Create $target_file "
            ogr2ogr -f 'ESRI Shapefile' "$target_file" "$file" -lco ENCODING=UTF-8
            APPEND=1
        else
            echo -n "Update $target_file "
            ogr2ogr -f 'ESRI Shapefile' -update -append "$target_file" "$file" -nln "$target_layer"
        fi
        echo "(${target_file%.shp}; $file)"
    done
done

Next, using OGR foreign data wrapper for PostgreSQL, just create foreign tables, target tables, move data, and add indices. Takes around 2mins:

CREATE SERVER kadastrs
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource '/wherever/youve/downloaded/and/preprocessed/the/data/kadastrs',
    format 'ESRI Shapefile');

CREATE FOREIGN TABLE f_kkbuilding (
  fid bigint,
  geom Geometry(Polygon,3059),
  code varchar(14),
  objectcode varchar(10),
  parcelcode varchar(11),
  area_scale double precision,
  group_code varchar(7)
) SERVER kadastrs
OPTIONS (layer 'KKBuilding');

CREATE FOREIGN TABLE f_kkcadastralgroup (
  fid bigint,
  geom Geometry(PolygonZ,3059),
  code varchar(7),
  area_scale double precision,
  objectcode varchar(10)
) SERVER kadastrs
OPTIONS (layer 'KKCadastralGroup');


CREATE FOREIGN TABLE f_kkparcel (
  fid bigint,
  geom Geometry(Polygon,3059),
  code varchar(11),
  geom_act_d date,
  objectcode varchar(10),
  area_scale double precision,
  group_code varchar(7)
) SERVER kadastrs
OPTIONS (layer 'KKParcel');

CREATE FOREIGN TABLE f_kkparcelpart (
  fid bigint,
  geom Geometry(Polygon,3059),
  code varchar(15),
  objectcode varchar(10),
  parcelcode varchar(11),
  group_code varchar(7),
  area_scale double precision
) SERVER kadastrs
OPTIONS (layer 'KKParcelPart');

CREATE TABLE kkbuilding (
  fid bigint,
  geom Geometry(Polygon,3059),
  code varchar(14),
  objectcode varchar(10),
  parcelcode varchar(11),
  area_scale double precision,
  group_code varchar(7)
);
insert into kkbuilding select * from f_kkbuilding;
create index kkbuilding_code_idx on kkbuilding(code);
create index kkbuilding_geom_idx on kkbuilding using gist

CREATE TABLE kkcadastralgroup (
  fid bigint,
  geom Geometry(PolygonZ,3059),
  code varchar(7)
);
insert into kkcadastralgroup select * from f_kkcadastralgroup;
create index kkcadastralgroup_code_idx on kkcadastralgroup(code);
create index kkcadastralgroup_geom_idx on kkcadastralgroup using gist


CREATE TABLE kkparcel (
  fid bigint,
  geom Geometry(Polygon,3059),
  code varchar(11)
);
insert into kkparcel select * from f_kkparcel;
create index kkparcel_code_idx on kkparcel(code);
create index kkparcel_geom_idx on kkparcel using gist

CREATE TABLE kkparcelpart (
  fid bigint,
  geom Geometry(Polygon,3059),
  code varchar(15)
);
insert into kkparcelpart select * from f_kkparcelpart;
create index kkparcelpart_code_idx on kkparcelpart(code);
create index kkparcelpart_geom_idx on kkparcelpart using gist