SQLite as future-proof backend for Personal Geodabases

595
4
05-08-2019 11:23 AM
Status: Open
Occasional Contributor

Split fromEnable ArcGIS Pro to access ESRI Personal Geodatabases , which has a primary component of "let me read and manipulate MS Access databases from ArcGIS Pro". This idea is to highlight and make distinct the other major part of the thread, "let me have a db storage format that I can use industry standard non-Esri tools to work with".

Why Personal Geodatabases are still needed

Selections from the related thread on this distinct idea:

"No, a File Geodatabase is not a suitable alternative or replacement [for mdb].  A FGD is a silo and cuts the data off from most other programs.  It isolates the data and can only be accessed from ESRI programs or GIS programs that have access to it and cannot be read at all by office productivity software or any other database software.  It's not useful to me."

"This discussion is not about speed but of compatibility with products which use the gis data outside of ESRI.  Even though, slower, in the end due to the work effort - you reach your destination much faster."

"Further, FGDBs only support a restricted subset of the SQL language, and some parts of the subset are only accessible through ArcObjects (using their PostFixClause attribute). You can't, for example, use ORDER BY or DISTINCT to select by attribute with an FGDB, but you can with a PGDB. PGDBs aren't perfect, but they do have their uses."

"...my biggest issue isn't so much that "Pro can't read the pGDB" - I could theoretically convert all that data into a FGDB before losing Desktop altogether - but this would not solve my current problem. [...] I'm open to another solution from ESRI - it doesn't necessarily HAVE to be an Access-faced database, but SOMETHING that a non-ESRI-product-owning client can use WHILE IT IS STILL also a GIS database - i.e. the data storage device needs to be editable/accessible/view-able in both spatial format AND tabular format by both ESRI product owners and non-ESRI product owners. 

 

If my clients are forced to own ESRI software to work with my data, then they will chose a different solution and cut ESRI out completely - and I will lose work."

"Pro is a no-go for my users until pGDBs are supported. [...] We run Enterprise GDBs for Enterprise workflows. We have complex workflows at the organizational level that join disparate sources of data and run business units. For that we use and Enterprise Data basing solution. However the majority of our users have no need whatsoever for the level of complexity inherent in enterprise databasing. They want to fire up the software, do some editing, get a result. Wham Bam done.  And for that they use pGDBs because pGDBs are accessible to SQL, outside of ArcGIS. [...]


ESRI fGDBs are almost entirely useless as a "data" storage back end.  They can't be accessed by anything other than ArcGIS. [...] Data goes in but you can't get it back out. It's trapped there. [...] Yes pGDBs are 'slow' in I/O.  But they are extremely fast to the finish line for data users who have desktop needs. [...] ESRI could change that paradigm by releasing the full spec on the fGDB and a non-map interface where we can implement the SQL and data analysis workflows that are higher level than map making and what the users want. But they have not. So all we have is QGIS if we want to use fGDB data.

 

Our users will continue to use pGDBs until Microsoft ends MS Access.  Pro doesn't work with pGDBS, Pro doesn't work for our basic users. The enhancement is necessary in the Pro product before we transition. If it isn't there, we don't transition to Pro. The calculus is really that simple."

Ok, so why SQLite for the format and not accdb or something else?

I submit that a fruitful path forward would be to embrace SQLite as the future Personal Geodatabase format:

 

Note: this suggestion is different from Add support for SQLite/SpatiaLite and PostGIS geodatabases  (which I encourage people to vote for and comment on). What I'm proposing here is "make SQLite-gdb a first class citizen on par with File-GDB", which is more than just "supports", which Esri can argue it does already. (For me 'no editing' means

'not supported').

4 Comments

But which Sqlite spatial format? There are two systems already, the original spatialite which is handled with an extension to sqlite, like other databases, and the OGC geopackage format which is different to spatialite.

ESRI influenced the geopackage format, and spatialite has bent to include the new metadata files.

But there are still problems with the gpkg format. They forgot to include non-spatial tables in the format! So GDAL has added an extension that ESRI has not supported so far. Maybe they have recently, I haven't seen anything in What's New.

I think spatialite is excellent, but there are a few things that obviously worries Esri developers. The type checking is lenient, like Excel, so they add a whole lot of constraints to the tables when creating them to avoid including text in numeric fields. There is no date type so dates have to be handled in functions. Dates default to an epoch of seconds since 1900, unlike anyone else and there are no converters outside sqlite. I store dates as strings so they can be transferred.

FME is also very clunky when supporting spatialite. You have to use two writers and cannot use them together. It's all a mess.

Spatialite has its own built-in spatial operators, used an R-Tree spatial index and is very very fast compared to a filegeodatabase. Because it supports SQL better there are some operations that are thousands of times faster. For example, add a field to a non-spatial table and populate it with geometry from another many to one related table, even from another database.

Arcpy half-supports spatialite, you can use cursors and update tables but not geometry using the @shape operators.

Copying from spatialite to another database is almost instantaneous compared to exporting to a file geodatabase.

If you need to edit the data then simply use python and build an SQL query string by opening the database and extending the data to use spatial using the DLL already included in ArcGIS (because they use it under the hood for mobile apps).

For interactive editing you will have to resort to QGIS so why not simply open it up in ArcPro?? It can't be any different to other geodatabases such as PostGIS or SQL Server, but without the overhead of a server.

Thank you for contributing real information Kim. I'm sure all of these limitations and concerns could be addressed, but not if people aren't aware of them and aren't talking abou them.

I'm a bit surprised by the lenient type checking for dates, as the sqlite developers come across as especially conservative and careful, to excessiveness even. Maybe it's the spatiallite wrapper that's undoing some of that. [Later] oh I see, the blessed path is to use the native built in date functions.

And now at 10.8 the sqlite functions are broken again. Is it because Esri are now using Anaconda compilation? Sqlite3.dll has been compiled without RTrees! Great, that means we cannot use spatial data in python. The solution is to hack the installation and replace the sqlite3.dll in the DLLs folder with the latest version from sqlite.org. While we are hacking, why not replace the spatialite400x.dll with a full replacement for mod_spatialite by downloading from spatialite.org the latest extension suite. Since there is now a Libs/sqlite3 folder in the standard install, pop them in there and add it to the system path.

Looks like some issues may be fixed in Pro 2.6.


What’s New in the Geodatabase at ArcGIS Pro 2.6 


"GeoPackage editing
Last but not least, GeoPackages have emerged in the last few years as a format used in mobile applications and as a standards compliance exchange format. We’re happy to share that data stored in GeoPackages is now fully editable within ArcGIS Pro 2.6, including undo and redo capabilities."

Just ran a quick workflow - created geopackage in Pro, copied features and tables from SQL Server geodatabase into geopackage, edited/deleted/created geopackage spatial features and non-spatial tables in Pro.

Exited Pro and opened in QGIS. Deleted/edited/created/saved features in QGIS and worked with non-spatial table data. Exited QGIS and opened in Pro. Everything is there as it should be. Didn't test direct script access.