How do I dump spatial types, like point, with their SRIDs in MySQL?

Question

I am testing the point data type on Mysq 5.7 below is my java insert query.

String insertQuery1 ="INSERT INTO gtest3 SET " +
      "gDateTime='2017-07-16 01:00:00'"+
      ",vehicleID=1"+   
      ",alarmTypeID=1"+
      ",latlong=Point("+floatLatitude+","+floatLongitude+")";

The values are inserted accordingly but when I got to the row it shows something like GEOMETERY -25B but when I press to edit it show me this 'POINT(5.19878 100.492)',0 . I would like is this correct with the extra 0 at the back. Can this values be used for later spatial functions.

Answer 1

The 0 in that context is the SRID. Unlike in PostgreSQL, you don't have a method of setting that on a point. You will in version 8 of MySQL. For the purposes of calculation, it's assumed to be 0 on everything and this is horrible. There is likely to be substantial breakage when they fix this.

In MySQL, all computations are done assuming SRID 0, regardless of the actual SRID value. SRID 0 represents an infinite flat Cartesian plane with no units assigned to its axes. In the future, computations may use the specified SRID values. To ensure SRID 0 behavior, create geometry values using SRID 0. SRID 0 is the default for new geometry values if no SRID is specified. source

So the SRID will almost always be 0 because

• it doesn't yet matter
• There is no SRID mutator so you have to construct with ST_PointFromText. So the SRID will always be 0 if you construct with a point

That said, to get the SRID (future-safe) in the format provided above, you need to do this

SELECT CONCAT(ST_AsText(x), ',', ST_SRID(x))
FROM ( SELECT POINT(0,0) AS x ) AS t;

+---------------------------------------+
| CONCAT(ST_AsText(x), ',', ST_SRID(x)) |
+---------------------------------------+
| POINT(1 1),0                          |
+---------------------------------------+

This will show differently if your SRID is not 0.

SELECT CONCAT(ST_AsText(x), ',', ST_SRID(x))
FROM ( SELECT ST_PointFromText('POINT(1 1)',4326) AS x ) AS t;

+---------------------------------------+
| CONCAT(ST_AsText(x), ',', ST_SRID(x)) |
+---------------------------------------+
| POINT(1 1),4326                       |
+---------------------------------------+

Compare that to PostgreSQL,

SELECT ST_AsEWKT(x)
FROM ( VALUES (ST_SetSRID(ST_MakePoint(1,1),4326)) ) AS t(x);
      st_asewkt       
----------------------
 SRID=4326;POINT(1 1)
(1 row)

Update

Taking a look at your query, there is one glaring mistake in that you do (latitude,longitude) this should be (longitude,latitude)

So to be safe is my existing insert query is acceptable or should I add the SRID value now.

I would add the SRID value now, but not because your existing query "isn't safe". Only because you're not using placeholders and you're stringifying everything anyway.

",latlong=Point("+floatLatitude+","+floatLongitude+")";

Should be

",latlong=ST_PointFromText(POINT("+floatLongitude+" "+floatLatitude+"), 4326)";

Where 4326 is the SRID of your data..

But again, MySQL doesn't take into consideration SRID yet so be aware.

Objective facts

MySQL and MariaDB have horrible spatial support. Migrate to PostgreSQL and PostGIS, if possible.