PostGIS
Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage

Enabling PostGIS

PostGIS and other extensions are optional extensions that must be enabled in EVERY database you want to use them in. All the below extensions are included as part of the PostGIS windows bundle

CREATE EXTENSION postgis;
-- enabling raster support
CREATE EXTENSION postgis_raster;

-- enabling advanced 3d support
CREATE EXTENSION postgis_sfcgal;
-- enabling SQL/MM Net Topology
CREATE EXTENSION postgis_topology;

-- using US census data for geocoding and standardization
CREATE EXTENSION address_standardizer;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

These extensions are part of the PostGIS bundle, but you can also download the latest separately in Unreleased Versions.

pgRouting

If as the crow flies distance is not enough, and you need real measures of time based on road paths, walking paths, bike paths, and other obstacles, then pgRouting is for you. Run below to start your routing journey.

CREATE EXTENSION pgrouting;

For loading OpenStreetMap data (OSM) into pgrouting format, you can use osm2pgrouting or osm2po. Windows binaries for osm2pgrouting can be found alongside pgrouting for each version and are included in the windows bundle. Refer to instructions in the zip.

Additional extensions included in the PostGIS Bundle, that are not part of PostGIS project

These extension you’ll find in the extras PostgreSQL versioned folder if you want to use not yet released versions.

/** foreign data wrapper for querying external spatial
    and non-spatial data sources
    Note if you get an error accessing https sources,
    you need to define the CURL_CA_BUNDLE system environment variable to something like
    C:\Program Files\PostgreSQL\15\ssl\certs\ca-bundle.crt **/

CREATE EXTENSION ogr_fdw;

-- for managing pointcloud data
CREATE EXTENSION pointcloud;
CREATE EXTENSION pointcloud_postgis;

-- for using Uber h3 indexing scheme
CREATE EXTENSION h3;
CREATE EXTENSION h3_postgis;

MobilityDB

MobilityDB is for managing trajectory data. Refer to https://github.com/MobilityDB/MobilityDB for details. The mobilitydb extension, was introduced in PostGIS Bundle 3.3.3 (for PostgreSQL 12 and above). You will also find available in the extras PostgreSQL versioned folders.

-- confirm you don't have any existing pre-loaded libraries,
-- if you do make sure to readd when alter system
SHOW shared_preload_libraries;
ALTER SYSTEM  SET shared_preload_libraries = 'postgis-3';

SHOW max_locks_per_transaction;

-- if lower than 128, then run the below
ALTER SYSTEM SET max_locks_per_transaction = 128;

From Windows services, restart the PostgreSQL service.

Now you are ready to enable mobilitydb in a database.

CREATE EXTENSION mobilitydb;

If you failed to do the above steps or restart your service, you might get an error when you run the create extension command of the sort:

“C:/Program Files/PostgreSQL/15/lib/libMobilityDB-1.1.dll”: The specified module could not be found.

See the Getting Started section for more guidance.