Geocoding with UDFs

This tutorial shows how you can add geocoding to your data using Exasol UDFs.

The tutorials in this section are designed to work with the public demo system hosted by Exasol. If you have not already done so, sign up here to get access to the public demo system.

The following example shows you how to determine the latitude and longitude for a given place:

OPEN SCHEMA <YOUR SCHEMA>;
--/
CREATE or replace PYTHON3 SET SCRIPT geopy_geocode_long_lat(placename varchar(200)) EMITS (place varchar(20000), latitude double, longitude double) AS
import glob
sys.path.extend(glob.glob('/buckets/bucketfs1/showcase/*'))
import geopy.geocoders
from geopy.geocoders import Nominatim
 
geolocator = Nominatim()
   
def run(ctx):
 while True:
  try:
   geo = geolocator.geocode(ctx.placename, timeout=None) 
   place=ctx.placename
   lat=geo.latitude
   lo=geo.longitude
   ctx.emit(place, lat, lo)
  except:
   ctx.emit(place + " (Unavailable)",None,None)
  if not ctx.next(): break
/

Test the UDF against a specific address using Exasol's dual table:

select geopy_geocode_long_lat('10 Downing Street, London, UK') from dual;

To run this UDF against a table with location data, you can create a new table in your schema, for example, based off of NYC Taxi data.

CREATE TABLE
    "TEST_BOROUGH" AS
SELECT
    *
FROM
    NYC_TAXI.TAXI_ZONES lIMIT 15;

Execute the UDF against the data set.

SELECT DISTINCT
    geopy_geocode_long_lat(tz.BOROUGH)
FROM
    "TEST_BOROUGH" tz
ORDER BY
    1 ASC;

The result is as shown below: 

Reverse Geocoding

The following examples show reverse geocoding using latitude and longitude:

 --/
CREATE OR REPLACE PYTHON3 SET SCRIPT geopy_reverse_geocode(lat DOUBLE, lo DOUBLE) EMITS (latitude double, longitude double, address VARCHAR(20000)) AS
import glob
sys.path.extend(glob.glob('/buckets/bucketfs1/showcase/*'))
from geopy.geocoders import Nominatim
 
geolocator = Nominatim()
 
def run(ctx):
 while True:
  geo = geolocator.reverse([ctx.lat, ctx.lo])
  lat=ctx.lat
  lo=ctx.lo
  address=geo.address
  ctx.emit(lat, lo, address)
  if not ctx.next(): break
/

Test the UDF against specific coordinates using Exasol's dual table:

select geopy_reverse_geocode(51.5034066, -0.1275923) from dual;

To run this UDF against a table with location data, you can create a new table in your schema, for example, based off of NYC Taxi Trips data.

CREATE TABLE
   taxi_coordinates AS
SELECT
    t.id,
    t.pickup_longitude,
    t.pickup_latitude,
    t.dropoff_longitude,
    t.dropoff_latitude
FROM
    NYC_TAXI.TRIPS t limit 10;

Execute the UDF against the data set.

SELECT
    geopy_reverse_geocode(pickup_latitude, pickup_longitude)
FROM
    taxi_coordinates;

The result is as shown below: