Adding Geospatial Location Data - 2 Minute Guide

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Teradata Employee

Adding Geospatial Location Data - 2 Minute Guide

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.

For example, if you already have a table that includes columns for Latitude and Longitude such as:

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!

5 REPLIES

Re: Adding Geospatial Location Data - 2 Minute Guide

In the location data that I have, I see values like 24.3N 97.8W. How do I convert this to a regular lat/long value like the ones shown above?
Teradata Employee

Re: Adding Geospatial Location Data - 2 Minute Guide

In the examples that I'm using, the negative longitude values are West and negative latitude values would be South.
So your conversion will be 24.3N = 24.3, 97.8W = =97.8.

Re: Adding Geospatial Location Data - 2 Minute Guide

Hi Mike. Good series of articles. I've been experimenting lately with moving spatialized data from SQL server 2008 and MIPro into Teradata. The most useable way I've found to move geographic objects, is using the GeoImportExport utility. I would prefer however, to move these objects right from SQL server spatial rather than MIPro format. Any recommendations or articles on how to ?
Enthusiast

Re: Adding Geospatial Location Data - 2 Minute Guide

Mike, can fastload load geometry data in the WKB format?
Enthusiast

Re: Adding Geospatial Location Data - 2 Minute Guide

I read all your Gespatial articles. Very interesting and it has been explained nicely. We have TD 13.1 with native functions and I tried to calculate the distance between two locations. If we are not providing right UTM zone, it gives wrong result. I have Postal Zip code, longitute and lattitude details. However, I have few questions:

1) I do not know how do we identify the correct UTM zone for my calculation

2) How do we implement the ST_TRANSFORM function when we have source location is in different UTM zone and target is in different UTM zone?

Please adivse me on this. Thank you!