# This test test the ST_Transform function --echo # --echo # NULL values. --echo # --echo # At least one NULL parameter. Should return NULL. SELECT ST_TRANSFORM(NULL, NULL); SELECT ST_TRANSFORM(NULL, 4326); SELECT ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), NULL); --echo # --echo # Invalid parameters. --echo # --echo # Invalid geometry. Should raise error. --error ER_GIS_INVALID_DATA DO ST_TRANSFORM(x'00000000DEADBEEF', 4326); --echo # Non-existing SRID. Should raise error. # Assume SRID 1000000000 is undefined. --error ER_SRS_NOT_FOUND DO ST_TRANSFORM( x'94007735010100000000000000000000000000000000000000', 4326 ); --error ER_SRS_NOT_FOUND DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000000000); --echo # --echo # Transformation to/from SRID 0. --echo # --echo # Transformation from SRID 0 to SRID 0 is a no-op. Should return true. SELECT ST_EQUALS( ST_GEOMFROMTEXT('POINT(0 0)'), ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)'), 0) ) AS is_noop; --echo # Transformation between SRID 0 and geographic SRS. Should raise error. # Ellipsoidal: --error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)'), 4326); --error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 0); # Spherical: --error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)'), 4047); --error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4047), 0); --echo # Transformation between SRID 0 and projected SRS. Should raise error. --error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)'), 3857); --error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 3857), 0); --echo # --echo # Transformation between geographic SRSs. --echo # --echo # Transformation of every geometry type. Should succeed. DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 3819); DO ST_TRANSFORM(ST_GEOMFROMTEXT('LINESTRING(0 0, 1 1)', 4326), 3819); DO ST_TRANSFORM(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 3819); DO ST_TRANSFORM(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 3819); DO ST_TRANSFORM(ST_GEOMFROMTEXT('MULTIPOINT((0 0))', 4326), 3819); DO ST_TRANSFORM(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 3819); DO ST_TRANSFORM( ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 3819 ); --echo # Beginning of tests to verify that WGS 84 is recognized. --echo # WGS 84 with no authority codes. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with no authority codes exept EPSG 4326 for the entire SRS. --echo # Should succeed. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect authority code. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","0"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with authority code for the spheroid. Should succeed. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect authority code for the spheroid. Should raise --echo # error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","0"]]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect semi-major axis. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",1378137,298.257223563,AUTHORITY["EPSG","7030"]]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect inverse flattening. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,198.257223563,AUTHORITY["EPSG","7030"]]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with authority code for the datum. Should succeed. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect authority code for the datum. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563],AUTHORITY["EPSG","0"]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with authority code for the prime meridian. Should succeed. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect authority code for the prime meridian. Should --echo # raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","0"]],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect prime meridian. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",1,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with authority code for the unit. Should succeed. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect authority code for the unit. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","0"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect unit. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.01,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # WGS 84 with incorrect axes. Should raise error. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lon",EAST],AXIS["Lat",NORTH],AUTHORITY["EPSG","4326"]]'; --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # End of tests to verify that WGS 84 is recognized. --echo # Geographic SRS with TOWGS84 clause. Should succeed. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST]]'; DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # Spherical SRS with TOWGS84 clause. Should succeed. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,0],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["Greenwich",0],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST]]'; DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326); DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326); DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # Prime meridian 1 degree East of Greenwich. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,0],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["foo",1],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST]]'; --replace_numeric_round 15 SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326) ) AS lon_should_be_plus_one; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326) ) AS lon_should_be_minus_one; DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # Prime meridian 1 degree West of Greenwich. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,0],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["foo",-1],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST]]'; --replace_numeric_round 15 SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326) ) AS lon_should_be_minus_one; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326) ) AS lon_should_be_plus_one; DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # Prime meridian 180 degrees East of Greenwich. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,0],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["foo",180],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST]]'; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326) ) AS lon_should_be_180; # Because of floating point computations, -180 is not converted to 180, even # though the range is (-180, 180]. SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326) ) AS lon_should_be_minus_180; DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # Prime meridian 180 degrees West of Greenwich. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,0],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["foo",-180],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST]]'; # Because of floating point computations, -180 is not converted to 180, even # though the range is (-180, 180]. SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326) ) AS lon_should_be_minus_180; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326) ) AS lon_should_be_180; DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # Transformation between degrees and gradians. CREATE SPATIAL REFERENCE SYSTEM 1000004326 NAME 'foo' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["Greenwich",0],UNIT["grad",0.01570796326794895],AXIS["Lat",NORTH],AXIS["Lon",EAST]]'; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326) ) AS should_be_0_0; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326) ) AS should_be_0_0; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(100 100)', 1000004326), 4326) ) AS should_be_90_90; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(90 90)', 4326), 1000004326) ) AS should_be_100_100; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-100 -100)', 1000004326), 4326) ) AS should_be_minus_90_minus_90; SELECT ST_ASTEXT( ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-90 -90)', 4326), 1000004326) ) AS should_be_minus_100_minus_100; DROP SPATIAL REFERENCE SYSTEM 1000004326; --echo # Transformations between European Datum 1950 (ED50) and WGS 84. --echo # Positions in Western Europe will be further North and East in ED50 than --echo # in WGS 84. --echo # Trondheim. --replace_numeric_round 6 SELECT ST_ASTEXT( ST_TRANSFORM( ST_GEOMFROMTEXT('POINT(63.4269121428687 10.3969184885902)', 4326), 4230 ) ) AS ED50; --replace_numeric_round 6 SELECT ST_ASTEXT( ST_TRANSFORM( ST_GEOMFROMTEXT('POINT(63.42724046084803 10.398501870897267)', 4230), 4326 ) ) AS WGS84; --echo # Telegrafenberg, Potsdam (origin of ED50). --replace_numeric_round 6 SELECT ST_ASTEXT( ST_TRANSFORM( ST_GEOMFROMTEXT('POINT(52.381389 13.064444)', 4326), 4230 ) ) AS ED50; --replace_numeric_round 6 SELECT ST_ASTEXT( ST_TRANSFORM( ST_GEOMFROMTEXT('POINT(52.38208611407426 13.065520672345304)', 4230), 4326 ) ) AS WGS84; --echo # --echo # Transformation to/from projected SRSs. --echo # # Examples taken from IOGP Publication 373-7-2 – Geomatics Guidance Note number 7, part 2 – September 2019 # available at www.epsg.org # If IOGP does not contain an example then one is produced and tested against proj4,6 # EPSG 1024 Popular Visualisation Pseudo Mercator # EPSG 3857: the web mercator projection i.e. epsg 1024 with wgs84 as base ellipsoid # example from "Coordinate Conversions and Transformations including Formulas, # IOGP Publication 373-7-2 – Geomatics Guidance Note number 7, part 2 – September 2019", pg. 45 DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(24.381786962 -100.33333332)', 4326), 3857)); DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-11169055.574774178 2800000.005281789)', 3857), 4326)); # test user defined EPSG 1024 projection CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 33001 NAME 'webmodairy' ORGANIZATION 'EPSG' IDENTIFIED BY 33001 DEFINITION 'PROJCS["Modified Airy / Pseudo-Mercator",GEOGCS["modairy",DATUM["modairy",SPHEROID["Bessel 1841", 6377340.189,299.324937365, AUTHORITY["EPSG","7004"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6120"]],PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST], AUTHORITY["EPSG","4120"]],PROJECTION["Popular Visualisation Pseudo Mercator",AUTHORITY["EPSG","1024"]],PARAMETER["Latitude of natural origin",0,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",0.01,AUTHORITY["EPSG","8802"]],PARAMETER["False easting",100,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",-10,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","3857"]]'; DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0.0008983333 0.001796667)', 4326, 'axis-order=long-lat'), 33001)); DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-913.0663257585971 189.97410223677596)', 33001, 'axis-order=long-lat'), 4326), 'axis-order=long-lat'); DROP SPATIAL REFERENCE SYSTEM 33001; # EPSG 1027 Lambert Azimuthal Equal Area (Spherical) # create user defined srs based on 2163 but with a towgs84 clause CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 33001 NAME 'userdef' ORGANIZATION 'EPSG' IDENTIFIED BY 33001 DEFINITION 'PROJCS["US National Atlas Equal Area",GEOGCS["Unspecified datum based upon the Clarke 1866 Authalic Sphere",DATUM["Not specified (based on Clarke 1866 Authalic Sphere)",SPHEROID["Clarke 1866 Authalic Sphere",6370997,0,AUTHORITY["EPSG","7052"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6052"]],PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST], AUTHORITY["EPSG","4052"]],PROJECTION["Lambert Azimuthal Equal Area (Spherical)",AUTHORITY["EPSG","1027"]],PARAMETER["Latitude of natural origin",45,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",-100,AUTHORITY["EPSG","8802"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","2163"]]'; DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(52 10)', 4326), 33001)); DROP SPATIAL REFERENCE SYSTEM 33001; # EPSG 1028 Equidistant Cylindrical DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(52 10)', 4326), 4087)); # EPSG 1029 Equidistant Cylindrical (Spherical) # create user defined srs based on 4088 but with a towgs84 clause CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 33001 NAME 'userdef' ORGANIZATION 'EPSG' IDENTIFIED BY 33001 DEFINITION 'PROJCS["World Equidistant Cylindrical (Sphere)",GEOGCS["Unspecified datum based upon the GRS 1980 Authalic Sphere",DATUM["Not specified (based on GRS 1980 Authalic Sphere)",SPHEROID["GRS 1980 Authalic Sphere",6371007,0,AUTHORITY["EPSG","7048"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6047"]],PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST], AUTHORITY["EPSG","4047"]],PROJECTION["Equidistant Cylindrical (Spherical)",AUTHORITY["EPSG","1029"]],PARAMETER["Latitude of 1st standard parallel",0,AUTHORITY["EPSG","8823"]],PARAMETER["Longitude of natural origin",0,AUTHORITY["EPSG","8802"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","4088"]]'; DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(52 10)', 4326), 33001)); DROP SPATIAL REFERENCE SYSTEM 33001; # EPSG 1041 Krovak (North Orientated) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50.20901 16.84977)', 4326), 5514)); # EPSG 1051 Lambert Conic Conformal (2SP Michigan) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(43.750000014 -83.166666653)', 4326), 6201)); # EPSG 1052 Colombia Urban DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(4.7999999945 -74.250000023)', 4326), 6247)); # EPSG 9801 Lambert Conic Conformal (1SP) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(17.932166647 -76.943683174)', 4326), 24200)); # EPSG 9802 Lambert Conic Conformal (2SP) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(28.500000182 -95.99999989)', 4326), 32040)); # EPSG 9803 Lambert Conic Conformal (2SP Belgium) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50.679572292 5.80737015)', 4326), 31300)); # EPSG 9804 Mercator (variant A) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-3.0000001398 119.99999986)', 4326), 3002)); # EPSG 9805 Mercator (variant B) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(52.999999796 52.999999796)', 4326), 3388)); # EPSG 9806 Cassini-Soldner DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(10.000000275 -62.000000216)', 4326), 30200)); # EPSG 9807 Transverse Mercator # BG and proj4 compute POINT(577393.3402239741 69673.56274845602) # while IOGP note and proj6 compute POINT(577274.99 69740.50) # proj4 computes POINT(577274.99 69740.50) if we avoid the +wgs84 string of source DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50.499999871 0.50000021429)', 4326), 27700)); # EPSG 9808 Transverse Mercator (South Orientated) DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(-25.732028354 28.282632944)', 4326), 2053)); # EPSG 9809 Oblique Stereographic DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(53.000000025 5.9999999931)', 4326), 28992)); # EPSG 9810 Polar Stereographic (variant A) DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(73.000000003 44.000000007)', 4326), 5041)); #TODO check SRS with lat_0=-90 e.g. epsg 5482 # EPSG 9811 New Zealand Map Grid DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(-36.848461 174.763336)', 4326), 27200)); # EPSG 9812 Hotine Oblique Mercator (variant A) DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(12 117)', 4326), 3079)); # EPSG 9813 Laborde Oblique Mercator DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(-16.189799986 44.45757)', 4326), 8441)); #TODO: Add a usert defined Laborde Oblique Mercator projection for 29701 (Paris) #to test non-Greenwitch pms and non degree inputs (e.g. grad) # EPSG 9815 Hotine Oblique Mercator (variant B) DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(5.3872536023 115.80550545)', 4326), 29873)); # EPSG 9817 Lambert Conic Near-Conformal DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(37.521562493 34.136469742)', 4326), 22700)); # data source GIGS-5107-04 # EPSG 9818 American Polyconic DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(-6 -45)', 4674), 5880)); # EPSG 9819 Krovak DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50.20901 16.84977)', 4326), 5513)); # EPSG 9820 Lambert Azimuthal Equal Area DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50 5)', 4326), 3035)); # EPSG 9822 Albers Equal Area DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(42.749999987 -78.75)', 4326), 3174)); # EPSG 9824 Transverse Mercator Zoned Grid System DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(56 12)', 4326), 32600)); DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-44 174)', 4326), 32700)); # EPSG 9824 9828 Bonne (South Orientated) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(38.736946 -9.142685)', 4326), 5017)); # EPSG 9829 Polar Stereographic (variant B) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-75 120)', 4326), 3032)); # EPSG 9830 Polar Stereographic (variant C) DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-66.605227791 140.07140001)', 4326), 2985)); # EPSG 9831 Guam Projection DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(13.33903845 144.63533131)', 4326), 3993)); # EPSG 9832 Modified Azimuthal Equidistant DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(9.5965258594 138.19303001)', 4326), 3295)); CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 33001 NAME 'geocs3295' ORGANIZATION 'EPSG' IDENTIFIED BY 33001 DEFINITION 'GEOGCS["Guam 1963",DATUM["Guam 1963",SPHEROID["Clarke 1866",6378206.4,294.9786982138982,AUTHORITY["EPSG","7008"]],TOWGS84[-100,-248,259,0,0,0,0],AUTHORITY["EPSG","6675"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4675"]]'; DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(9.5965258594 138.19303)', 33001), 3295)); # EPSG 9833 Hyperbolic Cassini-Soldner DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-16.841456514 179.99433651)', 4326), 3139)); # EPSG 9834 Lambert Cylindrical Equal Area (Spherical) # create user defined srs based on 3410 but with a towgs84 clause CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 33001 NAME 'userdef' ORGANIZATION 'EPSG' IDENTIFIED BY 33001 DEFINITION 'PROJCS["NSIDC EASE-Grid Global",GEOGCS["Unspecified datum based upon the International 1924 Authalic Sphere",DATUM["Not specified (based on International 1924 Authalic Sphere)",SPHEROID["International 1924 Authalic Sphere",6371228,0,AUTHORITY["EPSG","7057"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6053"]],PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST], AUTHORITY["EPSG","4053"]],PROJECTION["Lambert Cylindrical Equal Area (Spherical)",AUTHORITY["EPSG","9834"]],PARAMETER["Latitude of 1st standard parallel",30,AUTHORITY["EPSG","8823"]],PARAMETER["Longitude of natural origin",0,AUTHORITY["EPSG","8802"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","3410"]]'; DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(52 10)', 4326), 33001)); # EPSG 9835 Lambert Cylindrical Equal Area # create user defined srs based on 6933 but with a towgs84 clause CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 33001 NAME 'userdef' ORGANIZATION 'EPSG' IDENTIFIED BY 33001 DEFINITION 'PROJCS["WGS 84 / NSIDC EASE-Grid 2.0 Global",GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST], AUTHORITY["EPSG","4326"]],PROJECTION["Lambert Cylindrical Equal Area",AUTHORITY["EPSG","9835"]],PARAMETER["Latitude of 1st standard parallel",30,AUTHORITY["EPSG","8823"]],PARAMETER["Longitude of natural origin",0,AUTHORITY["EPSG","8802"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","6933"]]'; DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(52 10)', 4326), 33001)); DROP SPATIAL REFERENCE SYSTEM 33001; --echo # Verify that it isn't possible to transform to/from the four not implemented projections: 1042, 1043, 9816, 9826 --error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 5515); --error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 5516); --error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 22300); CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 33001 NAME 'userdef' ORGANIZATION 'EPSG' IDENTIFIED BY 33001 DEFINITION 'PROJCS["Carthage (Paris) / Tunisia Mining Grid",GEOGCS["Carthage (Paris)",DATUM["Carthage (Paris)",SPHEROID["Clarke 1880 (IGN)",6378249.2,293.4660212936269,AUTHORITY["EPSG","7011"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6816"]],PRIMEM["Paris",2.5969213,AUTHORITY["EPSG","8903"]],UNIT["grad",0.01570796326794895,AUTHORITY["EPSG","9105"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4816"]],PROJECTION["Tunisia Mining Grid",AUTHORITY["EPSG","9816"]],PARAMETER["Latitude of false origin",36.5964,AUTHORITY["EPSG","8821"]],PARAMETER["Longitude of false origin",7.83445,AUTHORITY["EPSG","8822"]],PARAMETER["Easting at false origin",270,AUTHORITY["EPSG","8826"]],PARAMETER["Northing at false origin",360,AUTHORITY["EPSG","8827"]],UNIT["kilometre",1000,AUTHORITY["EPSG","9036"]],AXIS["X",EAST],AXIS["Y",NORTH],AUTHORITY["EPSG","22300"]]'; --error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 33001); --error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 2218); --error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 5515), 4326); --error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 5516), 4326); --error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 22300), 4326); --error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 33001), 4326); DROP SPATIAL REFERENCE SYSTEM 33001; --error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 2218), 4326);