GoogleGeoCode UDF

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems > Transforming Data in s-Server >

GoogleGeoCode UDF

Previous pageReturn to chapter overviewNext page

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 UDF in this guide and CREATE FUNCTION in the 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

Req'd

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)));