3,2,1, GO !
1. Open http://www.gaia-gis.it/spatialite/, 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 !
Tags: Spatialite, SQLite
Hi..
I want to convert from shp to db sqlite, what should i do ?? Spatialite GUI can do it ??
thank’s
That’s the step 5 with the GUI
That’s the step 5 with the GUI
it is possible with the command line
.dumpshp
.loadshp
see:
http://www.gaia-gis.it/spatialite/spatialite-2.2_tutorial.html
Does anyone know if there is another language or set of commands beside SQL for talking with databases?
I’m working on a project and am doing some research thanks
Hi,
Unable to execute script:
init_spatialite-2.3.sql
Error:
row 24
SQLite SQL error: PRIMARY KEY must be unique
hi,how can i export the spatial-data to a shapefile?
Every shapefile I try to load gives the same error:
invalid SRID value.
They are properly projected in wgs-84. Not sure what’s going on here.
cheers,
Problems running init_spatialite-2.3.sql?
It turns out that since version 2.3, this step should be skipped. Check out:
http://groups.google.com/group/spatialite-users/browse_thread/thread/a8982ffb2d616b73#
Thanx for this superb post. But I had difficulty navigating past your site because I kept getting 502 bad gateway error. Just thought to let you know.
Can’t do step 3, “the init_spatialite.sql script required in order to initialize the Spatial MetaData tables”:
The requested URL /spatialite/init_spatialite-2.2.sql.zip was not found on this server.
Anyway, if we could download it, how would we run it?
Oh, never mind, I see that “init_spatialite-2.2.sql” is not longer necessary.
What is the purpose of the SRID field? Why is it 4326 in the screen shot?
I think I know what SRID is. It refers to a row in the spatial_ref_sys table that automatically appears when you create a new database, and it specifies the spatial projection. The row with srid=4326 refers to WGS 84, the standard GPS coordinate system.