[GIS] ogr2ogr from personal geodatabase to spatialite produces “non-spatial” tables

convertogr2ogrspatialite

I have around 4 GB of data in 10 separate Personal Geodatabases, each of which contains multiple feature classes. I'm trying to convert the smallest one to SpatiaLite via ogr2ogr, in preparation for converting them all. The geodatabase contains two feature classes, both of which are linestrings. Here is my command line:

ogr2ogr -f SQLite -dsco SPATIALITE=YES Utility.sqlite Utility.mdb

But I get inconsistent results when I attempt to display the imported data in QGIS.

If I create a connection to the SpatiaLite database and then open a layer via the SpatiaLite node in the browser, I get non-spatial tables. Specifically, the DB manager tells me "This is not a spatial table".

But if I load the same layer directly from the database's location on the file system (within the browser) it displays perfectly.

I've been experimenting with different ogr2ogr arguments and I've also tried running RecoverGeometryColumn() on the imported data, but I can't make the thing display properly. (RecoverGeometry returned an error about "no such column", but it's actually referring to the table name. I'm slightly confused.)

I have created several other SpatiaLite databases within QGIS or Spatialite_GUI, which do work properly. But I'm not familiar enough with the database schema to identify any obvious differences between the ones that work and the ones that don't.

I'm migrating from ArcGIS (having not used it for a few years) and I'm very new to the rawness of relying on SQL commands (and command line statements in general) to manage data, so I'm happy to accept that I've missed something obvious. I'd just love to know what it is.

Best Answer

I've tried a few different ways to carry out this conversion and in the end the simplest way turned out to be:

  • Loading all of the feature classes within an individual personal geodatabase into QGIS
  • Batch converting them to to a corresponding SpatiaLite database via the QSpatiaLite toolbar button, using the Import QGIS Layers option

It isn't ideal because it doesn't solve the problem of ogr2ogr creating databases that are spatial if you load them one way but non-spatial if you load them another. However, it does have a few advantages:

  • It avoids intermediate shapefiles, which don't support all of the capabilities of geodatabases and SpatiaLite databases
  • Each conversion is way faster than a batch file using the equivalent ogr2ogr conversion
  • It handles mixed feature classes within a single database without my having to cast them to one feature type or another
Related Question