Is DuckDB the Secret to Unlocking Your GIS Potential?

0:00geospatial analysis has always been an important topic in data but hard to dive into one big reason for this is that it's just hard to get you set up there are so many standards tools and dependency in geospatial it can be challenging to just iterate to walk around data transform it and plot something that being said we have a

0:21couple of new tools including Doug DB which helps you move much faster or should I say quack louder in this video we'll recap the really basic around geospatial data just enough to start building and create this fantastic heat map about electric vehicles charging spots using dougb and some Pyon library for the visualization so if you're new to geospatial analysis this video is for

0:45you and if you're already experienced with GEOS spal but new to ddb and Moder duck you may still get a wow at the end of this video so let's dive in to start your journey around GEOS special you need a essentially three thing first thing is knowledge around geospatial analysis that would include understanding geometries spatial relationship with spell shell joints

1:09understanding standard file format for geospatial the second thing is a data tool to read process and export geospatial data and the third thing is a tool to visualize and plot what you are doing in iterate this video only aims to cover some of the geospatial concept that we will need to build this map but I will leave more resources for you if

1:29you want to dig further when working with geospatial function you will learn how to work with geometry in short this can be a point line polygon or a collection of them a lot of databases support special function and special type to store these geometries POS is the gold standard here it has a lot of special function prefixes with store

1:51which stands for spal in temporal so if you have a coordinate a point in special type the way you would convert it in postes and dgdb also is by using using this function H point and in Doug DB the only requirement to install and load the special extension so aside from geometries in spal function the other important thing to understand is the

2:11file format in geospatial to share geospatial data there are multiple formats you can work with Vector data which represent the discrete features we just discussed such as points line and polygons example City location a road and raster data is more like a photo and is used to represent and continuous information consists of a grid of a cell

2:33or pix Cel and each cell has value representing something like temperature elevation or colors in a satellite image on the web you would find both but usually Vector data are easy to share because of their smaller size Gieson is the easiest one to work with as you can directly edit but it's pretty inefficient in term of size and

2:54performance ddb has a lot of possibility to read and write from many of these and you can access actually all the driver possibility by using from St driver this make it super useful again to convert and join data in a common format which is usually a big step for a geospatial project and again there are a lot of

3:14Standards so it's nice to be able to prepare all your Geo data with a single tool all right now that we have the crash course around the special type the special function and the file format let's start to get you set up with the actual tools so you can use SQL in ddb but it was still need something to

3:31display the data two things I would recommend here you can either work with the CLI or use some third party tool to explore your geojson so you would export the final data set in wdb to a geojson file and read that one for example in vs code you can actually visualize any gzone directly with an extension the

3:51other common way and the one we'll use is to use python in a notebook environment to directly render the map we'll use Google collab but any notebook environment is good for you it's just for the Simplicity of sharing and get you started and regarding the databas library there are many ones available Lea map is definitely interesting to use

4:10and pretty mat for this video however I will show you a new kit on the Block which is really poor ferment and doesn't use Geon as an intermediate step to transfer the data to the front end like a lot of these tools do the library is called lawn board and it's just a python Library as it is with dub now let's zoom

4:29in on the code and the data for this tutorial we use data from open charge map ocm the goal of this website is to document the word's electric vehicle charging points and they have produced uh data set over 200,000 charging point locations around the world and the data is sourced both from volunteers as well as official sources but what is great is

4:52that they have a public API easy to use and well documented yeah this is TE it's almost to bird to get the charging Point data we can do this with a single request and filter by a bonding box uh and I will filter here around France and I want to understand what are the de Zone if I'm traveling with an electric

5:12vehicle in Frances to get the bonding box coordinate around friends I asked my best friend I'm just kidding I asked Jack GPD to generate this for me it doesn't have to be super accurate so that's good for me but now that we cover the data source let's dive into the actual code all right we all dive into

5:30the notebook you can find it into the description it's a Google collab with just one link and a Google account you have access on a python run time and first thing we're going to do is install the dependencies we're going to install the latest version of WB 1.0 at this point at this video and we're going to

5:47also install laboard which is the library that we spoke about for visualization and plotting Maps I'm doing a weird work around here where I'm uninstalling Maloy because I don't need it and it's incop add to it has a hard constraint against WB 1.0 once it's done uh the next thing I'm want to do is create a ddb connection and here is

6:09creating a ddb connection in memory as a reminder D DB is an all laap in process database so it's going to be running into the same process into the same python process I mean and if you don't puras the data to a file or your Doug DB file format the data is going to be lost but we're going to see right after how

6:28to B this data while working with Mother dock next thing you're going to do is install and load the extension so if you're not familiar with ddb extension you might not because a lot of extension no ideas get Auto loaded for you in the background you don't have to do it by yourself but some extension like the

6:46spatial extension and Community extension require you explicitly to install un load those extension the next thing is that I built uh the URL to get the data from the API for uh the Eric elect vehicle charging point and as you can see here I'm passing a filter as we discussed the bonding box with basically the coordinate of the France country

7:09finally I'm creating a table to ingest this data and that's pretty much it so I'm going to run it and explain a bit that there is a lot of magic Happening Here the first one is that we are actually cing an API which is on an https endpoint uh directly using SQL and

7:27there is a ddb extension that's being loaded behind the scene which is httpfs and the second Point magic is that we use the function read Json Auto to directly parse uh the gson that is being returned and I we have directly a nice table to be querry and so this is done in really 12 seconds that was

7:47pretty fast and so we can uh describe uh this table and you can see that I have all the type I want and I have also a complex struct and our latitude and longitude will be in interest inesis where we going to uh constrict our geometry and if you want to look what the data looks like you can so just uh

8:08query disable ddb has a nice uh from first statement which avoid you to basically do just the select star if I do that you see I have a sample of the data all right the next thing we're going to do is basically create our geometries and as we introduced in the beginning of the video you can create

8:27points line and polygon for the it map we're going to create multiple points and here you see I'm using um the special uh function which is EST point if you're fam with poses is pretty similar and here I'm passing the longitude and latitude another thing magic is that if you remember this was a complex truck address info and how to

8:49access it is just with a DOT limitation and so this is the longitude uh field which is part of the struct adders info and the latitude um that's my geometry I keep the title and I keep the ID which is indentifying um the unique ID for the electric vehicle charging spots all right I'm going to run that query and uh that's

9:12pretty much it you see that's one really fast and now we have if we inspect that that data uh point which is spatial type

9:22uh with uh a latitude and a longitudes and then we still have the ID and the title so that's pretty much it now we only need to display this and with long board again you can grab the it map uh layer object uh use the from du DB meod and pass the query which contain basically here the the data so this is

9:44was my my query right and uh the ddb

9:49connection and this is how I insatiate the map and we'll display it so here in 5 seconds uh you see I have my map from a friends that is being displayed and you see I can uh I can zoom in to the different things I have actually a bit more uh than France probably because the bounding box was taking you see the

10:11limit here of France and we can see as it's a you know a box that uh it cover a bit a bit more to be sure to cover entirely uh friends but that doesn't matter so what is fun is that you can see that in the middle of France like right in the middle here it's kind of a

10:27dead spot there is not that much uh charging electric starting spot the second thing I want to show you is how to uh use this with Mod deck so you can create a an account and use the Fe here and also retrieve your token to the UI I put the links over there and once it's done you can put it basically uh here as

10:47uh you see I have the name mod du token you put your value of your own secret there and what we do here is just uh probating the mod duck on uh token environment which is store in the notebook as an unv viable now that is ready um we see basically it's kind of the same step right here instead of uh

11:08not passing anything so we had uh just basically uh this the only difference to connect to mother duck is this and I'm going to be able to basically uh use the cloud compute and put my table over there uh as a Doug DB table so the rest is pretty much the same the only thing that change here is basically I'm create

11:28a database if not exist uh ge playground and I'm create uh the table uh to persist the data so the nice thing here is that because mod deck is on the cloud especially when you're pulling data from uh a ss3 for example that can be much faster because this is happening uh on the cloud and now you can see that I can

11:51show my database and I have a plenty of database but this is the the the database we we just created and that's the one you should uh see if you're going through the tutorial and next basically is just doing the same query but instead I'm going to create a table and persist that data and so that is

12:12done and so now uh this query basically that I'm run here what which was similar to the one I did before is running on the cloud and those also on above it and what is nice here is that now you see instead of passing the qu I can directly um the table that contains uh the geometry for uh for friends and display

12:34the data we get basically the the same uh visualization and it's uh of course a bit much faster than what we use with a local uh ddb the last thing I want to uh show you is that how you can uh share your uh geometry data set um basically the one thing you can do is use modu

12:54share it's a feature for modu to share a database so the only thing you need to do is create a share so this is the uh the name of the share I'm giving and this is the from the from the database um anyone here with the share uh can access this so it's unrestricted and basically anyone with this uh share Ral

13:14can attach and access this data another way that you can share this kind of data uh as we talked initially in the video is to use geojson again I wouldn't recommend for large data set because GE Json is not really performant there is also GE parket that's doing pretty well for that but it's really handy because you can inspect the GSM directly and so

13:36again Doug DB can support that uh we saw that earlier with the different drivers and here uh basically this is how you export to gejon and so if I'm running this I'm going to export um my uh current uh modu table to uh basically a local file and if I go here you see I have my G Json uh ready that I can

13:59download and share all right time to wrap up what we cover we introduce different concepts around geospatial and tooling the hardc DB is a nice s army knife for geospatial data analysis as it enable us to quickly pull and transform from very special formats and directly AC from an API we also how easy it is to use with other python Library like lawn

14:21board for plotting and we leverage M duck also for cloud computing and storage for geot table asset finally we saw how easy to create a mod share or export your data to a local file like Gieson so let me know in the comments what would you like to see next around Space Shell and until then keep quacking

14:40and keep coding

14:47[Music]