2

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.

Improve this question
8
  • Perhaps try to export to shapefile first, the OGR2OGR default format, to see if it's OGR2OGR that's having a problem with the geodatabase or with spatial lite.
    – Michael Stimson
    Jun 25, 2015 at 5:24
  • @Michael, thanks for the suggestion. I've tried it and it successfully created two separate shapefiles in a folder named Utility.shp.
    – Frank
    Jun 25, 2015 at 5:33
  • Do they display in QGIS ok? if that is so then the problem is with the import to Spatial Lite (that's one less tree to bark up).. try converting the shapefiles now into a throw-away database to see if you can get them to import into that format.
    – Michael Stimson
    Jun 25, 2015 at 5:42
  • You can try to force the geometry type from ogr2ogr with -nlt LINESTRING too.
    – SaultDon
    Jun 25, 2015 at 6:04
  • 1
    Maybe consider using -nlt PROMOTE_TO_MULTI for that, where it converts all the features to multi-part geometries but allows you to import a geodatabase that's got multiple feature classes in one go.
    – SaultDon
    Jun 25, 2015 at 14:38

1 Answer 1

Reset to default
1

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
Improve this answer
1
  • @SaultDon, I tried using -nlt PROMOTE_TO_MULTI and I got the same bad result -- the tables in the resulting SpatiaLite database are marked as non-spatial. Actually the icon in the QGIS browser and layers panels is kind of weird -- it looks like an image (a raster layer?), instead of a table or a vector layer. I wish I could get this method to work as it produces much smaller files than QSpatiaLite.
    – Frank
    Jun 27, 2015 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.