Geospatial Analytics

This chapter contains the following information:

• About PostGIS
• LightDB-A PostGIS Extension
• Enabling and Removing PostGIS Support
• Usage
• PostGIS Extension Support and Limitations

For information about upgrading PostGIS on LightDB-A Database 6 systems, see Upgrading PostGIS 2.1.5 or 2.5.4

About PostGIS

PostGIS is a spatial database extension for PostgreSQL that allows GIS (Geographic Information Systems) objects to be stored in the database. The LightDB-A PostGIS extension includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects.

The LightDB-A PostGIS extension supports some PostGIS optional extensions and includes support for the PostGIS raster data type. With the PostGIS Raster objects, PostGIS geometry data type offers a single set of overlay SQL functions (such as ST_Intersects) operating seamlessly on vector and raster geospatial data. PostGIS Raster uses the GDAL (Geospatial Data Abstraction Library) translator library for raster geospatial data formats that presents a single raster abstract data model to a calling application.

For information about LightDB-A Database PostGIS extension support, see PostGIS Extension Support and Limitations.

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

For information about GDAL, see https://gdal.org/.

LightDB-A PostGIS Extension

The LightDB-A PostGIS extension package is available from VMware Tanzu Network. After you download the package, you can follow the instructions in Verifying the LightDB-A Database Software Download to verify the integrity of the download. You can install the package using the LightDB-A Package Manager (gppkg). For details, see gppkg in the LightDB-A Database Utility Guide.

LightDB-A Database supports the following PostGIS extension versions and components:

• PostGIS 2.5.4, and components Proj 4.8.0, Geos 3.10.2, GDAL 1.11.1, Json 0.12, Expat 2.4.4
• PostGIS 2.1.5, and components Proj 4.8.0, Geos 3.4.2, GDAL 1.11.1, Json 0.12, Expat 2.1.0

For information about the supported LightDB-A extension packages and software versions, see Extensions.

There are significant changes in PostGIS 2.5.4 compared with 2.1.5. For a list of new and enhanced functions in PostGIS 2.5, see the PostGIS documentation PostGIS Functions new or enhanced in 2.5 and Release 2.5.4.

Note: To upgrade PostGIS refer to Upgrading PostGIS 2.1.5 or 2.5.4.

This table lists the PostGIS extensions support by LightDB-A PostGIS.

Note The PostGIS topology extension postgis_topology and the PostGIS 3D and geoprocessing extension postgis_sfcgal are not supported by LightDB-A PostGIS and are not included in the LightDB-A PostGIS extension package.

For information about the PostGIS extensions, see the PostGIS 2.5 documentation.

For information about LightDB-A PostGIS feature support, see PostGIS Extension Support and Limitations.

Enabling and Removing PostGIS Support

This section describes how to enable and remove PostGIS and the supported PostGIS extensions, and how to configure PostGIS Raster.

• Enabling PostGIS Support
• Enabling GDAL Raster Drivers
• Enabling Out-of-Database Rasters
• Removing PostGIS Support

For information about upgrading PostGIS on LightDB-A Database 6 systems, see Upgrading PostGIS 2.1.5 or 2.5.4

Enabling PostGIS Support

To enable PostGIS support, install the LightDB-A PostGIS extension package into the LightDB-A Database system, and then use the CREATE EXTENSION command to enable PostGIS support for an individual database.

Installing the LightDB-A PostGIS Extension Package

Install LightDB-A PostGIS extension package with the gppkg utility. For example, this command installs the package for RHEL 7.

gppkg -i postgis-2.5.4+pivotal.2.build.1-gp6-rhel7-x86_64.gppkg

After installing the package, source the lightdb_a_path.sh file and restart LightDB-A Database. This command restarts LightDB-A Database.

gpstop -ra

Installing the LightDB-A PostGIS extension package updates the LightDB-A Database system, including installing the supported PostGIS extensions to the system and updating lightdb_a_path.sh file with these lines for PostGIS Raster support.

export GDAL_DATA=$GPHOME/share/gdal
export POSTGIS_ENABLE_OUTDB_RASTERS=0
export POSTGIS_GDAL_ENABLED_DRIVERS=DISABLE_ALL

Using the CREATE EXTENSION Command

These steps enable the PostGIS extension and the extensions that are used with PostGIS.

1. To enable PostGIS and PostGIS Raster in a database, run this command after logging into the database.

   CREATE EXTENSION postgis ;
   

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

   SHOW search_path ; -- display 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 the search_path and include the PostGIS schema in the search_path if needed.

2. If needed, enable the PostGIS TIGER geocoder after enabling the postgis extension.

   To enable the PostGIS TIGER geocoder, you must enable the fuzzystrmatch extension before enabling postgis_tiger_geocoder. These two commands enable the extensions.

   CREATE EXTENSION fuzzystrmatch ;
   CREATE EXTENSION postgis_tiger_geocoder ;
   

3. If needed, enable the rules-based address standardizer and add rules tables for the standardizer. These commands enable the extensions.

   CREATE EXTENSION address_standardizer ;
   CREATE EXTENSION address_standardizer_data_us ;
   

Enabling GDAL Raster Drivers

PostGIS uses GDAL raster drivers when processing raster data with commands such as ST_AsJPEG(). As the default, PostGIS deactivates all raster drivers. You enable raster drivers by setting the value of the POSTGIS_GDAL_ENABLED_DRIVERS environment variable in the lightdb_a_path.sh file on all LightDB-A Database hosts.

Alternatively, you can do it at the session level by setting postgis.gdal_enabled_drivers. For a LightDB-A Database session, this example SET command enables three GDAL raster drivers.

SET postgis.gdal_enabled_drivers TO 'GTiff PNG JPEG';

This SET command sets the enabled drivers to the default for a session.

SET postgis.gdal_enabled_drivers = default;

To see the list of supported GDAL raster drivers for a LightDB-A Database system, run the raster2pgsql utility with the -G option on the LightDB-A Database coordinator.

raster2pgsql -G 

The command lists the driver long format name. The GDAL Raster table at https://gdal.org/drivers/raster/index.html lists the long format names and the corresponding codes that you specify as the value of the environment variable. For example, the code for the long name Portable Network Graphics is PNG. This example export line enables four GDAL raster drivers.

export POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF"

The gpstop -r command restarts the LightDB-A Database system to use the updated settings in the lightdb_a_path.sh file.

After you have updated the lightdb_a_path.sh file on all hosts, and have restarted the LightDB-A Database system, you can display the enabled raster drivers with the ST_GDALDrivers() function. This SELECT command lists the enabled raster drivers.

SELECT short_name, long_name FROM ST_GDALDrivers();

Enabling Out-of-Database Rasters

After installing PostGIS, the default setting POSTGIS_ENABLE_OUTDB_RASTERS=0 in the lightdb_a_path.sh file deactivates support for out-of-database rasters. To enable this feature, you can set the value to true (a non-zero value) on all hosts and restart the LightDB-A Database system.

You can also activate or deactivate this feature for a LightDB-A Database session. For example, this SET command enables the feature for the current session.

SET postgis.enable_outdb_rasters = true;                

Note When the feature is enabled, the server configuration parameter postgis.gdal_enabled_drivers determines the accessible raster formats.

Removing PostGIS Support

You use the DROP EXTENSION command to remove support for the PostGIS extension and the extensions that are used with PostGIS.

Removing PostGIS support from a database does not remove these PostGIS Raster environment variables from the lightdb_a_path.sh file: GDAL_DATA, POSTGIS_ENABLE_OUTDB_RASTERS, POSTGIS_GDAL_ENABLED_DRIVERS. The environment variables are removed when you uninstall the PostGIS extension package.

Caution Removing PostGIS support from a database drops PostGIS database objects from the database without warning. Users accessing PostGIS objects might interfere with the dropping of PostGIS objects. See Notes.

Using the DROP EXTENSION Command

Depending on the extensions you enabled for PostGIS, drop support for the extensions in the database.

1. If you enabled the address standardizer and sample rules tables, these commands drop support for those extensions from the current database.

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

2. If you enabled the TIGER geocoder and the fuzzystrmatch extension to use the TIGER geocoder, these commands drop support for those extensions.

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

3. Drop support for PostGIS and PostGIS Raster. This command drops support for those extensions.

   DROP EXTENSION IF EXISTS postgis;
   

   If you enabled support for PostGIS and specified a specific schema with the CREATE EXTENSION command, you can update the search_path and drop the PostGIS schema if required.

Uninstalling the LightDB-A PostGIS Extension Package

After PostGIS support has been removed from all databases in the LightDB-A Database system, you can remove the PostGIS extension package. For example, this gppkg command removes the PostGIS extension package.

gppkg -r postgis-2.5.4+pivotal.2

After removing the package, ensure that these lines for PostGIS Raster support are removed from the lightdb_a_path.sh file.

export GDAL_DATA=$GPHOME/share/gdal
export POSTGIS_ENABLE_OUTDB_RASTERS=0
export POSTGIS_GDAL_ENABLED_DRIVERS=DISABLE_ALL

Source the lightdb_a_path.sh file and restart LightDB-A Database. This command restarts LightDB-A Database.

gpstop -ra

Notes

Removing PostGIS support from a database drops PostGIS objects from the database. Dropping the PostGIS objects cascades to objects that reference the PostGIS objects. Before removing PostGIS support, ensure that no users are accessing the database. Users accessing PostGIS objects might interfere with dropping PostGIS objects.

For example, this CREATE TABLE command creates a table with column b that is defined with the PostGIS geometry data type.

# CREATE TABLE test(a int, b geometry) DISTRIBUTED RANDOMLY;

This is the table definition in a database with PostGIS enabled.

# \d test
 Table "public.test"
 Column |   Type   | Modifiers
--------+----------+-----------
 a      | integer  |
 b      | geometry |
Distributed randomly

This is the table definition in a database after PostGIS support has been removed.

# \d test
  Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Distributed randomly

Usage

The following example SQL statements create non-OpenGIS tables and geometries.

CREATE TABLE geom_test ( gid int4, geom geometry, 
  name varchar(25) );

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 INTO geom_test ( gid, geom, name ) 
  VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' );
INSERT INTO geom_test ( gid, geom, name )
  VALUES ( 3, 'MULTIPOINT(3 4,8 9)', '2D Aggregate Point' );

SELECT * from geom_test WHERE geom &&
  Box3D(ST_GeomFromEWKT('LINESTRING(2 2 0, 3 3 0)'));

The following example SQL statements create a table and add a geometry column to the table with a SRID integer value that references an entry in the SPATIAL_REF_SYS table. The INSERT statements add two geopoints to the table.

CREATE TABLE geotest (id INT4, name VARCHAR(32) );
SELECT AddGeometryColumn('geotest','geopoint', 4326,'POINT',2);

INSERT INTO geotest (id, name, geopoint)
  VALUES (1, 'Olympia', ST_GeometryFromText('POINT(-122.90 46.97)', 4326));
INSERT INTO geotest (id, name, geopoint)
  VALUES (2, 'Renton', ST_GeometryFromText('POINT(-122.22 47.50)', 4326));

SELECT name,ST_AsText(geopoint) FROM geotest;

Spatial Indexes

PostgreSQL provides support for GiST spatial indexing. The GiST scheme offers indexing even on large objects. It uses a system of lossy indexing in which smaller objects act as proxies for larger ones in the index. In the PostGIS indexing system, all objects use their bounding boxes as proxies in the index.

Building a Spatial Index

You can build a GiST index as follows:

CREATE INDEX indexname
  ON tablename
  USING GIST ( geometryfield );

PostGIS Extension Support and Limitations

This section describes LightDB-A PostGIS extension feature support and limitations.

• Supported PostGIS Data Types
• Supported PostGIS Raster Data Types
• Supported PostGIS Index
• PostGIS Extension Limitations

In general, the LightDB-A PostGIS extension does not support the following features:

• The PostGIS topology extension postgis_topology
• The PostGIS 3D and geoprocessing extension postgis_sfcgal
• A small number of user defined functions and aggregates
• PostGIS long transactions

For the PostGIS extensions supported by LightDB-A PostGIS, see LightDB-A PostGIS Extension.

Supported PostGIS Data Types

LightDB-A PostGIS extension supports these PostGIS data types:

• box2d
• box3d
• geometry
• geography

For a list of PostGIS data types, operators, and functions, see the PostGIS reference documentation.

Supported PostGIS Raster Data Types

LightDB-A PostGIS supports these PostGIS Raster data types.

• geomval
• addbandarg
• rastbandarg
• raster
• reclassarg
• summarystats
• unionarg

For information about PostGIS Raster data Management, queries, and applications, see https://postgis.net/docs/manual-2.5/using_raster_dataman.html.

For a list of PostGIS Raster data types, operators, and functions, see the PostGIS Raster reference documentation.

Supported PostGIS Index

LightDB-A PostGIS extension supports the GiST (Generalized Search Tree) index.

PostGIS Extension Limitations

This section lists the LightDB-A PostGIS extension limitations for user-defined functions (UDFs), data types, and aggregates.

• Data types and functions related to PostGIS topology functionality, such as TopoGeometry, are not supported by LightDB-A Database.

• These PostGIS aggregates are not supported by LightDB-A Database:

  • ST_Collect
  • ST_MakeLine

  On a LightDB-A Database with multiple segments, the aggregate might return different answers if it is called several times repeatedly.

• LightDB-A Database does not support PostGIS long transactions.

  PostGIS relies on triggers and the PostGIS table public.authorization_table for long transaction support. When PostGIS attempts to acquire locks for long transactions, LightDB-A Database reports errors citing that the function cannot access the relation, authorization_table.

• LightDB-A Database does not support type modifiers for user defined types.

  The workaround is to use the AddGeometryColumn function for PostGIS geometry. For example, a table with PostGIS geometry cannot be created with the following SQL command:

  CREATE TABLE geometries(id INTEGER, geom geometry(LINESTRING));
  

  Use the AddGeometryColumn function to add PostGIS geometry to a table. For example, these following SQL statements create a table and add PostGIS geometry to the table:

  CREATE TABLE geometries(id INTEGER);
  SELECT AddGeometryColumn('public', 'geometries', 'geom', 0, 'LINESTRING', 2);
  

• The _postgis_index_extent function is not supported on LightDB-A Database 6 due to its dependence on spatial index operations.

• The <-> operator (geometry <-> geometry) returns the centroid/centroid distance for LightDB-A Database 6.

• The TIGER geocoder extension is supported. However, upgrading the TIGER geocoder extension is not supported.

• The standardize_address() function uses lex, gaz or rules tables as parameters. If you are using tables apart from us_lex, us_gaz or us_rules, you should create them with the distribution policy DISTRIBUTED REPLICATED to work for LightDB-A.