11

What is the correct order when setting a POINT in MySQL?

Even the answers in SO questions differ on this: Moving lat/lon text columns into a 'point' type column

Is it

POINT(lat lon)

or

POINT(lon lat)

As far, as i see it, it should be the first version (lat lon) as the POINT takes as params x and y, but i was not able to find an definite evidence.

Community
  • 1
  • 1
madc
  • 1,674
  • 2
  • 26
  • 41
  • You can try both ways and see what works, can't you? I would count on Quassnoi's answer. – ypercubeᵀᴹ Jun 18 '12 at 12:31
  • +1 though because I searched the MySQL docs for more than 3 minutes and couldn't find the relevant documentation. – ypercubeᵀᴹ Jun 18 '12 at 12:32
  • The only thing I found was the the [`X()` and `Y()` functions](http://dev.mysql.com/doc/refman/5.5/en/geometry-property-functions.html#point-property-functions) example. – ypercubeᵀᴹ Jun 18 '12 at 12:36
  • Basically both work. And as you access them in MySQL with X() and Y() I can only assume, that POINT(lat lon) is the correct order. But as you say, it is not well documented. – madc Jun 18 '12 at 12:37

3 Answers3

2

as one can see here https://stackoverflow.com/a/5757054/1393681 the order is (lon lat).

lon is basicly the y axis and lat the x if i look at my globe, but the lon's are traveling along the x axis and the lat's along the y axis so i think thats the reason why you use (lon lat)

Despite that, if you stay consistent in your application it shoudn't matter if the world is flipped inside your db ;-)

Community
  • 1
  • 1
mons droid
  • 1,038
  • 9
  • 10
  • 2
    You will confuse people. `lon` is the *x-axis*, and its gradations are displayed vertically. `lat` is the *y-axis*, so its gradations are horizontal. As with every 2D graph, gradations are perpendicular to the axis they measure. – Walf Mar 10 '15 at 08:12
  • 1
    it would be important to use POINT(lon lat) instead of POINT(lat lon) if you wish to use the GIS features built into MySQL 5.7+ – plong0 May 27 '21 at 23:10
1

Seems like lat lon to me. You can check this from your mysql shell:

mysql> SELECT ST_ASTEXT(coords), ST_LONGITUDE(coords), ST_LATITUDE(coords) FROM table;

+----------------------------+-----------------------+----------------------+
| ST_ASTEXT(coords)          | ST_LONGITUDE(coords)  | ST_LATITUDE(coords)  |
+----------------------------+-----------------------+----------------------+
| POINT(33.520571 -18.20072) | -18.20072             | 33.520571            |
+----------------------------+-----------------------+----------------------+

Note: SRID 4326 used.

See also: https://dev.mysql.com/doc/refman/8.0/en/gis-point-property-functions.html#function_st-latitude

Palisand
  • 1,302
  • 1
  • 14
  • 34
-3

Technically it does not matter. You are storing into the x and y coordinates of a point. You just have to read them the same way you write them.

If you care about a "proper way" I can only refer to the fact that Google Maps API creates coortinates using

var location = new google.maps.LatLng(lat, lng);

and in school you also learn about "latitude and logintude", not the other way arround.

Still, technically it really doesn't metter which way you store them. Just make sure that you use the same convention when you read them back.

stormofwar
  • 551
  • 5
  • 9
  • In programming, you will also find plenty of systems and functions which require lonlat parameters instead of latlon. However, in your DB, it still doesn't matter. – stormofwar May 06 '13 at 22:29