SQL MR: CrowMiles

Aster Field Strong
Teradata Employee

THIS IS A BETA CUSTOM SQL-MR STATEMENT AND IT IS NOT SUPPORTED BY TERADATA ENGINEERING, CUSTOMER SUPPORT, OR THE FIELD.

CrowMiles was written while analyzing shopping habits of people and identifying why they continue to ignore retail shops near their home location, and understanding if their is a loyality aspect with the retail chain despite people moving out of a certain area.

Overview

CrowMiles is the simplest of MR functions which calculates the distance between two longitude and latitude points.

Syntax

Select * From CrowMiles( on source_table_name

longstart(<start point longitude>)

longend(<end point longitude>)

latstart(<start point latitude>)

latend(<end point latitude>)

);

Arguments

LongStart

Required

Longitude of first point.

LatStart

Required

Latitude of first point

LongEnd

Required

Longitude of second point

LatEnd

Required

Latitude of second point

Output

For each input row, CrowMiles will return all input columns in addition to:

The distance between the two longitude and latitude points.

Error Messages

NumberFormatException -> The Longitude and Latitude points should be varchar columns containing numeric data.

CrowMiles.zip

2 Comments
Teradata Employee

John, I can imagine that crow miles would be valuable for a lot of other applications where total distance of separation would be important. How does the weather data at one geo-loc impact the events of another location?

In your use case (shopping habits) it would also be interesting to compare shortest driving distance (SDD).  Is there a way to "on the fly" source that data directly (assuming you don't work for Google or Apple)? 

Tim for offline driving distance type analysis with Aster you would need something like pgrouting. pgRouting Project — Open Source Routing Library.