Hi all,
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)
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
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