Posts Tagged ‘ spatialite ’

ESRI makes it (kinda) easy to move data around via Spatialite

With the release of ArcGIS 10.2 ESRI added support for Spatialite databases.  You can’t directly edit Spatialite data, but you can create a new database, import data into one, and export data from one.  You can even create new feature classes in a database along with new tables and views.  These features make Spatialite databases a great way to move data from organization to organization.

There isn’t a way though the GUI to create a spatialite database, but it only takes three lines of ArcPy code.

import arcpy

out_path = 'C:/Data/Test.sqlite'

arcpy.gp.CreateSQLiteDatabase(out_path, 'SPATIALITE')

If you use the Python Geoprocessing window you can get it down to two lines by skipping the import statement.  If you skip the optional ‘SPATIALITE’ parameter Arc will store the geometry in ESRI’s native ST_Geometry format, which shuts out people using OSGeo software, so please use the ‘SPATIALITE’.

Once your database is created you can use ArcCatalog to import data into it, export data from it, create a new table, create a new view or a new feature class.  You can view your data in ArcGIS, or view and edit your data with QGIS and other OSGeo GIS platforms, or you can use the spatialite GUI.

Advertisements

Use SQLite Virtual Tables to Connect to Shapefiles

Your data is in a shapefile and you want to run some spatial queries but don’t want to load your data into PostGIS or some other spatial database, what do you do?  You can use SQLite’s virtual tables to create a quick link to your data, and here’s how.

  • First, from the directory your shapefile is in start sqlite3.
  • Next, use SELECT load_extension(‘libspatialite.so’); to enable the spatialite extension.
  • Load your shapefile as a virtual table with:
CREATE VIRTUAL TABLE <table_name> 
USING VirtualShape(<shapefile_name>, 'UTF-8', );

Now you can run any read-only spatial query you want against your new table.  Note, the ‘UTF-8’ value will need to be changed if your shapefile isn’t using Unicode encoding.