Spatially enable an SQLite database

To spatially enable an existing SQLite database, do one of the following:

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 or copy it from the DatabaseSupportdirectory in the ArcGIS Desktop or ArcGIS Server installation location.
  1. If necessary, rename the database file to have the extension .sqlite.
  2. Connect to the database using an SQL editor, such as sqlite3.exe.
  3. 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.so','SDE_SQL_funcs_init');

  4. 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.
  1. If necessary, rename the database file to have the extension .gpkg.
  2. Connect to the database using an SQL editor, such as sqlite3.exe.
  3. 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.so','SDE_SQL_funcs_init');

  4. Call the CreateGpkgTables function to add GeoPackage tables to the database.

    SELECT CreateGpkgTables();