PostGIS/PostgreSQL ¶

Author: Jeff McKenna
Contact: jmckenna at gatewaygeomatics.com
Last Updated: 2022-01-25

Table of Contents

PostGIS/PostgreSQL

PostGIS/PostgreSQL ¶

PostGIS spatially enables the Open Source PostgreSQL database.

The PostGIS wiki page may include additional information.

Tip

Be sure that you have loaded the extension into your database: CREATE EXTENSION postgis;

Tip

If you have upgraded PostGIS, be sure to execute the following on your database: SELECT postgis_extensions_upgrade();

Data Access /Connection Method ¶

PostGIS is supported directly by MapServer and must be compiled into MapServer to work.

The PostgreSQL client libraries (libpq.so or libpq.dll) must be present in the system’s path environment for functionality to be present.

The CONNECTIONTYPE parameter must be set to POSTGIS.

The CONNECTION parameter is used to specify the parameters to connect to the database. CONNECTION parameters can be in any order. Most are optional. dbname is required. user is required. host defaults to localhost, port defaults to 5432 (the standard port for PostgreSQL).

The DATA parameter is used to specify the data used to draw the map. The form of DATA is “[geometry_column] from [table_name|sql_subquery] using unique [unique_key] using srid=[spatial_reference_id]”. The “using unique” and “using srid=” clauses are optional when drawing features, but using them improves performance. If you want to make MapServer query calls to a PostGIS layer, your DATA parameter must include “using unique”. Omitting it will cause the query to fail.

Tip

If you are trying to execute a query and receive the message “ERROR: column oid does not exist at character…” it is likely that you are missing the using unique part in your DATA parameter. If you don’t specify a unique ID column, then MapServer will try to look for one (at the cost of executing a few more queries). It is therefore best to always specify a unique ID column in your DATA statement. If your table does not have a unique ID column, you can add one such as:

ALTER TABLE mytable ADD COLUMN unique_id SERIAL PRIMARY KEY;

Warning

OIDs were removed from PostgreSQL as of the PostgreSQL 12.0 release. It is recommended to specify a different unique ID column instead, in your layer’s DATA statement, of your mapfile.

Note

Variable binding is supported by using BINDVALS and name value pairs.

Here is a simple generic example:

CONNECTIONTYPE POSTGIS
CONNECTION "host=yourhostname dbname=yourdatabasename user=yourdbusername
            password=yourdbpassword port=yourpgport"
DATA "geometrycolumn from yourtablename"

This example shows specifying the unique key and srid in the DATA line:

CONNECTIONTYPE POSTGIS
CONNECTION "dbname=yourdatabasename user=yourdbusername"
DATA "the_geom from the_database using unique gid using srid=4326"

This example shows using a SQL subquery to perform a join inside the database and map the result in MapServer. Note the “as subquery” string in the statement – everything between “from” and “using” is sent to the database for evaluation:

CONNECTIONTYPE POSTGIS
CONNECTION "dbname=yourdatabasename user=yourdbusername"
DATA "the_geom from (select g.gid, g.the_geom, a.attr1, a.attr2 from
      geotable g join attrtable a on g.gid = a.aid) as subquery
      using unique gid using srid=4326"

This example shows using a geometry function and database sort to limit the number of features and vertices returned to MapServer:

CONNECTIONTYPE POSTGIS
CONNECTION "dbname=yourdatabasename user=yourdbusername"
DATA "the_geom from (select g.gid, ST_Simplify(g.the_geom, 10.0) as
      the_geom from geotable g order by ST_Area(g.the_geom) desc
      limit 10) as subquery using unique gid using srid=4326"

This example shows the use of the !BOX! substitution string to over-ride the default inclusion of the map bounding box in the SQL. By default the spatial box clause is appended to the SQL in the DATA clause, but you can use !BOX! to insert it anywhere you like in the statement. In general, you won’t need to use !BOX!, because the PostgreSQL planner will generate the optimal plan from the generated SQL, but in some cases (complex sub-queries) a better plan can be generated by placing the !BOX! closer to the middle of the query:

CONNECTIONTYPE POSTGIS
CONNECTION "dbname=yourdatabasename user=yourdbusername"
DATA "the_geom from (select g.gid, ST_Union(g.the_geom, 10.0) as
      the_geom from geotable g where ST_Intersects(g.geom,!BOX!)) as
      subquery using unique gid using srid=4326"

OGRINFO Examples ¶

OGRINFO can be used to read out metadata about PostGIS tables directly from the database.

First you should make sure that your GDAL/OGR build contains the PostgreSQL driver, by using the ‘–formats’ command:

>ogrinfo --formats

  Supported formats:
  ...
    PGeo -vector- (ro): ESRI Personal GeoDatabase
    MSSQLSpatial -vector- (rw+): Microsoft SQL Server Spatial Database
    PostgreSQL -vector- (rw+): PostgreSQL/PostGIS
    MySQL -vector- (rw+): MySQL
  ...

If you don’t have the driver, you might want to try the FWTools or MS4W packages, which include the driver.

Once you have the driver you are ready to try an ogrinfo command on your database to get a list of spatial tables:

>ogrinfo PG:"host=127.0.0.1 user=postgres password=postgres dbname=canada port=5432"
      using driver `PostgreSQL' successful.
      1: province (Polygon)

Now use ogrinfo to get information on the structure of the spatial table:

>ogrinfo PG:"host=127.0.0.1 user=postgres password=postgres dbname=canada port=5432" province -summary

 INFO: Open of `PG:host=127.0.0.1 user=postgres password=postgres dbname=canada'
    using driver `PostgreSQL' successful.

  Layer name: province
  Geometry: Polygon
  Feature Count: 1068
  Extent: (-2340603.750000, -719746.062500) - (3009430.500000, 3836605.250000)
  Layer SRS WKT:
  PROJCS["NAD83 / Canada Atlas Lambert",
      GEOGCS["NAD83",
          DATUM["North_American_Datum_1983",
              SPHEROID["GRS 1980",6378137,298.257222101,
                  AUTHORITY["EPSG","7019"]],
              TOWGS84[0,0,0,0,0,0,0],
              AUTHORITY["EPSG","6269"]],
          PRIMEM["Greenwich",0,
              AUTHORITY["EPSG","8901"]],
          UNIT["degree",0.0174532925199433,
              AUTHORITY["EPSG","9122"]],
          AUTHORITY["EPSG","4269"]],
      PROJECTION["Lambert_Conformal_Conic_2SP"],
      PARAMETER["standard_parallel_1",49],
      PARAMETER["standard_parallel_2",77],
      PARAMETER["latitude_of_origin",49],
      PARAMETER["central_meridian",-95],
      PARAMETER["false_easting",0],
      PARAMETER["false_northing",0],
      UNIT["metre",1,
          AUTHORITY["EPSG","9001"]],
      AXIS["Easting",EAST],
      AXIS["Northing",NORTH],
      AUTHORITY["EPSG","3978"]]
  FID Column = ogc_fid
  Geometry Column = wkb_geometry
  ...

Mapfile Example ¶

LAYER
  NAME "province"
  STATUS ON
  TYPE POLYGON
  CONNECTIONTYPE POSTGIS
  CONNECTION "host=127.0.0.1 user=postgres password=postgres port=5432 dbname=canada"
  DATA "wkb_geometry FROM province USING UNIQUE ogc_fid USING srid=3978"
  CLASS
    NAME "Provincial Land"
    STYLE
      COLOR 240 240 240
      OUTLINECOLOR 199 199 199
    END
  END
  PROJECTION #source
    "init=epsg:3978"
  END
END

For more info about PostGIS and MapServer see the PostGIS docs: https://postgis.net/documentation/

PostGIS Raster (PGRaster)¶

Since the PostGIS 2.0 release, PostGIS includes support for raster data, which can be accessed by MapServer (through GDAL).

Tip

Be sure that you have loaded the extension into your database: CREATE EXTENSION postgis_raster;

Load Raster DATA ¶

You must use raster2pgsql to load your raster data into PostGIS (see postgis.net for usage), such as:

raster2pgsql -s 2961 -I -C -M -F -t auto -l 2,4,8 colorhillshade_dtm_1m_utm20_w_10_91.tif lunenburg_colorhillshade > raster.sql
psql -U postgres -p 5432 -d mydb -f raster.sql

The above will create a new lunenburg_colorhillshade table from a GeoTIFF file.

Important points for display in MapServer:

be sure to set the -t switch to tile the raster
be sure to set the -I switch to create a GiST index on the raster
be sure to set the -C switch to apply raster constraints
be sure to set the -F switch to add a column with the name of the raster file

Connect with GDALINFO ¶

Use the gdalinfo utility to list the available raster tables in your database, such as:

gdalinfo PG:"host=127.0.0.1 port=5432 user=postgres password=postgres dbname=mydb"

which returns something like:

Driver: PostGISRaster/PostGIS Raster driver

Subdatasets:
  ...
    SUBDATASET_3_NAME=PG:host=127.0.0.1 port=5432 user=postgres password=postgres dbname=mydb  schema='public' table='lunenburg_colorhillshade' column='rast'
    SUBDATASET_3_DESC=PostGIS Raster table at public.lunenburg_colorhillshade (rast)
  ...

Next specify the table and column, with the -nomd switch, such as:

gdalinfo PG:"host=127.0.0.1 port=5432 user=postgres password=postgres dbname=mydb table=lunenburg_colorhillshade column=rast" -nomd

which returns a summary of the raster, including its projection (EPSG:2961) :

Driver: PostGISRaster/PostGIS Raster driver
Coordinate System is:
PROJCS["NAD83(CSRS) / UTM zone 20N",
    GEOGCS["NAD83(CSRS)",
        DATUM["NAD83_Canadian_Spatial_Reference_System",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            TOWGS84[0,0,0,0,0,0,0],
            AUTHORITY["EPSG","6140"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4617"]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",0],
    PARAMETER["central_meridian",-63],
    PARAMETER["scale_factor",0.9996],
    PARAMETER["false_easting",500000],
    PARAMETER["false_northing",0],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["Easting",EAST],
    AXIS["Northing",NORTH],
    AUTHORITY["EPSG","2961"]]

Mapfile Layer ¶

The mapfile layer is similar to other raster layers, but the PostGIS connection parameters are specified in the DATA parameter, such as:

LAYER
  NAME "lunenburg_raster"
  TYPE RASTER
  STATUS ON
  DATA "PG:host=127.0.0.1 port=5432 sslmode=disable user=postgres password=postgres dbname=mydb schema=public table=lunenburg_colorhillshade column=rast mode=2"
  PROJECTION
    "init=epsg:2961"
  END
  CLASS
    NAME "Lunenburg"
  END
  PROCESSING "CLOSE_CONNECTION=DEFER"
END # layer

Note

The parameter mode=2 must be specified in the DATA connection string, for MapServer to load this properly, as the raster is tiled. See the PostGIS Raster Driver page for more info.

Test with shp2img ¶

shp2img -m postgis.map -o ttt.png -map_debug 3

  msDrawMap(): rendering using outputformat named png (AGG/PNG).
  msDrawMap(): WMS/WFS set-up and query, 0.000s
  msDrawRasterLayerLow(lunenburg_raster): entering.
  msDrawMap(): Layer 1 (lunenburg_raster), 0.355s
  msDrawMap(): Drawing Label Cache, 0.000s
  msDrawMap() total time: 0.356s
  msSaveImage(ttt.png) total time: 0.004s

Support for 2.5D geometries ¶

In addition to horizontal coordinates (X,Y or longitude,latitude), PostGIS can support geometries with a vertical component, often called 2.5D geometries.

As of MapServer 7.0, such 2.5D geometries will be taken into account if MapServer is built with -DWITH_POINT_Z_M=ON.

Note

Output of 2.5D geometries in WFS requires explicit metadata item to be specified at the layer level. See WFS server documentation.

Note

It is still possible to force 2D only geometries to be retrieved from PostGIS by setting the following PROCESSING option

PROCESSING "FORCE2D=YES"

Support for SQL/MM Curves ¶

PostGIS is able to store circular interpolated curves, as part of the SQL Multimedia Applications Spatial specification (read about the SQL/MM specification).

For more information about PostGIS’ support, see the SQL-MM Part 3 section in the PostGIS documentation, such as here.

As of MapServer 6.0, the PostGIS features CircularString, CompoundCurve, CurvePolygon, MultiCurve, and MultiSurface can be drawn through MapServer directly.

Example#1: CircularString in MapServer ¶

The following is the Well Known Text of the feature loading into PostGIS:

INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('CIRCULARSTRING(0 0,
                                    4 0, 4 4, 0 4, 0 0)', -1), 2);

An example MapServer layer might look like:

LAYER
  NAME "curves_poly"
  STATUS DEFAULT
  TYPE POLYGON
  CONNECTIONTYPE postgis
  CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
  DATA "g from test using SRID=-1 using unique id"
  CLASS
    STYLE
      COLOR 128 128 128
      ANTIALIAS true
    END
  END
END

And testing with shp2img should produce a map image of:

Example#2: CompoundCurve in MapServer ¶

The following is the Well Known Text of the feature loading into PostGIS:

INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('COMPOUNDCURVE(
                  CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))', -1), 3);

An example MapServer layer might look like:

LAYER
  NAME "curves_poly"
  STATUS DEFAULT
  TYPE POLYGON
  CONNECTIONTYPE postgis
  CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
  DATA "g from test using SRID=-1 using unique id"
  CLASS
    STYLE
      COLOR 128 128 128
      ANTIALIAS true
    END
  END
END

And testing with shp2img should produce a map image of:

Example#3: CurvePolygon in MapServer ¶

The following is the Well Known Text of the feature loading into PostGIS:

INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('CURVEPOLYGON(
                  CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3,
                  3 1, 1 1))', -1), 4);

An example MapServer layer might look like:

LAYER
  NAME "curves_poly"
  STATUS DEFAULT
  TYPE POLYGON
  CONNECTIONTYPE postgis
  CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
  DATA "g from test using SRID=-1 using unique id"
  CLASS
    STYLE
      COLOR 128 128 128
      ANTIALIAS true
    END
  END
END

And testing with shp2img should produce a map image of:

Example#4: MultiCurve in MapServer ¶

The following is the Well Known Text of the feature loading into PostGIS:

INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('MULTICURVE((0 0,
                  5 5),CIRCULARSTRING(4 0, 4 4, 8 4))', -1), 6);

An example MapServer layer might look like:

LAYER
  NAME "curves_poly"
  STATUS DEFAULT
  TYPE POLYGON
  CONNECTIONTYPE postgis
  CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
  DATA "g from test using SRID=-1 using unique id"
  CLASS
    STYLE
      COLOR 128 128 128
      ANTIALIAS true
    END
  END
END

And testing with shp2img should produce a map image of:

Example#5: MultiSurface in MapServer ¶

The following is the Well Known Text of the feature loading into PostGIS:

INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('MULTISURFACE(
                  CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4,
                  0 0),(1 1, 3 3, 3 1, 1 1)),((10 10, 14 12, 11 10,
                  10 10),(11 11, 11.5 11, 11 11.5, 11 11)))', -1), 7);

An example MapServer layer might look like:

LAYER
  NAME "curves_poly"
  STATUS DEFAULT
  TYPE POLYGON
  CONNECTIONTYPE postgis
  CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
  DATA "g from test using SRID=-1 using unique id"
  CLASS
    STYLE
      COLOR 128 128 128
      ANTIALIAS true
    END
  END
END

And testing with shp2img should produce a map image of:

Using MapServer < 6.0 ¶

If you cannot upgrade to MapServer 6.0, then you can use the PostGIS function ST_CurveToLine() in your MapServer LAYER to draw the curves (note that this is much slower however):

LAYER
  NAME "curves_poly"
  STATUS DEFAULT
  TYPE POLYGON
  CONNECTIONTYPE postgis
  CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
  DATA "wkb_geometry from (select c.id, ST_CurveToLine(c.g) as
                           wkb_geometry from c) as subquery using
                           unique id using SRID=-1"
  CLASS
    STYLE
      COLOR 128 128 128
      ANTIALIAS true
    END
  END
END

DOCS PREVIEW

PostGIS/PostgreSQL ¶

PostGIS/PostgreSQL ¶

Data Access /Connection Method ¶

OGRINFO Examples ¶

Mapfile Example ¶

PostGIS Raster (PGRaster)¶

Load Raster DATA ¶

Connect with GDALINFO ¶

Mapfile Layer ¶

Test with shp2img ¶

Support for 2.5D geometries ¶

Support for SQL/MM Curves ¶

Example#1: CircularString in MapServer ¶

Example#2: CompoundCurve in MapServer ¶

Example#3: CurvePolygon in MapServer ¶

Example#4: MultiCurve in MapServer ¶

Example#5: MultiSurface in MapServer ¶

Using MapServer < 6.0 ¶

Navigation

Current Table Of Contents

	Home \| Products \| Issue Tracker \| Service Providers \| FAQ \| Twitter \| Download \|