GIS, Home, Sticked!

From observation.org SQLITE dump to QGIS with Spatialite

The nature observation platform observation.org provides a SQLite-dump of your observations. As a geospatial nerd it is obvious to have a deeper look on the database and how the location of the observations is stored… and to think one step further: Make a Spatialite database of it and use it directly in QGIS or ArcGIS.

[1] Export your data from observation.org as SQLITE-dump:

Observation.org SQLite Download

To get more details on the database I opened it in Spatialite-GUI (just use DuckDuckGo or Google how to get it for your OS). The location of observations is stored in the table “observation” in the field “point” in JSON (GeoJSON)-syntax.

[2] Let’s make a Spatialite database out of it 🙂

What do we need?

  • Make the database a spatial database – enable Spatialite on the database
  • Add a geometry field
  • Fill the geometry with the coordinates from the point-field

Make the database a spatial one – enable Spatialite: To my surprise, this action takes quite some seconds…

SELECT InitSpatialMetaData();

Let’s get GISy: Add a geometry column (data-Type: geometry) for storing the location:

SELECT AddGeometryColumn('observation','Geometry',4326,'POINT','XY')

After doing this the geometry is NULL – now we have to fill the geometry column with the geometry from the point-field. I had some problems “converting” the JSON-coordinates from the point-field to a native  spatialite geometry. This SQL-statement worked for me (but looks strange :-/ – the reason is that only AsText allows to pass the necessary SRID parameter (???) ):

UPDATE observation SET Geometry = GeomFromText(AsText(GeomFromGeoJSON(point)),4326)

After updating the geometry it should not be NULL anymore and it is possible to check the geometry within Spatialite-GUI (right-click on geometry – Mapview)

[3] Use it with QGIS

Now it’s time to open the database with QGIS and use our “GIS-database” 🙂

  • Connect to the Spatialite-database and load the observation-table/layer

  • If you want to use the other tables within the SQLite-database from observation.org (e.g. to join some data), use the QGIS database manager to explore the database and add some of the tables to QGIS

  • A simple example: Maybe you want to label the observation-points with the name of the species – the observation table only provides the species-ID. QGIS makes it easy to join tables (layer properties – screenshot).

After joining the table “species to the “GIS-dataset/table” observations, labeling the observation-points with the name of the taxa is possible.