Spatially enable an SQLite database

You can either install SpatiaLite, add the ST_Geometry type, or create a GeoPackage to store spatial data in an existing SQLite database.

See SpatiaLite documentation for information on adding SpatiaLite to an SQLite database.

Use SQL to load the ST_Geometry library and either create the ST_Geometry type or a GeoPackage in an existing SQLite database, as described in the following sections.

Add ST_Geometry tables

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.

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 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();
   

Add GeoPackage tables

To add GeoPackage system tables to an existing SQLite database 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 adding GeoPackage tables to it.
• 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();