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.
|