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.

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-05-2010
02:35 PM

05-05-2010
02:35 PM

Quicker Method to Calculate Distances on the Globe

Someone asked a few days ago for an easier and quicker way to calculate distance between two points on a sphere without having to transform to the UTM SRS (Spatial Reference System) from the WGS84 SRS.

First, when using Teradata Geospatial database features all of the ST_GEOMETRY object calculations are based on a Cartesian coordinate system, except for selected distance methods.

The ST_GEOMETRY type supports the following three distance methods:

ST_DISTANCE: Computes distance between objects based upon a Cartesian coordinate system.

ST_SPHERICALDISTANCE: Computes distance between point objects based upon a geodetic coordinate system where the earth is modeled as a sphere.

ST_SPHEROIDALDISTANCE: Computes distance between point objects based upon a geodetic coordinate system where the earth is modeled as a spheroid, a flattened sphere. This method accepts inputs for the spheroids radius and flattening values.

So if you are looking to calculate a distance between two points that are somewhere on the earth, there are a few options:

First, you can perform Teradata spatial transformations to/from all of the SRS definitions stored in the SYSSPATIAL.SPATIAL_REF_SYS table. The purpose of SRS is to provide a common reference for defining the location of an object on the earth's surface. The ST_GEOMETRY ST_TRANSFORM() method will convert the spatial objects between the passed in SRS Well Known text definitions. The SRS definitions are taken from ESPG, http://www.epsg.org. The SQL/MM standard specifies that the transform method should pass the SRS by reference. Since Teradata does not support access to external data from within a method, in Teradata the SRS is passed by value to the transform method.

Here is an example where we want to find customers that are less than a half-mile from stores using the ST_TRANSFORM method. The original coordinates are in WGS84 and we will be transforming them to the UTM SRS using the SPATIAL_REF_SYS table:

SELECT S.id AS Store_ID,

C.id AS Customer_ID,

C.location.ST_Transform(SRS_1.srtext,SRS_2.srtext).ST_Distance(S.location.ST_Transform(

SRS_1.srtext,SRS_2.srtext) ) AS Distance_Meters

FROM Customers C, Stores S,

sysspatial.SPATIAL_REF_SYS SRS_1,

sysspatial.SPATIAL_REF_SYS SRS_2

WHERE SRS_1.AUTH_SRID = 32610 -- UTM 10 / WGS84

AND SRS_2.AUTH_SRID = 4326 -- WGS84

AND Distance < .5

ORDER BY 1,3;

-- Divide the Distance_Meters by 1609.344 to get the distance in miles

You can also calculate the same distances without any Teradata spatial transformations based on an internal geodetic coordinate system where the earth is modeled as a sphere. Here is the same example as above where we want to find customers that are less than a half-mile from each store using the ST_SPHERICALDISTANCE geospatial function method:

SELECT S.id AS Store_ID,

C.id AS Customer_ID,

cast(S.location as ST_POINT).SphericalDistance(C.location) AS Distance_Meters

FROM Customers C, Stores S

WHERE Distance < 0.5

ORDER BY 1,3;

-- Divide the Distance_Meters by 1609.344 to get the distance in miles

The results from the two methods will be the same.

Please note that the last method of using ST_SPHERICALDISTANCE can only be used when comparing two points (non-points don’t work) and both points are in the WGS84 SRS.

In summation, if you are looking for a quicker method to calculate distances on a sphere between points in the WGS84 SRS, instead of having to transform to the UTM SRS you can use the ST_SPHERICALDISTANCE function.

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-06-2010
07:58 AM

05-06-2010
07:58 AM

Re: Quicker Method to Calculate Distances on the Globe

There is some confusion about the names.

The articles say the names are ST_SPHERICALDISTANCE() and ST_SPHEROIDALDISTANCE() and the docco (Orange Book) says the same:

"except for the ST_SPHERICAL_DISTANCE_METHOD()."

but in other parts in the same docco and in the examples in the OB the name appears as ST_POINT.SPHERICALDISTANCE() .

It took me a while to find out that the real method name (at least in my DB with geospatial 1.0) is ST_POINT.SPHERICALDISTANCE(), WITHOUT the leading 'ST_', possibly an heritage from ST_DISTANCE() method.

Cheers.

Carlos.

The articles say the names are ST_SPHERICALDISTANCE() and ST_SPHEROIDALDISTANCE() and the docco (Orange Book) says the same:

"except for the ST_SPHERICAL_DISTANCE_METHOD()."

but in other parts in the same docco and in the examples in the OB the name appears as ST_POINT.SPHERICALDISTANCE() .

It took me a while to find out that the real method name (at least in my DB with geospatial 1.0) is ST_POINT.SPHERICALDISTANCE(), WITHOUT the leading 'ST_', possibly an heritage from ST_DISTANCE() method.

Cheers.

Carlos.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-17-2010
08:29 AM

06-17-2010
08:29 AM

Re: Quicker Method to Calculate Distances on the Globe

With Teradata Geospatial 1.0, there was an ST_POINT data type with its own distance methods. ST_POINT has been dropped with Teradata 13. With Teradata 13, the ST_GEOMETRY data type supports all spatial objects; lines, curves, polygons, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-18-2013
03:18 AM

07-18-2013
03:18 AM

Re: Quicker Method to Calculate Distances on the Globe

Does this mean that if I provide the co-ordinates of say a place in London and a place in New York, ST_SPHERICALDISTANCE will give me the actual accurate distance between the two???

Or, does ST_SPHERICALDISTANCE and ST_SPHEROIDALDISTANCE work accurately with two points from different UTM zones?

Thanks