GDAL / OGR Recipes

gdal_ogr_recipes.md

PostgreSQL to GeoPackage

GeoPackage exports, very handy. Only issue is that GeoPackage tables can have only one geom column.

GeoPackages are SQLite3 databases:

sqlite3 geopackage.gpkg

Exports the full database:

PGCLIENTENCODING=UTF-8 ogr2ogr \
  -f GPKG thegeopackage.gpkg \
  -lco GEOMETRY_NAME=geom \
  PG:"host=localhost user=postgres dbname=dental password=postgres port=5432" \
  -a_srs EPSG:4326

Exports two tables:

PGCLIENTENCODING=UTF-8 ogr2ogr \
  -f GPKG geopackage_file_name.gpkg \
  PG:"host=host user=user dbname=db password=password port=port" \
  "pg_schema.pg_table_to_export" test.bcn \
  -a_srs EPSG:4326

Exports several schemas:

PGCLIENTENCODING=UTF-8 ogr2ogr \
  -f GPKG thegeopackage.gpkg \
  -lco GEOMETRY_NAME=geom \
  PG:"host=localhost user=postgres dbname=dental password=postgres port=5432 schemas=def,test" \
  -a_srs EPSG:4326

When there are two geom columns in a table, export a given geom column:

PGCLIENTENCODING=UTF-8 ogr2ogr \
  -f GPKG ../data/900_out/rentas_celda_ieca.gpkg \
  -lco GEOMETRY_NAME=geom \
  PG:"host=localhost user=postgres dbname=dental password=postgres port=5432 tables=ieca_250_cells_renta.cells(geom_centroid)" \
  -a_srs EPSG:3035

GeoPackage to PostGIS

Import full GeoPackage:

PGCLIENTENCODING=UTF-8 ogr2ogr \
  -lco GEOMETRY_NAME=geom \
  -lco SCHEMA=renta_espe \
  -where "cod_comunidad='01'" \
  -a_srs EPSG:25830 \
  -f PostgreSQL PG:"host=$PG_HOST user=$PG_USER dbname=$PG_DB password=$PG_PASS port=$PG_PORT" \
  ../../data/000_in/renta.gpkg

Import a given table in the GeoPackage with a filter and renaming the table:

PGCLIENTENCODING=UTF-8 ogr2ogr \
  -lco GEOMETRY_NAME=geom \
  -nln renta_espe.renta_original \
  -where "cod_comunidad='01'" \
  -a_srs EPSG:25830 \
  -f PostgreSQL PG:"host=$PG_HOST user=$PG_USER dbname=$PG_DB password=$PG_PASS port=$PG_PORT" \
  ../../data/000_in/renta.gpkg renta

psql

psql PostGIS client:

PGCLIENTENCODING=UTF-8 PGPASSWORD=$PG_PASS psql \
  -h $PG_HOST \
  -p $PG_PORT \
  -U $PG_USER \
  -d $PG_DB \
  -c "create schema renta_espe;"

From Shapefile to PostGIS

Just:

PGCLIENTENCODING=UTF-8 ogr2ogr \
  -f "PostgreSQL" PG:"host=localhost user=postgres dbname=test_postgis password=postgres port=5432" \
  -a_srs "EPSG:25830" -lco SCHEMA=import -lco FID=gid \
  -lco OVERWRITE=YES -nln dera2013_cuadricula_10 \
  -lco GEOMETRY_NAME=geom -nlt MULTIPOLYGON \
  -lco PRECISION=YES \
  ig06_cuadricula_10.shp

PRECISION tells GDAL to try to mimic the numeric precision to its fullest by using the numeric type. Sometimes this creates problem, yielding the numeric field overflow error. In this case, set it to NO. Default is YES.

From PostGIS to Shapefile

Just:

SHAPE_ENCODING='ISO-8859-1' ogr2ogr -overwrite \
	-f "ESRI Shapefile" \
	path_to_store_shape_or_shp_shape_name \
	PG:"host=localhost user=postgres dbname=sildb password=postgres port=5434" \
	"geometries_eiel.edificio" \
    -a_srs EPSG:4326 \
	-nln shape_name \
	-lco ENCODING=ISO-8859-1 \
    -lco SHPT=ARC

# With a SQL query

SHAPE_ENCODING='UTF-8' ogr2ogr -overwrite \
	-f "ESRI Shapefile" \
	$OUTPUT_FOLDER \
	PG:"host=localhost user=postgres dbname=caser password=postgres port=5432" \
    -a_srs EPSG:25831 \
	-nln bcn_cat_construcciones \
	-lco ENCODING=UTF-8 \
    -sql "select gid, constru, height, volume, geom from data.bcn_cat_construcciones" \
    -lco SHPT=POLYGON

User must have read access to view public.geometry_columns.

CSV to PostGIS

A CSV exported with OGR as shown below:

PGCLIENTENCODING=UTF-8 ogr2ogr \
    -f "PostgreSQL" PG:"host=localhost user=postgres dbname=dental password=postgres port=5432" \
    -a_srs "EPSG:4326" \
    -lco SCHEMA=import \
    -lco FID=gid \
    -lco OVERWRITE=YES \
    -nln bcn_service_points \
    -lco KEEP_GEOM_COLUMNS=NO \
    -oo HEADERS=YES \
    ../../data/000-in/barcelona-puntos_prestacion_potenciales.csv

From PostGIS to CSV with Geometry

This creates a set of files with the geometry dump as WKT, column type metadata, and SRID info.

# Exports the full database to a bcn folder set of files

PGCLIENTENCODING=UTF-8 ogr2ogr \
    -f "CSV" bcn \
    -lco GEOMETRY=AS_WKT \
    -lco CREATE_CSVT=YES \
    -lco GEOMETRY_NAME=geom \
    PG:"host=localhost user=postgres dbname=dental password=postgres port=5432"

# As above, but only selected schemas

PGCLIENTENCODING=UTF-8 ogr2ogr \
    -f "CSV" bcn \
    -lco GEOMETRY=AS_WKT \
    -lco CREATE_CSVT=YES \
    -lco GEOMETRY_NAME=geom \
    PG:"host=localhost user=postgres dbname=dental password=postgres port=5432 schemas=test,def"

# As above, but only selected tables

PGCLIENTENCODING=UTF-8 ogr2ogr \
    -f "CSV" bcn \
    -lco GEOMETRY=AS_WKT \
    -lco CREATE_CSVT=YES \
    -lco GEOMETRY_NAME=geom \
    PG:"host=localhost user=postgres dbname=dental password=postgres port=5432" \
    def.bcn_service_points test.bcn