To spatially enable an existing SQLite database, do one of the following:
- Install SpatiaLite. See the SpatiaLite documentation for information on adding SpatiaLite to an SQLite database.
- Use SQL to load the ST_Geometry library and create the ST_Geometry type in an existing SQLite database.
- Use SQL to load the ST_Geometry library and create an OGC GeoPackage.
Add ST_Geometry tables to SQLite
To add ST_Geometry system tables to an existing SQLite database requires the following:
- The database file must have the extension .sqlite; therefore, if the database you want to use has a different extension, rename it. For example, if the database is named projdata.d1, rename it to projdata.sqlite before adding the ST_Geometry type to it.
- The database cannot already contain another spatial type.
- The Esri ST_Geometry library must be accessible from the SQL editor from which you load it. You can obtain the library from My Esri (download the Database Support zip file).
- If necessary, rename the database file to have the extension .sqlite.
- Connect to the database using an SQL editor, such as sqlite3.exe.
- Load the ST_Geometry library.
This example loads the ST_Geometry library to an SQLite database on a Microsoft Windows computer:
SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init');
This example loads the ST_Geometry library to an SQLite database on a Linux computer:
SELECT load_extension('libstgeometry_sqlite','SDE_SQL_funcs_init');
- Call the CreateOGCTables function to add ST_Geometry tables to the database.
SELECT CreateOGCTables();
Create a GeoPackage
To convert an existing SQLite database to a GeoPackage requires the following:
- The database file must have the extension .gpkg; therefore, if the database you want to use has a different extension, rename it. For example, if the database is named study.db, rename it to study.gpkg before you convert it to a GeoPackage.
- The database cannot already contain another spatial type.
- If necessary, rename the database file to have the extension .gpkg.
- Connect to the database using an SQL editor, such as sqlite3.exe.
- Load the ST_Geometry library.
This example loads the ST_Geometry library to an SQLite database on a Windows computer:
SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init');
This example loads the ST_Geometry library to an SQLite database on a Linux computer:
SELECT load_extension('libstgeometry_sqlite','SDE_SQL_funcs_init');
- Call the CreateGpkgTables function to add GeoPackage tables to the database.
SELECT CreateGpkgTables();