SQLite as future-proof backend for Personal Geodabases

Idea created by Matt.Wilkie.Yukon on May 8, 2019

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