Postgis/PostgreSQL

作者

杰夫麦克纳

联系

jmckenna在gatewaygeomatics.com

最后更新

2022-04-25

目录

• Postgis/PostgreSQL

  • Postgis/PostgreSQL

  • 数据访问/连接方法

  • Ogrinfo示例

  • Mapfile 示例

  • PostGIS栅格(PGRaster)

    • 加载栅格数据

    • 与GDALINFO连接

    • Mapfile 层

    • 使用shp2img进行测试

  • 支持2.5d几何图形

  • 支持sql/mm曲线

    • 示例1:MapServer中的循环字符串

    • 示例2:MapServer中的复合曲线

    • 示例3:MapServer中的曲线多边形

    • 示例4:MapServer中的多曲线

    • 示例5:MapServer中的多曲面

    • 使用MapServer<6.0

Postgis/PostgreSQL

PostGIS 在空间上启用开放源码 PostgreSQL 数据库。

这个 PostGIS wiki page 可能包括其他信息。

小技巧

确保已将扩展加载到数据库中： 创建扩展PostGIS；

小技巧

如果您已升级PostGIS，请确保在数据库上执行以下操作： SELECT postgis_extensions_upgrade();

参见

矢量数据管理与优化

数据访问/连接方法

Postgis由MapServer直接支持，必须编译到MapServer才能工作。

PostgreSQL客户端库( libpq.so 或 libpq.dll )必须存在于系统的路径环境中，才能提供功能。

这个 CONNECTIONTYPE 参数必须设置为POSTGIS。

这个 CONNECTION 参数用于指定连接到数据库的参数。连接参数可以采用任何顺序。大多数是可选的。 数据库名 是必需的。 user 是必需的。 host 默认为本地主机， port 默认为5432(PostgreSQL的标准端口)。

数据参数用于指定用于绘制地图的数据。数据的形式是“[表名SQL U子查询]中的[几何体_列]使用唯一的[唯一的_键]使用srid=[空间_引用_id]”。绘制功能时，“使用唯一”和“使用srid=”子句是可选的，但使用它们可以提高性能。如果要对PostGIS层进行MapServer查询调用，则数据参数必须包含“using unique”。省略它将导致查询失败。

小技巧

如果您正在尝试执行查询并接收消息 "ERROR: column oid does not exist at character..." 很可能您错过了 using unique 数据参数中的一部分。如果您没有指定唯一的ID列，那么MapServer将尝试查找一个ID列(代价是再执行几个查询)。因此，最好是 始终 在数据语句中指定唯一ID列。如果您的表没有唯一ID列，则可以添加一个列，如下所示：

ALTER TABLE mytable ADD COLUMN unique_id SERIAL PRIMARY KEY;

警告

从PostgreSQL 12.0版开始，从PostgreSQL中删除了OID。建议在图层的DATA语句中为映射文件指定不同的唯一ID列。

备注

通过使用支持变量绑定 BINDVALS 和名称值对。

下面是一个简单的通用示例：

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

此示例显示如何在数据行中指定唯一键和srid：

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

此示例显示如何使用SQL子查询在数据库内执行联接并将结果映射到MapServer。注意语句中的“as subquery”字符串——从“from”到“using”之间的所有内容都将发送到数据库进行评估：

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"

此示例显示如何使用几何函数和数据库排序来限制返回到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"

这个例子展示了的用法！盒子！替换字符串以覆盖SQL中映射边界框的默认包含。默认情况下，空间框子句将附加到数据子句中的SQL，但您可以使用！盒子！在语句中任意位置插入它。一般来说，您不需要使用！盒子！，因为PostgreSQL规划器将从生成的SQL生成最佳计划，但在某些情况下（复杂的子查询），通过放置可以生成更好的计划！盒子！靠近查询中间：

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示例

可以使用ogrinfo直接从数据库中读取关于postgis表的元数据。

首先，应使用“--formats”命令确保GDAL/OGR构建包含PostgreSQL驱动程序：

>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
  ...

如果你没有司机，你可能想试试 FWTools 或 MS4W 包，其中包括驱动程序。

一旦您有了驱动程序，就可以尝试在数据库上使用ogrinfo命令来获取空间表列表：

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

现在使用ogrinfo获取有关空间表结构的信息：

>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 示例

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"
  EXTENT -2340603.750000 -719746.062500 3009430.500000 3836605.250000 # for maximum performance
  PROCESSING "CLOSE_CONNECTION=DEFER" # for maximum performance
  CLASS
    NAME "Provincial Land"
    STYLE
      COLOR 240 240 240
      OUTLINECOLOR 199 199 199
    END
  END
  PROJECTION #source
    "init=epsg:3978"
  END
END

有关postgis和地图服务器的更多信息，请参阅postgis文档：https://postgis.net/documentation/

PostGIS栅格(PGRaster)

从PostGIS 2.0版本开始，PostGIS包括对栅格数据的支持，可以通过MapServer(通过GDAL)访问栅格数据。

小技巧

确保已将扩展加载到数据库中： CREATE EXTENSION postgis_raster;

加载栅格数据

您必须使用 栅格2pgsql 要将栅格数据加载到PostGIS中(请参见 postgis.net 以供使用)，例如：

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

以上将创造一个新的 lunenburg_colorhillshade 来自GeoTIFF文件的表。

在MapServer中显示的要点：

• 请务必将 -t 切换以平铺栅格

• 请务必将 -I 切换以在栅格上创建概要索引

• 请务必将 -C 切换以应用栅格约束

• 请务必将 -F 切换以添加具有栅格文件名称的列

与GDALINFO连接

使用 gdalinfo 用于列出数据库中可用栅格表的实用程序，例如：

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

它返回类似如下的内容：

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)
  ...

接下来，指定 表格 和 立柱 ，使用 -nomd 交换机，例如：

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

它返回栅格的摘要，包括其投影 (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 层

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

备注

该参数 mode=2 必须在数据连接字符串中指定，才能使MapServer正确加载，因为栅格是平铺的。请参阅 PostGIS Raster Driver 有关更多信息，请访问页面。

使用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

支持2.5d几何图形

除了水平坐标（X、Y或经度、纬度）之外，PostGIS还可以支持具有垂直分量的几何图形，通常称为2.5D几何图形。

从mapserver 7.0开始，如果mapserver是在-dwith_point_z_m=on上构建的，则会考虑这样的2.5d几何图形。

备注

WFS中2.5D几何图形的输出要求在层级别指定显式元数据项。见 WFS server 文档。

备注

通过设置以下处理选项，仍然可以强制仅从PostGIS检索二维几何图形。

PROCESSING "FORCE2D=YES"

支持sql/mm曲线

PostGIS能够存储圆形内插曲线，作为SQL多媒体应用程序空间规范的一部分（阅读 SQL/MM specification ）

有关PostGIS支持的更多信息，请参阅 SQL-MM Part 3 部分，例如 here 。

从mapserver 6.0开始，postgis的特点是可以通过mapserver直接绘制出循环字符串、复合曲线、曲线多边形、多曲线和多曲面。

示例1:MapServer中的循环字符串

以下是加载到PostGIS中的功能的已知文本：

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

MAPServer层的示例如下：

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

和测试 Sp2IMG 应生成以下地图图像：

示例2:MapServer中的复合曲线

以下是加载到PostGIS中的功能的已知文本：

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

MAPServer层的示例如下：

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

和测试 Sp2IMG 应生成以下地图图像：

示例3:MapServer中的曲线多边形

以下是加载到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);

MAPServer层的示例如下：

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

和测试 Sp2IMG 应生成以下地图图像：

示例4:MapServer中的多曲线

以下是加载到PostGIS中的功能的已知文本：

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

MAPServer层的示例如下：

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

和测试 Sp2IMG 应生成以下地图图像：

示例5:MapServer中的多曲面

以下是加载到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);

MAPServer层的示例如下：

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

和测试 Sp2IMG 应生成以下地图图像：

使用MapServer<6.0

如果无法升级到MapServer 6.0，则可以使用MapServer层中的postgis函数*st_curveToline（）*绘制曲线（请注意，这要慢得多）：

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