Geospatial – Actualités

KISS: spatialite in 5 minutes

14 février 2009

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 !

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

15 commentaires

  1. Clemens Gröpl dit :

    The download link is broken and should point to the recent version:

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

    http://www.gaia-gis.it/spatialite/init_spatialite-2.2.sql.zip

    =>

    http://www.gaia-gis.it/spatialite/init_spatialite-2.3.sql.gz

  2. Cédric@camptocamp dit :

    That’s the step 5 with the GUI

  3. David dit :

    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.

  4. David dit :

    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?

  5. David dit :

    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?

  6. Steve Call dit :

    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.

  7. Frans dit :

    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#

  8. Fredd dit :

    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,

  9. lhy dit :

    hi,how can i export the spatial-data to a shapefile?

  10. Joe dit :

    Hi,

    Unable to execute script:
    init_spatialite-2.3.sql

    Error:
    row 24
    SQLite SQL error: PRIMARY KEY must be unique

  11. SQL Tutorials dit :

    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

  12. Anonymous dit :

    it is possible with the command line
    .dumpshp
    .loadshp

    see:
    http://www.gaia-gis.it/spatialite/spatialite-2.2_tutorial.html

  13. Cédric@camptocamp dit :

    That’s the step 5 with the GUI

  14. Anonymous dit :

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

    thank’s