As far as I can see there is no direct support for SQLLite in Pro. Is that a planned feature ? Can I access SQLite through phyton and import data as layers that way?
Just tested the Make Feature Layer Tool on version 1.0 and it does not see sqlite as a valid data source in the GUI. I could also not make it work using scripting directly, but that could be for other reasons as the error message is quite generic.
Strange though, because looking in the Pro folder structure both sqlite3 and ST_Geometry dlls are distributed with Pro.
Are you trying to load spatial data or just a table? Was the SQLite database created by ArcGIS Desktop or some other application? If the latter, try making a SQLite database from ArcGIS Desktop and putting some data in it, and then see if ArcGIS Pro sees that database, especially if working with spatial data.
Thanks Josh, I am trying both simple table and spatial data. Also tried to start of with a SQLite created by ArcGIS. It doesn't make any difference. In the GUI neither the extension .sqlite or .gpkg is recognized in pro, but works fine in ArcMAp 10.3 with the same tools.
- 1 person found this helpful
The tool won't "see" the SQLite database for the same reason the Project pane doesn't; however, the tool works with them. Don't rely on the tools Browse button, fully specify the path to the table in the SQLite database in the Input Features text box.
I have tried almost every combination for entering the SQLite path and feature class as input argument to the tool. Both in GUI and from Py scripting. The py script tests OK in ArcMap but not in PRO. PRO will give me and 'unsupported or does not exist' error message.
Josh, did you succeed with this method in the release version of PRO?
- 1 person found this helpful
I am using ArcGIS Pro 1.0.0. Yes, it works, I tested it before posting the other day. I just tested it again, and it worked without any issues:
arcpy.MakeFeatureLayer_management(r"D:\tmp\test.sqlite\webMerc1","webMerc") <Result 'webMerc'>
It is helpful if you can post specific code and error messages, especially since it keeps not working for you.
I am not sure what is different between our machines. I recently re-imaged my machine using Windows 7 64-bit Enterprise Edition. I installed ArcGIS 10.3 before I got around to putting ArcGIS Pro on the machine, but the installations should be compartmentalized from each other. Anyhow, I was loading SQLite databases into Pro with the beta versions and pre-releases before I re-imaged my machine.
Have you tried creating a layer file in ArcGIS Desktop that points to a SQLite data source and then load that layer file into ArcGIS Pro?
As has been pointed out, SQLite isn't officially supported yet, so the best approach in the interim might be to find an alternate data store.
- 3 people found this helpful
Sqlite, SpatiaLite and GeoPackages are not supported in Pro 1.0. They didn't make the cut. We are trying to get them into Pro 1.1.
Regards,
Lance Shipman
Geodata Product Engineer, File Geodatabase, Sqlite, Shapefile
Esri, Redlands, California
As far as I know, the below workspaces are not yet supported with ArcGIS Pro:
- Enterprise Databases (Netezza, Teradata, SQLite)
- SQLite GDB (only supported as part of map packages and offline editing)
Presently supported Workspaces should be --
- File Geodatabase
- Compressed File Geodatabases
- Feature Service Layers
- Enterprise Geodatabases (Oracle, SQL Server, PostgreSQL, DB2)
Is there any update on this? I would like to open a sqlite geodatabase (a ".geodatabase" file) in ArcMap or ArcGIS Pro. Is this possible?
Thanks,
Brad
- 1 person found this helpful
Sqlite, SpatiaLite and GeoPackages are supported in Pro. Mobile Geodatabases (.geodatabase) support is being considered for a later release, but not in the Pro 2.1 currently under development.
Lance
I think mobile geodatabases (.geodatabase) are great, SQLite meets geodatabase. Does Esri have plans to support native access within the desktop clients instead of having to convert back and forth between file geodatabases?
Confirmed. I have successfully created both a Spatialite and GeoPackage database. However, I cannot edit the data...getting a 'not registered in the Database' error. I try to register the data and that also errors out. Is it possible to edit SQLite databases in Pro, and if so how does one do this?
Editing SQLite data is not currently supported in ArcMap or Pro. We are looking at it for a later (post 2.1) release.
Lance
Great. It would be helpful to have that clarified either here - Supported data types and items—ArcGIS Pro | ArcGIS Desktop or here - Work with SQLite databases in ArcGIS Pro—Help | ArcGIS Desktop, would have saved me asking in this forum.
Still doesn't look like SQLite is editable in Pro 2.3. Any news/speculation about future releases?
I don't think there's going to be any rush for sqlite support anytime soon. Many organizations are blocking it from the network, too many security holes.
Let's not advertise then that GeoPackage is built on top of SQLite.
In defense of SQLite, it isn't any more prone to issues than most software, and the group behind SQLite does address vulnerabilities fairly quickly. If having CVE entries was sufficient to block software from the network, I am not sure how many applications would remain on the network.
There was something specific about one of the entries, and how it was fixed (or not), that put it in "jail".
- 1 person found this helpful
It is true that sqlite is still not supported for interactive editing as at Pro 2.3 and it is also not supported for editing in ArcMap.
But don't let that put you off using sqlite and the spatial extensions combined with Esri tools. It is still very useful for lots of purposes. It can fill in holes in ArcTools and for some operations it is 100 times faster.
The way I use it is to keep the spatial tables (ie featureclasses) and aspatial tables (ie tables) in sqlite (with the spatial extension DLL built into Arc*) and run python scripts with SQL commands. You can do any (non interactive) editing with that method!
After a few seconds of processing I then copy the spatial tables back into a filegeodatabase if I need to use other tools that do not support sqlite. I can see the featureclasses and tables in ArcCatalog and they can usually be a read-only source of data.
The great benefit to exporting data as a sqlite database is that I can send it to a user without a GIS installation and they can easily read it with an ODBC driver.It is a single file database excellent for exchange. There are no size limits. Some of my databases are 6GB. Because they have good indexing they are way faster than Access ever was. Instead of an awful CSV file you get a proper database table with a full schema, metadata, and the flexibility of a relational database.
Need the equivalent of Access?There are several open source equivalents available
I purchased sqlitepro http://www.sqliteexpert.com
to test out my sql expressions.
What sort of tools? Consider if you have a many to one relate between owners and parcels. You would like a point layer of owners 'geocoded' by using the parcel centroid. The obvious solution is to add a spatial column to Owners and populate it with parcel centroids from a relate to the parcels. This typically takes 2 minutes for 5 million records. This is not easy with ArcTools. MakeQueryLayer is not available for filegeodatabases, MakeQueryTable is slow, does not scale well enough to complete. The only workaround I have found is to create a new empty Owner featureclass, copy all the data across with a cursor, then create a dictionary of geometry objects from the parcels and update the Owner featureclass. This takes some advanced python skills so I may as well write a SQL expression in spatialite (sqlite + mod spatialite)
sql = """UPDATE {0}
SET shape = (SELECT shape
FROM parcel.Parcel_Label B
WHERE B.par_id = {0}.first_par_id)
""".format(new_title)time 0:00:17.682000
count 2149376This was as expected because it was a port from ARC/INFO and AML which took a similar time.
I think Esri would agree, i.e., SQLite isn't currently fully supported in ArcGIS Pro.
Although "seeing" SQLite databases isn't possible through the Project pane/window, you can load SQLite databases using the Make Feature Layer tool either through the GUI or ArcPy. At least during beta testing, Spatialite-based spatial data wouldn't load because the correct DLLs weren't packaged with the installer, not sure if that changed. I have successfully loading ST_Geometry and GeoPackage spatial data into ArcGIS Pro.