CODEWITHSUNDEEP
phpmysqlspatial
Frildoren
Frildoren

Reputation: 243

Exact distance between two points in MySQL Spatial table in KMs

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.

Upvotes: 0

Views: 3291

Answers (1)

jsist
jsist

Reputation: 5253

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

Upvotes: 3

Related Questions