exa-Nico
Community Manager
Community Manager

Background

One way to work with geocoding is to use a GIS (geographic information system) service like ArcGIS (www.arcgis.com) from Esri.

The following website shows different use cases when such a service might be useful:
https://developers.arcgis.com

In this tutorial, we would like to show you how to use the ArcGIS REST API from Python:
http://resources.arcgis.com/en/help/arcgis-rest-api

In practice, you might face the following challenges:

  • Connect to a REST API from an Exasol UDF language (Python, Java, R, Lua).
  • If you want to benefit from Exasol's GEOMETRY data type (e.g. in order to run geospatial functions), you need to think about converting this data to Python strings and back.

This solution uses Python and the python package requests to connect to the REST API.
As an example, we only demonstrate geocoding and reverse geocoding.

Prerequisites

  • You should create a bucket and make sure the database has access to this bucket. You can find further information on doing this here
  • The database must have access to the internet and have a DNS name configured (instructions)

How to use ArcGIS Rest API in a UDF

Step 1 - Install Python Package Requests (optional)

In Version 6.2, the Python package "Requests" already comes in the standard language container shipped with the database. However, if you are using a nonstandard container or an earlier version, you may need to install the Python package beforehand. You can confirm this is the case by running the examples below. If you get an error message stating "No module named Requests", then the package needs to be installed. You can find more information on how to do this here

Step 2 - Adapt arcgis_demo.sql (optional)

If you do not have the proper packages installed (see step 1) - you may need to update each UDF with code like this:

import sys
import glob
 
sys.path.extend(glob.glob('/path/to/bucket/*'))

Step 3: Run arcgis_cities

Run the attached script "arcgis_cities.sql" to create the data that you will use in the example

Step 4: Run arcgis_demo 

Run the attached demo to get the geo information for the cities specified!

Additional Notes

For better performance when working with large datasets, we recommend batch geocoding (https://developers.arcgis.com/rest/geocode/api-reference/geocoding-geocode-addresses.htm) instead of geocoding – but in order to use this, you need to have an ArcGIS online organizational account which you might be charged for.

Additional References