1

I must apologize in advance, I'm just getting into GIS and trying to figure things out.

I have an SQLite spatial database that I'm pretty sure was created in TatukGIS. I've tried to open it in QGIS but the database format is different than that of a Spatialite database. I would like to find a way to convert the SQLite database created in TatukGIS to a Spatialite Database that can be opened in QGIS.

I know it has something to do with the OpenGIS simple features specification and how Spatialite may not fully comply with those specifications, but I don't have enough experience to know how to go about converting it (if it's even possible).

Here is a link to the Databases to give you an idea how they are organized.

https://drive.google.com/folderview?id=0B2TMeZ6iEFvHa3BBOVJpVVpBY1E&usp=sharing

TatukGIS.sqlite is in epsg:3569, Spatialite is in epsg:3857

  • 1
    I had a quick look and do not bother to write an answer. It looks like a tailored but rather simple structure and probably it would not be very difficult to read geometries from the WKB_GEOMETRY table. Because the OGC GeoPackage standard is accepted in February and has already support in GDAL, libgpkg, Geotools and next versions of the ESRI products and Spatialite I suppose that this variant will not receive wide usage. Read news about GeoPackage from geopackage.org. – user30184 Jun 2 '14 at 15:35
2

It was finally pretty easy to get your TatukGIS DB to show in QGIS. First good news is that GDAL finds layers from the DB

>ogrinfo tatukgis.sqlite
INFO: Open of `tatukgis.sqlite'
      using driver `SQLite' successful.
1: GEOMETRY_COLUMNS (None)
2: SPATIAL_REFERENCE_SYSTEMS (None)
3: SQLITETEST
4: SQLITETEST_FEA (None)

What is best is that GDAL also finds geometries from the SQLITETEST table

>ogrinfo tatukgis.sqlite sqlitetest --debug 5
INFO: Open of `tatukgis.sqlite'
      using driver `SQLite' successful.

Layer name: SQLITETEST
Geometry: Unknown (any)
Feature Count: 4
Extent: (1596740.303373, 7261874.067157) - (1597712.327176, 7263118.257626)
Layer SRS WKT:
(unknown)
FID Column = GID
Geometry Column = WKB_GEOMETRY
XMIN: Real (0.0)
YMIN: Real (0.0)
XMAX: Real (0.0)
YMAX: Real (0.0)
OGRFeature(SQLITETEST):1
  XMIN (Real) = 1596837.50575301
  YMIN (Real) = 7263098.81714951
  XMAX (Real) = 1596837.50575301
  YMAX (Real) = 7263098.81714951
  POINT (1596837.5057530145 7263098.8171495069)

...

GDAL can't utilise the metadata tables because it does not recognize the geometry type nor the projection. We can live with it because we know them both and they can also be checked from the TatukGIS DB for example with spatialite-gui. Geometry type can be found from the GEOMETRY_COLUMNS table: geometry_type=1 (point), coord_dimensions=2 (2D), SRID=1. SRID is unknown until having a look into SPATIAL_REFERENCE_SYSTEMS table where the SRID column holds the ID that was used in the GEOMETRY_COLUMNS.

Now we just convert the TatukGIS layer into a new Spatialite DB.

>ogr2ogr -f SQLite -dsco spatialite=yes -a_srs epsg:3569 test2.sqlite tatukgis.sqlite sqlitetest -nlt point

Result in QGIS

TatukGIS converted into Spatialite and opened with QGIS

  • That makes sense, I was just having a hard time reconciling the database structure of the TatukGIS layer, I'm used to working with Spatialite. Thanks for the thorough answer. – Herb Fargus Jun 3 '14 at 14:13
2

The Tatukgis.sqlite db seems to only contain 4 Points located in the NW part of Utah Meridian 26, Township 6S, Range 3E, Section 31. The layer name is Sqlitetest. The SRS is Fips 4302, Utah Stateplane Central, similar to EPSG 3569, as you listed.

These are the coordinates:
1596835.7622,7263099.6599
1597709.8572,7263120.1174
1597672.6616,7261875.9269
1596739.0538,7261914.9822

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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