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.
 Export your data from observation.org as SQLITE-dump:
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.
 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…
Let’s get GISy: Add a geometry column (data-Type: geometry) for storing the location:
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)
 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.