[gdal-dev] ogr2ogr sqlite dialect and fgb format

[Scott]

There may be a way to address the underlying issue, I don't know what it 
is. In lieu of that, here's a work around in 2 steps in 3.7.

Create a gpkg:

ogr2ogr -sql "select * from roadbed where source_id = 13350003311" 
tmp.gpkg /vsizip/NYC_DoITT_Planimetric_OpenData.gdb.zip

Then do it with -dialect sqlite on the gpkg:

ogr2ogr -f GeoJSONseq -sql "select shape from roadbed where source_id = 
13350003311" -dialect sqlite /vsistdout/ tmp.gpkg | more




On 6/28/23 08:50, Patrick Young wrote:
> Here's the CREATE TABLE from 3.4:
> 
> OGR2SQLITE: sqlite3_declare_vtab(CREATE TABLE "roadbed"("SOURCE_ID" 
> FLOAT,"FEATURE_CODE" INTEGER_INT16,"SUB_FEATURE_CODE" INTEGER,"STATUS" 
> VARCHAR(16),"SHAPE_Length" FLOAT,"SHAPE_Area" FLOAT,OGR_STYLE VARCHAR 
> HIDDEN,"SHAPE" BLOB_MULTIPOLYGON_XY_2263, OGR_NATIVE_DATA VARCH
> AR, OGR_NATIVE_MEDIA_TYPE VARCHAR))
> 
> In the 3.5+ versions, sqlite is angry about the 
> INTEGER_INT16_BEGIN_DOMAIN_NAME_64526F6164626564_END_DOMAIN_NAME
> 
> On Wed, Jun 28, 2023 at 9:18 AM Patrick Young 
> <patrick.mckendree.young at gmail.com 
> <mailto:patrick.mckendree.young at gmail.com>> wrote:
> 
>     Hi,
> 
>     I'm playing around with the gdb dataset
> 
>     https://data.cityofnewyork.us/Transportation/NYC-Planimetrics/wt4d-p43d <https://data.cityofnewyork.us/Transportation/NYC-Planimetrics/wt4d-p43d>
> 
>     and getting errors starting with gdal 3.5 when I try to use the
>     sqlite dialect:
> 
>     ogr2ogr -f GeoJSONSeq -sql "select shape from roadbed where
>     source_id = 13350003311" -dialect sqlite /vsistdout/
>     NYC_DoITT_Planimetric_OpenData.gdb.zip
> 
>     ERROR 6: Unsupported field type for range domain: esriFieldTypeDate
>     ERROR 1: Cannot create virtual table for layer 'roadbed' : CREATE
>     VIRTUAL: invalid SQL statement : CREATE TABLE "roadbed"("SOURCE_ID"
>     FLOAT,"FEATURE_CODE"
>     INTEGER_INT16_BEGIN_DOMAIN_NAME_64526F6164626564_END_DOMAIN_NAME,"SUB_FEATURE_CODE" INTEGER,"STATUS" VARCHAR(16)_BEGIN_DOMAIN_NAME_645374617475735F31_END_DOMAIN_NAME,"SHAPE_Length" FLOAT,"SHAPE_Area" FLOAT,OGR_STYLE VARCHAR HIDDEN,"SHAPE" BLOB_MULTIPOLYGON_XY_2263, OGR_NATIVE_DATA VARCHAR, OGR_NATIVE_MEDIA_TYPE VARCHAR)
>     ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select shape from
>     roadbed where source_id = 13350003311):
>        no such table: roadbed
> 
>     This command works in GDAL 3.4 (and also works with the ogr sql
>     dialect); I was wanting to use the sqlite dialect so that i can use
>     CASE statements.  Is this a bug or expected?
> 
>     Thanks,
>     Patrick
> 
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev