[Git][debian-gis-team/pgsql-ogr-fdw][upstream] New upstream version 1.0.10
Bas Couwenberg
gitlab at salsa.debian.org
Wed May 13 07:58:00 BST 2020
Bas Couwenberg pushed to branch upstream at Debian GIS Project / pgsql-ogr-fdw
Commits:
8d1fb1e2 by Bas Couwenberg at 2020-05-13T08:37:06+02:00
New upstream version 1.0.10
- - - - -
14 changed files:
- .editorconfig
- .travis.yml
- README.md
- + ci/gdal_build.sh
- input/file.source
- input/pgsql.source
- + input/postgis.source
- ogr_fdw.c
- ogr_fdw.h
- ogr_fdw_common.h
- ogr_fdw_deparse.c
- output/file.source
- output/pgsql.source
- + output/postgis.source
Changes:
=====================================
.editorconfig
=====================================
@@ -11,8 +11,8 @@ insert_final_newline = true
trim_trailing_whitespace = true
# Test files need kid gloves
-[*.source]
-insert_final_newline = false
+[*.{source,out}]
+insert_final_newline = true
trim_trailing_whitespace = false
# C files want tab indentation
=====================================
.travis.yml
=====================================
@@ -1,4 +1,4 @@
-dist: trusty
+dist: xenial
sudo: required
language: cpp
compiler: gcc
@@ -17,8 +17,8 @@ env:
- PG_VERSION=9.6 GDAL_VERSION=1.11
- PG_VERSION=9.6 GDAL_VERSION=2.2.4
- PG_VERSION=10 GDAL_VERSION=2.3.3
- - PG_VERSION=10 GDAL_VERSION=2.4.1
- - PG_VERSION=11 GDAL_VERSION=2.4.1
+ - PG_VERSION=11 GDAL_VERSION=2.4.4
+ - PG_VERSION=12 GDAL_VERSION=2.4.4
before_script:
- sudo /etc/init.d/postgresql stop
@@ -34,14 +34,17 @@ before_script:
- sudo apt-get -y autoremove
- sudo rm -rf /var/lib/postgresql
- apt-key adv --fetch-keys https://www.postgresql.org/media/keys/ACCC4CF8.asc
- - sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main $PG_VERSION"
+ - sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main $PG_VERSION"
- sudo apt-get update -qq
- sudo apt-get -y install postgresql-$PG_VERSION postgresql-server-dev-$PG_VERSION
- sudo cp ci/pg_hba.conf /etc/postgresql/$PG_VERSION/main/pg_hba.conf
- sudo /etc/init.d/postgresql reload
- - if [[ $GDAL_VERSION == 1* ]]; then sudo apt-get install libgdal1h libgdal-dev; fi
- - if [[ $GDAL_VERSION == 2* ]]; then wget http://download.osgeo.org/gdal/$GDAL_VERSION/gdal-$GDAL_VERSION.tar.xz; tar xJf gdal-$GDAL_VERSION.tar.xz; cd gdal-$GDAL_VERSION; ./configure --prefix=/usr --enable-debug --without-libtool; make -j4 >/dev/null; sudo make install >/dev/null; cd ..; gdalinfo --version; fi
+ - if [[ $GDAL_VERSION == 1* ]]; then sudo apt-get install libgdal1i libgdal-dev; fi
+ - if [[ $GDAL_VERSION == 2* ]]; then bash ci/gdal_build.sh $GDAL_VERSION; fi
+ - if [[ $GDAL_VERSION == 3* ]]; then bash ci/gdal_build.sh $GDAL_VERSION; fi
+ # needs proj6
+
script:
- make && sudo make install && sudo chmod 755 $HOME && (PGUSER=postgres make installcheck || (cat regression.diffs && /bin/false))
=====================================
README.md
=====================================
@@ -4,16 +4,16 @@ Travis: [
## Motivation
-OGR is the vector half of the [GDAL](http://www.gdal.org/) spatial data access library. It allows access to a [large number of GIS data formats](http://www.gdal.org/ogr_formats.html) using a [simple C API](http://www.gdal.org/ogr__api_8h.html) for data reading and writing. Since OGR exposes a simple table structure and PostgreSQL [foreign data wrappers](https://wiki.postgresql.org/wiki/Foreign_data_wrappers) allow access to table structures, the fit seems pretty perfect.
+OGR is the **vector** half of the [GDAL](http://www.gdal.org/) spatial data access library. It allows access to a [large number of GIS data formats](http://www.gdal.org/ogr_formats.html) using a [simple C API](http://www.gdal.org/ogr__api_8h.html) for data reading and writing. Since OGR exposes a simple table structure and PostgreSQL [foreign data wrappers](https://wiki.postgresql.org/wiki/Foreign_data_wrappers) allow access to table structures, the fit seems pretty perfect.
## Limitations
This implementation currently has the following limitations:
-* **PostgreSQL 9.3+** This wrapper does not support the FDW implementations in older versions of PostgreSQL.
-* **Only non-spatial query restrictions are pushed down to the OGR driver.** PostgreSQL foreign data wrappers support delegating portions of the SQL query to the underlying data source, in this case OGR. This implementation currently pushes down only non-spatial query restrictions, and only for the small subset of comparison operators (>, <, <=, >=, =) supported by OGR.
-* **Spatial restrictions are not pushed down.** OGR can handle basic bounding box restrictions and even (for some drivers) more explicit intersection restrictions, but those are not passed to the OGR driver yet.
-* **OGR connections every time** Rather than pooling OGR connections, each query makes (and disposes of) two new ones, which seems to be the largest performance drag at the moment for restricted (small) queries.
+* **PostgreSQL 9.3 or higher.** This wrapper does not support the FDW implementations in older versions of PostgreSQL.
+* **Limited non-spatial query restrictions are pushed down to OGR.** OGR only supports a minimal set of SQL operators (>, <, <=, >=, =).
+* **Only bounding box filters (&&) are pushed down.** Spatial filtering is possible, but only bounding boxes, and only using the && operator.
+* **OGR connections every time** Rather than pooling OGR connections, each query makes (and disposes of) **two** new ones, which seems to be the largest performance drag at the moment for restricted (small) queries.
* **All columns are retrieved every time.** PostgreSQL foreign data wrappers don't require all columns all the time, and some efficiencies can be gained by only requesting the columns needed to fulfill a query. This would be a minimal efficiency improvement, but can be removed given some development time, since the OGR API supports returning a subset of columns.
## Download
@@ -34,71 +34,92 @@ To build the wrapper, make sure you have the GDAL library and development packag
Build the wrapper with `make` and `make install`. Now you are ready to create a foreign table.
First install the `postgis` and `ogr_fdw` extensions in your database.
-
- -- Install the required extensions
- CREATE EXTENSION postgis;
- CREATE EXTENSION ogr_fdw;
-
+```sql
+-- Install the required extensions
+CREATE EXTENSION postgis;
+CREATE EXTENSION ogr_fdw;
+```
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).
Use the `ogr_fdw_info` tool to read an OGR data source and output a server and table definition for a particular layer. (You can write these manually, but the utility makes it a little more foolproof.)
-
- > ogr_fdw_info -f
-
- Supported Formats:
- -> "PCIDSK" (read/write)
- -> "netCDF" (read/write)
- ...
- -> "HTTP" (readonly)
-
-
- > ogr_fdw_info -s /tmp/test
-
- Layers:
- pt_two
-
-
- > 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');
+```
+# ogr_fdw_info -f
+
+Supported Formats:
+ -> "PCIDSK" (read/write)
+ -> "netCDF" (read/write)
+ ...
+ -> "HTTP" (readonly)
+
+# ogr_fdw_info -s /tmp/test
+
+Layers:
+ pt_two
+
+# 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');
+```
Copy the `CREATE SERVER` and `CREATE FOREIGN SERVER` SQL commands into the database and you'll have your foreign table definition.
-
- 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')
-
+```
+ 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')
+```
And you can query the table directly, even though it's really just a shape file.
- > 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
-
+```sql
+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
+```
+
+You can also apply filters, and see the portions that will be pushed down to the OGR driver, by setting the debug level to `DEBUG1`.
+
+```sql
+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: OGR SQL: (height < 5.7)
+DEBUG: OGR spatial filter (0 0, 1 1)
+ name | age | height
+-------+-----+--------
+ Peter | 45 | 5.6
+(1 row)
+```
## Examples
@@ -106,110 +127,126 @@ And you can query the table directly, even though it's really just a shape file.
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');
-
+```sql
+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');
+```
### FGDB FDW
Unzip the `Querying.zip` file from the `data` directory to get a `Querying.gdb` file, and put it somewhere public (like `/tmp`). Now run the `ogr_fdw_info` tool on it to get a table definition.
-
- CREATE SERVER fgdbtest
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/Querying.gdb',
- format 'OpenFileGDB' );
-
- CREATE FOREIGN TABLE cities (
- fid integer,
- geom geometry(Point, 4326),
- city_fips varchar,
- city_name varchar,
- state_fips varchar,
- state_name varchar,
- state_city varchar,
- type varchar,
- capital varchar,
- elevation integer,
- pop1990 integer,
- popcat integer )
- SERVER fgdbtest
- OPTIONS (layer 'Cities');
-
+```sql
+CREATE SERVER fgdbtest
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/Querying.gdb',
+ format 'OpenFileGDB' );
+
+CREATE FOREIGN TABLE cities (
+ fid integer,
+ geom geometry(Point, 4326),
+ city_fips varchar,
+ city_name varchar,
+ state_fips varchar,
+ state_name varchar,
+ state_city varchar,
+ type varchar,
+ capital varchar,
+ elevation integer,
+ pop1990 integer,
+ popcat integer
+ )
+ SERVER fgdbtest
+ OPTIONS (layer 'Cities');
+```
Query away!
### PostgreSQL FDW
Wraparound action! Handy for testing. Connect your database back to your database and watch the fur fly.
-
- CREATE TABLE typetest (
- fid serial primary key,
- geom geometry(Point, 4326),
- num real,
- name varchar,
- clock time,
- calendar date,
- tstmp timestamp
- );
-
- INSERT INTO typetest
- VALUES (1, 'SRID=4326;POINT(-126 46)', 4.5, 'Paul', '09:34:23', 'June 1, 2013', '12:34:56 December 14, 1823');
- INSERT INTO typetest
- VALUES (2, 'SRID=4326;POINT(-126 46)', 4.8, 'Peter', '14:34:53', 'July 12, 2011', '1:34:12 December 24, 1923');
-
- CREATE SERVER wraparound
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource 'Pg:dbname=fdw user=postgres',
- format 'PostgreSQL' );
-
- CREATE FOREIGN TABLE typetest_fdw (
- fid integer,
- geom geometry(Point, 4326),
- num real,
- name varchar,
- clock time,
- calendar date,
- tstmp timestamp )
- SERVER wraparound
- OPTIONS (layer 'typetest');
-
- SELECT * FROM typetest_fdw;
+```sql
+CREATE TABLE apostles (
+ fid serial primary key,
+ geom geometry(Point, 4326),
+ joined integer,
+ name text,
+ height real,
+ born date,
+ clock time,
+ ts timestamp
+);
+
+INSERT INTO apostles (name, geom, joined, height, born, clock, ts) VALUES
+ ('Peter', 'SRID=4326;POINT(30.31 59.93)', 1, 1.6, '1912-01-10', '10:10:01', '1912-01-10 10:10:01'),
+ ('Andrew', 'SRID=4326;POINT(-2.8 56.34)', 2, 1.8, '1911-02-11', '10:10:02', '1911-02-11 10:10:02'),
+ ('James', 'SRID=4326;POINT(-79.23 42.1)', 3, 1.72, '1910-03-12', '10:10:03', '1910-03-12 10:10:03'),
+ ('John', 'SRID=4326;POINT(13.2 47.35)', 4, 1.45, '1909-04-01', '10:10:04', '1909-04-01 10:10:04'),
+ ('Philip', 'SRID=4326;POINT(-75.19 40.69)', 5, 1.65, '1908-05-02', '10:10:05', '1908-05-02 10:10:05'),
+ ('Bartholomew', 'SRID=4326;POINT(-62 18)', 6, 1.69, '1907-06-03', '10:10:06', '1907-06-03 10:10:06'),
+ ('Thomas', 'SRID=4326;POINT(-80.08 35.88)', 7, 1.68, '1906-07-04', '10:10:07', '1906-07-04 10:10:07'),
+ ('Matthew', 'SRID=4326;POINT(-73.67 20.94)', 8, 1.65, '1905-08-05', '10:10:08', '1905-08-05 10:10:08'),
+ ('James Alpheus', 'SRID=4326;POINT(-84.29 34.07)', 9, 1.78, '1904-09-06', '10:10:09', '1904-09-06 10:10:09'),
+ ('Thaddaeus', 'SRID=4326;POINT(79.13 10.78)', 10, 1.88, '1903-10-07', '10:10:10', '1903-10-07 10:10:10'),
+ ('Simon', 'SRID=4326;POINT(-85.97 41.75)', 11, 1.61, '1902-11-08', '10:10:11', '1902-11-08 10:10:11'),
+ ('Judas Iscariot', 'SRID=4326;POINT(35.7 32.4)', 12, 1.71, '1901-12-09', '10:10:12', '1901-12-09 10:10:12');
+
+CREATE SERVER wraparound
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource 'Pg:dbname=fdw user=postgres',
+ format 'PostgreSQL' );
+
+CREATE FOREIGN TABLE apostles_fdw (
+ fid integer,
+ geom geometry(Point, 4326),
+ joined integer,
+ name text,
+ height real,
+ born date,
+ clock time,
+ ts timestamp
+)
+SERVER wraparound
+ OPTIONS (layer 'apostles');
+
+SELECT * FROM apostles_fdw;
+```
## Advanced Features
@@ -220,36 +257,36 @@ If the OGR driver you are using supports it, you can insert/update/delete record
For file-backed drivers, the user under which `postgres` runs will need read/write access to the file being altered. For database-backed drivers, your connection needs a user with read/write permissions to the database.
By default, servers and tables are updateable if the OGR driver supports it, but you can turn off updateability at a server or table level using the `updateable` option:
+```sql
+ALTER SERVER myserver
+ OPTIONS (ADD updateable 'false');
- ALTER SERVER myserver
- OPTIONS (ADD updatable 'false');
-
- ALTER FOREIGN TABLE mytable
- OPTIONS (ADD updatable 'false');
-
+ALTER FOREIGN TABLE mytable
+ OPTIONS (ADD updateable 'false');
+```
Writeable tables only work if you have included a `fid` column in your table definition. By default, tables imported by `IMPORT FOREIGN SCHEMA` or using the example SQL code from `ogr_fdw_info` include a `fid` column.
### Column Name Mapping
You can create an FDW table with any subset of columns from the OGR source you like, just by using the same column names as the source:
-
- CREATE FOREIGN TABLE typetest_fdw_partial (
- clock time,
- name varchar
- )
- SERVER wraparound
- OPTIONS (layer 'typetest');
-
+```sql
+CREATE FOREIGN TABLE typetest_fdw_partial (
+ clock time,
+ name varchar
+ )
+ SERVER wraparound
+ OPTIONS (layer 'typetest');
+```
You can also explicitly map remote column names to different local names using the `column_name` option:
-
- CREATE FOREIGN TABLE typetest_fdw_mapped (
- fid bigint,
- supertime time OPTIONS (column_name 'clock'),
- thebestnamething varchar OPTIONS (column_name 'name')
- )
- SERVER wraparound
- OPTIONS (layer 'typetest');
-
+```sql
+CREATE FOREIGN TABLE typetest_fdw_mapped (
+ fid bigint,
+ supertime time OPTIONS (column_name 'clock'),
+ thebestnamething varchar OPTIONS (column_name 'name')
+ )
+ SERVER wraparound
+ OPTIONS (layer 'typetest');
+```
### Automatic Foreign Table Creation
@@ -260,51 +297,51 @@ You can use the PostgreSQL `IMPORT FOREIGN SCHEMA` command to [import table defi
#### Import All Tables
If you want to import all tables in the OGR data source use the special schema called "ogr_all".
+```sql
+CREATE SCHEMA fgdball;
- CREATE SCHEMA fgdball;
-
- IMPORT FOREIGN SCHEMA ogr_all
- FROM SERVER fgdbtest
- INTO fgdball;
-
+IMPORT FOREIGN SCHEMA ogr_all
+ FROM SERVER fgdbtest
+ INTO fgdball;
+```
#### Import a Subset of Tables
Not all OGR data sources have a concept of schema, so we use the remote schema string as a prefix to match OGR layers. The matching is case sensitive, so make sure casing matches your layer names.
For example, the following will only import tables that start with *CitiesIn*. As long as you quote, you can handle true schemaed databases such as SQL Server or PostgreSQL by using something like *"dbo."*
+```sql
+CREATE SCHEMA fgdbcityinf;
- CREATE SCHEMA fgdbcityinf;
-
- IMPORT FOREIGN SCHEMA "CitiesIn"
- FROM SERVER fgdbtest
- INTO fgdbcityinf;
-
+IMPORT FOREIGN SCHEMA "CitiesIn"
+ FROM SERVER fgdbtest
+ INTO fgdbcityinf;
+```
You can also use PostgreSQL clauses `LIMIT TO` and `EXCEPT` to restrict the tables you are importing.
-
- CREATE SCHEMA fgdbcitysub;
-
- -- import only layer called Cities
- IMPORT FOREIGN SCHEMA ogr_all
- LIMIT TO(cities)
- FROM server fgdbtest
- INTO fgdbcitysub ;
-
- -- import only layers not called Cities or Countries
- IMPORT FOREIGN SCHEMA ogr_all
- EXCEPT (cities, countries)
- FROM server fgdbtest
- INTO fgdbcitysub;
-
- -- With table laundering turned off, need to use exact layer names
- DROP SCHEMA IF EXISTS fgdbcitysub CASCADE;
-
- -- import with un-laundered table name
- IMPORT FOREIGN SCHEMA ogr_all
- LIMIT TO("Cities")
- FROM server fgdbtest
- INTO fgdbcitysub
- OPTIONS (launder_table_names 'false') ;
-
+```sql
+CREATE SCHEMA fgdbcitysub;
+
+-- import only layer called Cities
+IMPORT FOREIGN SCHEMA ogr_all
+ LIMIT TO(cities)
+ FROM server fgdbtest
+ INTO fgdbcitysub ;
+
+-- import only layers not called Cities or Countries
+IMPORT FOREIGN SCHEMA ogr_all
+ EXCEPT (cities, countries)
+ FROM server fgdbtest
+ INTO fgdbcitysub;
+
+-- With table laundering turned off, need to use exact layer names
+DROP SCHEMA IF EXISTS fgdbcitysub CASCADE;
+
+ -- import with un-laundered table name
+IMPORT FOREIGN SCHEMA ogr_all
+ LIMIT TO("Cities")
+ FROM server fgdbtest
+ INTO fgdbcitysub
+ OPTIONS (launder_table_names 'false') ;
+```
#### Mixed Case and Special Characters
@@ -315,17 +352,17 @@ By default, when `IMPORT FOREIGN SCHEMA` is run on an OGR foreign data server, t
Laundering is not desirable in all cases. You can override this behavior with two `IMPORT FOREIGN SCHEMA` options specific to `ogr_fdw` servers: `launder_column_names` and `launder_table_names`.
To preserve casing and other funky characters in both column names and table names, do the following:
-
- CREATE SCHEMA fgdbcitypreserve;
-
- IMPORT FOREIGN SCHEMA ogr_all
- FROM SERVER fgdbtest
- INTO fgdbpreserve
- OPTIONS (
- launder_table_names 'false',
- launder_column_names 'false'
- ) ;
-
+```sql
+CREATE SCHEMA fgdbcitypreserve;
+
+IMPORT FOREIGN SCHEMA ogr_all
+ FROM SERVER fgdbtest
+ INTO fgdbpreserve
+ OPTIONS (
+ launder_table_names 'false',
+ launder_column_names 'false'
+ );
+```
### GDAL Options
@@ -333,43 +370,57 @@ The behavior of your GDAL/OGR connection can be altered by passing GDAL `config_
Since many Shapefiles are encoded using LATIN1, and most PostgreSQL databases are encoded in UTF-8, it is useful to specify the encoding to get proper handling of special characters like accents.
- CREATE SERVER myserver_latin1
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/test',
- format 'ESRI Shapefile',
- config_options 'SHAPE_ENCODING=LATIN1' );
+```sql
+CREATE SERVER myserver_latin1
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/test',
+ format 'ESRI Shapefile',
+ config_options 'SHAPE_ENCODING=LATIN1'
+ );
+```
Multiple config options can be passed at one time by supplying a **space-separated** list of options.
If you are using GDAL 2.0 or higher, you can also pass "open options" to your OGR foreign data wrapper, using the `open_options` parameter. In GDAL 2.0, the global `SHAPE_ENCODING` option has been superceded by a driver-specific `ENCODING` option, which can be called like this:
- CREATE SERVER myserver_latin1
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/test',
- format 'ESRI Shapefile',
- open_options 'ENCODING=LATIN1' );
+```sql
+CREATE SERVER myserver_latin1
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/test',
+ format 'ESRI Shapefile',
+ open_options 'ENCODING=LATIN1'
+ );
+```
### GDAL Debugging
If you are getting odd behavior and you want to see what GDAL is doing behind the scenes, enable debug logging in your server:
- CREATE SERVER myserver_latin1
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/test',
- format 'ESRI Shapefile',
- config_options 'SHAPE_ENCODING=LATIN1 CPL_DEBUG=ON'
- );
+```sql
+CREATE SERVER myserver_latin1
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/test',
+ format 'ESRI Shapefile',
+ config_options 'SHAPE_ENCODING=LATIN1 CPL_DEBUG=ON'
+ );
+```
GDAL-level messages will be logged at the PostgreSQL **DEBUG2** level, so to see them when running a query, alter your `client_min_messages` setting.
- SET client_min_messages = debug2;
+```sql
+SET client_min_messages = debug2;
+```
Once you've figured out your issue, don't forget to remove the `CPL_DEBUG` option from your server definition, and set your messages back to **NOTICE** level.
- SET client_min_messages = notice;
- ALTER SERVER myserver_latin1 OPTIONS (SET config_options 'SHAPE_ENCODING=LATIN1');
-
+```sql
+SET client_min_messages = notice;
+ALTER SERVER myserver_latin1
+ OPTIONS (
+ SET config_options 'SHAPE_ENCODING=LATIN1'
+ );
+```
=====================================
ci/gdal_build.sh
=====================================
@@ -0,0 +1,13 @@
+#!/bin/bash
+
+GDAL_VERSION=$1
+
+echo "Building GDAL $GDAL_VERSION"
+wget http://download.osgeo.org/gdal/$GDAL_VERSION/gdal-$GDAL_VERSION.tar.xz
+tar xJf gdal-$GDAL_VERSION.tar.xz; cd gdal-$GDAL_VERSION
+./configure --prefix=/usr --enable-debug --without-libtool
+make -j4
+sudo make install
+cd ..
+gdalinfo --version
+
=====================================
input/file.source
=====================================
@@ -116,4 +116,34 @@ SELECT c.*
JOIN no_geom c
ON (c.fid = g.g);
+
+
------------------------------------------------
+-- FGDB test
+
+CREATE SERVER fgdbserver
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/vsizip/@abs_srcdir@/data/Querying.zip/Querying.gdb',
+ format 'OpenFileGDB' );
+
+CREATE FOREIGN TABLE cities (
+ fid bigint,
+ shape bytea,
+ city_fips varchar(5),
+ city_name varchar(40),
+ state_fips varchar(2),
+ state_name varchar(25),
+ state_city varchar(7),
+ type varchar(25),
+ capital varchar(1),
+ elevation integer,
+ pop1990 integer,
+ popcat integer
+) SERVER "fgdbserver"
+OPTIONS (layer 'Cities');
+
+SET client_min_messages = DEBUG1;
+
+SELECT fid, city_name, pop1990 FROM cities WHERE pop1990 = 17710;
+SELECT fid, city_name, pop1990 FROM cities WHERE city_name = 'Williston';
=====================================
input/pgsql.source
=====================================
@@ -1,9 +1,6 @@
-CREATE SERVER pgserver
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource 'PG:dbname=contrib_regression host=localhost',
- format 'PostgreSQL' );
-
+----------------------------------------------------------------------
+-- Create local table
+
CREATE TABLE bytea_local (
fid serial primary key,
geom bytea,
@@ -20,6 +17,7 @@ CREATE TABLE bytea_local (
);
----------------------------------------------------------------------
+-- Populate local table
INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
VALUES ('Jim', '14232'::bytea, 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' );
@@ -29,6 +27,13 @@ INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch,
VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
----------------------------------------------------------------------
+-- Create remote table
+
+CREATE SERVER pgserver
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource 'PG:dbname=contrib_regression host=localhost',
+ format 'PostgreSQL' );
CREATE FOREIGN TABLE bytea_fdw (
fid integer,
@@ -57,15 +62,55 @@ EXPLAIN VERBOSE
FROM bytea_fdw;
----------------------------------------------------------------------
+-- Remote Query and OGR SQL pushdown
-INSERT INTO bytea_fdw (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
-VALUES ('Margaret', '2222'::bytea, 12, 5, 1.4, 19.13, '2001-11-23'::date, '9:12:34'::time, '2001-02-11 09:23:11'::timestamp, 'them', 'y' )
-RETURNING fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn;
+SET client_min_messages = DEBUG1;
SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
FROM bytea_fdw
WHERE fid = 4;
+SELECT fid, name, dt
+ FROM bytea_fdw
+ WHERE name IS NULL;
+
+SELECT fid, name
+ FROM bytea_fdw
+ WHERE name = 'Jim' AND age <= 30;
+
+SELECT fid, name, dt
+ FROM bytea_fdw
+ WHERE name = 'Jim' AND age <= 30 AND dt > '2010-10-1'::date;
+
+SELECT fid, name
+ FROM bytea_fdw
+ WHERE name = 'Jim' OR name IS NULL;
+
+----------------------------------------------------------------------
+-- Cached query case, exercised by statement handles or
+-- functions.
+
+CREATE OR REPLACE FUNCTION get_names()
+ RETURNS varchar AS
+ $$
+ BEGIN
+ RETURN (SELECT string_agg(name,',')
+ FROM bytea_fdw
+ WHERE name = 'Jim' OR name IS NULL);
+ END;
+ $$
+ LANGUAGE 'plpgsql';
+
+SELECT get_names();
+DROP FUNCTION get_names();
+
+----------------------------------------------------------------------
+-- Remote Update
+
+INSERT INTO bytea_fdw (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
+VALUES ('Margaret', '2222'::bytea, 12, 5, 1.4, 19.13, '2001-11-23'::date, '9:12:34'::time, '2001-02-11 09:23:11'::timestamp, 'them', 'y' )
+RETURNING fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn;
+
UPDATE bytea_fdw
SET name = 'Maggie', num = 45.34, yn = 'n'
WHERE age = 12;
@@ -89,4 +134,4 @@ SELECT a.fid, a.name, b.name
FROM bytea_local a
JOIN bytea_fdw b
USING (fid);
-
\ No newline at end of file
+
=====================================
input/postgis.source
=====================================
@@ -0,0 +1,89 @@
+CREATE EXTENSION postgis;
+
+CREATE TABLE geometry_local (
+ fid serial primary key,
+ geom geometry(Point, 4326),
+ name varchar,
+ age bigint,
+ size integer,
+ value float8,
+ num numeric(6,2),
+ dt date,
+ tm time,
+ dttm timestamp,
+ varch char(8),
+ yn char
+);
+
+----------------------------------------------------------------------
+-- Populate local table
+
+INSERT INTO geometry_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
+ VALUES ('Jim', 'SRID=4326;POINT(0 0)', 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' );
+INSERT INTO geometry_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
+ VALUES ('Marvin', 'SRID=4326;POINT(100 0)', 34, 2, 5.4, 10.13, '2011-11-11'::date, '15:21:45'::time, '2011-11-11 15:21:45'::timestamp, 'that', 'n' );
+INSERT INTO geometry_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
+ VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
+
+----------------------------------------------------------------------
+-- Create remote table
+
+CREATE SERVER pgservergeom
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource 'PG:dbname=contrib_regression host=localhost',
+ format 'PostgreSQL' );
+
+CREATE FOREIGN TABLE geometry_fdw (
+ fid integer,
+ geom geometry(point, 4326),
+ name varchar,
+ age bigint,
+ size integer,
+ value float8,
+ num numeric(6,2),
+ dt date,
+ tm time,
+ dttm timestamp,
+ varch char(8),
+ yn char
+) SERVER pgservergeom OPTIONS (layer 'geometry_local');
+
+
+SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn FROM geometry_fdw;
+
+SELECT a.name, b.name
+ FROM geometry_local a
+ JOIN geometry_fdw b
+ USING (fid);
+
+EXPLAIN VERBOSE
+ SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
+ FROM geometry_fdw;
+
+----------------------------------------------------------------------
+-- Remote Query and OGR SQL pushdown
+
+SET client_min_messages = DEBUG1;
+
+SELECT name, age, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE name = 'Jim' AND age <= 30
+ AND geom && ST_MakeEnvelope(-1, -1, 1, 1, 4326);
+
+SELECT name, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE ST_Intersects(geom, ST_MakeEnvelope(-1, -1, 1, 1, 4326));
+
+SELECT name, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE geom && ST_MakeEnvelope(-180, -90, 180, 90, 4326);
+
+SELECT name, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE ST_MakeEnvelope(-180, -90, 180, 90, 4326) && geom;
+
+SELECT name, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE ST_MakeEnvelope(-180, -90, 180, 90, 4326) &&
+ ST_MakeEnvelope(-180, -90, 180, 90, 4326);
=====================================
ogr_fdw.c
=====================================
@@ -931,7 +931,47 @@ ogrGetForeignPaths(PlannerInfo* root,
}
+/*
+ * Convert an OgrFdwSpatialFilter into a List so it can
+ * be safely passed through the fdw_private list.
+ */
+static List*
+ogrSpatialFilterToList(const OgrFdwSpatialFilter* spatial_filter)
+{
+ List *l = NIL;
+ if (spatial_filter)
+ {
+ l = lappend(l, makeInteger(spatial_filter->ogrfldnum));
+ l = lappend(l, makeFloat(psprintf("%.17g", spatial_filter->minx)));
+ l = lappend(l, makeFloat(psprintf("%.17g", spatial_filter->miny)));
+ l = lappend(l, makeFloat(psprintf("%.17g", spatial_filter->maxx)));
+ l = lappend(l, makeFloat(psprintf("%.17g", spatial_filter->maxy)));
+ }
+ return l;
+}
+
+/*
+ * Convert the List form back into an OgrFdwSpatialFilter
+ * after passing through fdw_private.
+ */
+static OgrFdwSpatialFilter*
+ogrSpatialFilterFromList(const List* lst)
+{
+ OgrFdwSpatialFilter* spatial_filter;
+ if (lst == NIL)
+ return NULL;
+
+ Assert(list_length(lst) == 5);
+
+ spatial_filter = palloc(sizeof(OgrFdwSpatialFilter));
+ spatial_filter->ogrfldnum = intVal(linitial(lst));
+ spatial_filter->minx = floatVal(lsecond(lst));
+ spatial_filter->miny = floatVal(lthird(lst));
+ spatial_filter->maxx = floatVal(lfourth(lst));
+ spatial_filter->maxy = floatVal(list_nth(lst, 5));
+ return spatial_filter;
+}
/*
* fileGetForeignPlan
@@ -956,6 +996,8 @@ ogrGetForeignPlan(PlannerInfo* root,
List* fdw_private;
OgrFdwPlanState* planstate = (OgrFdwPlanState*)(baserel->fdw_private);
OgrFdwState* state = (OgrFdwState*)(baserel->fdw_private);
+ OgrFdwSpatialFilter* spatial_filter = NULL;
+ char* attribute_filter = NULL;
/* Add in column mapping data to build SQL with the right OGR column names */
ogrReadColumnData(state);
@@ -966,9 +1008,19 @@ ogrGetForeignPlan(PlannerInfo* root,
* down to OGR via OGR_L_SetAttributeFilter (done) and (TODO) OGR_L_SetSpatialFilter.
*/
initStringInfo(&sql);
- sql_generated = ogrDeparse(&sql, root, baserel, scan_clauses, state, ¶ms_list);
- elog(DEBUG1, "OGR SQL: %s", sql.data);
-
+ sql_generated = ogrDeparse(&sql, root, baserel, scan_clauses, state, ¶ms_list, &spatial_filter);
+
+ /* Extract the OGR SQL from the StringInfoData */
+ if (sql_generated && sql.len > 0)
+ attribute_filter = sql.data;
+
+ /* Log filters at debug level one as necessary */
+ if (attribute_filter)
+ elog(DEBUG1, "OGR SQL: %s", attribute_filter);
+ if (spatial_filter)
+ elog(DEBUG1, "OGR spatial filter (%g %g, %g %g)",
+ spatial_filter->minx, spatial_filter->miny,
+ spatial_filter->maxx, spatial_filter->maxy);
/*
* Here we strip RestrictInfo
* nodes from the clauses and ignore pseudoconstants (which will be
@@ -982,25 +1034,13 @@ ogrGetForeignPlan(PlannerInfo* root,
*/
scan_clauses = extract_actual_clauses(scan_clauses, false);
- /*
- * Serialize the data we want to pass to the execution stage.
- * This is ugly but seems to be the only way to pass our constructed
- * OGR SQL command to execution.
- *
- * TODO: Pass a spatial filter down also.
- */
- if (sql_generated)
- {
- fdw_private = list_make2(makeString(sql.data), params_list);
- }
- else
- {
- fdw_private = list_make2(NULL, params_list);
- }
+ /* Pack the data we want to pass to the execution stage into a List. */
+ /* The members of this list must by copyable by PgSQL, which means */
+ /* they need to be Lists themselves, or Value nodes, otherwise when */
+ /* the plan gets copied the copy might fail. */
+ fdw_private = list_make3(makeString(attribute_filter), params_list, ogrSpatialFilterToList(spatial_filter));
- /*
- * Clean up our connection
- */
+ /* Clean up our connection */
ogrFinishConnection(&(planstate->ogr));
/* Create the ForeignScan node */
@@ -1207,7 +1247,7 @@ ogrBytesToHex(unsigned char* bytes, size_t size)
int i;
if (! bytes || ! size)
{
- elog(ERROR, "hexbytes_from_bytes: invalid input");
+ elog(ERROR, "ogrBytesToHex: invalid input");
return NULL;
}
hex = palloc(size * 2 + 1);
@@ -1511,12 +1551,19 @@ ogrLookupGeometryFunctionOid(const char* proname)
static void
ogrBeginForeignScan(ForeignScanState* node, int eflags)
{
+ OgrFdwState* state;
+ OgrFdwExecState* execstate;
+ OgrFdwSpatialFilter* spatial_filter;
Oid foreigntableid = RelationGetRelid(node->ss.ss_currentRelation);
ForeignScan* fsplan = (ForeignScan*)node->ss.ps.plan;
+ /* Do nothing in EXPLAIN (no ANALYZE) case */
+ if (eflags & EXEC_FLAG_EXPLAIN_ONLY)
+ return;
+
/* Initialize OGR connection */
- OgrFdwState* state = getOgrFdwState(foreigntableid, OGR_EXEC_STATE);
- OgrFdwExecState* execstate = (OgrFdwExecState*)state;
+ state = getOgrFdwState(foreigntableid, OGR_EXEC_STATE);
+ execstate = (OgrFdwExecState*)state;
/* Read the OGR layer definition and PgSQL foreign table definitions */
ogrReadColumnData(state);
@@ -1525,10 +1572,26 @@ ogrBeginForeignScan(ForeignScanState* node, int eflags)
execstate->setsridfunc = ogrLookupGeometryFunctionOid("st_setsrid");
execstate->typmodsridfunc = ogrLookupGeometryFunctionOid("postgis_typmod_srid");
- /* Get private info created by planner functions. */
- execstate->sql = strVal(list_nth(fsplan->fdw_private, 0));
+ /* Get OGR SQL generated by the deparse step during the planner function. */
+ execstate->sql = (char*) strVal(list_nth(fsplan->fdw_private, 0));
+
+ /* TODO: Use the parse step attribute list to restrict requested columns */
// execstate->retrieved_attrs = (List *) list_nth(fsplan->fdw_private, 1);
+ /* Get spatial filter generated by the deparse step. */
+ spatial_filter = ogrSpatialFilterFromList(list_nth(fsplan->fdw_private, 2));
+
+ if (spatial_filter)
+ {
+ OGR_L_SetSpatialFilterRectEx(execstate->ogr.lyr,
+ spatial_filter->ogrfldnum,
+ spatial_filter->minx,
+ spatial_filter->miny,
+ spatial_filter->maxx,
+ spatial_filter->maxy
+ );
+ }
+
if (execstate->sql && strlen(execstate->sql) > 0)
{
OGRErr err = OGR_L_SetAttributeFilter(execstate->ogr.lyr, execstate->sql);
@@ -1944,6 +2007,20 @@ ogrEwkbStripSrid(unsigned char* wkb, size_t wkbsize)
return newwkbsize;
}
+OGRErr
+pgDatumToOgrGeometry (Datum pg_geometry, Oid pgsendfunc, OGRGeometryH* ogr_geometry)
+{
+ OGRErr err;
+ bytea* wkb_bytea = DatumGetByteaP(OidFunctionCall1(pgsendfunc, pg_geometry));
+ unsigned char* wkb = (unsigned char*)VARDATA_ANY(wkb_bytea);
+ size_t wkbsize = VARSIZE_ANY_EXHDR(wkb_bytea);
+ wkbsize = ogrEwkbStripSrid(wkb, wkbsize);
+ err = OGR_G_CreateFromWkb(wkb, NULL, ogr_geometry, wkbsize);
+ if (wkb_bytea)
+ pfree(wkb_bytea);
+ return err;
+}
+
static OGRErr
ogrSlotToFeature(const TupleTableSlot* slot, OGRFeatureH feat, const OgrFdwTable* tbl)
{
@@ -2037,21 +2114,9 @@ ogrSlotToFeature(const TupleTableSlot* slot, OGRFeatureH feat, const OgrFdwTable
else
{
OGRGeometryH geom;
- bytea* wkb_bytea = DatumGetByteaP(OidFunctionCall1(col.pgsendfunc, values[i]));
- unsigned char* wkb = (unsigned char*)VARDATA_ANY(wkb_bytea);
- int wkbsize = VARSIZE_ANY_EXHDR(wkb_bytea);
- wkbsize = ogrEwkbStripSrid(wkb, wkbsize);
-
- /* TODO, create geometry with SRS of table? */
- err = OGR_G_CreateFromWkb(wkb, NULL, &geom, wkbsize);
- if (wkb_bytea)
- {
- pfree(wkb_bytea);
- }
+ err = pgDatumToOgrGeometry (values[i], col.pgsendfunc, &geom);
if (err != OGRERR_NONE)
- {
return err;
- }
#if (GDAL_VERSION_NUM >= GDAL_COMPUTE_VERSION(1,11,0))
err = OGR_F_SetGeomFieldDirectly(feat, ogrfldnum, geom);
@@ -2305,10 +2370,11 @@ static void
ogrEndForeignScan(ForeignScanState* node)
{
OgrFdwExecState* execstate = (OgrFdwExecState*) node->fdw_state;
-
- elog(DEBUG2, "processed %d rows from OGR", execstate->rownum);
-
- ogrFinishConnection(&(execstate->ogr));
+ if (execstate)
+ {
+ elog(DEBUG2, "OGR FDW processed %d rows from OGR", execstate->rownum);
+ ogrFinishConnection(&(execstate->ogr));
+ }
return;
}
=====================================
ogr_fdw.h
=====================================
@@ -91,11 +91,11 @@ typedef enum {
typedef struct OgrFdwColumn
{
/* PgSQL metadata */
- int pgattnum; /* PostgreSQL attribute number */
- int pgattisdropped; /* PostgreSQL attribute dropped? */
- char *pgname; /* PostgreSQL column name */
- Oid pgtype; /* PostgreSQL data type */
- int pgtypmod; /* PostgreSQL type modifier */
+ int pgattnum; /* PostgreSQL attribute number */
+ int pgattisdropped; /* PostgreSQL attribute dropped? */
+ char* pgname; /* PostgreSQL column name */
+ Oid pgtype; /* PostgreSQL data type */
+ int pgtypmod; /* PostgreSQL type modifier */
/* For reading */
Oid pginputfunc; /* PostgreSQL function to convert cstring to type */
@@ -104,9 +104,9 @@ typedef struct OgrFdwColumn
Oid pgrecvioparam;
/* For writing */
- Oid pgoutputfunc; /* PostgreSQL function to convert type to cstring */
+ Oid pgoutputfunc; /* PostgreSQL function to convert type to cstring */
bool pgoutputvarlena;
- Oid pgsendfunc; /* PostgreSQL function to convert type to binary */
+ Oid pgsendfunc; /* PostgreSQL function to convert type to binary */
bool pgsendvarlena;
/* OGR metadata */
@@ -118,17 +118,23 @@ typedef struct OgrFdwColumn
typedef struct OgrFdwTable
{
int ncols;
- char *tblname;
- OgrFdwColumn *cols;
+ char* tblname;
+ OgrFdwColumn* cols;
} OgrFdwTable;
+typedef struct OgrFdwSpatialFilter
+{
+ int ogrfldnum;
+ double minx, miny, maxx, maxy;
+} OgrFdwSpatialFilter;
+
typedef struct OgrConnection
{
- const char *ds_str; /* datasource connection string */
- const char *dr_str; /* driver (format) name */
- char *lyr_str; /* layer name */
- const char *config_options; /* GDAL config options */
- const char *open_options; /* GDAL open options */
+ const char* ds_str; /* datasource connection string */
+ const char* dr_str; /* driver (format) name */
+ char* lyr_str; /* layer name */
+ const char* config_options; /* GDAL config options */
+ const char* open_options; /* GDAL open options */
OgrUpdateable ds_updateable;
OgrUpdateable lyr_updateable;
bool lyr_utf8; /* OGR layer will return UTF8 strings */
@@ -147,8 +153,8 @@ typedef struct OgrFdwState
{
OgrFdwStateType type;
Oid foreigntableid;
- OgrConnection ogr; /* connection object */
- OgrFdwTable *table;
+ OgrConnection ogr; /* connection object */
+ OgrFdwTable* table;
TupleDesc tupdesc;
} OgrFdwState;
@@ -157,12 +163,12 @@ typedef struct OgrFdwPlanState
OgrFdwStateType type;
Oid foreigntableid;
OgrConnection ogr;
- OgrFdwTable *table;
+ OgrFdwTable* table;
TupleDesc tupdesc;
int nrows; /* estimate of number of rows in file */
Cost startup_cost;
Cost total_cost;
- bool *pushdown_clauses;
+ bool* pushdown_clauses;
} OgrFdwPlanState;
typedef struct OgrFdwExecState
@@ -172,10 +178,10 @@ typedef struct OgrFdwExecState
OgrConnection ogr;
OgrFdwTable *table;
TupleDesc tupdesc;
- char *sql; /* OGR SQL for attribute filter */
- int rownum; /* how many rows have we read thus far? */
- Oid setsridfunc; /* ST_SetSRID() */
- Oid typmodsridfunc; /* postgis_typmod_srid() */
+ char* sql; /* OGR SQL for attribute filter */
+ int rownum; /* how many rows have we read thus far? */
+ Oid setsridfunc; /* ST_SetSRID() */
+ Oid typmodsridfunc; /* postgis_typmod_srid() */
} OgrFdwExecState;
typedef struct OgrFdwModifyState
@@ -183,13 +189,13 @@ typedef struct OgrFdwModifyState
OgrFdwStateType type;
Oid foreigntableid;
OgrConnection ogr; /* connection object */
- OgrFdwTable *table;
+ OgrFdwTable* table;
TupleDesc tupdesc;
} OgrFdwModifyState;
/* Shared function signatures */
-bool ogrDeparse(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, List *exprs, OgrFdwState *state, List **param);
-
+bool ogrDeparse(StringInfo buf, PlannerInfo* root, RelOptInfo* foreignrel, List* exprs, OgrFdwState* state, List** params_list, OgrFdwSpatialFilter** sf);
Oid ogrGetGeometryOid(void);
+OGRErr pgDatumToOgrGeometry (Datum pg_geometry, Oid pgsendfunc, OGRGeometryH* ogr_geometry);
#endif /* _OGR_FDW_H */
=====================================
ogr_fdw_common.h
=====================================
@@ -25,9 +25,8 @@
/* Re-write a string in place with laundering rules */
void ogrStringLaunder(char *str);
-OGRErr ogrLayerToSQL (const OGRLayerH ogr_lyr,
- const char *fwd_server, int launder_table_names, int launder_column_names,
- int use_postgis_geometry, stringbuffer_t *buf);
-
+OGRErr ogrLayerToSQL (const OGRLayerH ogr_lyr, const char *fwd_server,
+ int launder_table_names, int launder_column_names,
+ int use_postgis_geometry, stringbuffer_t *buf);
#endif /* _OGR_FDW_COMMON_H */
=====================================
ogr_fdw_deparse.c
=====================================
@@ -23,7 +23,7 @@ typedef struct OgrDeparseCtx
RelOptInfo* foreignrel; /* the foreign relation we are planning for */
StringInfo buf; /* output buffer to append to */
List** params_list; /* exprs that will become remote Params */
- OGRGeometryH geom; /* if filter contains a geometry constant, it resides here */
+ OgrFdwSpatialFilter* spatial_filter; /* spatial filter bounds and fieldnumber */
OgrFdwState* state; /* to convert local column names to OGR names */
} OgrDeparseCtx;
@@ -148,9 +148,9 @@ ogrDeparseConst(Const* constant, OgrDeparseCtx* context)
Datum wkbdatum;
char* gser;
char* wkb;
+ char* wkt;
int wkb_size;
OGRGeometryH ogrgeom;
- OGRErr err;
/*
* Given a type oid (geometry in this case),
@@ -166,22 +166,12 @@ ogrDeparseConst(Const* constant, OgrDeparseCtx* context)
gser = DatumGetPointer(wkbdatum);
wkb = VARDATA(gser);
wkb_size = VARSIZE(gser) - VARHDRSZ;
- err = OGR_G_CreateFromWkb((unsigned char*)wkb, NULL, &ogrgeom, wkb_size);
+ OGR_G_CreateFromWkb((unsigned char*)wkb, NULL, &ogrgeom, wkb_size);
+ OGR_G_ExportToWkt(ogrgeom, &wkt);
+ elog(DEBUG1, "ogrDeparseConst got a geometry: %s", wkt);
+ free(wkt);
+ OGR_G_DestroyGeometry(ogrgeom);
- /*
- * Save the result
- */
- if (err != OGRERR_NONE)
- {
- if (! context->geom)
- {
- context->geom = ogrgeom;
- }
- else
- {
- elog(WARNING, "got two geometries in OGR FDW query, only using the first");
- }
- }
/*
* geometry doesn't play a role in the deparsed SQL
*/
@@ -238,62 +228,81 @@ ogrIsLegalVarName(const char* varname)
}
static bool
-ogrDeparseVar(Var* node, OgrDeparseCtx* context)
+ogrDeparseVarOgrColumn(const Var* node, const OgrDeparseCtx* context, OgrFdwColumn *col)
{
- StringInfoData* buf = context->buf;
+ /* Var belongs to foreign table */
+ int i;
+ OgrFdwTable* table = context->state->table;
- if (node->varno == context->foreignrel->relid && node->varlevelsup == 0)
+ for (i = 0; i < table->ncols; i++)
{
- /* Var belongs to foreign table */
- int i;
- OgrFdwTable* table = context->state->table;
- OGRLayerH lyr = context->state->ogr.lyr;
- bool done = false;
-
- /* varno must not be any of OUTER_VAR, INNER_VAR and INDEX_VAR. */
- Assert(!IS_SPECIAL_VARNO(node->varno));
+ if (table->cols[i].pgattnum == node->varattno)
+ {
+ *col = table->cols[i];
+ return true;
+ }
+ }
+ return false;
+}
- /* TODO: Handle case of mapping columns to OGR columns that don't share their name */
- /* TODO: Lookup OGR column name by going from varattno -> OGR via a table/OGR map */
+static const char *
+ogrDeparseVarName(const Var* node, const OgrDeparseCtx* context)
+{
+ /* Var belongs to foreign table */
+ OGRLayerH lyr = context->state->ogr.lyr;
+ OgrFdwColumn col;
- for (i = 0; i < table->ncols; i++)
+ if (ogrDeparseVarOgrColumn(node, context, &col))
+ {
+ const char* fldname = NULL;
+ if (col.ogrvariant == OGR_FID)
{
- if (table->cols[i].pgattnum == node->varattno)
+ fldname = OGR_L_GetFIDColumn(lyr);
+ if (! fldname || strlen(fldname) == 0)
{
- const char* fldname = NULL;
-
- if (table->cols[i].ogrvariant == OGR_FID)
- {
- fldname = OGR_L_GetFIDColumn(lyr);
- if (! fldname || strlen(fldname) == 0)
- {
- fldname = "fid";
- }
- }
- else if (table->cols[i].ogrvariant == OGR_FIELD)
- {
- OGRFeatureDefnH fd = OGR_L_GetLayerDefn(lyr);
- OGRFieldDefnH fld = OGR_FD_GetFieldDefn(fd, table->cols[i].ogrfldnum);
- fldname = OGR_Fld_GetNameRef(fld);
- }
-
- if (fldname)
- {
- if (ogrIsLegalVarName(fldname))
- {
- appendStringInfoString(buf, fldname);
- }
- else
- {
- appendStringInfo(buf, "\"%s\"", fldname);
- }
-
- done = true;
- }
+ fldname = "fid";
}
}
+ else if (col.ogrvariant == OGR_FIELD)
+ {
+ OGRFeatureDefnH fd = OGR_L_GetLayerDefn(lyr);
+ OGRFieldDefnH fld = OGR_FD_GetFieldDefn(fd, col.ogrfldnum);
+ fldname = OGR_Fld_GetNameRef(fld);
+ }
+
+ if (fldname)
+ return fldname;
+ }
+ return NULL;
+}
+
+static bool
+ogrDeparseVar(const Var* node, OgrDeparseCtx* context)
+{
+ StringInfoData* buf = context->buf;
+
+ /* varno must not be any of OUTER_VAR, INNER_VAR and INDEX_VAR. */
+ Assert(!IS_SPECIAL_VARNO(node->varno));
+
+ if (node->varno == context->foreignrel->relid && node->varlevelsup == 0)
+ {
+ const char* fldname = ogrDeparseVarName(node, context);
- return done;
+ if (fldname)
+ {
+ if (ogrIsLegalVarName(fldname))
+ {
+ appendStringInfoString(buf, fldname);
+ }
+ else
+ {
+ appendStringInfo(buf, "\"%s\"", fldname);
+ }
+ }
+ else
+ {
+ return false;
+ }
}
else
{
@@ -319,16 +328,85 @@ ogrOperatorIsSupported(const char* opname)
elog(DEBUG3, "ogrOperatorIsSupported got operator '%s'", opname);
- if (bsearch(&opname, ogrOperators, 10, sizeof(char*), ogrOperatorCmpFunc))
+ return NULL != bsearch(&opname, ogrOperators, 10, sizeof(char*), ogrOperatorCmpFunc);
+}
+
+
+static bool ogrDeparseOpExprSpatial(OpExpr* node, OgrDeparseCtx* context)
+{
+ Expr* r_arg = lfirst(list_head(node->args));
+ Expr* l_arg = lfirst(list_tail(node->args));
+ Const* constant;
+ Var* var;
+ OgrFdwColumn col;
+ OGRLayerH lyr;
+ OGRFeatureDefnH fdh;
+ OGRGeomFieldDefnH gfdh;
+ OGRGeometryH geom;
+ OGREnvelope env;
+ OGRErr err;
+ const char* fldname;
+
+ elog(DEBUG4, "%s:%d entered ogrDeparseOpExprSpatial", __FILE__, __LINE__);
+
+ /* We need a Geometry T_Const on one side and a T_Var */
+ /* column on the other side that is from the FDW relation */
+ /* Both of those implies and OGR spatial filter can be reasonably */
+ /* set. */
+ if (nodeTag(r_arg) == T_Const && nodeTag(l_arg) == T_Var)
+ {
+ constant = (Const*)r_arg;
+ var = (Var*)l_arg;
+ }
+ else if (nodeTag(l_arg) == T_Const && nodeTag(r_arg) == T_Var)
{
- return true;
+ constant = (Const*)l_arg;
+ var = (Var*)r_arg;
}
else
{
return false;
}
-}
+ /* Const isn't a geometry type? Done. */
+ if (constant->consttype != ogrGetGeometryOid() || constant->constisnull || constant->constbyval)
+ return false;
+
+ /* Var doesn't match an OGR field? Done. */
+ if (!ogrDeparseVarOgrColumn(var, context, &col))
+ return false;
+
+ /* Matched field isn't an OGR geometry? Done. */
+ if (col.ogrvariant != OGR_GEOMETRY)
+ return false;
+
+ lyr = context->state->ogr.lyr;
+ fdh = OGR_L_GetLayerDefn(lyr);
+ gfdh = OGR_FD_GetGeomFieldDefn(fdh, col.ogrfldnum);
+ fldname = OGR_GFld_GetNameRef(gfdh);
+ elog(DEBUG4, "%s:%d geometry fieldname '%s'", __FILE__, __LINE__, fldname);
+
+ err = pgDatumToOgrGeometry (constant->constvalue, col.pgsendfunc, &geom);
+ if (err != OGRERR_NONE)
+ return false;
+
+ elog(DEBUG4, "%s:%d geometry constant is %s", __FILE__, __LINE__, OGR_G_ExportToJson(geom));
+
+ OGR_G_GetEnvelope(geom, &env);
+ OGR_G_DestroyGeometry(geom);
+ context->spatial_filter = palloc(sizeof(OgrFdwSpatialFilter));
+ context->spatial_filter->minx = env.MinX;
+ context->spatial_filter->maxx = env.MaxX;
+ context->spatial_filter->miny = env.MinY;
+ context->spatial_filter->maxy = env.MaxY;
+ context->spatial_filter->ogrfldnum = col.ogrfldnum;
+
+ elog(DEBUG4, "%s:%d OGR spatial filter is (%f %f, %f %f)",
+ __FILE__, __LINE__,
+ env.MinX, env.MinY, env.MaxX, env.MaxY);
+
+ return false;
+}
static bool
ogrDeparseOpExpr(OpExpr* node, OgrDeparseCtx* context)
@@ -358,36 +436,13 @@ ogrDeparseOpExpr(OpExpr* node, OgrDeparseCtx* context)
return false;
}
- /* TODO: When a && operator is found, we need to do special */
- /* handling to send the box back up to OGR for SetSpatialFilter */
-
- /* Overlaps operator is special case: if one side is a constant, */
+ /* Overlaps operator is special case: if one side is a */
+ /* constant (T_Const), and the other is a table column (T_Var), */
/* then we can pass it as a spatial filter to OGR */
if (strcmp("&&", opname) == 0)
{
- // Expr *r_arg = lfirst(list_head(node->args));
- // Expr *l_arg = lfirst(list_tail(node->args));
- // Const *constant;
-
- elog(DEBUG1, "whoa, dude, found a && operator");
-
- /* Specifically, we need a Geometry Const on one side and a Var */
- /* column on the other side that is from the FDW relation */
- /* Both of those implies and OGR spatial filter can be reasonably */
- /* set. */
-
- // if ( nodeTag(r_arg) == T_Const )
- // constant = (Const*)r_arg;
- // else if ( nodeTag(l_arg) == T_Const)
- // constant = (Const*)l_arg;
- // else
- // return false;
-
- // if ( constant->consttype != ogrGetGeometryOid() )
-
ReleaseSysCache(tuple);
-
- return false;
+ return ogrDeparseOpExprSpatial(node, context);
}
/* Sanity check. */
@@ -567,28 +622,28 @@ ogrDeparseExpr(Expr* node, OgrDeparseCtx* context)
return ogrDeparseRelabelType((RelabelType*) node, context);
case T_ScalarArrayOpExpr:
/* TODO: Handle this to support the "IN" operator */
- elog(NOTICE, "unsupported OGR FDW expression type, T_ScalarArrayOpExpr");
+ elog(DEBUG2, "unsupported OGR FDW expression type, T_ScalarArrayOpExpr");
return false;
#if PG_VERSION_NUM < 120000
case T_ArrayRef:
- elog(NOTICE, "unsupported OGR FDW expression type, T_ArrayRef");
+ elog(DEBUG2, "unsupported OGR FDW expression type, T_ArrayRef");
return false;
#else
case T_SubscriptingRef:
- elog(NOTICE, "unsupported OGR FDW expression type, T_SubscriptingRef");
+ elog(DEBUG2, "unsupported OGR FDW expression type, T_SubscriptingRef");
return false;
#endif
case T_ArrayExpr:
- elog(NOTICE, "unsupported OGR FDW expression type, T_ArrayExpr");
+ elog(DEBUG2, "unsupported OGR FDW expression type, T_ArrayExpr");
return false;
case T_FuncExpr:
- elog(NOTICE, "unsupported OGR FDW expression type, T_FuncExpr");
+ elog(DEBUG2, "unsupported OGR FDW expression type, T_FuncExpr");
return false;
case T_DistinctExpr:
- elog(NOTICE, "unsupported OGR FDW expression type, T_DistinctExpr");
+ elog(DEBUG2, "unsupported OGR FDW expression type, T_DistinctExpr");
return false;
default:
- elog(NOTICE, "unsupported OGR FDW expression type for deparse: %d", (int) nodeTag(node));
+ elog(DEBUG2, "unsupported OGR FDW expression type for deparse: %d", (int) nodeTag(node));
return false;
}
@@ -596,27 +651,26 @@ ogrDeparseExpr(Expr* node, OgrDeparseCtx* context)
bool
-ogrDeparse(StringInfo buf, PlannerInfo* root, RelOptInfo* foreignrel, List* exprs, OgrFdwState* state, List** params)
+ogrDeparse(StringInfo buf, PlannerInfo* root, RelOptInfo* foreignrel, List* exprs, OgrFdwState* state, List** params_list, OgrFdwSpatialFilter** sf)
{
OgrDeparseCtx context;
ListCell* lc;
bool first = true;
/* initialize result list to empty */
- if (params)
+ if (params_list)
{
- *params = NIL;
+ *params_list = NIL;
}
/* Set up context struct for recursion */
+ memset(&context, 0, sizeof(OgrDeparseCtx));
context.buf = buf;
context.root = root;
context.foreignrel = foreignrel;
- context.params_list = params;
- context.geom = NULL;
+ context.params_list = params_list;
context.state = state;
- // context.geom_op = NULL;
- // context.geom_func = NULL;
+ context.spatial_filter = NULL;
foreach (lc, exprs)
{
@@ -631,9 +685,7 @@ ogrDeparse(StringInfo buf, PlannerInfo* root, RelOptInfo* foreignrel, List* expr
}
/* Unparse the expression, if possible */
- // appendStringInfoChar(buf, '(');
result = ogrDeparseExpr(ri->clause, &context);
- // appendStringInfoChar(buf, ')');
if (! result)
{
@@ -648,6 +700,9 @@ ogrDeparse(StringInfo buf, PlannerInfo* root, RelOptInfo* foreignrel, List* expr
}
}
+ if (context.spatial_filter)
+ *sf = context.spatial_filter;
+
return true;
}
=====================================
output/file.source
=====================================
@@ -139,3 +139,39 @@ SELECT c.*
(4 rows)
------------------------------------------------
+-- FGDB test
+CREATE SERVER fgdbserver
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/vsizip/@abs_srcdir@/data/Querying.zip/Querying.gdb',
+ format 'OpenFileGDB' );
+CREATE FOREIGN TABLE cities (
+ fid bigint,
+ shape bytea,
+ city_fips varchar(5),
+ city_name varchar(40),
+ state_fips varchar(2),
+ state_name varchar(25),
+ state_city varchar(7),
+ type varchar(25),
+ capital varchar(1),
+ elevation integer,
+ pop1990 integer,
+ popcat integer
+) SERVER "fgdbserver"
+OPTIONS (layer 'Cities');
+SET client_min_messages = DEBUG1;
+SELECT fid, city_name, pop1990 FROM cities WHERE pop1990 = 17710;
+DEBUG: OGR SQL: (POP1990 = 17710)
+ fid | city_name | pop1990
+-----+--------------+---------
+ 9 | Port Angeles | 17710
+(1 row)
+
+SELECT fid, city_name, pop1990 FROM cities WHERE city_name = 'Williston';
+DEBUG: OGR SQL: ("CITY_NAME" = 'Williston')
+ fid | city_name | pop1990
+-----+-----------+---------
+ 8 | Williston | 13131
+(1 row)
+
=====================================
output/pgsql.source
=====================================
@@ -1,9 +1,5 @@
-CREATE SERVER pgserver
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource 'PG:dbname=contrib_regression host=localhost',
- format 'PostgreSQL' );
-
+----------------------------------------------------------------------
+-- Create local table
CREATE TABLE bytea_local (
fid serial primary key,
geom bytea,
@@ -19,6 +15,7 @@ CREATE TABLE bytea_local (
yn char
);
----------------------------------------------------------------------
+-- Populate local table
INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
VALUES ('Jim', '14232'::bytea, 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' );
INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
@@ -26,6 +23,12 @@ INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch,
INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
----------------------------------------------------------------------
+-- Create remote table
+CREATE SERVER pgserver
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource 'PG:dbname=contrib_regression host=localhost',
+ format 'PostgreSQL' );
CREATE FOREIGN TABLE bytea_fdw (
fid integer,
geom bytea,
@@ -69,17 +72,79 @@ EXPLAIN VERBOSE
(2 rows)
----------------------------------------------------------------------
-INSERT INTO bytea_fdw (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
-VALUES ('Margaret', '2222'::bytea, 12, 5, 1.4, 19.13, '2001-11-23'::date, '9:12:34'::time, '2001-02-11 09:23:11'::timestamp, 'them', 'y' )
-RETURNING fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn;
- fid | name | geom | age | size | value | num | dt | tm | dttm | varch | yn
------+----------+------------+-----+------+-------+-------+------------+----------+--------------------------+----------+----
- 4 | Margaret | \x32323232 | 12 | 5 | 1.4 | 19.13 | 11-23-2001 | 09:12:34 | Sun Feb 11 09:23:11 2001 | them | y
-(1 row)
-
+-- Remote Query and OGR SQL pushdown
+SET client_min_messages = DEBUG1;
SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
FROM bytea_fdw
WHERE fid = 4;
+DEBUG: OGR SQL: (fid = 4)
+ fid | name | geom | age | size | value | num | dt | tm | dttm | varch | yn
+-----+------+------+-----+------+-------+-----+----+----+------+-------+----
+(0 rows)
+
+SELECT fid, name, dt
+ FROM bytea_fdw
+ WHERE name IS NULL;
+DEBUG: OGR SQL: (name IS NULL)
+ fid | name | dt
+-----+------+----
+ 3 | |
+(1 row)
+
+SELECT fid, name
+ FROM bytea_fdw
+ WHERE name = 'Jim' AND age <= 30;
+DEBUG: OGR SQL: (age <= 30) AND (name = 'Jim')
+ fid | name
+-----+------
+ 1 | Jim
+(1 row)
+
+SELECT fid, name, dt
+ FROM bytea_fdw
+ WHERE name = 'Jim' AND age <= 30 AND dt > '2010-10-1'::date;
+DEBUG: OGR SQL: (age <= 30) AND (dt > '10-01-2010') AND (name = 'Jim')
+ fid | name | dt
+-----+------+------------
+ 1 | Jim | 10-10-2010
+(1 row)
+
+SELECT fid, name
+ FROM bytea_fdw
+ WHERE name = 'Jim' OR name IS NULL;
+DEBUG: OGR SQL: ((name = 'Jim') OR (name IS NULL))
+ fid | name
+-----+------
+ 1 | Jim
+ 3 |
+(2 rows)
+
+----------------------------------------------------------------------
+-- Cached query case, exercised by statement handles or
+-- functions.
+CREATE OR REPLACE FUNCTION get_names()
+ RETURNS varchar AS
+ $$
+ BEGIN
+ RETURN (SELECT string_agg(name,',')
+ FROM bytea_fdw
+ WHERE name = 'Jim' OR name IS NULL);
+ END;
+ $$
+ LANGUAGE 'plpgsql';
+SELECT get_names();
+DEBUG: OGR SQL: ((name = 'Jim') OR (name IS NULL))
+ get_names
+-----------
+ Jim
+(1 row)
+
+DROP FUNCTION get_names();
+----------------------------------------------------------------------
+-- Remote Update
+INSERT INTO bytea_fdw (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
+VALUES ('Margaret', '2222'::bytea, 12, 5, 1.4, 19.13, '2001-11-23'::date, '9:12:34'::time, '2001-02-11 09:23:11'::timestamp, 'them', 'y' )
+RETURNING fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn;
fid | name | geom | age | size | value | num | dt | tm | dttm | varch | yn
-----+----------+------------+-----+------+-------+-------+------------+----------+--------------------------+----------+----
4 | Margaret | \x32323232 | 12 | 5 | 1.4 | 19.13 | 11-23-2001 | 09:12:34 | Sun Feb 11 09:23:11 2001 | them | y
@@ -88,9 +153,11 @@ SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
UPDATE bytea_fdw
SET name = 'Maggie', num = 45.34, yn = 'n'
WHERE age = 12;
+DEBUG: OGR SQL: (age = 12)
SELECT fid, name, num, yn
FROM bytea_fdw
WHERE fid = 4;
+DEBUG: OGR SQL: (fid = 4)
fid | name | num | yn
-----+--------+-------+----
4 | Maggie | 45.34 | n
@@ -99,9 +166,11 @@ SELECT fid, name, num, yn
UPDATE bytea_fdw
SET dt = '2089-12-13', tm = '01:23:45'
WHERE num = 45.34;
+DEBUG: OGR SQL: (num = 45.34)
SELECT fid, dt, tm
FROM bytea_fdw
WHERE fid = 4;
+DEBUG: OGR SQL: (fid = 4)
fid | dt | tm
-----+------------+----------
4 | 12-13-2089 | 01:23:45
@@ -109,6 +178,7 @@ SELECT fid, dt, tm
DELETE FROM bytea_fdw
WHERE fid = 4;
+DEBUG: OGR SQL: (fid = 4)
SELECT a.fid, a.name, b.name
FROM bytea_local a
=====================================
output/postgis.source
=====================================
@@ -0,0 +1,126 @@
+CREATE EXTENSION postgis;
+CREATE TABLE geometry_local (
+ fid serial primary key,
+ geom geometry(Point, 4326),
+ name varchar,
+ age bigint,
+ size integer,
+ value float8,
+ num numeric(6,2),
+ dt date,
+ tm time,
+ dttm timestamp,
+ varch char(8),
+ yn char
+);
+----------------------------------------------------------------------
+-- Populate local table
+INSERT INTO geometry_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
+ VALUES ('Jim', 'SRID=4326;POINT(0 0)', 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' );
+INSERT INTO geometry_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
+ VALUES ('Marvin', 'SRID=4326;POINT(100 0)', 34, 2, 5.4, 10.13, '2011-11-11'::date, '15:21:45'::time, '2011-11-11 15:21:45'::timestamp, 'that', 'n' );
+INSERT INTO geometry_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
+ VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
+----------------------------------------------------------------------
+-- Create remote table
+CREATE SERVER pgservergeom
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource 'PG:dbname=contrib_regression host=localhost',
+ format 'PostgreSQL' );
+CREATE FOREIGN TABLE geometry_fdw (
+ fid integer,
+ geom geometry(point, 4326),
+ name varchar,
+ age bigint,
+ size integer,
+ value float8,
+ num numeric(6,2),
+ dt date,
+ tm time,
+ dttm timestamp,
+ varch char(8),
+ yn char
+) SERVER pgservergeom OPTIONS (layer 'geometry_local');
+SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn FROM geometry_fdw;
+ fid | name | geom | age | size | value | num | dt | tm | dttm | varch | yn
+-----+--------+----------------------------------------------------+-----+------+-------+-------+------------+----------+--------------------------+----------+----
+ 1 | Jim | 0101000020E610000000000000000000000000000000000000 | 23 | 1 | 4.3 | 5.50 | 10-10-2010 | 13:23:21 | Sun Oct 10 13:23:21 2010 | this | y
+ 2 | Marvin | 0101000020E610000000000000000059400000000000000000 | 34 | 2 | 5.4 | 10.13 | 11-11-2011 | 15:21:45 | Fri Nov 11 15:21:45 2011 | that | n
+ 3 | | | | | | | | | | |
+(3 rows)
+
+SELECT a.name, b.name
+ FROM geometry_local a
+ JOIN geometry_fdw b
+ USING (fid);
+ name | name
+--------+--------
+ Jim | Jim
+ Marvin | Marvin
+ |
+(3 rows)
+
+EXPLAIN VERBOSE
+ SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
+ FROM geometry_fdw;
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Foreign Scan on public.geometry_fdw (cost=25.00..1025.00 rows=1000 width=166)
+ Output: fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
+(2 rows)
+
+----------------------------------------------------------------------
+-- Remote Query and OGR SQL pushdown
+SET client_min_messages = DEBUG1;
+SELECT name, age, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE name = 'Jim' AND age <= 30
+ AND geom && ST_MakeEnvelope(-1, -1, 1, 1, 4326);
+DEBUG: OGR SQL: (age <= 30) AND (name = 'Jim')
+DEBUG: OGR spatial filter (-1 -1, 1 1)
+ name | age | st_astext
+------+-----+------------
+ Jim | 23 | POINT(0 0)
+(1 row)
+
+SELECT name, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE ST_Intersects(geom, ST_MakeEnvelope(-1, -1, 1, 1, 4326));
+DEBUG: OGR spatial filter (-1 -1, 1 1)
+ name | st_astext
+------+------------
+ Jim | POINT(0 0)
+(1 row)
+
+SELECT name, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE geom && ST_MakeEnvelope(-180, -90, 180, 90, 4326);
+DEBUG: OGR spatial filter (-180 -90, 180 90)
+ name | st_astext
+--------+--------------
+ Jim | POINT(0 0)
+ Marvin | POINT(100 0)
+(2 rows)
+
+SELECT name, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE ST_MakeEnvelope(-180, -90, 180, 90, 4326) && geom;
+DEBUG: OGR spatial filter (-180 -90, 180 90)
+ name | st_astext
+--------+--------------
+ Jim | POINT(0 0)
+ Marvin | POINT(100 0)
+(2 rows)
+
+SELECT name, ST_AsText(geom)
+ FROM geometry_fdw
+ WHERE ST_MakeEnvelope(-180, -90, 180, 90, 4326) &&
+ ST_MakeEnvelope(-180, -90, 180, 90, 4326);
+ name | st_astext
+--------+--------------
+ Jim | POINT(0 0)
+ Marvin | POINT(100 0)
+ |
+(3 rows)
+
View it on GitLab: https://salsa.debian.org/debian-gis-team/pgsql-ogr-fdw/-/commit/8d1fb1e2cde7fbc444871abaf16c003c5104fcaf
--
View it on GitLab: https://salsa.debian.org/debian-gis-team/pgsql-ogr-fdw/-/commit/8d1fb1e2cde7fbc444871abaf16c003c5104fcaf
You're receiving this email because of your account on salsa.debian.org.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://alioth-lists.debian.net/pipermail/pkg-grass-devel/attachments/20200513/5fc63922/attachment-0001.html>
More information about the Pkg-grass-devel
mailing list