ogr2ogr – Fixing ‘No Such Table’ Error in ogr2ogr

ogr2ogr

I have downloaded a shape file of Chicago building footprints from here. Unzipped it and ran

ogr2ogr -f csv -dialect sqlite -sql "select AsGeoJSON(geometry) AS geom, * from 'geo_export_98941210-c995-4502-8ebb-93a09f29d615'" chicago_building_footprints.csv geo_export_98941210-c995-4502-8ebb-93a09f29d615.shp

Why do I get the following error? Have no issues with several other shape files downloaded from the same site. Using GDAL 2.2.3, released 2017/11/20 on Ubuntu 18.04.2.

ERROR 1: Cannot create virtual table for layer
'geo_export_98941210-c995-4502-8ebb-93a09f29d615' : CREATE VIRTUAL:
invalid SQL statement : CREATE TABLE
"geo_export_98941210-c995-4502-8ebb-93a09f29d615"("date_bldg_"
DATE,"time_bldg_" VARCHAR(254),"bldg_condi" VARCHAR(254),"date_bldg_"
DATE,"time_bldg_" VARCHAR(254),"date_bldg_" DATE,"time_bldg_"
VARCHAR(254),"bldg_id" FLOAT,"bldg_name1" VARCHAR(254),"bldg_name2"
VARCHAR(254),"bldg_sq_fo" FLOAT,"bldg_statu" VARCHAR(254),"cdb_city_i"
VARCHAR(254),"comments" VARCHAR(254),"date_condi" DATE,"time_condi"
VARCHAR(254),"create_use" VARCHAR(254),"date_demol" DATE,"time_demol"
VARCHAR(254),"date_edit_" DATE,"time_edit_" VARCHAR(254),"edit_sourc"
VARCHAR(254),"edit_useri" VARCHAR(254),"f_add1" FLOAT,"footprint_"
VARCHAR(254),"harris_str" VARCHAR(254),"label_hous"
VARCHAR(254),"no_of_unit" FLOAT,"no_stories" FLOAT,"non_standa"
VARCHAR(254),"orig_bldg_" FLOAT,"pre_dir1" VARCHAR(254),"date_qc_da"
DATE,"time_qc_da" VARCHAR(254),"qc_source" VARCHAR(254),"qc_userid"
VARCHAR(254),"shape_area" FLOAT,"shape_len" FLOAT,"st_name1"
VARCHAR(254),"st_type1" VARCHAR(254),"stories" FLOAT,"suf_dir1"
VARCHAR(254),"t_add1" FLOAT,"unit_name" VARCHAR(254),"vacancy_st"
VARCHAR(254),"x_coord" FLOAT,"y_coord" FLOAT,"year_built"
FLOAT,"z_coord" FLOAT,OGR_STYLE VARCHAR HIDDEN,"GEOMETRY"
BLOB_POLYGON_XY_4326, OGR_NATIVE_DATA VARCHAR, OGR_NATIVE_MEDIA_TYPE
VARCHAR) ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select
AsGeoJSON(geometry) AS geom, * from
'geo_export_98941210-c995-4502-8ebb-93a09f29d615'): no such table:
geo_export_98941210-c995-4502-8ebb-93a09f29d615

Best Answer

That site seems to deliver an invalid shapefile that has duplicate field names in the attributes. The shapefile is also invalid for another reason according to the shapefile specification because the .dbf part is more than 6 GB in size while the standard allows only 2 GB. Technically the .dbf part is valid and GDAL can handle that but a workaround is needed for fixing the duplicate field names.

The error message that you got does not tell the real reason for the failure but I reused the CREATE TABLE part with spatialite-gui which prints a clear message:

SQL error: dublicate column name: date_bldg_

Next I verified that with ogrinfo:

ogrinfo -al -so geo_export_98941210-c995-4502-8ebb-93a09f29d615.shp
INFO: Open of `geo_export_98941210-c995-4502-8ebb-93a09f29d615.shp'
      using driver `ESRI Shapefile' successful.

Layer name: geo_export_98941210-c995-4502-8ebb-93a09f29d615
Metadata:
  DBF_DATE_LAST_UPDATE=1919-03-27
Geometry: Polygon
Feature Count: 820606
Extent: (-87.939814, 41.644595) - (-87.524208, 42.023000)
...
date_bldg_: Date (10.0)     <==
time_bldg_: String (254.0)
bldg_condi: String (254.0)
date_bldg_: Date (10.0)     <==
time_bldg_: String (254.0)
date_bldg_: Date (10.0)     <==
time_bldg_: String (254.0)

The workaround is to let GDAL to convert all the data into some suitable format like GeoPackage or SpatiaLite. Because there seems to be some problem with GeoPackage and your workflow with current software versions you'd better use Spatialite as interim format for fixing the data.

ogr2ogr -f sqlite -dsco spatialite=yes fix.sqlite geo_export_98941210-c995-4502-8ebb-93a09f29d615.shp -nlt MULTIPOLYGON
Warning 1: Field 'date_bldg_' already exists. Renaming it as 'date_bldg_2'
Warning 1: Field 'time_bldg_' already exists. Renaming it as 'time_bldg_2'
Warning 1: Field 'date_bldg_' already exists. Renaming it as 'date_bldg_3'
Warning 1: Field 'time_bldg_' already exists. Renaming it as 'time_bldg_3'

Now your command will work with some changes:

GDAL SQLite driver launders table names and changes dashes (SQL thinks they are minus operators!) into underscores so the new table name does not require escaping.

Test the command with a couple of features:

ogr2ogr -f csv -dialect sqlite -sql "select AsGeoJSON(geometry) AS geom, * from geo_export_98941210_c995_4502_8ebb_93a09f29d615 limit 2" chicago_building_footprints.csv fix.sqlite

Chect the result

geom,date_bldg_,time_bldg_,bldg_condi,date_bldg_2,time_bldg_2,date_bldg_3,time_bldg_3,bldg_id,bldg_name1,bldg_name2,bldg_sq_fo,bldg_statu,cdb_city_i,comments,date_condi,time_condi,create_use,date_demol,time_demol,date_edit_,time_edit_,edit_sourc,edit_useri,f_add1,footprint_,harris_str,label_hous,no_of_unit,no_stories,non_standa,orig_bldg_,pre_dir1,date_qc_da,time_qc_da,qc_source,qc_userid,shape_area,shape_len,st_name1,st_type1,stories,suf_dir1,t_add1,unit_name,vacancy_st,x_coord,y_coord,year_built,z_coord
"{""type"":""MultiPolygon"",""coordinates"":[[[[-87.6667306636366,41.70797603324222],[-87.66686799082457,41.70797682976895],[-87.66686709803042,41.70806327088081],[-87.66668398069386,41.70806358088658],[-87.66668285842598,41.70799496621002],[-87.66673046519738,41.70799524237777],[-87.6667306636366,41.70797603324222]]]]}",1998/04/01,08:00:00.000,SOUND,1998/04/01,08:00:00.000,,,729719,,,2196,ACTIVE,,,2003/03/01,08:00:00.000,,,,,,,,10203,AERIALS98,"02507416001000","10203",1,0,,729719,S,2003/06/16,07:00:00.000,PARCELS,DS06284,1506.25,164.007499438,WOOD,ST,2,,10203,,,1166255.28615297,1836900.51661469,1899,0
"{""type"":""MultiPolygon"",""coordinates"":[[[[-87.74054777420342,41.98823925861186],[-87.74054890043631,41.98831610197826],[-87.74039808424241,41.98831669519352],[-87.74039694545855,41.98824122386017],[-87.74054777420342,41.98823925861186]]]]}",1998/04/01,08:00:00.000,SOUND,1998/04/01,08:00:00.000,,,46828,,,1927,ACTIVE,,,2003/05/01,07:00:00.000,,,,,,,,5901,AERIALS98,"01303306018000","5901",1,0,,46828,N,2003/06/16,07:00:00.000,PARCELS,DS06284,1138.125,137.524250985,KILBOURN,AVE,2,,5901,,,1145426.86651843,1938878.05056531,1937,0

Looks perfect so remove limit 2 and run the command again.

Please contact the City of Chicago and ask them to stop delivering invalid shapefiles. I would recommend them to use GeoPackage format instead.

Related Solutions

Convert Shapefile to MSSQL with Ogr2ogr – Connection Error Fix

The ODBC documentation suggests keywords UID and PWD instead of User ID and Password.

[GIS] Load data to exists table with ogr2ogr

According to the documentation of the OCI driver (http://www.gdal.org/ogr/drv_oci.html) ogr always creates a column called OGR_FID to be used as row identifier. When appending, the new rows get new values for OGR_FID.

That is the behaviour I observe too: - the first load creates the table, including an OGR_FID column. - the subsequent append loads and keeps on populating OGR_FID.

According to the documentation: "If the table has an integer column called OGR_FID it will be used as the feature id by OGR". I take that to mean that it is fine for an existing table to have no OGR_FID column, but that does not work: the -append load fails with a segmentation fault.

The doc also says: "To force use of a particular field name the OCI_FID configuration variable (ie. environment variable) can be set to the target field name." That works fine: if you set

export OCI_FID=ID

before calling ogr2ogr, then the unique identifier column will be called ID. That works for the -append runs too.

But in both cases, the "feature identifier" column - whatever its name: the default OGR_FID or your own name - is always created and managed by ogr2ogr. From what I see it is not possible for you to fill it with your own values.

If all you want is to use another name, then use the export method above. If you want to fully manage it yourself, for example populate if with your own values, then I don't see this as being possible. Your best approach is just to create the output table with your own unique identifier column (FID) that you can them populate at will, while letting ogr define and mange its own identifier (OGR_FID).

Note that ogr does not create OGR_FID as a primary key.

Best Answer

Related Solutions

Convert Shapefile to MSSQL with Ogr2ogr – Connection Error Fix

[GIS] Load data to exists table with ogr2ogr

Related Question