Subscribe to this thread
Home - General / All posts - reading geometries from SQLite
artlembo


3,400 post(s)
#26-Jan-21 17:31

just wondered if anyone had an answer to this. If not, I'll send the file to tech.

I have a sqlite database with geometries stored in them (not geopackage). I have two types of geometry tables: ones that are imported in with Spatialite, and ones where I create the geometry table with SQL. I always issue a recovergeometrycolumn and create a spatial index with SpatiaLite.

Manifold 9 can read the sqlite database, and can display the geometries for those tables where I imported a geometry file. However, Manifold 9 cannot display the geometries from the table that I created in SQLite by issuing an SQL query with CREATE TABLE AS .... It identifies the table as a geometry table, and even defines the correct coordinate system, but the geometry field is NULL. In the properties, it is identified as nvarchar.

Those same geometry tables CAN be read with QGIS and ArcGIS.

I'm guessing it has something to do with the format of the geometry column, and that is why it comes in as blank, null values. But, it is curious that QGIS and ArcGIS have no problem with it.

Like I said, if someone has encountered there, great. If not, I'll send the .sqlite file to tech so they can perhaps fix the issue.

adamw


10,447 post(s)
#27-Jan-21 14:49

You can try removing paths to SQLITE3.DLL on your machine and working with the database in this way (we have SQLITE built-in now). If this doesn't help, we need to take a look at the database file. (One thing that immediately comes to mind is that we might bail out due to an unexpected field type: SQLITE is an interesting piece of technology where the declared type of a field might not match the actual stored type of values, so the values themselves might be fine, but the type declaration of a field wrong.)

artlembo


3,400 post(s)
#20-Feb-21 03:41

I did a little more experimentation. The issue is that QGIS is very forgiving - basically, I think they are ignoring the rules. So, they just read whatever is ingeometry_columns as a reference, and then pull the data in from the main table.

In ArcGIS Pro and Manifold 9's case, you guys are obeying the rules, and the way I was creating the tables sort of violates that (although, it was nice to write shorter SQL statements and have QGIS read it).

The original way I was writing the queries was like this:

DROP TABLE IF EXISTS spatialesri;

CREATE TABLE spatialesri AS

SELECT state, sum(positiveIncrease) AS positive,sum(negativeIncrease) as negative,

sum(deathIncrease) AS  deaths, geometry

FROM daily, state

WHERE stusps = daily.state

GROUP BY geometry, state;

SELECT recovergeometrycolumn('spatialesri','geometry',4269,'MULTIPOLYGON');

but, in order for ArcGIS or M9 to properly read the geometry tables, it had to be written like this:

DROP TABLE IF EXISTS spatialesri;

CREATE TABLE spatialesri AS

SELECT state.rowid AS pk_uid, state,

CAST(sum(deathIncrease) AS Integer) AS  deaths, geometry

FROM daily, state

WHERE stusps = daily.state

GROUP BY geometry, state;

SELECT addGeometryColumn('spatialesri','g',4269,'MULTIPOLYGON','XY');

UPDATE spatialesri SET g = geometry;

SELECT recovergeometrycolumn('spatialesri','g',4269,'MULTIPOLYGON', 'XY');

notice the need for addGeometryColumn and the UPDATE statement. Also, at least for ArcPro, it was necessary to properly CAST the fields or it would crash ArcPro.

This solution does work (I tested it in 9), but I don't know if you guys want to re-evaluate relaxing the requirements so you don't have to create the new column, etc.

The reason for all of this is because I'm teaching a workshop in SQL, spatial SQL, and COVID-19, and I was doing the visualizations in QGIS, and also wanted to do the visualizations in ArcPro and Manifold as a bonus feature.

So, while the SQL above will work for M9, this video shows how to issue the SQLite queries, and have ArcPro read it. You can simply have M9 attach to the SQLite database.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.