SQLite and ArcGIS

You can connect from ArcGIS to an SQLite database to create maps and perform spatial analysis on your data.

You connect directly to the SQLite database file from your ArcGIS client. See Connecting to SQLite for instructions.

You must install either the Esri ST_Geometry type or SpatiaLite in your SQLite database to store spatial data in it. You must use one or the other; you can't use both in the same SQLite database. You can use the CreateSQLiteDatabase ArcPy function to create an SQLite database with either of these spatial data types. See Add the ST_Geometry type to an SQLite database or the SpatiaLite site for instructions on adding a spatial type to an existing database.

SQLite data in ArcGIS

Access to the data

You control access to an SQLite database by controlling permissions on the folder where the SQLite database 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 can be read by multiple users, but you should not make any updates to the database while another user is making updates. For example, do not append data into an existing table or create a table in the database while another user is appending data into an existing table or creating a table in the database.

Data types

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 let you store numbers with decimals in it. It will even let you store text in it. For example, it will let you 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 will appear as follows in ArcGIS because the weight field is defined as integer:

id

item

weight

store

1

magnetic dual elliptical trainer with seat

75

CardioPlus Equipment

2

superfit treadmill4000

81

Sports Pit

3

serenity yoga mat

0

Aerobic Angels Sporting Goods

4

swim fins

0

The Plunge

See DBMS data types supported in ArcGIS for a list of which SQLite data types map to which ArcGIS data types.

Related Topics

2/5/2015