PostgreSQL ORG GIS fdw 外部表支持pushdown - 支持多数据源（file,jdbc,odbc,oci,ms sql,pg,mongo等等）

作者

digoal

日期

2019-12-06

标签

PostgreSQL , orgfdw , org , pushdown

背景

OGR is the vector half of the GDAL spatial data access library. It allows access to a large number of GIS data formats using a simple C API for data reading and writing. Since OGR exposes a simple table structure and PostgreSQL foreign data wrappers allow access to table structures, the fit seems pretty perfect.

ORG 是gdal空间数据访问lib库吗，支持访问大量gis数据格式，包括关系数据库，web，file，jdbc，oci等等，详见如下：

https://gdal.org/drivers/vector/index.html

通过ogr 接口，甚至还能访问mongo, ms sql, oracle等数据库.

PostgreSQL ogr_fdw，除了能访问gis数据源，同样还能访问mongo, ms sql, oracle等数据库.

目前ogr_fdw外部表插件支持了pushdown，where条件可以下推到远程执行。

例子

安装，依赖postgis

-- Install the required extensions CREATE EXTENSION postgis; CREATE EXTENSION ogr_fdw;

使用ogr_fdw_info 查询当前ogr安装环境支持的gdal 空间数据格式

```

ogr_fdw_info -f

Supported Formats:  
    -> "PCIDSK" (read/write)  
    -> "netCDF" (read/write)  
    ...  
    -> "HTTP" (readonly)
复制

```

For a test data set, copy the pt_two example shape file from the data directory to a location where the PostgreSQL server can read it (like /tmp/test/ for example).

查询/tmp/test下支持的shape文件

```

ogr_fdw_info -s /tmp/test

Layers:  
    pt_two
复制

```

转换为fdw格式

```

ogr_fdw_info -s /tmp/test -l pt_two

CREATE SERVER myserver  
    FOREIGN DATA WRAPPER ogr_fdw  
    OPTIONS (  
        datasource '/tmp/test',  
        format 'ESRI Shapefile' );

CREATE FOREIGN TABLE pt_two (  
    fid integer,  
    geom geometry(Point, 4326),  
    name varchar,  
    age integer,  
    height real,  
    birthdate date )  
    SERVER myserver  
    OPTIONS (layer 'pt_two');
复制

```

在数据库中执行以上sql，创建外部表

Foreign table "public.pt_two" Column | Type | Modifiers | FDW Options ----------+-------------------+-----------+------------- fid | integer | | geom | geometry | | name | character varying | | age | integer | | height | real | | birthday | date | | Server: tmp_shape FDW Options: (layer 'pt_two')

通过fdw外部表查询shape文件内容

SELECT * FROM pt_two; fid | geom | name | age | height | birthday -----+--------------------------------------------+-------+-----+--------+------------ 0 | 0101000000C00497D1162CB93F8CBAEF08A080E63F | Peter | 45 | 5.6 | 1965-04-12 1 | 010100000054E943ACD697E2BFC0895EE54A46CF3F | Paul | 33 | 5.84 | 1971-03-25

调试ogr_fdw

```
SET client_min_messages = debug1;

SELECT name, age, height
FROM pt_two
WHERE height < 5.7
AND geom && ST_MakeEnvelope(0, 0, 1, 1);
```

查询debug输出如下

```
DEBUG: OGR SQL: (height < 5.7)
DEBUG: OGR spatial filter (0 0, 1 1)

name | age | height
-------+-----+--------
Peter | 45 | 5.6
(1 row)
```

wfs gdal gis数据源例子

Since we can access any OGR data source as a table, how about a public WFS server?

```
CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;

CREATE SERVER geoserver  
    FOREIGN DATA WRAPPER ogr_fdw  
    OPTIONS (
复制

datasource 'WFS:https://demo.geo-solutions.it/geoserver/wfs',
format 'WFS' );

CREATE FOREIGN TABLE topp_states (  
    fid bigint,  
    the_geom Geometry(MultiSurface,4326),  
    gml_id varchar,  
    state_name varchar,  
    state_fips varchar,  
    sub_region varchar,  
    state_abbr varchar,  
    land_km double precision,  
    water_km double precision,  
    persons double precision,  
    families double precision,  
    houshold double precision,  
    male double precision,  
    female double precision,  
    workers double precision,  
    drvalone double precision,  
    carpool double precision,  
    pubtrans double precision,  
    employed double precision,  
    unemploy double precision,  
    service double precision,  
    manual double precision,  
    p_male double precision,  
    p_female double precision,  
    samp_pop double precision  
) SERVER "geoserver"  
OPTIONS (layer 'topp:states');
复制

```

wfs gdal gis数据源例子2

```
CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;

CREATE SERVER wfsserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'WFS:http://geoservices.informatievlaanderen.be/overdrachtdiensten/Haltes/wfs',
format 'WFS',
config_options 'CPL_DEBUG=ON'
);

CREATE FOREIGN TABLE haltes (
fid bigint,
shape Geometry(Point,31370),
gml_id varchar,
uidn double precision,
oidn double precision,
stopid double precision,
naamhalte varchar,
typehalte integer,
lbltypehal varchar,
codegem varchar,
naamgem varchar
)
SERVER wfsserver
OPTIONS (
layer 'Haltes:Halte'
);
```

调试信息

```
SET client_min_messages = DEBUG1;

SELECT gml_id, ST_AsText(shape) AS shape, naamhalte, lbltypehal
FROM haltes
WHERE lbltypehal = 'Niet-belbus'
AND shape && ST_MakeEnvelope(207950, 186590, 207960, 186600, 31370);
We get back one record, and two debug entries:

DEBUG: OGR SQL: (LBLTYPEHAL = 'Niet-belbus')
DEBUG: OGR spatial filter (207950 186590, 207960 186600)
-[ RECORD 1 ]-----------------------
gml_id | Halte.10328
shape | POINT(207956 186596)
naamhalte | Lummen Frederickxstraat
lbltypehal | Niet-belbus
```

pushdown例子

```
SET client_min_messages = DEBUG2;

SELECT gml_id, ST_AsText(shape) AS shape, naamhalte, lbltypehal
FROM haltes
WHERE lbltypehal = 'Niet-belbus'
AND shape && ST_MakeEnvelope(207950, 186590, 207960, 186600, 31370);
```

日志

DEBUG: GDAL None [0] WFS: http://geoservices.informatievlaanderen.be/overdrachtdiensten/Haltes/wfs?SERVICE=WFS&VERSION=1.1.0&REQUEST=GetFeature&TYPENAME=Haltes:Halte&FILTER=%3CFilter%20xmlns%3D%22http:%2F%2Fwww.opengis.net%2Fogc%22%20xmlns:Haltes%3D%22informatievlaanderen.be%2FHaltes%22%20xmlns:gml%3D%22http:%2F%2Fwww.opengis.net%2Fgml%22%3E%3CAnd%3E%3CPropertyIsEqualTo%3E%3CPropertyName%3ELBLTYPEHAL%3C%2FPropertyName%3E%3CLiteral%3ENiet%2Dbelbus%3C%2FLiteral%3E%3C%2FPropertyIsEqualTo%3E%3CBBOX%3E%3CPropertyName%3EHaltes:SHAPE%3C%2FPropertyName%3E%3Cgml:Box%3E%3Cgml:coordinates%3E207950.0000000000000000,186590.0000000000000000%20207960.0000000000000000,186600.0000000000000000%3C%2Fgml:coordinates%3E%3C%2Fgml:Box%3E%3C%2FBBOX%3E%3C%2FAnd%3E%3C%2FFilter%3E

转换后可以看到格式如下

<Filter xmlns="http://www.opengis.net/ogc" xmlns:Haltes="informatievlaanderen.be/Haltes" xmlns:gml="http://www.opengis.net/gml"> <And> <PropertyIsEqualTo> <PropertyName>LBLTYPEHAL</PropertyName> <Literal>Niet-belbus</Literal> </PropertyIsEqualTo> <BBOX> <PropertyName>Haltes:SHAPE</PropertyName> <gml:Box> <gml:coordinates> 207950.0000000000000000,186590.0000000000000000 207960.0000000000000000,186600.0000000000000000 </gml:coordinates> </gml:Box> </BBOX> </And> </Filter>

条件下推到了远程wfs服务.

参考

https://github.com/pramsey/pgsql-ogr-fdw

https://github.com/pramsey/pgsql-ogr-fdw/commit/a7d277b0e4a18a4a526344430958cd6d211c4d25

http://blog.cleverelephant.ca/2019/11/ogr-fdw-spatial-filter.html

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.