Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PostgreSQL: How ORG GIS FDW External Table Supports Pushdown

PostgreSQL: How ORG GIS FDW External Table Supports Pushdown

In this article, the author demonstrates the support for external table pushdown in PostgreSQL and explains how multiple data sources are supported.

By digoal

Background

ORG is the vector half of the GDAL spatial data access library. It helps access many GIS data formats using a simple C API for data reading and writing, including relational databases, web, file, JDBC, and OCI, among others. ORG exposes a simple table structure and PostgreSQL external data wrappers allow access to table structures. For more details on supported vector drivers, navigate to https://gdal.org/drivers/vector/index.html

In addition to the GIS data sources, PostgreSQL ogr_fdw can also help you access MongoDB, MS SQL, Oracle, and other databases.

Currently, the plug-in for ogr_fdw external tables supports pushdown. You can push down the where condition to the remote data source for execution.

Example

First, you should proceed with the installation, which is dependent on PostGIS.

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

Use ogr_fdw_info to query the GDAL spatial data formats that the current ogr installation environment supports.

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

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

Query the shapefiles supported in the /tmp/test directory.

> ogr_fdw_info -s /tmp/test  
  
    Layers:  
        pt_two

Convert the shapefiles into the fdw format.

> 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');

Execute the preceding SQL statements in the database to create an external table.

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

Query the shapefile contents in the fdw external table.

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

Debug 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);

Query debug output is as follows:

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 Data Source Example One

Since you can access any OGR data source as a table, how about accessing 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 Data Source Example Two

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'  
  );

Debug information:

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 Example

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

Log:

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

The format after conversion is as follows:

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

The condition is pushed down to the remote WFS service.

References

PostgreSQL Database Relational databases ogr_fdw ORG Pushdown GDAL
0 0 0
Share on

Read previous post:

Alibaba Cloud: How to Use FIO to Test the IO Performance of ECS Local SSD and ESSD (Part 2)

Read next post:

PostgreSQL v12: How pg_stat_statements Causes High-concurrency Performance Issues

digoal

282 posts | 24 followers

You may also like

Comments

digoal

282 posts | 24 followers

Related Products

More Posts by digoal

See All