MySQL Forums
Forum List  »  GIS

Re: How long is a piece of string?
Posted by: Lenz Grimmer
Date: August 19, 2009 05:11AM

Hi,

Andrew Dodson Wrote:
-------------------------------------------------------
> I've read alot of the documentation on mysql site
> which fail to mention the unit of measurement
> returned.
>
> KM, Miles, Acres, Hops, Skips or as big as your
> back garden?

It does not actually have a unit, it uses the same units that you use to specify the coordinates of your geometry.

> In particular i'm trying to recreate one such
> offender the "Distance() function" from what i
> believe to be commonly recreated like so
>
> ------------
> SELECT
> GLength(LineStringFromWKB(LineString(AsBinary(Geom
> FromText('POINT(40.756054 -73.986951)')),
> AsBinary(GeomFromText('POINT(51.5001524
> -0.1262362)'))))) AS distance
> ------------
> OUTPUT:
> 74.6
>
> In this example i'm trying to find the distance
> between New York and London, which is about
> 3,500miles or 5600KM.
>
> WTF is 74.6?

You might want to try using DISTANCE() instead of GLength() - it is included in the preview builds we provide at http://forge.mysql.com/wiki/GIS_Functions

This blog post might also be useful: http://maisonbisson.com/blog/post/12146/calculating-distance-between-points-in-mysql/

Note that the units depend on the coordinate system you use. From the manual at http://dev.mysql.com/doc/refman/5.1/en/gis-class-geometry.html

Quote

A geometry value has the following properties:

* Its type. Each geometry belongs to one of the instantiable classes in the hierarchy.
* Its SRID, or Spatial Reference Identifier. This value identifies the geometry's associated Spatial Reference System that describes the coordinate space in which the geometry object is defined.

In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry.
* Its coordinates in its Spatial Reference System, represented as double-precision (eight-byte) numbers. All nonempty geometries include at least one pair of (X,Y) coordinates. Empty geometries contain no coordinates.

Coordinates are related to the SRID. For example, in different coordinate systems, the distance between two objects may differ even when objects have the same coordinates, because the distance on the planar coordinate system and the distance on the geocentric system (coordinates on the Earth's surface) are different things.

If you want to calculate distances on a sphere, also take a look at this extension:

http://forums.mysql.com/read.php?23,263139,263139#msg-263139

Hope that helps,

LenZ

Lenz Grimmer - MySQL Community Relations Manager - http://de.sun.com/
Sun Microsystems GmbH, Sonnenallee 1, 85551 Kirchheim-Heimstetten, DE
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels
Vorsitz d. Aufsichtsrates: Martin Haering AG Muenchen: HRB161028

Options: ReplyQuote


Subject
Views
Written By
Posted
4493
August 18, 2009 10:51AM
2918
August 18, 2009 12:37PM
3341
August 18, 2009 01:48PM
Re: How long is a piece of string?
5362
August 19, 2009 05:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.