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).
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,
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.
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,
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) ) || ')'
(Note: use the CAST to DECIMAL to avoid any format issues with the original FLOAT datatype.)
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:
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,
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!
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!