GoogleGeoCode UDF

The GoogleGeoCode UDF uses the Google Map webservice (either the Google GeoCode API or  Google Maps for Business Users) to determine the longitude and latitude of an address, or look up the nearest address using a longitude and latitude. This process is known as “geocoding”.

To use the GoogleGeoCode UDF, you first need to define it as a function. See the topics Writing a Java UDF in the Integration Guide and CREATE FUNCTION in the SQLstream Streaming SQL Reference Guide for more details on defining functions.

A simple declaration for determining  the longitude and latitude of an address is as follows:

CREATE OR REPLACE FUNCTION "getLatLonUDF"(address VARCHAR(128)
   RETURNS VARCHAR(40)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getLatitudeLongitude';

See https://developers.google.com/maps/documentation/geocoding/ for more details on using geocoding with the Google Maps webservice.

Options for Use with GoogleGeoCode UDF

The following are options that you can declare when creating a GoogleGeoCode function.

Option Explanation Default Required
clientId Either the Google GeoCode API key to be used or the client parameter for Maps for Business Users NULL No
signature If clientId used for Google GeoCode API, must be specified as CAST(NULL AS VARCHAR(128).If cliendId used for for Maps for Business Users, signature is the signature parameter for Maps for Business Users NULL No
maxrate Maximum number of requests per second allowed for your Google Account. If the rate is exceeded, the UDF slows down the pipeline to avoid overuns of the allowed rate. 0 No
https Boolean value that specifies whether the request to Google GeoCode API is http: or https: FALSE No
address The address to be geocoded. Examples include “2450 Market Street, San Francisco”; “New York City”; “Golden Gate Bridge” Yes for latlong lookup
lat Latitude to be reverse geocoded specified as character string or double. Either lat and lon or latlon need to be specified for reverse lookup.
lon Longitude to be reverse geocoded specified as character string or double Either lat and lon or latlon need to be specified for reverse lookup.
latlon The latitude/longitude to be reverse geocoded specified as a single comma separated character string. No embedded blanks are allowed in a single-string lat/lon pair. Either lat and lon or latlon need to be specified for reverse lookup.

Creating Functions to call GoogleGeoCode UDF

You need to create separate functions for geocoding a latitude/longitude from an address and reverse geocoding an address from latitude/longitude.

Defining a Function to Geocode a Latitude/Longitude from an Address

To geocode a latitude/longitude from an address use one of the following declarations. Simple version:

CREATE OR REPLACE FUNCTION "getLatLonUDF"(address VARCHAR(128)
   RETURNS VARCHAR(40)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getLatitudeLongitude';

Version with more options:

CREATE OR REPLACE FUNCTION "getLatLonUDF"(address VARCHAR(128), clientId
VARCHAR(128), signature VARCHAR(128), maxrate INT, https BOOLEAN)
   RETURNS VARCHAR(40)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getLatitudeLongitude';

#### Example use

​```
--create a test schema
CREATE OR REPLACE SCHEMA "test";
SET SCHEMA '"test"';
--create stream with list of addresses (these would need to be pumped into the stream from another code block)
CREATE STREAM "S" ("ADDRESS" VARCHAR(132));
SELECT "GETLATLONUDF"("ADDRESS") AS "LATLON" FROM "S";

#### Sample values for addresses

​```
values("getLatLonUDF"('Hollywood and Vine, Los Angeles'));
values("getLatLonUDF"('Coit Tower, San Francisco'));
values("getLatLonUDF"('City Hall, SF'));

Reverse Geocoding an Address from Latitude/Longitude

To reverse geocode an address from a latitude/longitude use one of the following declarations. Simple version:

CREATE OR REPLACE FUNCTION "getLatLonUDF"(lat DOUBLE, lon DOUBLE)
   RETURNS VARCHAR(4096)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getAddress';

Versions with more options:

CREATE OR REPLACE FUNCTION "getAddressUDF"(lat VARCHAR(32), lon VARCHAR(32))
   RETURNS VARCHAR(4096)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getAddress';

CREATE OR REPLACE FUNCTION "getAddressUDF"(lat DOUBLE, lon DOUBLE, clientId
VARCHAR(128), signature VARCHAR(128), maxrate INT, https BOOLEAN)
   RETURNS VARCHAR(4096)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getAddress';

CREATE OR REPLACE FUNCTION "getAddressUDF"(lat VARCHAR(32), lon VARCHAR(32),
clientId VARCHAR(128), signature VARCHAR(128), maxrate INT, https BOOLEAN)
   RETURNS VARCHAR(4096)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getAddress';

CREATE OR REPLACE FUNCTION "getAddressUDF"(latlon, clientId VARCHAR(128),
signature VARCHAR(128), maxrate INT, https BOOLEAN)
   RETURNS VARCHAR(4096)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getAddress';

CREATE OR REPLACE FUNCTION "getAddressUDF"(latlon VARCHAR(32))
   RETURNS VARCHAR(4096)
   LANGUAGE JAVA
   PARAMETER STYLE SYSTEM DEFINED JAVA
   NO SQL
   EXTERNAL NAME 'class
com.sqlstream.plugin.googlegeocode.GeoCode.getAddress';  

#### Example use
​```
--create a test schema
CREATE OR REPLACE SCHEMA "test";
SET SCHEMA '"test"';
--create stream with list of latlons (these would need to be pumped into the stream from another code block)
CREATE STREAM "S" ("LATLON" VARCHAR(132));
SELECT "GETADDR1"("ADDRESS") AS "LATLON" FROM "S";

Sample values

values("getAddr1"('37.7751960','-122.4192040'));
values("getAddr1"('36.7782610','-119.4179324'));
values("getAddr1"('-32.7222325','-140.0187653'));
--no embedded blanks allowed in single-string lat/lon pair
values("getAddr3"('37.7750280, -122.4199660'));
values("getAddr3"('37.7750280,%20-122.4199660'));
--other inputs that fail
values("getAddr2"(0.0, 0.0));
values("getAddr2"(89.0, 179.0));
values("getAddr2"(91.0, 181.0));
values("getAddr2"(-91.0, -181.0));
values("getAddr1"(cast(null as varchar), cast(null as varchar)));