postgis installer for windows has old proj

[zellner]

Comparing the results of coordinate transformation from NAV4 to WGS84 (and back) I receive different results when using a Postgresql database on Linux or Windows. The difference is in the used example about 176 meters. The Linux result seems to be ok compared to conversion in internet tools or in Oracle database. I used the following Postgresql and Postgis versions:

SELECT version();
-- Linux: PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
-- Windows: PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit

SELECT postgis_full_version();
-- Linux: POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Internal)"
-- Windows: POSTGIS="3.0.1 3.0.1" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)"

Block to be executed - as an example there are used the coordinates of a bus stop:

DO $$
DECLARE

srIdFrom INTEGER := 31468; -- NAV4
xFrom float := 4443865;
yFrom float := 5506116;
srIdTo INTEGER := 4326; -- WGS84
point geometry;

BEGIN

RAISE NOTICE '-----------------------------------';
point := ST_SetSRID(ST_Point(xFrom, yFrom),srIdFrom);
RAISE NOTICE 'NAV4 POINT(% %)', ST_X(point)::text, ST_Y(point)::text;
point := ST_Transform(point, srIdTo);
RAISE NOTICE 'WGS84 POINT(% %)', ST_X(point)::text, ST_Y(point)::text;
point := ST_Transform(ST_SetSRID(point,srIdTo), srIdFrom);
RAISE NOTICE 'NAV4 POINT(% %)', ST_X(point)::text, ST_Y(point)::text;

END;
$$

(You can also find the results in the attached Excel file I'll try to attach)

Results: Longitude Latitude Diff Long (Oracle) Diff Lat (Oracle)
Oracle: 11.2205674400711 49.6890969484351 0 0
Postgres (Linux): 11.220567440054587 49.68909694949477 -1.66001E-11 1.0596E-09
Postgres (Windows): 11.22192780146776 49.690197542586986 0.001360361 0,00110059

Differences (from Oracle): Longitude [°] Latitude [°] Diff Long [°] Diff Lat [°] Diff Long [m] Diff Lat [m] Diff Long/Lat [m] Lat. Factor 1° [m]
Oracle 11.22056744 49.68909695 0 0 0 0 0 0.838438009 111111.111
Postgres (Linux) 11.22056744 49.68909695 -1.66001E-11 1.0596E-09 -1.54646E-06 0.00011773 0.000117743
Postgres (Windows) 11.2219278 49.69019754 0.001360361 0.001100594 126.7309668 122.288239 176.1111903

The results within one database are consistent, so if I convert from NAV4 to WGS84 and back again, then the original value is achieve very exactly (only rounding errors) also on a Windows system.

Best regards Axel Zellner

[zellner]

Unfortunately I cannot attach any File. The error message:

---

Trac hat einen internen Fehler festgestellt:

IndexError: pop from empty list

---

Axel

[Algunenano]

Coordinate transformation is handled via PROJ. Can you try updating your PROJ installation in Windows to match the one you are using under Linux (6.3.1 vs 5.2.0) to see if the issue persist?

[robe]

I'm guessing this will be fixed when I finally upgrade the proj shipped with Windows to Proj 7.0.1. I still have some conflicts to work out before I can do that unfortunately.

Since this is not a PostGIS issue, I'm flipping to packaging.

[gdt]

Changing description so that one gets the right impression from scanning tickets. It seemed obvious from the previous description that this was a proj issue, but not obvious why it belonged in the postgis tracker at all.

[zellner]

I am still watching this ticket even if I did not react directly. Currently I have no Windows PostgreSQL databases running productively but only Linux. So I can live with the prediction that the problem will be solved as soon as a new proj is packed into a Windows postgis package.

But I am still looking forward to it.

Axel

[robe]

Okay I switched winnie to start building 3.0 and 3.1 branches against Proj 6.3.2 and GDAL 3.0.2. I haven't tested your example above.

I've still got some kinks to work out before I can put Proj 7.1.0 in place, but hoping to ship 3.1.0 with Proj 7.1.0 or higher.

Please give - the binaries a try - ​https://winnie.postgis.net/download/windows (for 3.0.3dev and higher). If all is good I'll ship 3.0.3 with the new proj.

[zellner]

Step by step I tried 3 zip files to update my PostgreSQL directory using all subfolders and even the version.txt file downloaded from: ​https://winnie.postgis.net/download/windows/pg12/buildbot/ These are the files:

• postgis-pg12-binaries-3.1.0devw64gcc81.zip
• pgrouting-pg12-binaries-developw64gcc81.zip
• pg12pgh3_w64.zip

I always stopped the PostgreSQL service and started it again. At last I restarted the computer. There are still shown the old versions: SELECT version() UNION ALL SELECT PostGIS_full_version();

PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Internal)"

And the results of ST_Transform(...) are still the wrong ones comparing "my" PostgreSQL database running on Windows with a Linunx database: -- (Windows wrong) --------------------------------- NAV4 POINT(4443865 5506116) WGS84 POINT(11.22192780146776 49.690197542586986) NAV4 POINT(4443864.999999998 5506115.999999998) -- (Linux right) --------------------------------- NAV4 POINT(4443865 5506116) WGS84 POINT(11.220567440054587 49.68909694949477) NAV4 POINT(4443865.000344981 5506116.000483332)

The Linux versions are: PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Internal)"

So the problem is not solved. Did I do something wrong when substituting the sources as described above? Can you test it? You find the test block in the very first comment in the ticket.

Axel

[robe]

Note I have PostgreSQL 13 3.0.2 built with Proj 6.3.2. I plan to do the same for older versions as well.

[robe]

Okay I just pushed to stackbuilder PostGIS 3.0.4 compiled with Proj 7.1.1. I'm planning to ship Proj 7.2 or above when PostGIS 3.1.0 is released, but need to recompile GDAL first as I seem to get some weird proj.db errors if I don't with non-existent column yada yada.

Can also download.osgeo.org as described in ​https://postgis.net/windows_downloads/

I retested your example with:

PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit POSTGIS="3.0.3 3.0.3" [EXTENSION] PGSQL="130" GEOS="3.8.1-CAPI-1.13.3" SFCGAL="1.3.8" PROJ="7.1.1" GDAL="GDAL 3.2.0, released 2020/10/26" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" TOPOLOGY RASTER

and results now agree with Linux answers:

NOTICE:  -----------------------------------
NOTICE:  NAV4 POINT(4443865 5506116)
NOTICE:  WGS84 POINT(11.220567440054587 49.689096949494754)
NOTICE:  NAV4 POINT(4443865.000344981 5506116.000483331)
DO

[zellner]

Another test I provided on Windows with: PostgreSQL 12.5, compiled by Visual C++ build 1914, 64-bit POSTGIS="3.0.3 3.0.3" [EXTENSION] PGSQL="120" 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)" (core procs from "3.0.1 3.0.1" need upgrade)

The results are:

---

NAV4 POINT(4443865 5506116) WGS84 POINT(11.220567701891326 49.68910121081871) NAV4 POINT(4443865 5506116)

---

Thus these results are MUCH better but there is still a difference to Oracle and PostGIS Linux calculation of 0.47 meters.

For our application this is quite ok. Still often enough it will result in a difference of a meter (calculating with this accuracy). But if you deal with real geodetic data this will not be enough. Keep in mind that Oracle and Linux PostgreSQL calculation only differs by 0.000118 meters!

Regards Axel

[zellner]

In the meantime I have installed 3 versions of PostgreSQL on my Windows PC: Version 12.5, 13.1, 14.1. I repeated my tests on these 3 versions with the result that V13 and V14 differed from Oracle transformation only by 0.1 mm but in V12 it differed by about 0,47 m.

Then I found out that another difference in the PostGIS versions (SELECT PostGIS_full_version();) was (besides some version numbers), that only in V12 the output compared to the others was extended by "(core procs from "3.0.1 3.0.1" need upgrade)"

So I think that this might have been the reason for the remaining half a meter difference in the transformation.

So from my point of view the ticket can be closed, now.

Regards Axel