TERADATA: Adding Geospatial Location Data - 2 Minute Guide

Adding Geospatial Location Data - 2 Minute Guide

Article by Teradata Mike on 30 Jun 2010  Tags: geospatial st_geometry point location

Teradata has added geospatial features to Teradata 13 (and earlier versions with the optional extension package - see my earlier article here).  These features enable powerful location based analytics, but often I'm asked how to get started, especially by customers who already capture Latitude/Longitude location data.  So to help, I've put together this quick 2 minute guide on converting your existing location data to the new ST_Geometry data type in Teradata so that you can leverage the powerful library of geospatial methods now within Teradata.

Teradata Geospatial supports a wide range of geometry data; from simple points to more complex lines, curves and polygons. This is all supported by one new data type - ST_Geometry.  To populate an ST_Geometry column requires a specific format for each type of geometry when adding the data.  This format is called the WKT or Well Know Text format. (Click here for my article on using each of the basic geometry types).

Loading New Location Data

For adding location Point data, the WKT format is 'Point(X Y)'. Note that the X and Y coordinates are separated by a space, not a comma and the text is enclosed in single quotes.

If this is our simple table containing an ST_Geometry column:

CREATE TABLE bldg_location

(

    bldg_id    INT NOT NULL,

    geo        ST_GEOMETRY

);

Our simple SQL insert statement would look something like:

INSERT bldg_location(bldg_id, geo) VALUES(1,'Point(-117.093860 33.020725)');

In this example, geo is the ST_Geometry column in the table bldg_location and the location of the building that we are adding is -117.093860 degress Longitude and 33.020725 degrees Latitude (Longitude is the X coordinate and Latitude is the Y coordinate).  Updating data load scripts to build this formatted SQL statement is all that is needed to populate an ST_Geometry column.

Converting Existing Location Data

And for those with location coordinates already captured in their database, we can leverage Teradata's SQL string functions.

CREATE TABLE bldg_location

(

    bldg_id    INT NOT NULL,

    latitude   FLOAT,

    longitude  FLOAT

);

And it's already populated with data such as:

INSERT bldg_location(bldg_id, longitude, latitude) VALUES(1,-116.0345,35.3673);

INSERT bldg_location(bldg_id, longitude, latitude) VALUES(2,-115.5597,34.7393);

INSERT bldg_location(bldg_id, longitude, latitude) VALUES(3,-117.2278,33.1397);

INSERT bldg_location(bldg_id, longitude, latitude) VALUES(4,-116.8905,31.5309);

INSERT bldg_location(bldg_id, longitude, latitude) VALUES(5,-117.7627,34.2326);

INSERT bldg_location(bldg_id, longitude, latitude) VALUES(6,-116.7558,31.8009);

We can quickly create the WKT format using SQL string functions:

SELECT 'Point(' || CAST(longitude AS DECIMAL(15,6) ) || ' ' || CAST(latitude AS DECIMAL(15,6) ) || ')'

FROM bldg_location;

(Note: use the CAST to DECIMAL to avoid any format issues with the original FLOAT datatype.)

(((('Point('||longitude)||' ')||latitude)||')')

Point( -117.762700 34.232600)

Point( -117.227800 33.139700)

Point( -116.034500 35.367300)

Point( -116.755800 31.800900)

Point( -116.890500 31.530900)

Point( -115.559700 34.739300)

We can now take this same approach to populate an ST_GEOMETRY column in our table. First lets add the new ST_Geometry column to our table:

ALTER TABLE bldg_location ADD geo ST_GEOMETRY;

And now we'll construct the UPDATE SQL using the same SQL string manipulation technique:

UPDATE bldg_location

SET geo = 'Point(' || CAST(longitude AS Decimal(15,6) ) || ' ' || CAST(latitude AS Decimal(15,6) ) || ')'

A quick SELECT statement to verify our new ST_GEOMETRY X and Y coordinates match the original values (I changed the SQL Assistant default display for FLOAT columns from 2 decimal places to 4).  Notice too that I'm using the ST_GEOMETRY ST_X() and ST_Y() methods to access the coordinates.  With Teradata 13, this new data type is implemented using LOBs.  Unfortunately, that poses some challenges for query tools when trying to simply return the column value.

SELECT bldg_id, longitude, latitude,

       geo.ST_X(),

       geo.ST_Y()

FROM bldg_location;

Output:

bldg_id longitude     latitude    geo.ST_X()    geo.ST_Y()

5       -117.7627     34.2326     -117.7627     34.2326

3       -117.2278     33.1397     -117.2278     33.1397

1       -116.0345     35.3673     -116.0345     35.3673

6       -116.7558     31.8009     -116.7558     31.8009

4       -116.8905     31.5309     -116.8905     31.5309

2       -115.5597     34.7393     -115.5597     34.7393

That's all there is to it!  We now have an ST_GEOMETRY column representing each building's location. And along with this new data type, we have access to the powerful geospatial methods within Teradata which will allow us to perform powerful location based analytics on our data.  Have fun!