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;


-- need to upgrade the postgis packaged extensions do 
SELECT postgis_extensions_upgrade();

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

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 upgrading an existing install
ALTER EXTENSION pgrouting UPDATE;

-- see version details 
SELECT * FROM pgr_full_version();

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.

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 
    This is an option in installer for PostGIS 3.4.1 bundle and above**/

CREATE EXTENSION ogr_fdw;

-- for managing pointcloud data
CREATE EXTENSION pointcloud;
CREATE EXTENSION pointcloud_postgis;
-- see version installed
SELECT pc_version();

-- upgrade
ALTER EXTENSION pointcloud UPDATE;
ALTER EXTENSION pointcloud_postgis UPDATE;

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

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;
-- show current version and dependencies
SELECT mobilitydb_full_version();
-- updating existing install 
ALTER EXTENSION mobilitydb UPDATE;

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.

pgSphere is an extension used for managing spherical geometries. It is used primarily for managing astrononomical data. It has no reliance on PostGIS. It is not yet packaged with the PostGIS bundle, but will be in the future. You will find the binaries for it in the respective extras folder of each PostgreSQL buildbot folder of the respective PostgreSQL versions.

To install, copy the files into your PostgreSQL install and run in your databases of choice

CREATE EXTENSION pg_sphere;