Skip to content

Instantly share code, notes, and snippets.

View StephanGeorg's full-sized avatar
🌍

Stephan Georg StephanGeorg

🌍
33 followers · 24 following
View GitHub Profile
@StephanGeorg
StephanGeorg / .duckdbrc
Created February 13, 2025 09:33
configs
.timer off
INSTALL spatial;
LOAD spatial;
INSTALL h3 FROM community;
LOAD h3;
.timer on
.width 180
@StephanGeorg
StephanGeorg / 01-A-data-set_overview.txt
Last active August 27, 2024 10:21
NL HVD address data-set overview
# BAG
# Website: https://www.kadaster.nl/zakelijk/registraties/basisregistraties/bag
## BAG Light
# Date of source (last modified): 01-08-2024
# Atom: https://service.pdok.nl/lv/bag/atom/bag.xml
# Data: https://service.pdok.nl/lv/bag/atom/downloads/bag-light.gpkg
## BAG Extract
# Atom: https://service.pdok.nl/lv/bag/atom/bag.xml
# Data: # Data: https://service.pdok.nl/kadaster/adressen/atom/v1_0/downloads/lvbag-extract-nl.zip
# Date of source (last modified): 08-08-2024
@StephanGeorg
StephanGeorg / transform.sh
Last active June 1, 2025 09:11
UN/Locode coordinates conversion to Lat,Lng in SQL (DuckDB dialect)
# fix encoding issues
iconv -f iso-8859-1 -t UTF-8 2024-2\ UNLOCODE\ CodeListPart1.csv > unlocode_utf8_part1.csv
iconv -f iso-8859-1 -t UTF-8 2024-2\ UNLOCODE\ CodeListPart2.csv > unlocode_utf8_part2.csv
iconv -f iso-8859-1 -t UTF-8 2024-2\ UNLOCODE\ CodeListPart3.csv > unlocode_utf8_part3.csv
# remove empty country dividers
grep -Ev '(,){8,}' unlocode_utf8_part1.csv > unlocode_utf8_part1_fixed.csv
grep -Ev '(,){8,}' unlocode_utf8_part2.csv > unlocode_utf8_part2_fixed.csv
grep -Ev '(,){8,}' unlocode_utf8_part3.csv > unlocode_utf8_part3_fixed.csv
@StephanGeorg
StephanGeorg / inticap.sql
Last active May 15, 2024 07:46
INITCAP function to make a string Title Case for DuckDB
-- Usage: SELECT INITCAP('the quick brown fox jumped over the lazy dog');
-- Output: The Quick Brown Fox Jumped Over The Lazy Dog
-- Limitation: Does not work for SELECT INITCAP('the-quick_brown-fox-jumped-over-the-lazy-dog');
CREATE OR REPLACE MACRO INITCAP(a) AS ([UPPER(x[1])||LOWER(x[2:]) for x in (a).string_split(' ')]).list_aggr('string_agg',' ');
@StephanGeorg
StephanGeorg / Area building
Last active June 12, 2023 12:49
# Extract building polygons
ogr2ogr -where "building IS NOT NULL" DE-building.fgb DE.pbf multipolygons -oo CONFIG_FILE=../osmconf_building.ini
@StephanGeorg
StephanGeorg / 30855.geojson
Last active March 18, 2021 13:10
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@StephanGeorg
StephanGeorg / map.geojson
Last active March 18, 2021 13:05
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@StephanGeorg
StephanGeorg / ourairports.sh
Created February 11, 2021 11:52
OurAirports import to Postgres with Postgis indexes
#!/bin/bash
# Globals
PWD="$(pwd)"
WORKPATH=$PWD
# DB config
DBHOST="localhost"
DBPORT="5432"
DBUSER="postgres"
@StephanGeorg
StephanGeorg / geonames-premium.sh
Last active February 8, 2021 14:57
Import Geonames Premium Data to Postgres
#!/bin/bash
#===============================================================================
#
# FILE: getgeo.sh
#
# USAGE: ./getgeo.sh
#
# DESCRIPTION: run the script so that the geodata will be downloaded and inserted into your
# database
#
@StephanGeorg
StephanGeorg / gist:3736a1b4d9861e347e5e0e066eaf6dbc
Created December 5, 2020 08:23 — forked from WhoAteDaCake/gist:37823722bdf27fc03527f5b54c0ca6f0
Script to import Geonames into PostgreSQL
#!/bin/bash
#===============================================================================
#
# FILE: getgeo.sh
#
# USAGE: ./getgeo.sh
#
# DESCRIPTION: run the script so that the geodata will be downloaded and inserted into your
# database
#