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.

Discussion
06 May 2010

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.

17 Jun 2010

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.

18 Jul 2013

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