Extensibility

## 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_MetersFROM    Customers C, Stores S,        sysspatial.SPATIAL_REF_SYS SRS_1,        sysspatial.SPATIAL_REF_SYS SRS_2WHERE   SRS_1.AUTH_SRID = 32610         -- UTM 10 / WGS84AND     SRS_2.AUTH_SRID = 4326          -- WGS84AND     Distance < .5ORDER 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_MetersFROM Customers C, Stores SWHERE Distance < 0.5ORDER 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.

Tags (2)
3 REPLIES 3
Supporter

## 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.