SpatiaLite is an SQLite database engine with spatial functions added.
This Quick Start describes how to open a database with the command line and the GUI application.
Open a console and open a sample database with spatialite:
spatialite /home/user/data/spatialite/trento.sqlite
Helpful commands in the CLI interface:
.help
.tables
.quit
Some sample spatial queries:
SELECT lc.NOME, lc.NOME_PROV, lc.COM, mh.Perimeter
FROM LocalCouncils AS lc, MunicipalHalls AS mh
WHERE CONTAINS(lc.Geometry,mh.Geometry) AND lc.NOME LIKE 'VILLA%'
ORDER BY lc.NOME;
.headers ON
SELECT COMUNE, LOCALITA, Area(Geometry)/1000000 AS "Area Sq.km."
FROM PopulatedPlaces ORDER BY "Area Sq.km." DESC LIMIT 10 ;
SELECT lc.NOME AS "Provence Name", X(mh.Geometry) AS X_COORD, Y(mh.Geometry) AS Y_COORD
FROM LocalCouncils AS lc, MunicipalHalls AS mh
WHERE mh.COMU=lc.COM ORDER BY "Provence Name" LIMIT 10;
Click the “Disconnecting current SQLite DB” button
Click the “Connect existing SQLite DB” button
Browse to the /home/user/data/spatialite directory and choose trento.sqlite
Right click on the MunicpalHallsView table and select “Show Columns”
Right Click on the PopulatedPlaces table and select “Edit table rows”
In the upper SQL pane type:
SELECT NOME, X(Geometry) AS Longitude, Y(Geometry) AS Latitude
FROM "MunicipalHallsView"
WHERE NOME_PROV LIKE "BRESCIA";
and click the “Execute SQL” button at the right
You should see a map of Trento Provence in Italy
- Right click on the Highways layer and select Hide
- Right Click on the LocalCouncilsTrento layer and select Layer Configuration->Classify and choose “Shape Area” for the column. Select 4 Classes and click on the Min and Max color patches to choose a dark and light color. Now click to see a Choropleth display of the provence areas.
- Change border color by right click on LocalCouncils and select Layer configuration->Graphics and select a different color under Border Graphics.
- Zoom in slightly. Right click on the PopulatedPlaces layer and select Indentify on. Now click on one of the Populated Places to see the attributes for that feature.
Here are some additional challenges for you to try:
To learn more about SpatiaLite, a starting point is the SpatiaLite project page.
and be sure to visit the tutorial Spatialite cookbook