Features missing geometry data cause query to fail.

[chander]

In some cases there appears to be an issue when attempting to query geometry fields on external layers if one or more attributes is missing a geometry.

It would be great if there was an option to set the geoms to null when they were missing - or at least didn't fail the query. I looked in the OGR options for the GeoJSON layer type, but it appears no such OPEN option exists.

TL;DR - computing the extent (select st_extent(geom) from esrijson) on the external layer https://services.arcgis.com/jDGuO8tYggdCCnUJ/ArcGIS/rest/services/CA_High_Mag_Faults_10_16_13/FeatureServer/0/query?where=1%3D1&outfields=*&f=json results in the error message in the subject:

pgogr=# select st_extent(geom) from esrijson ;
ERROR:  GDAL AppDefined [1] Invalid Feature object. Missing 'geometry' member.

This appears to be because the service has numerous features without geometry data; you can see that here:

root@1089049f47b4:/# ogrinfo -ro -al  'https://services.arcgis.com/jDGuO8tYggdCCnUJ/ArcGIS/rest/services/CA_High_Mag_Faults_10_16_13/FeatureServer/0/query?where=1%3D1&outfields=*&f=json' > /dev/null
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.
ERROR 1: Invalid Feature object. Missing 'geometry' member.

Here's the ogrinfo output from the layer in question - it is publically accessible, so should be easy to reproduce:

root@1089049f47b4:/# ogrinfo -ro -al -so 'https://services.arcgis.com/jDGuO8tYggdCCnUJ/ArcGIS/rest/services/CA_High_Mag_Faults_10_16_13/FeatureServer/0/query?where=1%3D1&outfields=*&f=json'
INFO: Open of `https://services.arcgis.com/jDGuO8tYggdCCnUJ/ArcGIS/rest/services/CA_High_Mag_Faults_10_16_13/FeatureServer/0/query?where=1%3D1&outfields=*&f=json'
      using driver `ESRIJSON' successful.

Layer name: ESRIJSON
Geometry: Line String
Feature Count: 12030
Extent: (-126.383239, 32.535806) - (-115.375759, 42.136007)
Layer SRS WKT:
PROJCS["WGS 84 / Pseudo-Mercator",
    GEOGCS["WGS 84",
        DATUM["WGS_1984",
            SPHEROID["WGS 84",6378137,298.257223563,
                AUTHORITY["EPSG","7030"]],
            AUTHORITY["EPSG","6326"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4326"]],
    PROJECTION["Mercator_1SP"],
    PARAMETER["central_meridian",0],
    PARAMETER["scale_factor",1],
    PARAMETER["false_easting",0],
    PARAMETER["false_northing",0],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["X",EAST],
    AXIS["Y",NORTH],
    EXTENSION["PROJ4","+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs"],
    AUTHORITY["EPSG","3857"]]
OBJECTID: Integer (0.0)
QFAULTL_25: Real (0.0)
NAME: String (80.0)
CODE: Real (0.0)
NUM: String (6.0)
SLIP: String (4.0)
AGE: String (30.0)
RATE: String (15.0)
SLIPSENSE: String (12.0)
DIPDIRECTI: String (15.0)
ACODE: Real (0.0)
WWWURL: Real (0.0)

Running ogr_fdw_info results in, which creates fine - but returns the error mentioned when an extent is queried:

root@1089049f47b4:/# /usr/lib/postgresql/12/bin/ogr_fdw_info -s "https://services.arcgis.com/jDGuO8tYggdCCnUJ/ArcGIS/rest/services/CA_High_Mag_Faults_10_16_13/FeatureServer/0/query?where=1%3D1&outfields=*&f=json" -l ESRIJSON

CREATE SERVER myserver
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
        datasource 'https://services.arcgis.com/jDGuO8tYggdCCnUJ/ArcGIS/rest/services/CA_High_Mag_Faults_10_16_13/FeatureServer/0/query?where=objectid+%3D+objectid&outfields=*&f=json',
        format 'ESRIJSON' );

CREATE FOREIGN TABLE esrijson (
  fid bigint,
  geom Geometry(LineString,3857),
  objectid integer,
  qfaultl_25 double precision,
  name varchar(80),
  code double precision,
  num varchar(6),
  slip varchar(4),
  age varchar(30),
  rate varchar(15),
  slipsense varchar(12),
  dipdirecti varchar(15),
  acode double precision,
  wwwurl double precision
) SERVER "myserver"
OPTIONS (layer 'ESRIJSON');

On a side note, this also has the is not null issue that was remarked on in another ticket (I only mention this because it's easily reproducible here):

pgogr=# select geom from esrijson where geom is not null;
ERROR:  GDAL AppDefined [1] SQL Expression Parsing Error: syntax error, unexpected IS. Occurred around :
( IS NOT NULL)

[pramsey]

Hm, I'm not seeing the problem... I think it may have been a problem with OGR, tossing an error on a missing geometry instead of being chill about it. The FDW naturally stops processing when it is given an ERROR condition. Now that it's not getting that, things work.

ogr_fdw=# select st_extent(geom) from esrijson;
                                 st_extent                                 
---------------------------------------------------------------------------
 BOX(-14068917.7610828 3833850.78400869,-12843570.764635 5181374.40356445)
(1 row)

[pramsey]

I can, however, replicate the geometry null filter issue

ogr_fdw=# select geom from esrijson where geom is not null;
ERROR:  GDAL AppDefined [1] SQL Expression Parsing Error: syntax error, unexpected IS. Occurred around :
( IS NOT NULL)
  ^