mguzelevich/gis

docker with gdal, postgres + postgis + h3 + other useful extentions

prepare env

$ mkdir sql src output
$ touch stack.yml
$ touch Makefile
$ echo "COMPOSE_PROJECT_NAME=my-gis" > .env

insert content to files

docker compose file stack.yml

version: '3.1'

services:
  builder:
    build: .
    image: mguzelevich/gis
    command: echo "ok"
  pg:
    image: mguzelevich/gis
    restart: always
    environment:
      POSTGRES_DB: gis
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - 5432:5432
    volumes:
      - type: bind
        source: ./src
        target: /gis
      - type: bind
        source: ./output
        target: /output
      - type: bind
        source: ./sql
        target: /sql
    healthcheck:
      test: "exit 0"
  gdal:
    image: osgeo/gdal:alpine-small-latest
    volumes:
      - type: bind
        source: ./src
        target: /gis
      - type: bind
        source: ./output
        target: /output
      - type: bind
        source: ./sql
        target: /sql
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Makefile with helpers

.PHONY: run test clean

exec_root = $(shell pwd)
ts = $(shell date +%Y%m%d_%H%M%S)
short_ts = $(shell date +%Y%m%d)

GDAL_IMAGE = osgeo/gdal:alpine-small-latest
POSTGIS_IMAGE = mguzelevich/gis

CONN_SERVER = host=pg port=5432 user=postgres password=postgres
CONN = dbname=gis ${CONN_SERVER}

CSV_FLAGS = -oo AUTODETECT_TYPE=YES -oo X_POSSIBLE_NAMES=longitude -oo Y_POSSIBLE_NAMES=latitude
SHP_FLAGS = -s_srs EPSG:4326 -t_srs EPSG:4326

#
prepare:
	echo "prepare"

build-gis-docker:
	docker-compose -f stack.yml build builder

run-gis-server:
	docker-compose -f stack.yml up -d

psql:
	docker-compose -f stack.yml run -it --rm pg psql "${CONN}"

psql-sql: sql_file = file.sql
psql-sql:
	docker-compose -f stack.yml run -it --rm pg psql "${CONN}" -f ${sql_file}

psql-exec: sql = select true as ok;
psql-exec:
	docker-compose -f stack.yml run --rm pg sh -c 'echo "${sql}" | psql "${CONN}"'

init-db:
	make psql-exec sql='CREATE EXTENSION postgis; CREATE EXTENSION postgis_raster; CREATE EXTENSION h3; CREATE EXTENSION h3_postgis;'

### /tools

fmt:
	gofmt -w .

load-gpkg: input = data.gpkg
load-gpkg: target = table
load-gpkg:
	make psql-exec sql='drop table if exists ${target};'
	docker-compose -f stack.yml run -it --rm gdal \
		sh -c 'ogr2ogr -f "PostgreSQL" PG:"${CONN}" "/gis/${input}" -nln ${target}'

load-csv: input = datasource.csv
load-csv: target = table
load-csv:
	make psql-exec sql='drop table if exists ${target};'
	docker-compose -f stack.yml run -it --rm gdal \
		sh -c 'ogr2ogr -f "PostgreSQL" PG:"${CONN}" "CSV:/gis/${input}" ${CSV_FLAGS} -nln ${target}'

load-shp: input = shape.shp
load-shp: target = table
load-shp:
	make psql-exec sql='drop table if exists ${target};'
	docker-compose -f stack.yml run -it --rm gdal \
		sh -c 'ogr2ogr -f "PostgreSQL" PG:"${CONN}" "/gis/${input}" ${SHP_FLAGS} -nln ${target}'

export-csv: sql_file = query.sql
export-csv: prefix = data
export-csv: out_csv = ${prefix}.csv
export-csv:
	cd output; sudo rm ${prefix}.* || echo "ok"
	docker-compose -f stack.yml run -it --rm gdal \
		sh -c 'ogr2ogr -f "CSV" -t_srs EPSG:4326 "/output/${out_csv}" PG:"${CONN}" -sql "@/sql/${sql_file}"'
	cd output; bzip2 -k ${out_csv}

export-shp: sql_file = query.sql
export-shp: prefix = shape
export-shp: out_shp = ${prefix}.shp
export-shp:
	cd output; sudo rm ${prefix}.* || echo "ok"
	docker-compose -f stack.yml run -it --rm gdal \
		sh -c 'ogr2ogr -f "ESRI Shapefile" ${SHP_FLAGS} "/output/${out_shp}" PG:"${CONN}" -sql "@/sql/${sql_file}"'
	cd output; bzip2 -k ${out_shp}

export-gpkg: sql_file = query.sql
export-gpkg: prefix = package
export-gpkg: out_gpkg = ${prefix}.gpkg
export-gpkg:
	cd output; sudo rm ${prefix}.* || echo "ok"
	docker-compose -f stack.yml run -it --rm gdal \
		sh -c 'ogr2ogr -f "ESRI Shapefile" ${SHP_FLAGS} "/output/${out_gpkg}" PG:"${CONN}" -sql "@/sql/${sql_file}"'
	cd output; bzip2 -k ${out_gpkg}

run & init server

$ make run-gis-server init-db

get started

test

$ make psql-exec sql="CREATE EXTENSION h3; SELECT h3_lat_lng_to_cell(POINT('37.3615593,-122.0553238'), 5);"

import some data (ex. [***]

$ make load-gpkg input=kontur.gpkg target=kontur_population_dataset

test

$ make psql-exec sql="SELECT * from kontur_population_dataset where fid<10;"

export data

$ make export-shp sql_file=export.query.sql prefix=population_dataset

stop server

$ docker-compose -f stack.yml stop