This repository contains a collection of code snippets for geo developers
-
- Export a new shapefile filtered by criteria
- Export a shapefile to GML
- Export a shapefile to GeoJSON
- Export a shapefile to KML
- Export dbf table to PostgreSQL
- Import to PostgreSQL table from csv and Virtual File Format
- Export a csv file to dbf
- Export a csv file to shapefile
- Export a Postgres table to GeoPackage
- Export many Postgres tables to GeoPackage
- Export a whole Postgres database to GeoPackage
- Load a single layer GeoPackage into Postgres
- PostGIS to SQLite
- Import Geodatabase to PostGIS
- Reading the meta data using ogrinfo
createdb -U user -p 5432 -h localhost -E UTF8 -e databasename
psql -U user -p 5432 -h localhost -d databasename -c "CREATE EXTENSION postgis;"
psql -U user -p 5432 -h localhost -d databasename -c "CREATE EXTENSION pgrouting;"
CREATE EXTENSION IF NOT EXISTS postgis;
psql -U user -d databasename -h localhost -p 5432 -f input_file.sql
COPY table_name FROM 'data.csv' WITH DELIMITER ',' CSV HEADER;
COPY (SELECT array_to_json(array_agg(t))
FROM schema.table as t) to '/output.json'
SELECT ROW_NUMBER() OVER (ORDER BY column ) as id
FROM schema.table;
SELECT pg_size_pretty(pg_database_size('databasename')) as dbsize;
select pg_size_pretty(CAST((SELECT
SUM(pg_total_relation_size(table_schema || '.' || table_name) )
FROM information_schema.tables
WHERE table_schema = 'schema') as bigint) ) as schema_size;
SELECT column, COUNT(column) AS numocurrences
FROM schema.table
GROUP BY column
HAVING ( COUNT(column) > 1 );
SELECT st_geometrytype(the_geom) FROM schema.table;
SELECT t.f_table_catalog, t.f_table_schema, t.f_table_name, t.f_geometry_column, t.coord_dimension, t.srid, t.type
FROM geometry_columns t
WHERE t.srid <> 4326 ORDER BY t.f_table_schema, t.f_table_name;
SELECT Find_SRID('schema', 'table', 'the_geom');
SELECT UpdateGeometrySRID('schema', 'table', 'the_geom',4326);
SELECT *
FROM table1 a, table2 b
WHERE ST_Within(a.the_geom, b.the_geom);
SELECT *
FROM schema.table
WHERE ST_IsValid(the_geom) = true;
SELECT ST_XMax(r) AS xmax, ST_XMin(r) AS xmin, ST_YMax(r) AS ymax, ST_YMin(r) AS ymin
FROM (SELECT ST_Collect(the_geom) AS r FROM schema.table) AS foo;
ALTER TABLE schema.table ALTER COLUMN the_geom TYPE Geometry(MultiLineString, 3115)
USING ST_Transform(the_geom, 3115);
shp2pgsql -g the_geom -s 4326 -W latin1 shapefile table_name > output.sql
ogr2ogr -sql "SELECT * FROM input WHERE criteria=1" output.shp input.shp
ogr2ogr -f GML -t_srs crs:84 output.gml input.shp
ogr2ogr -f GeoJSON -t_srs crs:84 output.geojson input.shp
ogr2ogr -f KML -t_srs crs:84 output.KML input.shp
ogr2ogr -f "PostgreSQL" PG:"dbname=database host=localhost user=postgres password=postgres port=5432" table_dbf.dbf -nln "newtable"
ogr2ogr -a_srs epsg:4326 -f "PostgreSQL" PG:"dbname=database host=localhost user=postgres password=postgres port=5432" file.vrt
ogr2ogr -f "ESRI Shapefile" output.dbf input.csv
ogr2ogr -f "ESRI Shapefile" output_shp_xy data.vrt
ogr2ogr -f GPKG output.gpkg PG:"dbname=database host=localhost user=postgres password=postgres port=5432" "table"
ogr2ogr -f GPKG output.gpkg PG:"dbname=database host=localhost user=postgres password=postgres port=5432 tables=table1,table2,table3"
ogr2ogr -f GPKG output.gpkg PG:"dbname=database host=localhost user=postgres password=postgres port=5432"
ogr2ogr -f "PostgreSQL" PG:"dbname=database host=localhost user=postgres password=postgres port=5432" input.gpkg -nln "new_table"
ogr2ogr -progress -gt 65536 -f "SQLite" data.sqlite PG:"dbname=database host=localhost user=postgres password=postgres port=5432" -a_srs "EPSG:4326"
ogr2ogr -f "PostgreSQL" PG:"dbname=database host=localhost user=postgres password=postgres port=5432" data.gdb -a_srs EPSG:4326
ogrinfo -ro data.shp
osm2pgsql -c -d database -U postgres -H localhost -S osm2pgsql/default.style input_file.osm.pbf
gdalbuildvrt file.vrt folder_name/*.tif
gdal2tiles.py input.tif output_folder -z 6-18 -r antialias
*Zoom level 6 to 18
gdaldem hillshade inputdem.tif outputhillshade.tif
gdalwarp -overwrite -s_srs EPSG:3115 -q -cutline shapefile.shp -of GTiff input.tiff output.tiff
gdal_merge.py -o output.tiff -of GTiff -ps 10 10 -separate band1.tiff band2.tiff band3.tiff
*10 x 10 meter pixel
gdal_merge.py -o output.tiff -of GTiff band1.tiff band2.tiff band3.tiff
gdal_translate -outsize 50% 50% input.tif output.tif
*Resample to 50%
gpsbabel -t -i garmin_fit -f input.FIT -o gpx -F output.gpx
@echo allshp2dbpgsql.bat
@echo off
color 20
for %%x in (*.shp) do ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres dbname=database password=12345 port:5432" %%~nx.shp
pause
exit
@echo allshapes2pgsql.bat
@echo off
color 20
for %%x in (*.shp) do shp2pgsql -g the_geom -s 4326 -W latin1 %%~nx %%~nx > %%~nx.sql
pause
exit
@echo allfit2gpx.bat
@echo off
color 20
for %%x in (*.fit) do gpsbabel -t -i garmin_fit -f %%~nx.fit -o gpx -F %%~nx.gpx
pause
exit
PotreeConverter.exe C:/temporal/input.las -o C:/temporal/data_converted
License: MIT - do anything with the code, but don't blame me if it does not work.
Spread the word: tweet, star on github, etc.