Insights Blog

Clean your Geospatial Data with Exasol’s In-Memory Database

Add Google’s unsurpassed mapping capability to Exasol’s Loading...In-Memory Database and never be lost again.

Graham Mossman shows you how to clean and extend your geospatial data and to make distance-based recommendations, all within Exasol’s easy-to-use SQL environment.

I can’t remember the last time I used a paper map. Or a paper telephone directory for that matter. One of the big retail groups keeps sending me a catalogue every season, but it goes straight into the recycling bin.

More and more, all you need these days is a mobile phone and an internet connection. Nobody need carry a small library of reference books with them. Instead you simply do an online search and some web service (usually Google’s) will serve you all the maps, telephone numbers, catalogues etc. that you need – constantly updated, and containing only the small amount of information you actually asked for.

It’s similar with database systems – nowadays you don’t have to store all the information you need for your business in a database – often you just need to call out to a web service when you need something in particular.

For example, if I wanted to know which bakeries were closest to Exasol headquarters in Nuremberg, I could load details of all the businesses in Nuremberg into my database. I could then regularly keep this data up to date as businesses open and close and move premises. I could then run a query similar to

SELECT business
FROM businesses
WHERE business_type=’BAKERY’
ORDER BY distance_from_Exasol;

Or …
A much better way is to use the Google Maps APIs which were launched at the start of this year. These allow you to run queries on exactly the same data that is used to run the Google Maps online service.

There are quite a few benefits to using this data :

  • Free of charge
  • Google maintains the data for you
  • There is a lot of good and varied data available
  • You can calculate walking/driving distances and times
  • This opens up the possibility of giving distance-based recommendations to your customers
  • You only download the data you actually need, not the whole database

A number of programming languages are supported by these Google APIs. My choice is Loading...Python, but you may prefer Loading...Java. All I need to do is to import the Python libraries for these APIs into Exasol and write a simple Python User Defined Function, and suddenly the entire Google data lake is accessible to my Exasol database, and I can mix and match in the same query data that lives in the Exasol database and Google data.

I have created a video and a knowledge base article on the User Portal to illustrate this process with some sample code.

Now all you need is an Exasol database – so why not download a free copy and see how our database engine with Google’s data can give you an edge?