4

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

Rohit Namjoshi
  • 143
  • 5
  • Table names should be enclosed between double quotes in SQL. In the command line you'd need to escape the inner quotes. On Windows in would look like \"geo_export_98941210-c995-4502-8ebb-93a09f29d615\" – user30184 Mar 27 '19 at 05:19
  • @user30184 Thanks, but that is not the issue. As I mentioned, it works fine for several other shapefiles with similar names. Changed to "select AsGeoJSON(geometry) AS geom, * from \"geo_export_98941210-c995-4502-8ebb-93a09f29d615\"" and the error is the same no such table: geo_export_98941210-c995-4502-8ebb-93a09f29d615 – Rohit Namjoshi Mar 27 '19 at 18:20

1 Answers1

2

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.

user30184
  • 65,331
  • 4
  • 65
  • 118