Teradata Magazine Online

Tech2Tech

Hands On

Enabling geospatial

Automated features change latitude and longitude into actionable data.

by William M. Smith

The sophistication of geospatial abilities and the benefits they bring make understanding this developing business intelligence (BI) component a competitive necessity. In response to this challenge, Teradata 13 has included geospatial features that store and analyze a location’s longitude and latitude information. With this level of detail, organizations can link locations to business events.

Teradata’s in-database geospatial analysis is based on the SQL/MM spatial standard:

Click to enlarge

• The ST_GEOMETRY data type enables the storage of geometric data, such as points, lines and polygons in the Teradata Database.
• More than 60 analytic methods enable spatial analysis of data.

These tools enable businesses to integrate geospatial with other more traditional data and glean new information from the resulting analytics. Insights could include identifying the biggest-selling product for consumers living in a particular neighborhood or the prime location to build a store with specialized products geared to a specific demographic.

How it’s done

The following SQL example shows how ST_GEOMETRY converts latitude and longitude coordinates for “attractions” to be stored in the Teradata Database. First, the ST_GEOMETRY data type is created:

CREATE TABLE attractions
(
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(100),
  city VARCHAR(50),
  state CHAR(2),
  geo ST_GEOMETRY
)

The data type is then loaded with appropriate geocodes:

INSERT attractions
  (id,name,city,state,geo) values 
  (2,’George Bush Intercontinental 
  Airport’,’Houston’,’TX’,
  ’Point( -95.347761 29.983982)’);

Automated features

Teradata’s geospatial features provide automatic functions to standardize the two critical specifications of coordinate formats and coordinate reference systems (CRSs) required for geocoding data for an enterprise data warehouse (EDW). (See figure.)

Coordinate formats

Three generally accepted formats for representing coordinates in latitude and longitude—and their examples—are:

• Degrees, minutes, seconds (DMS): 49°30’00”N, 123°30’00”W
• Decimal, minutes (DM): 49°30.0’, -123°30.0’, 49d30.0m, -123d30.0’
• Decimal, degrees (DD): 49.5000°, -123.5000°, generally with four to six decimal numbers

DMS, the most common format, is standard on all charts and maps. DD expresses coordinates as decimal fractions and is the most convenient for calculations or computations like those performed with SQL spatial functions.

Depending on the format of coordinates provided, it is likely that the EDW will require a conversion to DD to enable analysis. This mathematical function is automated in Teradata 13. For instance, the DMS coordinate W87°43’41” is converted to DD with these steps:

1. Calculate the total number of seconds: 43’41” = (43*60 + 41) = 2,621 seconds
2. The fractional part is the total number of seconds divided by 3,600: 2,621 / 3,600 ≈ 0.728056
3. Add fractional degrees to whole degrees to produce the final result: 87 + 0.728056 = 87.728056
4. Negate the result for a longitude west of the Prime Meridian
5. Final result is -87.728056

Once the latitude and longitude coordinates are converted, the next step of tagging business events to a location can take place.

Coordinate reference systems

CRSs use satellite and remote sensing imagery to provide location information. Many CRSs are available, but the two most common are World Geodetic System (WGS) and North American Datum (NAD).

In the same way that the formats are automatically converted in the Teradata Database to match a business’s requirements, so too is the CRS. This assures that the coordinates used to geocode, or tag, business events are standardized so that geospatial analytics can be applied uniformly and consistently across an enterprise.

This ST_TRANSFORM function shows how the CRS for “attractions” is converted from NAD 83 to WGS 84:

INSERT attraction
SELECT A.id, 2, A.geo.ST_Transform
  (SRS_1.srtext, SRS_2.srtext) 
  .ST_Transform(SRS_2.srtext,SRS_1.srtext)
FROM attractions A,
  sysspatial.SPATIAL_REF_SYS SRS_1,
  sysspatial.SPATIAL_REF_SYS SRS_2
WHERE SRS_1.AUTH_SRID = 32615 -- UTM 15 / WGS84
AND SRS_2.AUTH_SRID = 4326 -- WGS84

Other techniques include assigning coordinates to geometric shapes on the surface of Earth the way surveyors calculate their dimensions.

Geocoding alternatives

To achieve more scalable geocoding, businesses can provide address files to service bureaus for conversion, or acquire software and perform geocoding in-house.

Outsourced geocoding

Tele Atlas, a subsidiary of the navigation system manufacturer TomTom, and NAVTEQ, a subsidiary of mobile phone manufacturer Nokia, continuously collect data from sources around the world to update their databases with the latitude and longitude of addresses and points of interest (POIs). Both vendors:

• Offer services by subscription or on a per-geocoded-address basis
• Accept files of addresses periodically
• Match addresses to their coordinates
• Return files of geocoded addresses

The subscribing business loads the geocoded addresses to its internal database and links the geocodes to, for instance, its operational and financial data.

In-house geocoding

Google, Yahoo and Microsoft provide application programming interfaces (APIs) to their geocoding services, allowing businesses to:

• Invoke the APIs from business software applications
• Send addresses to the geocoding service for latitude and longitude conversion

Vendors such as Informatica offer geocoding as part of their address-cleansing transaction during the extract, transform and load process. The software matches the standardized addresses to the appropriate latitude and longitude coordinates (based on the geometric data types or values provided by the business) and stores the coordinates in the address file.

Geographic information system providers such as ESRI and Pitney Bowes Business Insight also provide geocoding technology as part of their complete solutions.

Another vendor, First American Proxix Solutions, developed PxPoint, an application designed to find geospatial relationships from data on natural hazards and property information. PxPoint processes spatial layer files that contain information about a set of spatial features, such as:

• Street center-lines provided by map provider NAVTEQ
• POIs in North America and Europe
• US parcel polygons, which identify size, location and associated information of properties

These are a few of the geocoding solutions obtainable. Availability of geocoding technology varies widely in the different geographic regions.

Within reach

Geocoding describes locations, enabling businesses to use geospatial information for deeper analytics. With this level of sophistication, organizations can expand their service and products to reach the areas that are most lucrative.

William “Bill” M. Smith is a principal with Claraview, a division of Teradata, which offers specialized services in geospatial analytics.