| Bug #109666 | ST_Transform fails when transforming from 4326 to 6350 | ||
|---|---|---|---|
| Submitted: | 17 Jan 2023 19:12 | Modified: | 18 Jan 2023 8:06 |
| Reporter: | Daniel Trksak | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S4 (Feature request) |
| Version: | 8.0.32 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[18 Jan 2023 8:06]
MySQL Verification Team
Hello Daniel, Thank you for the report and feedback. regards, Umesh
[10 Feb 2023 12:27]
Norvald Ryeng
Posted by developer: Thank you for the report. I'm changing it from a bug report to a feature request. The spatial reference system definitions in MySQL are based on the EPSG Dataset. The current definitions are generated from the EPSG Dataset version 9.3. SRID 6350 "NAD83(2011) / Conus Albers" is a projection based on SRID 6318 "NAD83(2011)". In order for MySQL to include a TOWGS84 clause in the definition of SRID 6350, a transformation must exist in the EPSG Dataset from SRID 6318 to SRID 4326 or SRID 4979 (2d and 3d "WGS 84", respectively). The EPSG Dataset version 9.3 does not contain any such transformation. In fact, it contains no transformations at all to/from SRID 6318. Therefore, no TOWGS84 clause is added in MySQL's spatial reference system definitions for SRID 6318 and SRID 6350. However, in newer versions of the EPSG Dataset, there is a transformation from SRID 6318 to SRID 4326 (transformation 9774). Therefore, I'm changing this to a feature request that can be fixed by generating spatial reference system definitions from a newer version of the EPSG Dataset.
Description: When attempting to transform from EPSG:4326 to EPSG:6350 mysql returns the error: 3744 (22S00): Transformation to SRID 6350 is not supported. The spatial reference system has no TOWGS84 clause. I am unaware if this issue persists for other SRIDs. How to repeat: SELECT ST_Transform(ST_SRID(ST_GEOMFROMTEXT('POLYGON((-109.31357024642256 35.12997643044041,-109.31804339914228 35.130049241712705,-109.31798979607369 35.12892560052572,-109.31788302807182 35.1267306417569,-109.31341346274006 35.12665877324706,-109.31351779564095 35.128863401029335,-109.31357024642256 35.12997643044041))'), 4326), 6350); Suggested fix: Add TOWGS84[0,0,0,0,0,0,0] to the DEFINITION of 6350. Working fix: CREATE SPATIAL REFERENCE SYSTEM 63500 NAME 'NEW-6350' ORGANIZATION 'EPSG' IDENTIFIED BY 63500 DEFINITION 'PROJCS["NAD83(2011) / Conus Albers",GEOGCS["NAD83(2011)",DATUM["NAD83 (National Spatial Reference System 2011)",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","1116"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6318"]],PROJECTION["Albers Equal Area",AUTHORITY["EPSG","9822"]],PARAMETER["Latitude of false origin",23,AUTHORITY["EPSG","8821"]],PARAMETER["Longitude of false origin",-96,AUTHORITY["EPSG","8822"]],PARAMETER["Latitude of 1st standard parallel",29.5,AUTHORITY["EPSG","8823"]],PARAMETER["Latitude of 2nd standard parallel",45.5111111111111,AUTHORITY["EPSG","8824"]],PARAMETER["Easting at false origin",0,AUTHORITY["EPSG","8826"]],PARAMETER["Northing at false origin",0,AUTHORITY["EPSG","8827"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","6350"]]'; SELECT ST_Transform(ST_SRID(ST_GEOMFROMTEXT('POLYGON((-109.31357024642256 35.12997643044041,-109.31804339914228 35.130049241712705,-109.31798979607369 35.12892560052572,-109.31788302807182 35.1267306417569,-109.31341346274006 35.12665877324706,-109.31351779564095 35.128863401029335,-109.31357024642256 35.12997643044041))'), 4326), 63500);