8

I recently installed PostGIS on my Ubuntu machine and ran into an issue transforming many SRID (but not all). For example the first query below fails (ERROR: could not form projection (PJ) from 'srid=4326' to 'srid=2279') but the second one succeeds on my machine.

select st_transform('SRID=4326;POINT(-97.50 25.91)', 2279);

select st_transform('SRID=4326;POINT(-97.50 25.91)', 3395);

When I look at the PostGIS extension I see the following, which seems to be related but I cannot figure out why the conditions are there and how to adjust.

{"WHERE NOT (
srid BETWEEN 2000 AND 2217
OR srid BETWEEN 2219 AND 2220
OR srid BETWEEN 2222 AND 2292
OR srid BETWEEN 2294 AND 2295
OR srid BETWEEN 2308 AND 2962
OR srid BETWEEN 2964 AND 2973
OR srid BETWEEN 2975 AND 2984
OR srid BETWEEN 2987 AND 3051
OR srid BETWEEN 3054 AND 3138
OR srid BETWEEN 3140 AND 3143
OR srid BETWEEN 3146 AND 3172
OR srid BETWEEN 3174 AND 3791
OR srid BETWEEN 3793 AND 3802
OR srid = 3812
OR srid BETWEEN 3814 AND 3816
OR srid = 3819
OR srid BETWEEN 3821 AND 3829
OR srid BETWEEN 3832 AND 3852
OR srid = 3854
OR srid = 3857
OR srid BETWEEN 3873 AND 3885
OR srid BETWEEN 3887 AND 3893
OR srid BETWEEN 3901 AND 3903
OR srid BETWEEN 3906 AND 3912
OR srid = 3920
OR srid BETWEEN 3942 AND 3950
OR srid BETWEEN 3968 AND 3970
OR srid BETWEEN 3973 AND 3976
OR srid BETWEEN 3978 AND 3979
OR srid BETWEEN 3985 AND 3989
OR srid BETWEEN 3991 AND 3997
OR srid BETWEEN 4000 AND 4063
OR srid = 4071
OR srid BETWEEN 4073 AND 4075
OR srid BETWEEN 4079 AND 4083
OR srid BETWEEN 4087 AND 4088
OR srid BETWEEN 4093 AND 4100
OR srid BETWEEN 4120 AND 4176
OR srid BETWEEN 4178 AND 4185
OR srid BETWEEN 4188 AND 4289
OR srid BETWEEN 4291 AND 4319
OR srid = 4322
OR srid = 4324
OR srid BETWEEN 4326 AND 4415
OR srid BETWEEN 4417 AND 4434
OR srid BETWEEN 4437 AND 4439
OR srid BETWEEN 4455 AND 4457
OR srid BETWEEN 4462 AND 4463
OR srid BETWEEN 4465 AND 4475
OR srid BETWEEN 4479 AND 4559
OR srid BETWEEN 4568 AND 4589
OR srid BETWEEN 4600 AND 4647
OR srid BETWEEN 4652 AND 4824
OR srid = 4826
OR srid = 4839
OR srid BETWEEN 4855 AND 4880
OR srid BETWEEN 4882 AND 4904
OR srid BETWEEN 4906 AND 4999
OR srid BETWEEN 5011 AND 5016
OR srid = 5018
OR srid BETWEEN 5041 AND 5042
OR srid = 5048
OR srid BETWEEN 5069 AND 5072
OR srid BETWEEN 5105 AND 5130
OR srid = 5132
OR srid BETWEEN 5167 AND 5188
OR srid = 5221
OR srid = 5223
OR srid BETWEEN 5228 AND 5229
OR srid BETWEEN 5233 AND 5235
OR srid BETWEEN 5243 AND 5247
OR srid BETWEEN 5250 AND 5259
OR srid BETWEEN 5262 AND 5264
OR srid = 5266
OR srid BETWEEN 5269 AND 5275
OR srid BETWEEN 5292 AND 5311
OR srid = 5316
OR srid = 5318
OR srid BETWEEN 5320 AND 5325
OR srid BETWEEN 5329 AND 5332
OR srid = 5337
OR srid BETWEEN 5340 AND 5349
OR srid BETWEEN 5352 AND 5365
OR srid BETWEEN 5367 AND 5373
OR srid BETWEEN 5379 AND 5383
OR srid BETWEEN 5387 AND 5389
OR srid BETWEEN 5391 AND 5393
OR srid = 5396
OR srid = 5451
OR srid BETWEEN 5456 AND 5464
OR srid BETWEEN 5466 AND 5467
OR srid = 5469
OR srid = 5472
OR srid BETWEEN 5479 AND 5482
OR srid BETWEEN 5487 AND 5490
OR srid BETWEEN 5498 AND 5500
OR srid BETWEEN 5513 AND 5514
OR srid BETWEEN 5518 AND 5520
OR srid BETWEEN 5523 AND 5524
OR srid = 5527
OR srid BETWEEN 5530 AND 5539
OR srid BETWEEN 5544 AND 5546
OR srid BETWEEN 5550 AND 5552
OR srid BETWEEN 5554 AND 5556
OR srid BETWEEN 5558 AND 5583
OR srid BETWEEN 5588 AND 5589
OR srid BETWEEN 5591 AND 5593
OR srid = 5596
OR srid = 5598
OR srid BETWEEN 5623 AND 5625
OR srid BETWEEN 5627 AND 5629
OR srid BETWEEN 5631 AND 5639
OR srid = 5641
OR srid BETWEEN 5643 AND 5644
OR srid = 5646
OR srid BETWEEN 5649 AND 5655
OR srid = 5659
OR srid BETWEEN 5663 AND 5685
OR srid BETWEEN 5698 AND 5700
OR srid BETWEEN 5707 AND 5708
OR srid = 5825
OR srid = 5828
OR srid = 5830
OR srid BETWEEN 5832 AND 5837
OR srid = 5839
OR srid = 5842
OR srid BETWEEN 5844 AND 5858
OR srid BETWEEN 5875 AND 5877
OR srid BETWEEN 5879 AND 5880
OR srid BETWEEN 5884 AND 5887
OR srid = 5890
OR srid BETWEEN 5896 AND 5899
OR srid BETWEEN 5921 AND 5940
OR srid = 5942
OR srid BETWEEN 5945 AND 5976
OR srid BETWEEN 6050 AND 6125
OR srid BETWEEN 6128 AND 6129
OR srid BETWEEN 6133 AND 6135
OR srid = 6141
OR srid BETWEEN 6144 AND 6176
OR srid = 6190
OR srid BETWEEN 6200 AND 6202
OR srid = 6204
OR srid = 6207
OR srid BETWEEN 6210 AND 6211
OR srid = 6307
OR srid BETWEEN 6309 AND 6312
OR srid BETWEEN 6316 AND 6325
OR srid BETWEEN 6328 AND 6356
OR srid BETWEEN 6362 AND 6372
OR srid BETWEEN 6381 AND 6387
OR srid = 6391
OR srid BETWEEN 6393 AND 6637
OR srid = 6646
OR srid BETWEEN 6649 AND 6692
OR srid BETWEEN 6696 AND 6697
OR srid = 6700
OR srid BETWEEN 6703 AND 6709
OR srid BETWEEN 6720 AND 6723
OR srid BETWEEN 6732 AND 6738
OR srid BETWEEN 6781 AND 6863
OR srid BETWEEN 6867 AND 6868
OR srid BETWEEN 6870 AND 6871
OR srid BETWEEN 6875 AND 6876
OR srid BETWEEN 6879 AND 6887
OR srid BETWEEN 6892 AND 6894
OR srid = 6915
OR srid = 6917
OR srid BETWEEN 6922 AND 6925
OR srid = 6927
OR srid BETWEEN 6931 AND 6934
OR srid BETWEEN 6956 AND 6959
OR srid = 6962
OR srid = 6966
OR srid BETWEEN 6978 AND 6991
OR srid BETWEEN 6996 AND 6997
OR srid BETWEEN 7005 AND 7007
OR srid BETWEEN 7034 AND 7042
OR srid BETWEEN 7057 AND 7081
OR srid BETWEEN 7084 AND 7088
OR srid BETWEEN 7109 AND 7128
OR srid BETWEEN 7131 AND 7139
OR srid = 7142
OR srid BETWEEN 7257 AND 7376
OR srid BETWEEN 7400 AND 7423
OR srid BETWEEN 7528 AND 7645
OR srid BETWEEN 7656 AND 7665
OR srid BETWEEN 7677 AND 7686
OR srid BETWEEN 7692 AND 7696
OR srid BETWEEN 7755 AND 7787
OR srid = 7789
OR srid BETWEEN 7791 AND 7801
OR srid BETWEEN 7803 AND 7805
OR srid BETWEEN 7815 AND 7816
OR srid BETWEEN 7825 AND 7831
OR srid BETWEEN 7842 AND 7859
OR srid BETWEEN 7877 AND 7887
OR srid BETWEEN 7899 AND 7912
OR srid BETWEEN 7914 AND 7931
OR srid BETWEEN 7954 AND 7956
OR srid BETWEEN 7991 AND 7992
OR srid BETWEEN 8013 AND 8032
OR srid BETWEEN 8035 AND 8036
OR srid BETWEEN 8042 AND 8045
OR srid BETWEEN 8058 AND 8059
OR srid BETWEEN 8065 AND 8068
OR srid BETWEEN 8082 AND 8086
OR srid = 8088
OR srid BETWEEN 8090 AND 8093
OR srid BETWEEN 8095 AND 8173
OR srid = 8177
OR srid BETWEEN 8179 AND 8182
OR srid BETWEEN 8184 AND 8185
OR srid = 8187
OR srid = 8189
OR srid = 8191
OR srid = 8193
OR srid BETWEEN 8196 AND 8198
OR srid BETWEEN 8200 AND 8210
OR srid BETWEEN 8212 AND 8214
OR srid = 8216
OR srid = 8218
OR srid = 8220
OR srid = 8222
OR srid BETWEEN 8224 AND 8227
OR srid BETWEEN 8230 AND 8233
OR srid = 8235
OR srid BETWEEN 8237 AND 8240
OR srid = 8242
OR srid = 8244
OR srid BETWEEN 8246 AND 8255
OR srid BETWEEN 8311 AND 8353
OR srid = 8360
OR srid = 8370
OR srid BETWEEN 8379 AND 8385
OR srid = 8387
OR srid = 8391
OR srid = 8395
OR srid = 8397
OR srid = 8399
OR srid = 8401
OR srid = 8403
OR srid BETWEEN 8425 AND 8431
OR srid = 8433
OR srid = 8441
OR srid = 8449
OR srid BETWEEN 8455 AND 8456
OR srid BETWEEN 8518 AND 8529
OR srid = 8531
OR srid BETWEEN 8533 AND 8536
OR srid BETWEEN 8538 AND 8545
OR srid BETWEEN 8677 AND 8679
OR srid BETWEEN 8682 AND 8687
OR srid BETWEEN 8692 AND 8693
OR srid BETWEEN 8700 AND 8818
OR srid = 8826
OR srid BETWEEN 8836 AND 8840
OR srid BETWEEN 8857 AND 8860
OR srid BETWEEN 8898 AND 8903
OR srid BETWEEN 8905 AND 8910
OR srid = 8912
OR srid BETWEEN 8988 AND 9019
OR srid BETWEEN 20002 AND 20032
OR srid BETWEEN 20062 AND 20092
OR srid BETWEEN 20135 AND 20138
OR srid BETWEEN 20248 AND 20258
OR srid BETWEEN 20348 AND 20358
OR srid BETWEEN 20436 AND 20440
OR srid = 20499
OR srid BETWEEN 20538 AND 20539
OR srid BETWEEN 20790 AND 20791
OR srid BETWEEN 20822 AND 20824
OR srid BETWEEN 20934 AND 20936
OR srid BETWEEN 21035 AND 21037
OR srid BETWEEN 21095 AND 21097
OR srid = 21100
OR srid BETWEEN 21148 AND 21150
OR srid BETWEEN 21291 AND 21292
OR srid BETWEEN 21413 AND 21423
OR srid BETWEEN 21453 AND 21463
OR srid BETWEEN 21473 AND 21483
OR srid = 21500
OR srid BETWEEN 21780 AND 21782
OR srid BETWEEN 21817 AND 21818
OR srid BETWEEN 21891 AND 21894
OR srid BETWEEN 21896 AND 21899
OR srid BETWEEN 22032 AND 22033
OR srid BETWEEN 22091 AND 22092
OR srid BETWEEN 22171 AND 22177
OR srid BETWEEN 22181 AND 22187
OR srid BETWEEN 22191 AND 22197
OR srid BETWEEN 22234 AND 22236
OR srid = 22275
OR srid = 22277
OR srid = 22279
OR srid = 22281
OR srid = 22283
OR srid = 22285
OR srid = 22287
OR srid = 22289
OR srid = 22291
OR srid = 22293
OR srid = 22300
OR srid = 22332
OR srid BETWEEN 22391 AND 22392
OR srid BETWEEN 22521 AND 22525
OR srid = 22700
OR srid = 22770
OR srid = 22780
OR srid = 22832
OR srid BETWEEN 22991 AND 22994
OR srid BETWEEN 23028 AND 23038
OR srid = 23090
OR srid = 23095
OR srid BETWEEN 23239 AND 23240
OR srid = 23433
OR srid = 23700
OR srid BETWEEN 23830 AND 23853
OR srid BETWEEN 23866 AND 23872
OR srid BETWEEN 23877 AND 23884
OR srid BETWEEN 23886 AND 23894
OR srid BETWEEN 23946 AND 23948
OR srid BETWEEN 24047 AND 24048
OR srid = 24100
OR srid = 24200
OR srid BETWEEN 24305 AND 24306
OR srid BETWEEN 24311 AND 24313
OR srid BETWEEN 24342 AND 24347
OR srid BETWEEN 24370 AND 24383
OR srid = 24500
OR srid BETWEEN 24547 AND 24548
OR srid = 24571
OR srid = 24600
OR srid BETWEEN 24718 AND 24721
OR srid BETWEEN 24817 AND 24821
OR srid BETWEEN 24877 AND 24882
OR srid BETWEEN 24891 AND 24893
OR srid = 25000
OR srid = 25231
OR srid BETWEEN 25391 AND 25395
OR srid = 25700
OR srid BETWEEN 25828 AND 25838
OR srid = 25884
OR srid = 25932
OR srid BETWEEN 26191 AND 26195
OR srid = 26237
OR srid BETWEEN 26331 AND 26332
OR srid BETWEEN 26391 AND 26393
OR srid = 26432
OR srid BETWEEN 26591 AND 26592
OR srid = 26632
OR srid = 26692
OR srid BETWEEN 26701 AND 26722
OR srid BETWEEN 26729 AND 26799
OR srid BETWEEN 26801 AND 26803
OR srid BETWEEN 26811 AND 26815
OR srid BETWEEN 26819 AND 26826
OR srid BETWEEN 26830 AND 26837
OR srid BETWEEN 26841 AND 26870
OR srid BETWEEN 26891 AND 26899
OR srid BETWEEN 26901 AND 26923
OR srid BETWEEN 26929 AND 26946
OR srid BETWEEN 26948 AND 26998
OR srid BETWEEN 27037 AND 27040
OR srid = 27120
OR srid = 27200
OR srid BETWEEN 27205 AND 27232
OR srid BETWEEN 27258 AND 27260
OR srid BETWEEN 27291 AND 27292
OR srid BETWEEN 27391 AND 27398
OR srid = 27429
OR srid BETWEEN 27492 AND 27493
OR srid = 27500
OR srid BETWEEN 27561 AND 27564
OR srid BETWEEN 27571 AND 27574
OR srid BETWEEN 27581 AND 27584
OR srid BETWEEN 27591 AND 27594
OR srid = 27700
OR srid BETWEEN 28191 AND 28193
OR srid = 28232
OR srid BETWEEN 28348 AND 28358
OR srid BETWEEN 28402 AND 28432
OR srid BETWEEN 28462 AND 28492
OR srid = 28600
OR srid BETWEEN 28991 AND 28992
OR srid BETWEEN 29100 AND 29101
OR srid BETWEEN 29118 AND 29122
OR srid BETWEEN 29168 AND 29172
OR srid BETWEEN 29177 AND 29185
OR srid BETWEEN 29187 AND 29195
OR srid BETWEEN 29220 AND 29221
OR srid = 29333
OR srid = 29371
OR srid = 29373
OR srid = 29375
OR srid = 29377
OR srid = 29379
OR srid = 29381
OR srid = 29383
OR srid = 29385
OR srid BETWEEN 29635 AND 29636
OR srid BETWEEN 29700 AND 29702
OR srid BETWEEN 29738 AND 29739
OR srid BETWEEN 29849 AND 29850
OR srid BETWEEN 29871 AND 29873
OR srid BETWEEN 29900 AND 29903
OR srid BETWEEN 30161 AND 30179
OR srid = 30200
OR srid BETWEEN 30339 AND 30340
OR srid BETWEEN 30491 AND 30494
OR srid BETWEEN 30591 AND 30592
OR srid BETWEEN 30729 AND 30732
OR srid BETWEEN 30791 AND 30792
OR srid = 30800
OR srid = 31028
OR srid = 31121
OR srid = 31154
OR srid BETWEEN 31170 AND 31171
OR srid BETWEEN 31251 AND 31259
OR srid BETWEEN 31265 AND 31268
OR srid BETWEEN 31275 AND 31279
OR srid BETWEEN 31281 AND 31297
OR srid = 31300
OR srid = 31370
OR srid BETWEEN 31461 AND 31469
OR srid BETWEEN 31491 AND 31495
OR srid BETWEEN 31528 AND 31529
OR srid = 31600
OR srid = 31700
OR srid BETWEEN 31838 AND 31839
OR srid BETWEEN 31900 AND 31901
OR srid BETWEEN 31917 AND 31922
OR srid BETWEEN 31965 AND 32003
OR srid BETWEEN 32005 AND 32031
OR srid BETWEEN 32033 AND 32062
OR srid BETWEEN 32064 AND 32067
OR srid BETWEEN 32074 AND 32077
OR srid BETWEEN 32081 AND 32086
OR srid BETWEEN 32098 AND 32100
OR srid = 32104
OR srid BETWEEN 32107 AND 32130
OR srid BETWEEN 32133 AND 32158
OR srid = 32161
OR srid BETWEEN 32164 AND 32167
OR srid BETWEEN 32180 AND 32199
OR srid BETWEEN 32201 AND 32260
OR srid BETWEEN 32301 AND 32360
OR srid BETWEEN 32401 AND 32460
OR srid BETWEEN 32501 AND 32560
OR srid BETWEEN 32601 AND 32667
OR srid BETWEEN 32701 AND 32761
OR srid = 32766
OR srid BETWEEN 37001 AND 37008
OR srid BETWEEN 37201 AND 37208
OR srid BETWEEN 37211 AND 37235
OR srid BETWEEN 37237 AND 37243
OR srid BETWEEN 37245 AND 37247
OR srid BETWEEN 37249 AND 37255
OR srid = 37257
OR srid BETWEEN 37259 AND 37260
OR srid BETWEEN 53001 AND 53004
OR srid BETWEEN 53008 AND 53016
OR srid BETWEEN 53018 AND 53019
OR srid BETWEEN 53021 AND 53032
OR srid BETWEEN 53034 AND 53037
OR srid BETWEEN 53042 AND 53043
OR srid = 53046
OR srid BETWEEN 53074 AND 53076
OR srid BETWEEN 54001 AND 54004
OR srid BETWEEN 54008 AND 54016
OR srid BETWEEN 54018 AND 54019
OR srid BETWEEN 54021 AND 54032
OR srid BETWEEN 54034 AND 54037
OR srid BETWEEN 54042 AND 54043
OR srid = 54046
OR srid BETWEEN 54052 AND 54053
OR srid BETWEEN 54074 AND 54076
OR srid BETWEEN 65061 AND 65062
OR srid = 65161
OR srid = 65163
OR srid BETWEEN 102001 AND 102298
OR srid = 102300
OR srid BETWEEN 102304 AND 102459
OR srid BETWEEN 102461 AND 102569
OR srid BETWEEN 102581 AND 102730
OR srid BETWEEN 102733 AND 102758
OR srid BETWEEN 102761 AND 102766
OR srid BETWEEN 102799 AND 102900
OR srid BETWEEN 102962 AND 102963
OR srid BETWEEN 102965 AND 103585
OR srid BETWEEN 103600 AND 103695
OR srid BETWEEN 103700 AND 103871
OR srid BETWEEN 103900 AND 103971
OR srid = 104000
OR srid BETWEEN 104009 AND 104026
OR srid = 104047
OR srid = 104050
OR srid BETWEEN 104100 AND 104145
OR srid BETWEEN 104179 AND 104186
OR srid = 104199
OR srid = 104223
OR srid = 104248
OR srid BETWEEN 104256 AND 104261
OR srid BETWEEN 104286 AND 104287
OR srid BETWEEN 104304 AND 104305
OR srid BETWEEN 104700 AND 104786
OR srid BETWEEN 104800 AND 104871
OR srid = 104896
OR srid BETWEEN 104900 AND 104975
OR srid BETWEEN 104990 AND 104992
OR srid = 900913
)"}
5
  • What is the output of select postgis_proj_version();?
    – CL.
    Commented May 7, 2021 at 14:21
  • Version 6.3.1 is what it shows Commented May 7, 2021 at 20:04
  • 1
    I'm running into this too, and think it might actually be a GDAL error. @TrevorNederlof I can reproduce with Postgis 3 and GDAL 3.2.1 (released 2020/12/29) but when I switch to GDAL 3.0.4 your MWE succeeds. Not sure where the issue is, but hope that helps! Commented May 10, 2021 at 18:44
  • Ooo thank you Charlie that helps a lot, when I am back at my comp I will try to go with GDAL 3.0.04 and see if it works. Commented May 10, 2021 at 19:02
  • any luck fixing this?
    – Theo F
    Commented Feb 4, 2022 at 14:08

1 Answer 1

2

I solved this by updating my PostGIS. I had 3.0.3 when it appeared and SELECT postgis_full_version() returned

POSTGIS="3.0.3 3.0.3" [EXTENSION] PGSQL="130" GEOS="3.8.1-CAPI-1.13.3" PROJ="7.1.1" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)"

So, I reinstalled PostGIS per the official documentation but it didn't solve it. It returned this.

"POSTGIS=""3.0.3 3.0.3"" [EXTENSION] PGSQL=""130"" GEOS=""3.8.1-CAPI-1.13.3"" PROJ=""7.1.1"" GDAL=""GDAL 3.2.0, released 2020/10/26 GDAL_DATA not found"" LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.4.3 (Internal)"" RASTER" 

Notice the GDAL_DATA not found. I figured I can't transform the geometries due to GDAL not being found by PostGIS.

I installed 3.2 via StackBuilder, uninstalled 3.0.3, upgraded PostGIS in all my database via SELECT postgis_extensions_upgrade() and it solved the problem.

"POSTGIS=""3.2.0 3.2.0"" [EXTENSION] PGSQL=""130"" GEOS=""3.10.1-CAPI-1.16.0"" PROJ=""7.2.1"" GDAL=""GDAL 3.3.3, released 2021/10/25"" LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"" RASTER"

enter image description here

Edit. The update removed shp2pgsql, raster2pgsql, etc. I just downloaded PostGIS here http://download.osgeo.org/postgis/windows/pg13/, ove out my bin folder (some files can't be moved so leave them), and then paste the bin folder from the download. No problems so far.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.