pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bug: Postgresql with Postgis: Different result in coordinate conversion NAV4 <=> WGS84 in Linux and Windows
2+ messages / 2 participants
[nested] [flat]

* Bug: Postgresql with Postgis: Different result in coordinate conversion NAV4 <=> WGS84 in Linux and Windows
@ 2020-06-25 13:43  Axel Zellner <zellner@mentz.net>
  0 siblings, 1 reply; 2+ messages in thread

From: Axel Zellner @ 2020-06-25 13:43 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

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

Attachments:

  TTP 66223 Coordinate Transformation Error.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, 10.7K) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Bug: Postgresql with Postgis: Different result in coordinate conversion NAV4 <=> WGS84 in Linux and Windows
@ 2020-06-25 15:52  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Axel Zellner <zellner@mentz.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2020-06-25 15:52 UTC (permalink / raw)
  To: Axel Zellner <zellner@mentz.net>; +Cc: pgsql-bugs@lists.postgresql.org

Axel Zellner <zellner@mentz.net> writes:
> 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.

You'd probably have better luck asking about that on a Postgis-specific
list; whatever the difference is, it's likely not in core Postgres.

Personally I'm wondering about the significantly different PROJ
version numbers you show, but that might be a red herring.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-06-25 15:52 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-25 13:43 Bug: Postgresql with Postgis: Different result in coordinate conversion NAV4 <=> WGS84 in Linux and Windows Axel Zellner <zellner@mentz.net>
2020-06-25 15:52 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox