[gdal-dev] ogr2ogr VRT conversion with SQLite query not working in v. 3.0.1

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] ogr2ogr VRT conversion with SQLite query not working in v. 3.0.1

Deschamps, Benjamin (EC)

Hi,

 

I have a simple VRT merging data from three Shapefile sources, using an SQLITE query. The command executes without any issues with ogr2ogr v. 2.3.0 dev and with 2.1.3, but fails my new 3.0.1 install.

 

ogr2ogr.exe -f "ESRI Shapefile" c:\pathto\test.shp f:\pathto\cis_SGRDAMERGED_20190925_pl_a.vrt

 

The VRT is:

 

<OGRVRTDataSource>

<OGRVRTUnionLayer name="test">

  <OGRVRTLayer name="cis_SGRDAHA_20190925T1800Z_pl_a">

   <SrcSQL dialect="SQLITE">SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAHA_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")</SrcSQL>

   <SrcDataSource>F:\pathto\cis_SGRDAHA_20190925T1800Z_pl_a.shp</SrcDataSource>

  </OGRVRTLayer>

  <OGRVRTLayer name="cis_SGRDAMID_20190925T1800Z_pl_a">

   <SrcSQL dialect="SQLITE">SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAMID_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")</SrcSQL>

   <SrcDataSource>F:\pathto\cis_SGRDAMID_20190925T1800Z_pl_a.shp</SrcDataSource>

  </OGRVRTLayer>

  <OGRVRTLayer name="cis_SGRDAWA_20190925T1800Z_pl_a">

   <SrcSQL dialect="SQLITE">SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAWA_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")</SrcSQL>

   <SrcDataSource>F:\pathto\cis_SGRDAWA_20190925T1800Z_pl_a.shp</SrcDataSource>

  </OGRVRTLayer>

</OGRVRTUnionLayer>

</OGRVRTDataSource>

 

===

Error:

 

ERROR 1: SQL Expression Parsing Error: syntax error, unexpected string, expecting NULL or NOT. Occurred around :

POLY_TYPE IS 'I' AND CT IS NOT '02' AND CT IS NOT '01

             ^

ERROR 1: In ExecuteSQL(): sqlite3_step(SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAHA_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")):

  Cannot apply attribute filter : POLY_TYPE IS 'I' AND CT IS NOT '02' AND CT IS NOT '01' AND CT IS NOT '00'

ERROR 1: SQL statement failed, or returned no layer result:

SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAHA_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")

ERROR 1: SQL Expression Parsing Error: syntax error, unexpected string, expecting NULL or NOT. Occurred around :

POLY_TYPE IS 'I' AND CT IS NOT '02' AND CT IS NOT '01

             ^

ERROR 1: In ExecuteSQL(): sqlite3_step(SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAMID_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")):

  Cannot apply attribute filter : POLY_TYPE IS 'I' AND CT IS NOT '02' AND CT IS NOT '01' AND CT IS NOT '00'

ERROR 1: SQL statement failed, or returned no layer result:

SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAMID_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")

ERROR 1: SQL Expression Parsing Error: syntax error, unexpected string, expecting NULL or NOT. Occurred around :

POLY_TYPE IS 'I' AND CT IS NOT '02' AND CT IS NOT '01

             ^

ERROR 1: In ExecuteSQL(): sqlite3_step(SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAWA_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")):

  Cannot apply attribute filter : POLY_TYPE IS 'I' AND CT IS NOT '02' AND CT IS NOT '01' AND CT IS NOT '00'

ERROR 1: SQL statement failed, or returned no layer result:

SELECT GEOMETRY, POLY_TYPE FROM cis_SGRDAWA_20190925T1800Z_pl_a WHERE (POLY_TYPE IS "I") AND (CT IS NOT "00") AND (CT IS NOT "01") AND (CT IS NOT "02")

 

Any known issues already reported?

 

Thanks,

 

Benjamin


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr VRT conversion with SQLite query not working in v. 3.0.1

Even Rouault-2
On jeudi 26 septembre 2019 15:22:13 CEST Deschamps, Benjamin (EC) wrote:
> Hi,
>
> I have a simple VRT merging data from three Shapefile sources, using an
> SQLITE query. The command executes without any issues with ogr2ogr v. 2.3.0
> dev and with 2.1.3, but fails my new 3.0.1 install.

Benjamin,

The issue is likely not the upgrade of GDAL version, but the fact that it must
use version >= 3.21 whereas older versions used an older SQLite version. With
SQLite >= 3.21, GDAL tries to propagate IS constant / IS NOT constant
constructs to OGR SQL, but OGR SQL doesn't support those constructs. I've just
push a fix to avoid that propagation. If you don't strongly depend on the
difference of semantics between IS and = (how they deal with NULL values),
then just use =

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev