# 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);