Geospatial Data Analysis with PostGIS

PostGIS is a spatial database extension for PostgreSQL that enables databases to store Geographic Information System (GIS) objects. SynxDB Cloud’s extended support for PostGIS includes optional features such as GiST-based R-Tree spatial indexes and functions for analyzing and processing GIS objects. Additionally, it supports PostGIS Raster data types. PostGIS Raster and PostGIS geometric data types together provide a set of SQL functions (such as ST_Intersects) that enable seamless integration of vector and raster geospatial data. PostGIS Raster uses GDAL (Geospatial Data Abstraction Library) as its raster geospatial data format conversion library, providing applications with a unified raster data model.

For details about SynxDB Cloud’s support for PostGIS extensions, refer to PostGIS Extension Support and Limitations.

For more information about PostGIS, see https://postgis.net/.

For more information about GDAL, visit https://gdal.org/.

Adding Extensions

The following steps are used to enable PostGIS extensions and extensions used with PostGIS.

  1. Start the TpServer to ensure PostGIS functionality is executed on the TpServer. For details, refer to TpServer.

  2. To enable PostGIS and PostGIS Raster in the database, execute the following command after logging into the database.

    CREATE EXTENSION postgis;

    To enable PostGIS and PostGIS Raster in a specific schema, first create the schema, set search_path to the PostGIS schema, and then use the WITH SCHEMA clause to enable the postgis extension.

    SHOW search_path; -- View the current search_path
CREATE SCHEMA <schema_name>;
SET search_path TO <schema_name>;
CREATE EXTENSION postgis WITH SCHEMA <schema_name>;

    After enabling the extension, reset search_path. If needed, you can include the PostGIS schema in search_path.

  3. If needed, you can enable the PostGIS TIGER geocoder after enabling the postgis extension.

    To enable the PostGIS TIGER geocoder, you need to enable the fuzzystrmatch extension before enabling postgis_tiger_geocoder. The following two commands are used to enable the related extensions.

    CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

  4. If needed, you can also enable the rule-based address standardizer and add rule tables for the standardizer.

    CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;

Enabling GDAL Raster Drivers

When PostGIS processes raster data, such as when executing functions like ST_AsJPEG(), it requires GDAL raster drivers. However, by default, all raster drivers are disabled. To enable raster drivers, you need to configure the POSTGIS_GDAL_ENABLED_DRIVERS environment variable in the cloudberry-env.sh file on all SynxDB Cloud cluster hosts.

Alternatively, you can set this at the session level by directly modifying the postgis.gdal_enabled_drivers parameter. For example, in a SynxDB Cloud session, the following SET command can enable 3 GDAL raster drivers.

SET postgis.gdal_enabled_drivers TO 'GTiff PNG JPEG';

The following SET command sets the drivers enabled in the session to default values.

SET postgis.gdal_enabled_drivers = default;

To view the list of GDAL raster drivers supported by the SynxDB Cloud system, run the raster2pgsql tool on the SynxDB Cloud coordinator with the -G option.

raster2pgsql -G

This command displays the long format names of the drivers. The GDAL raster driver table can be found at https://gdal.org/drivers/raster/index.html, which lists long format names and their corresponding codes that can be set as environment variable values. For example, the code for the long name Portable Network Graphics is PNG. The following example export command enables 4 GDAL raster drivers.

export POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF"

Use the gpstop -r command to restart the SynxDB Cloud system to make the updated settings in the cloudberry-env.sh file take effect.

After updating the cloudberry-env.sh file on all hosts and restarting the SynxDB Cloud system, you can display the enabled raster drivers using the ST_GDALDrivers() function. This SELECT command lists all enabled raster drivers.

SELECT short_name, long_name FROM ST_GDALDrivers();

Enabling Out-of-Database Raster Functionality

After installing PostGIS, out-of-database raster support is disabled by default, with the POSTGIS_ENABLE_OUTDB_RASTERS setting in the cloudberry-env.sh file set to 0. If you need to enable this functionality, you need to change this parameter value to true (i.e., any non-zero value), make the same modification on all hosts, and then restart the SynxDB Cloud system.

Additionally, you can enable or disable this functionality only in the current SynxDB Cloud session. For example, the following SET command can enable this functionality only for the current session.

SET postgis.enable_outdb_rasters = true;

Note

After enabling out-of-database rasters, you can determine which raster formats to use through the server configuration parameter postgis.gdal_enabled_drivers.

Removing PostGIS Support

To remove support for PostGIS extensions and related extensions, you need to use the DROP EXTENSION command.

Removing PostGIS support from the database does not delete these PostGIS raster environment variables from the cloudberry-env.sh file: GDAL_DATA, POSTGIS_ENABLE_OUTDB_RASTERS, POSTGIS_GDAL_ENABLED_DRIVERS.

Warning

Removing PostGIS support from the database will delete PostGIS data objects in the database without any warning. Users accessing PostGIS objects may interfere with the removal process.

Using the DROP EXTENSION Command

Depending on the extensions you enabled for PostGIS, you can remove support for these extensions from the database.

  • If you enabled the address standardizer and sample rule tables, use the following commands to remove extension support from the current database.

    DROP EXTENSION IF EXISTS address_standardizer_data_us;
DROP EXTENSION IF EXISTS address_standardizer;

  • If you enabled the TIGER geocoder and fuzzystrmatch extension, use the following commands to remove extension support from the current database.

    DROP EXTENSION IF EXISTS postgis_tiger_geocoder;
DROP EXTENSION IF EXISTS fuzzystrmatch;

  • Remove support for PostGIS and PostGIS Raster. Use the following command to remove extension support from the current database.

    DROP EXTENSION IF EXISTS postgis;

  • If you enabled PostGIS support and specified a particular schema in the CREATE EXTENSION command, you can update search_path and remove the PostGIS schema as needed.

Usage Examples

Scenario 1: Using PostGIS to Create Non-OpenGIS Tables in the Database and Insert and Query Various Geometric Objects

-- Create a table named geom_test.
CREATE TABLE geom_test ( gid int4, geom geometry,
  name varchar(25) );

-- Insert a row of data into the table, with gid 1, geometry field using WKT format to represent a 3D polygon object (a 3D square), and name '3D Square'.
INSERT INTO geom_test ( gid, geom, name )
  VALUES ( 1, 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square');

-- Insert the second row of data, with gid 2, geometry as a 3D linestring, and name '3D Line'.
INSERT INTO geom_test ( gid, geom, name )
  VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' );

-- Insert the third row, with gid 3, geometry as a 2D multipoint object, and name '2D Aggregate Point'.
INSERT INTO geom_test ( gid, geom, name )
  VALUES ( 3, 'MULTIPOINT(3 4,8 9)', '2D Aggregate Point' );

-- First use ST_GeomFromEWKT to construct a 3D linestring object from EWKT
-- Then use Box3D to get the 3D bounding box of that object. Then use the && operator to query all rows in the geom_test table where the geom field intersects with that bounding box.
SELECT * from geom_test WHERE geom &&
  Box3D(ST_GeomFromEWKT('LINESTRING(2 2 0, 3 3 0)'));

Scenario 2: Using PostGIS to Create Tables with Georeferencing, Insert Geocoded Point Data, and Output Point Data in Standard Text Format

-- Create a table named geotest.
CREATE TABLE geotest (id INT4, name VARCHAR(32) );

-- Add a geometry column named geopoint to the geotest table, defined as POINT type
-- with coordinate dimension 2, and specify its spatial reference system (SRID) as 4326 (representing the WGS84 geographic coordinate system).
SELECT AddGeometryColumn('geotest','geopoint', 4326,'POINT',2);

-- Insert the first row of data, with id 1, name 'Olympia', and geopoint is a point object constructed using ST_GeometryFromText
-- from WKT text, with coordinates (-122.90, 46.97) and SRID 4326.
INSERT INTO geotest (id, name, geopoint)
  VALUES (1, 'Olympia', ST_GeometryFromText('POINT(-122.90 46.97)', 4326));

-- Insert the second row of data, with id 2, name 'Renton'
-- point coordinates (-122.22, 47.50), also with SRID 4326.
INSERT INTO geotest (id, name, geopoint)
  VALUES (2, 'Renton', ST_GeometryFromText('POINT(-122.22 47.50)', 4326));

-- Select the name and geopoint fields from the geotest table, but convert the geopoint field
-- using the ST_AsText function to output in standard text (WKT) format.
SELECT name,ST_AsText(geopoint) FROM geotest;

Scenario 3: Supporting Spatial Index Functionality

-- Create a table
CREATE TABLE spatial_data (
  id SERIAL PRIMARY KEY,
  geom geometry
);

-- Insert data
INSERT INTO spatial_data (geom) VALUES
(ST_GeomFromText('POINT(0 0)')),
(ST_GeomFromText('POINT(1 1)')),
(ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'));

-- Create a spatial index
CREATE INDEX spatial_data_gist_idx
  ON spatial_data
  USING GIST (geom);

PostGIS Support and Usage Limitations

This section describes the features supported by the SynxDB Cloud PostGIS extension and its limitations. Typically, the SynxDB Cloud PostGIS extension does not support the following features:

  • PostGIS topology extension (postgis_topology)

  • PostGIS 3D and geographic processing extension (postgis_sfcgal)

  • Some user-defined functions and aggregate operations

  • PostGIS long transaction processing

Supported PostGIS Data Types

The SynxDB Cloud PostGIS extension supports the following PostGIS data types:

  • box2d

  • box3d

  • geometry

  • geography

For complete PostGIS data types, operators, and functions, see the PostGIS Reference Documentation.

Supported PostGIS Indexes

The SynxDB Cloud PostGIS extension supports GiST (Generalized Search Tree) indexes.

PostGIS Extension Usage Limitations

This section lists the limitations of the SynxDB Cloud PostGIS extension regarding user-defined functions (UDFs), data types, and aggregates.

  • SynxDB Cloud does not support data types and functions related to PostGIS topology functionality, such as TopoGeometry.

  • SynxDB Cloud does not support the following PostGIS aggregates:

    • ST_Collect

    • ST_MakeLine

    In a SynxDB Cloud cluster with multiple Segments, if the same aggregate function is called multiple times consecutively, different results may be obtained.

  • SynxDB Cloud does not support PostGIS long transaction processing.

    PostGIS relies on triggers and the PostGIS table public.authorization_table to implement long transaction support. When PostGIS attempts to lock a long transaction, SynxDB Cloud will report an error indicating that the function cannot access the table named authorization_table.

  • SynxDB Cloud does not support the _postgis_index_extent function.

  • The <-> operator (geometry <-> geometry) is used to return the distance between the center points of two geometries.

  • SynxDB Cloud supports the TIGER geocoder extension but does not support upgrading the TIGER geocoder extension.

  • The standardize_address() function takes lex, gaz, or rules tables as parameters. If you are using tables other than us_lex, us_gaz, or us_rules, you should set them to the DISTRIBUTED REPLICATED distribution strategy to ensure they work properly on SynxDB Cloud.