Skip to content

Instantly share code, notes, and snippets.

@laacz
Created August 8, 2022 15:56
Show Gist options
  • Save laacz/d0e492a61de0e2d4286be76cf3ce763b to your computer and use it in GitHub Desktop.
Save laacz/d0e492a61de0e2d4286be76cf3ce763b to your computer and use it in GitHub Desktop.
Kadastrs data download and import into PostGIS

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment