This is the third post in my series of DuckDB and Enso Analytics. In the last post we talked about how bringing spatial capabilities to Enso was a big motivation for adding DuckDB. In this post we will take a look at what spatial capabilities we now have and how they work.

Spatial Visualisation

This has actually existed in Enso for a long time now and the capabilities here haven't changed in this release. Enso has the ability to visualise points on a map when a data set has latitude and longitude points.

For example if I want to use the overpass api to visualise restaurants in Cambridge (or any other location) I can pull the data and quickly visualise it on a map:

Future releases will expand on this capability to allow more spatial types to be visualised and extra options around the visualisation itself.

Spatial Data formats

This is some new functionality brought to us via DuckDB, so we can start by looking at their help page.

https://duckdb.org/docs/stable/core_extensions/spatial/gdal

And of interest here, following on from my last post about us using DuckDB's spatial capabilities, we can see that DuckDB uses the GDAL library to implement its spatial file reading.

And following through we can see that running this query

SELECT * FROM ST_Drivers();

will give us all of the supported file formats. So let's do that in Enso and see what spatial file support we gained from DuckDB in Enso:

That is a huge 54 supported spatial file formats. Complete with clickable links to help pages describing each format.

To actually load a spatial file you again need to connect to DuckDB using Database.connect, but don't worry you don't need a DuckDB instance, Enso will just create one in memory for you.

And then you can use read_file to read in your spatial file

The geom column is the spatial object.

It is worth noting that today the spatial visualisation doesn't know how to render these spatial objects (that will be coming in a future release) so to visualise these points on a map we will have to make use of some of DuckDBs spatial capabilities!

Spatial Expressions

Now we have some spatial data, what can we do with it? Well we now have spatial functions in the expression language and this is a great opportunity to show another new feature of the latest Enso release: Expression Autocomplete!

If we add a set component and select expression and start typing st_ we can get a list of the spatial functions complete with a description of what they do:

To be able to visualise the points on the map we need two columns with the latitude and longitude values. We can do that using the st_latitude and st_longitude functions in two set components, and then we are able to see the points from our shape file that we loaded in previously: