PostGIS/PostgreSQL

Last Updated:2014-11-06

PostGIS/PostgreSQL

PostGIS spatially enables the Open Source PostgreSQL database.

The PostGIS wiki page may include additional information.

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.

Le paramètre CONNECTIONTYPE doit être défini à POSTGIS.

Le paramètre CONNECTION est utilisé pour spécifier les paramètres pour se connecter à la base de données. Les paramètres de CONNECTION peuvent être dans n’importe quel ordre. La plupart sont en option. Le nom de la base de données est nécessaire. L’utilisateur est requis. host par défaut à localhost, port par défaut à 5432 (le port standard pour 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.

Voici un exemple générique simple :

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"

Exemples OGRINFO

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

D’abord, vous devez vous assurer que votre version de GDAL/OGR contient le pilote PostgreSQL, en utilisant la commande ‘–formats’ :

>ogrinfo --formats
  Loaded OGR Format Drivers:
  ...
  -> "PGeo" (readonly)
  -> "PostgreSQL" (read/write)
  -> "MySQL" (read/write)
  ...

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

Une fois que vous avez le pilote vous êtes prêt à essayer une commande ogrinfo sur votre base de données pour obtenir une liste des tables spatiales :

>ogrinfo PG:"host=127.0.0.1 user=postgres password=postgres dbname=canada port=5432"
      using driver `PostgreSQL' successful.
      1: province (Multi 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: Multi Polygon
  Feature Count: 1068
  Extent: (-2340603.750000, -719746.062500) - (3009430.500000, 3836605.250000)
  Layer SRS WKT:
  (unknown)
  FID Column = gid
  Geometry Column = the_geom
  area: Real (0.0)
  island: String (30.0)
  island_e: String (30.0)
  island_f: String (30.0)
  name: String (30.0)
  ...

Exemple de mapfile

LAYER
  NAME "province"
  STATUS ON
  TYPE POLYGON
  CONNECTIONTYPE POSTGIS
  CONNECTION "host=127.0.0.1 port=5432 dbname=canada user=postgres password=postgres"
  DATA "the_geom from province"
  CLASS
     ...
  END
END

Pour plus d’informations à propos de PostGIS et MapServer regardez les docs de PostGIS : http://postgis.net/documentation/

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:

../../_images/circularstring.png

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:

../../_images/compoundcurve.png

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:

../../_images/curvepolygon.png

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:

../../_images/multicurve.png

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:

../../_images/multisurface.png

Utilisation d’une version de 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