simonw / shapefile-to-sqlite

Load shapefiles into a SQLite (optionally SpatiaLite) database
Apache License 2.0
28 stars 4 forks source link

"duplicate column name" importing Santa Clara County Parks #5

Closed simonw closed 4 years ago

simonw commented 4 years ago

https://www.sccgov.org/sites/parks/Parks-Maps/Maps-Data/Pages/home.aspx

This shapefile package: https://www.sccgov.org/sites/parks/Documents/shape.zip

(shapefile-to-sqlite) ~/Dropbox/Development/shapefile-to-sqlite $ shapefile-to-sqlite parks.db shape/*.shp
  [###########-------------------------]   31%  00:04:41
Traceback (most recent call last):
  File "/Users/simonw/.local/share/virtualenvs/shapefile-to-sqlite-53xajEHi/bin/shapefile-to-sqlite", line 11, in <module>
    load_entry_point('shapefile-to-sqlite', 'console_scripts', 'shapefile-to-sqlite')()
  File "/Users/simonw/.local/share/virtualenvs/shapefile-to-sqlite-53xajEHi/lib/python3.7/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/Users/simonw/.local/share/virtualenvs/shapefile-to-sqlite-53xajEHi/lib/python3.7/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/Users/simonw/.local/share/virtualenvs/shapefile-to-sqlite-53xajEHi/lib/python3.7/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/simonw/.local/share/virtualenvs/shapefile-to-sqlite-53xajEHi/lib/python3.7/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/Users/simonw/Dropbox/Development/shapefile-to-sqlite/shapefile_to_sqlite/cli.py", line 45, in cli
    spatialite_mod=spatialite_mod,
  File "/Users/simonw/Dropbox/Development/shapefile-to-sqlite/shapefile_to_sqlite/utils.py", line 67, in import_features
    db[table].insert_all(features_iter, conversions=conversions, alter=alter)
  File "/Users/simonw/.local/share/virtualenvs/shapefile-to-sqlite-53xajEHi/lib/python3.7/site-packages/sqlite_utils/db.py", line 997, in insert_all
    extracts=extracts,
  File "/Users/simonw/.local/share/virtualenvs/shapefile-to-sqlite-53xajEHi/lib/python3.7/site-packages/sqlite_utils/db.py", line 618, in create
    extracts=extracts,
  File "/Users/simonw/.local/share/virtualenvs/shapefile-to-sqlite-53xajEHi/lib/python3.7/site-packages/sqlite_utils/db.py", line 310, in create_table
    self.conn.execute(sql)
sqlite3.OperationalError: duplicate column name: ID

Firing up the debugger:

$ python -i $(which shapefile-to-sqlite) parks.db shape/*.shp
>>> import pdb
>>> pdb.pm()
...
{'id': '0', 'geometry': {'type': 'Point', 'coordinates': (6210529.4865178615, 1824833.228932187)}, 'PARK': 'Mt. Madonna', 'CAPACITY': 8, 'TYPE': 'tent', 'ID': '221', 'comment': None, 'DESIGNATIO': None, 'CAMPGROUND': 'Valley View 2'}
simonw commented 4 years ago

I'm going to fix this by spotting keys in the properties array called id (or ID) and renaming them to id_.