Load Esri geodatabase tables into SQLite

I have blogged earlier about loading Esri file geodatabase feature classes into SQLite database by using standard geoprocessing tools. This time, I’ve written a short sample that creates a SQLite database, reads a file geodatabase feature class rows, and then creates a new table and insert the features into it. Everything except reading file geodatabase (I use arcpy to access features with a cursor) is done using pure Python and SQL. The logic is rather simple – you read the schema of a geodatabase table and then mirror it inside a SQLite database table. Then you insert the features’ geometry and attributes into right columns.

I was pleased to observe excellent performance of data insertion and query processing. Of course, the initial data load is an overhead that will present in the script, but the efficiency of data manipulation using SQL and spatial analysis using ST_Geometry functions outweighs it, in my opinion. If you will work with rather larger datasets, you should consider tuning the performance of SQLite spatial indices because using ST_Geometry may be initially slow.

It is also worth noting that with some minor modifications, it would be possible to load a non-spatial file geodatabase table into an SQLite database table if you want to perform some SQL-based data aggregation. A very unique feature of SQLite database is that it is possible to generate such a database completely in memory without writing anything on disk. This is suitable for scripts where you want to do the processing on the fly without creating any additional scratch workspaces. Use the syntax for creating SQLite databases in memory (:memory:). I’ve done tests loading the spatial extension and some data and it worked fine.


Changing the order of fields in a geodatabase feature class

At some point, it might be necessary to change the order of fields in a feature class. Perhaps a new field has been added and you want that all your users that will add this feature class to their map document to see the field in a right place when opening the attribute table.

An easy solution that could work is to create a layer file (.lyr) that contains among other things the order of fields that was set up for the map layer at the moment of layer file export. Now your users can add this layer file into ArcMap session to see the fields in the needed order when opening the attribute table or when editing feature class features. Creating and updating layer files can be done with Python and arcpy scripting.

If your data don’t reside in an enterprise geodatabase, such as SQL Server, your only choice is to copy your feature class to a new feature class providing a field mapping object that will contain the information on the needed field order. Then you need to replace the source feature class replacing it with the newly created one. This approach would work for a standalone dataset that is not part of any complex container such as topology, network dataset, or a geometric network and doesn’t have any domains specified for its fields. Otherwise, you would need to take the feature class out of the containers (for instance, you cannot delete a feature class that is part of a network dataset), replace it with the feature class that have the right field order, and then reassign the domains, add feature class to its containers and so forth. Many of those operations cannot be automated which makes it re-ordering the fields in a feature class quite complicated.

If your data reside in a DBMS such as SQL Server, though, you can use your DBMS tools for defining the order of the fields. If you want to provide your users access to a feature class with a particular order of the fields, you could create a view using SQL or ArcGIS geoprocessing tools. However, a view cannot be edited in ArcMap (you won’t be able to update the underlying feature class through a view).

If you do need to change the original feature class field order and let users edit it, you can use the SQL Server. The detailed instructions are available on SO in this answer. This can be done using SSMS without writing any SQL queries.

I have tested this on a feature class that was part of a network dataset and a topology and also had domains associated with its fields. After changing the columns order in SQL Server, I was able to use the feature class in ArcMap and all behaviors associated with its containers seemed to be intact and working properly. The domains were left in place, too.

If you want to take a look at the SQL query that is being run behind the scene, look here at DBA site of SE.

To see the changes in the field order, restart ArcMap, as it seems to cache the order of fields in feature class within the session. I’m not sure whether changing the order of fields for a feature class is something that is officially supported by Esri, so be careful if you decide to do this – always back your data up before doing this.

When writing any code that works with feature class columns, always refer to columns by names and not by their index. If using arcpy and Python, it is super easy to use named tuples (I blogged about this earlier).

Then you are not dependent any longer on the order of fields in your feature class, so if the order will change, as long as a field has the same name, your code won’t break.