1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
|
#
# NULL values.
#
# At least one NULL parameter. Should return NULL.
SELECT ST_TRANSFORM(NULL, NULL);
ST_TRANSFORM(NULL, NULL)
NULL
SELECT ST_TRANSFORM(NULL, 4326);
ST_TRANSFORM(NULL, 4326)
NULL
SELECT ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), NULL);
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), NULL)
NULL
#
# Invalid parameters.
#
# Invalid geometry. Should raise error.
DO ST_TRANSFORM(x'00000000DEADBEEF', 4326);
ERROR 22023: Invalid GIS data provided to function st_transform.
# Non-existing SRID. Should raise error.
DO ST_TRANSFORM(
x'94007735010100000000000000000000000000000000000000',
4326
);
ERROR SR001: There's no spatial reference system with SRID 896991380.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000000000);
ERROR SR001: There's no spatial reference system with SRID 1000000000.
#
# Transformation to/from SRID 0.
#
# 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;
is_noop
1
# Transformation between SRID 0 and geographic SRS. Should raise error.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)'), 4326);
ERROR 22S00: Transformation from SRID 0 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 0);
ERROR 22S00: Transformation to SRID 0 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)'), 4047);
ERROR 22S00: Transformation from SRID 0 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4047), 0);
ERROR 22S00: Transformation to SRID 0 is not supported.
# Transformation between SRID 0 and projected SRS. Should raise error.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)'), 3857);
ERROR 22S00: Transformation from SRID 0 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 3857), 0);
ERROR 22S00: Transformation to SRID 0 is not supported.
#
# Transformation between geographic SRSs.
#
# 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
);
# Beginning of tests to verify that WGS 84 is recognized.
# 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]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# WGS 84 with no authority codes exept EPSG 4326 for the entire SRS.
# 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;
# 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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;
# WGS 84 with incorrect authority code for the spheroid. 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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;
# 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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;
# WGS 84 with incorrect authority code for the 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",0,AUTHORITY["EPSG","0"]],UNIT["degree",0.017453292519943278],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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;
# 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326);
ERROR 22S00: Transformation from SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326);
ERROR 22S00: Transformation to SRID 1000004326 is not supported. The spatial reference system has no TOWGS84 clause.
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# End of tests to verify that WGS 84 is recognized.
# 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;
# 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;
# 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]]';
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326)
) AS lon_should_be_plus_one;
lon_should_be_plus_one
POINT(0 1)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326)
) AS lon_should_be_minus_one;
lon_should_be_minus_one
POINT(0 -1)
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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]]';
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326)
) AS lon_should_be_minus_one;
lon_should_be_minus_one
POINT(0 -1)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326)
) AS lon_should_be_plus_one;
lon_should_be_plus_one
POINT(0 1)
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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;
lon_should_be_180
POINT(0 180)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326)
) AS lon_should_be_minus_180;
lon_should_be_minus_180
POINT(0 -180)
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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]]';
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 1000004326), 4326)
) AS lon_should_be_minus_180;
lon_should_be_minus_180
POINT(0 -180)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326)
) AS lon_should_be_180;
lon_should_be_180
POINT(0 180)
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# 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;
should_be_0_0
POINT(0 0)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1000004326)
) AS should_be_0_0;
should_be_0_0
POINT(0 0)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(100 100)', 1000004326), 4326)
) AS should_be_90_90;
should_be_90_90
POINT(90 90)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(90 90)', 4326), 1000004326)
) AS should_be_100_100;
should_be_100_100
POINT(100 100)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-100 -100)', 1000004326), 4326)
) AS should_be_minus_90_minus_90;
should_be_minus_90_minus_90
POINT(-90 -90)
SELECT
ST_ASTEXT(
ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-90 -90)', 4326), 1000004326)
) AS should_be_minus_100_minus_100;
should_be_minus_100_minus_100
POINT(-100 -100)
DROP SPATIAL REFERENCE SYSTEM 1000004326;
# Transformations between European Datum 1950 (ED50) and WGS 84.
# Positions in Western Europe will be further North and East in ED50 than
# in WGS 84.
# Trondheim.
SELECT
ST_ASTEXT(
ST_TRANSFORM(
ST_GEOMFROMTEXT('POINT(63.426912 10.396918)', 4326),
4230
)
) AS ED50;
ED50
POINT(63.42724 10.398502)
SELECT
ST_ASTEXT(
ST_TRANSFORM(
ST_GEOMFROMTEXT('POINT(63.42724 10.398502)', 4230),
4326
)
) AS WGS84;
WGS84
POINT(63.426912 10.396918)
# Telegrafenberg, Potsdam (origin of ED50).
SELECT
ST_ASTEXT(
ST_TRANSFORM(
ST_GEOMFROMTEXT('POINT(52.381389 13.064444)', 4326),
4230
)
) AS ED50;
ED50
POINT(52.382086 13.065521)
SELECT
ST_ASTEXT(
ST_TRANSFORM(
ST_GEOMFROMTEXT('POINT(52.382086 13.065521)', 4230),
4326
)
) AS WGS84;
WGS84
POINT(52.381389 13.064444)
#
# Transformation to/from projected SRSs.
#
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));
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;
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;
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(52 10)', 4326), 4087));
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;
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50.20901 16.84977)', 4326), 5514));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(43.750000014 -83.166666653)', 4326), 6201));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(4.7999999945 -74.250000023)', 4326), 6247));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(17.932166647 -76.943683174)', 4326), 24200));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(28.500000182 -95.99999989)', 4326), 32040));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50.679572292 5.80737015)', 4326), 31300));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-3.0000001398 119.99999986)', 4326), 3002));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(52.999999796 52.999999796)', 4326), 3388));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(10.000000275 -62.000000216)', 4326), 30200));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50.499999871 0.50000021429)', 4326), 27700));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(-25.732028354 28.282632944)', 4326), 2053));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(53.000000025 5.9999999931)', 4326), 28992));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(73.000000003 44.000000007)', 4326), 5041));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(-36.848461 174.763336)', 4326), 27200));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(12 117)', 4326), 3079));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(-16.189799986 44.45757)', 4326), 8441));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(5.3872536023 115.80550545)', 4326), 29873));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(37.521562493 34.136469742)', 4326), 22700));
DO ST_AsText(ST_Transform(ST_GeomFromText('POINT(-6 -45)', 4674), 5880));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50.20901 16.84977)', 4326), 5513));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(50 5)', 4326), 3035));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(42.749999987 -78.75)', 4326), 3174));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(56 12)', 4326), 32600));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-44 174)', 4326), 32700));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(38.736946 -9.142685)', 4326), 5017));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-75 120)', 4326), 3032));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-66.605227791 140.07140001)', 4326), 2985));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(13.33903845 144.63533131)', 4326), 3993));
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));
DO ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(-16.841456514 179.99433651)', 4326), 3139));
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));
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;
# Verify that it isn't possible to transform to/from the four not implemented projections: 1042, 1043, 9816, 9826
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 5515);
ERROR 22S00: Transformation to SRID 5515 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 5516);
ERROR 22S00: Transformation to SRID 5516 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 22300);
ERROR 22S00: Transformation to SRID 22300 is not supported. The spatial reference system has no TOWGS84 clause.
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"]]';
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 33001);
ERROR 22S00: Transformation to SRID 33001 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 2218);
ERROR 22S00: Transformation to SRID 2218 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 5515), 4326);
ERROR 22S00: Transformation from SRID 5515 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 5516), 4326);
ERROR 22S00: Transformation from SRID 5516 is not supported.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 22300), 4326);
ERROR 22S00: Transformation from SRID 22300 is not supported. The spatial reference system has no TOWGS84 clause.
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 33001), 4326);
ERROR 22S00: Transformation from SRID 33001 is not supported.
DROP SPATIAL REFERENCE SYSTEM 33001;
DO ST_TRANSFORM(ST_GEOMFROMTEXT('POINT(0 0)', 2218), 4326);
ERROR 22S00: Transformation from SRID 2218 is not supported.
|
