Postgis 3.1.1 for Desserts 9.6

geovista.by.vincent@gmail.com

To install the latest version ofPostgis 3.1.1 (64 bits) published on 3 February 2021, first you have to installPostgreSQL 9.6 (64 bits), then triggered by the following SQL commands:

CREATE EXTENSION postgis;
-- in PostGIS 3, postgis_raster is a separate extension from postgis
-- so do this in addition if you are experimenting PostGIS 3+
CREATE EXTENSION postgis_raster;
 
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_tiger_geocoder;

Once the command is installed to run in the terminal CMD.exe:

cd "C:\Program Files\PostgreSQL\9.6\bin"
raster2pgsql -s _SRID/EPSG_ -I -C -M _rasterfile_.tif -F -t 100x100 public._rastertable_ | psql -U postgres -d _dbname_

SQL command to change EPSG projection:4236 a EPSG:32719 from a tableraster:

select ST_SetSRID(rast,32719) FROM _rastertable_;

Medium / Tutorials for handling Raster in PostGIS

The PostGIS package 3.1.1 It includes:

  • PostGIS 3.1.1
    • with raster library
    • GEOS 3.9.0
    • PROJ 7.1.1, SFCGAL support (1.3.8)
    • address_standardizer
    • topology
  • PostGIS Tiger geocoder extension - Tiger 2019
  • pgRouting: version 3.1 & 3.1.3 to calculate itinerary
  • raster2pgsql: command line raster loader
  • shp2pgsql, pgsql2shp: import / shapefile export
  • osm2pgrouting 2.3.7 to load data from .osm files in pgRouting routable format
  • shp2pgsql-gui which has import and export support for geometry / geography
  • ogrfdw 1.1.0: to read spatial data (the spatial columns are converted to PostGIS-like "geometry") and not spatial. IMPORT FOREIGN SCHEMA supports new functions in this version: character encoding option and ogr_fdw_drivers utility functions (), ogr_fdw_version ()
  • GDAL 3.2.1 con OpenJPEG 2.3.1 (JPEG 2000), ODBC, Curl, SQLite3 (for compatibility with GeoPackage and OSM), excel (XLS) (via FreeXL 1.0.6), libreoffice, XLSX spreadsheet (through expat) (used by both PostGIS raster and ogrfdw)
  • pgpointcloud 1.2.0 to query the lidar point cloud and input functions / output to convert to PostGIS geometry

Additional extensions:

  • PgRouting: https://winnie.postgis.net/download/windows/pg96/buildbot/pgrouting-pg96-binaries-3.1.3w64gcc81.zip
  • Osm2PgRouting: https://winnie.postgis.net/download/windows/pg96/buildbot/osm2pgrouting-pg96-binaries-2.3.7w64gcc81.zip

Leave a Reply