Download the necessary software:

• PostgreSQL 15 installer
• PostGIS extension installer

Installing PostgreSQL

Run the PostgreSQL installer and follow the default installation steps, modifying the installation directory if needed.

Environment Variable Configuration

Access the system environment variables via System Properties > Advanced > Environment Variables.

1. Create a new user variable named GDAL_DATA. Set its value to the installation path of PostGIS.
2. Edit the Path variable to include the PostgreSQL binary directory: %PG_HOME%\bin\.
3. Verify the configuration by opening a command prompt and running: psql --version.
4. Start the PostgreSQL database service from the command line before installing PostGIS. Use the following command, replacing the path with your PostgreSQL data folder location:

   pg_ctl -D D:\ProgramFile\postgresql\15\data start
   

Installing PostGIS

Execute the PostGIS installer. During the setup, you may optionally create a new spatial database template. Connect to the PostgreSQL server using a database client like Navicat with the credentials:

• Username: postgres
• Password: postgis

Enabling PostGIS Extensions

Within your new or existing PostgreSQL database, run the following SQL commands to activate core geospatial functionalities.

-- Core spatial data support
CREATE EXTENSION postgis;
-- Raster data handling capabilities
CREATE EXTENSION postgis_raster;
-- Topological geometry modeling
CREATE EXTENSION postgis_topology;
-- Advanced 3D geometry operations using SFCGAL
CREATE EXTENSION postgis_sfcgal;
-- Functions for fuzzy string matching
CREATE EXTENSION fuzzystrmatch;
-- Address parsing and standardization tools
CREATE EXTENSION address_standardizer;
-- US-specific data for address standardization
CREATE EXTENSION address_standardizer_data_us;
-- US TIGER/Line geocoder functionality
CREATE EXTENSION postgis_tiger_geocoder;

Other useful PostgreSQL extensions include pgcrypto for encryption, hstore for key-value storage, citext for case-insensitive text, pg_stat_statements for query performance monitoring, uuid-ossp for UUID generation, pg_trgm for text similarity search, intarray for integer array operations, and pg_stat_activity for monitoring current database sessions.

Installing QGIS for Visualization

Download and install QGIS. After installlation, configure a connection to your PostgreSQL/PostGIS database:

1. In the Browser panel, locate and right-click on PostgreSQL.
2. Select New Connection and enter your database connection parameters.
3. Once connected, you can drag database tables onto the map canvas to visualize spatial data.

Verifying the Setup with Sample Data

Execute the following SQL to create and populate test tables with 3D geometries.

-- Sample table for 3D point features
CREATE TABLE spatial_points (
  feat_id SERIAL PRIMARY KEY,
  feat_name TEXT,
  coord geometry(PointZ, 4326)
);

INSERT INTO spatial_points (feat_name, coord) VALUES
('TestPoint_A', ST_GeomFromText('POINTZ(10 20 5)', 4326)),
('TestPoint_B', ST_GeomFromText('POINTZ(30 40 10)', 4326));

-- Sample table for 3D line features
CREATE TABLE spatial_lines (
  line_id SERIAL PRIMARY KEY,
  line_name TEXT,
  shape geometry(MultiLineStringZ, 4326)
);

INSERT INTO spatial_lines (line_name, shape) VALUES
('Road_Alpha', ST_GeomFromText('MULTILINESTRINGZ((0 5 0, 10 15 0, 20 25 0), (30 35 10, 40 45 20))', 4326));

-- Sample table for 3D polygon features
CREATE TABLE spatial_polygons (
  poly_id SERIAL PRIMARY KEY,
  poly_name TEXT,
  boundary geometry(PolygonZ, 4326)
);

INSERT INTO spatial_polygons (poly_name, boundary) VALUES
('Building_One', ST_GeomFromText('POLYGON Z((0 0 0, 0 100 0, 100 100 0, 0 0 0))', 4326));