Saturday, February 14, 2009

KISS: spatialite in 5 minutes

3,2,1, GO !

1. Open, download spatialite-gui GUI and unzip it.

2. Start Spatialite-gui.exe (I tested it with Windows ;-) and click on Files / Creating a new (empty) SQLite DB

3. Download "the init_spatialite.sql script required in order to initialize the Spatial MetaData tables" and start it with Spatialite GUI

4. Download from GeoFabrik the OpenStreetMap data of your country as shapefile.

5. Use the function Load Shapefile of spatialite-gui to load the roads, for example. It takes some seconds to load 125256 roads, in my case.

6. Build a spatial index on the geometry attribute

7. And start playing with the spatial functions: select * from roads where MBRContains(BuildMBR(7.5,46.5,7.6,46.6),Geometry) = 1; Pretty fast, you will see !

8. If you don't like the GUI, use the command line:

FINISHED ! It should have taken less than 5 minutes. It took more time to write this blog...

Nothing really new (Personal Geodatabase, SDF, others exist for a while) except the extrem simplicity, the file portability (cross plattforms), the possibility to access the shapefile as "virtual tables" (you can start SQL statements directly on shapefiles) and the OS license.

Of course PostGIS or Oracle Spatial will provide more functions or will extract data faster but I can imagine dozen of applications where the simplicity of spatialite is an huge advantage (SQLite is the most widely deployed SQL database engine in the world). Imagine a complete GIS system on an iPhone or Apache as cartographic server.

Thanks to Alessandro Furieri.

Keep It Simple... but not Stupid !


Anonymous said...

I want to convert from shp to db sqlite, what should i do ?? Spatialite GUI can do it ??


Cédric Moullet said...

That's the step 5 with the GUI

Anonymous said...

it is possible with the command line


Unknown said...


Unable to execute script:

row 24
SQLite SQL error: PRIMARY KEY must be unique