Description:
Currently, MySQL provides a set of functions that can be used to quickly & easily generate & convert geometry data types that use the 'internal' binary format.
more info:
https://dev.mysql.com/doc/refman/5.6/en/gis-mysql-specific-functions.html
https://dev.mysql.com/doc/refman/5.6/en/gis-data-formats.html#gis-internal-format
The only drawback to the use of these functions is that they don't allow for explicitly setting the SRID value upon geometry creation and/or conversion.
The SRID value is set to zero when using these MySQL-specific functions to create new geometries, which can be limiting in many cases.
mysql> set @p=POINT(-100, 30);
Query OK, 0 rows affected (0.00 sec)
mysql> select ST_SRID(@p);
+-------------+
| ST_SRID(@p) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
In many use cases, it is necessary to set the SRID for arbitrarily-created geometries in order to spatially test against existing geometrys values that are in a specific spatial ref system.
a very common scenario:
- having a pair of lat/lng input parameters representing a set of coordinates that need to be spatially tested (ex., using the ST_Within() function) with respect to a column of global polygons that are in a global grid reference system (eg., SRID 4326).
Currently, the user would be forced to do something like the following in order to explicity cast the point into a geometry with the desired SRID...
...for pre-MySQL 8.0, you'd do the something like the following...
SELECT ST_Within(
ST_PointFromWKB(ST_AsWKB(POINT(@pLon, @pLat)), 4326), myGlobalPolygon
) as result;
... in MySQL 8.0, things are a bit better, but an extra conversion function call is still required...
SELECT ST_Within(
ST_SRID(POINT(@pLon, @pLat), 4326), myGlobalPolygon
) as result;
How to repeat:
n/a
Suggested fix:
suggestion/feature request:
- make it possible to set the SRID when using the MySQL-specific geometry functions
Something like in the following two examples:
SELECT ST_Within(
POINT(@pLon, @pLat, 4326), myGlobalPolygon
) as result;
UPDATE `pointsOfInterest`
SET `geoCoord` = POINT(@ptLon, @ptLat, 4326)
WHERE `pointID` = @ptID;
sound reasonable?
