10

I am trying the following insert query on mysql 8.

Insert Into fence Set       
            fenceName='aa',
            radius=2,                   
            fenceGeometry=ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326)

It works perfectly on mysql 5.7 but here in mysql 8 I get this error Latitude 102.189331 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000].

Based on some suggestion I also did this.

ALTER TABLE fence MODIFY fenceGeometry geometry NOT NULL SRID 4326;

But yet the it gives me the same results.

2 Answers 2

18

That's because on MySQL 8, they did the unthinkable and implemented the stupid method of doing geography coordinates (like MySQL). This broke backwards compatability.

  • When you use Geometry on MySQL (any SRID) except 4326 you use (long,lat). That's (x,y) on a Cartesian system.
  • When you use Geography on MySQL (SRID = 4326) you use (lat, long). That's (y,x) typically. This is done because English speakers tend to say "latitude and longitude" rather than "longitude, latitude"

Amusingly Oracle which owns MySQL does not do this on Oracle Spatial. Oracle Spatial and PostGIS are consistently (long,lat). To resolve this switch the order, not

ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326)

But, this,

ST_GeomFromText('POINT(3.880696482497261 102.1893310546875)', 4326)

In MySQL 5.x there was no real geographical coordinate system.

Side note

You shouldn't be using WKT for this if you have the points you can just use Point(), and ST_SRID(pt,SRID)

ST_SRID(Point(3.880696482497261, 102.1893310546875), 4326)

More information

For more information see also,

5
  • There are two ways to construct a point, one is constructing from text (ST_GeomFromText and ST_PointFromText), the other is using the Point Constructor. If you're making calls to construct point you're better of just using the point constructor than using text (which then has to be parsed). Commented Jul 3, 2019 at 17:01
  • actually I am going to be using points, polygon and lines. So which is best then should I just stick with ST_GeomFromText
    – newbie
    Commented Jul 3, 2019 at 17:02
  • Yes, if you're doing the other things too that's really the only way in MySQL. Commented Jul 3, 2019 at 17:03
  • 2
    Looks like they added ST_SwapXY function to help mitigate this change.
    – Mark G
    Commented Sep 25, 2020 at 16:30
  • 3
    Thank you for this. This is not only not backwards compatible it makes me want to punch myself in the face. Commented Jun 29, 2022 at 15:19
3

Latest answer to allow for backwards compatibility is to provide additional parameter to force order.

'axis-order=long-lat'

For example

ST_GeomFromText('POINT($lng $lat)', 4326, 'axis-order=long-lat')

So your query would be like this.

INSERT INTO
   fence 
SET       
   fenceName='aa',
   radius=2,                   
   fenceGeometry=ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326, 'axis-order=long-lat')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.