Exact distance between two points in MySQL Spatial table in KMs

Question

Greetings.

I have seen a few posts and answers on this topic, but I think the result given might not be really accurate.

Let's say I have rows with different points with Longitude and Latitude, in a MySQL spatial table. I just want to retrieve the distance between them, with a simple SELECT query. I have tried:

GLENGTH(
        LINESTRINGFROMWKB(
            LINESTRING(
                GEOMFROMTEXT(
                    ASTEXT(
                        POINTFROMWKB(
                            POINT( X(user_location) , Y(user_location))))),
                GEOMFROMTEXT(
                    ASTEXT(
                        POINTFROMWKB(
                            POINT( $latitude, $longitude )))))))*100

I know its kind of 'ugly', and I'm afraid the result is not given in KiloMeters.

How am I supposed to do this? Many thanks.

It would be helpful to format your formula so it's readable. Also, what you're looking for is known as the "haversine" or "great circle" distance formula. — O. Jones, Sep 27 '12 at 16:57
Also: it depends on how accurate you need to be. If you're doing a store-finder application, the haversine formula is fine. If you're doing an engineering plan for a bridge, you need to learn about cartographic projections; the earth isn't perfectly spherical. — O. Jones, Sep 27 '12 at 17:26

score 3 · Answer 1 · edited May 23 '17 at 10:24

Method you are using will not give you accurate results at all. Since GLENGTH uses the simple Cartesian distance formula. You can as well read in the comments here on GLENGTH's MySQL page that it cant be used on Spherical surfaces, it is there for Cartesian plane.

In this Stackoverflow answer, there is a modified formula and how to use it explained clearly, have a look at it. You will need to modify that to suit your purpose. But it will definitely guide you on how to proceed.

Hope it helps...

Exact distance between two points in MySQL Spatial table in KMs

1 Answers1