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. 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