You can connect from ArcGIS for Desktop to an SQLite database to create maps and perform spatial analysis.
ArcGIS supports spatial data in SQLite databases that contain one of the following storage types:
- Esri ST_Geometry—The database file must have the extension .sqlite to be used with ArcGIS.
- SpatiaLite—The database file must have the extension .sqlite to be used with ArcGIS.
- GeoPackages—The GeoPackage file must have the extension .gpkg to be used with ArcGIS.
You can use only one spatial storage type per SQLite database. See Spatially enable an SQLite database for information on adding a spatial storage type to an existing database.
To create an SQLite database that uses either ST_Geometry or SpatiaLite storage, or to create a GeoPackage, use the CreateSQLiteDatabase ArcPy function.
SQLite data in ArcGIS
You control access to an SQLite database or GeoPackage by controlling permissions on the folder where it is stored. Unlike other databases, you do not create users who are authenticated by the database, and you do not grant privileges on specific datasets to other users.
SQLite and GeoPackages can be read by multiple users, but you should not make any updates to the database while someone else is editing. For example, do not append data into an existing table or create a table in the database while someone else is appending data into an existing table or creating a table in the database.
SQLite is also different from other databases in that fields are not assigned specific data types and data type definitions are not strictly enforced. Instead, SQLite uses storage classes in which values of different data types can be stored.
However, ArcGIS can only work with one data type per field and does strictly enforce data types. You should be aware of this difference in data type enforcement when viewing SQLite data in ArcGIS.
The following example creates a table with integer and text fields:
CREATE TABLE mytable ( id INTEGER PRIMARY KEY NOT NULL, item TEXT, weight INTEGER, store TEXT;
Even though the weight field is defined as an integer, SQLite will allow you to store numbers with decimals in it. It will even allow you to store text in it. For example, you can insert the following records:
INSERT INTO mytable (id, item, weight, store) VALUES( 1, “magnetic dual elliptical trainer with seat”, 75, “CardioPlus Equipment” ); INSERT INTO mytable (id, item, weight, store) VALUES( 2, “superfit treadmill4000”, 81.2, “Sports Pit” ); INSERT INTO mytable (id, item, weight, store) VALUES( 3, “serenity yoga mat”, .4588, “Aerobic Angels Sporting Goods” ); INSERT INTO mytable (id, item, weight, store) VALUES( 4, “swim fins”, "two", “The Plunge” );
However, the values appear as follows in ArcGIS because the weight field is defined as integer:
magnetic dual elliptical trainer with seat
serenity yoga mat
Aerobic Angels Sporting Goods
See DBMS data types supported in ArcGIS for a list of which SQLite data types map to which ArcGIS data types.