ST_Transform decimal issue on Win32

[colivier]

On Win32,

SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=28992;LINESTRING(121197.452 483388.474 0 ,121194.232643364 483391.530431554 0)));

Should produce (Unix platform result): LINESTRING(SRID=4326;4.89123288936406 52.3373804303248 0, 4.89118534786195 52.3374077015756 0)

Instead of (Win32 result): LINESTRING(SRID=4326;4.891594203993788 52.338389215425934 0, 4.891546655317309 52.338416489781068 0)

Decimal digits should be the same upon platforms at least till 10E-6.

Initial problem reported by Laurens Janzen, on postgis-users (seems to be present on both trunk and 1.4)

Nota: regression tests on ST_Transform only care about integer

[colivier]

SELECT postgis_full_version() "POSTGIS="1.5.0SVN" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August 2008" LIBXML="2.7.6" USE_STATS"

[pramsey]

Are you pointing up a problem in transform or in output formatting? Does the problem manifest w/o transform? There is a datum shift from bessel to wgs84 which is probably the issue. Does the windows installation have a PROJDIR environment variable set, and a complete set of datum shift grids?

[pramsey]

And vice versa: does the unix environment have a datumshift enabled proj installation and grids in /usr/local/share/proj ? My bet is you have mismatched installations.

[mcayland]

Also see the SetPROJ4LibPath() function in lwgeom_transform.c; the Windows installation adds a PROJ search path entry which should get used first. Maybe this clashes with other PROJ.4 installations in C:\PROJ?

Incidentally, I also see a bug here: the hard-coded path to the search path entry should probably be changed as per the new directory-versioning install.

[colivier]

Paul, Mark

I didn't have try to reproduce it (no win32 available),

Problem seems related as describted by Laurens to ST_Transform function, and not in output formatting.

The windows installation is the one from Regina (Experimental Build) dunno the PROJDIR status about it.

[robe]

I think its a problem with our packaging because I can reproduce it on our end. Something must have changed with where SetProj4LibPath() is lookin in 1.4 install for windows. Mark can you check that?

We have two databases on the same PostgreSQL service one is running 1.3.6 and one is running 1.4.1. the 1.3.6 is producing the stated Linux results, but 1.4.1 is not.

SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=28992;LINESTRING(121197.452 483388.474 0 ,121194.232643364 483391.530431554 0)'),4326)); 


"SRID=4326;LINESTRING(4.8912328883848 52.3373804295345 43.0455399095536,4.89118534688269 52.3374077007853 43.0454913372059)" -- 1.3.6


"SRID=4326;LINESTRING(4.89159420399379 52.3383892154259 0,4.89154665531731 52.338416489781 0)"  -- 1.4.1

I had assumed both would be looking in the same location for proj. We never really fussed with that.

Paul - I disagree with changing the proj location to be versioned. The reason is that on the same postgresql install, both will be using the same proj.dll anyway so they should be using the same datum shift files as well.

[robe]

Actually this may be more than a windows issue or I'm just being stupid. I get the same answers on my CentOS dev box when comparing my 1.3.6 database with 1.4 install. Both in theory are sharing the same proj and same datum shift since I didn't recompile proj when compiling PostGIS 1.4 for this particular install

SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=28992;LINESTRING(121197.452 483388.474 0 ,121194.232643364 483391.530431554 0)'),4326)); 

-- PostgreSQL 8.4.1 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit POSTGIS="1.4.1rc1" GEOS="3.2.0rc2-CAPI-1.6.0" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS"
SRID=4326;LINESTRING(4.89159420399379 52.3383892154259 0,4.89154665531731 52.3384164897811 0)

-- PostgreSQL 8.4.1 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit POSTGIS="1.3.6" GEOS="3.2.0rc2-CAPI-1.6.0" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS
SRID=4326;LINESTRING(4.8912328883848 52.3373804295345 43.0455399099737,4.89118534688269 52.3374077007853 43.0454913368449)

So i really don't know which one is right at this point. If its a mistake in teh dataum shift, then I must be doing the same thing wrong on my linux installs

[robe]

I think the difference is more brain dead obvious. So which spatial_ref_sys entry is correct for this: On my installs they are different and I suspect that is the difference

select proj4text from spatial_ref_sys where srid = 28992;

"+proj=sterea +lat_0=52.15616055555555 +lon_0=5.38763888888889 +k=0.9999079 +x_0=155000 +y_0=463000 +ellps=bessel +units=m +no_defs "  --1.4/1.5


"+proj=sterea +lat_0=52.15616055555555 +lon_0=5.38763888888889 +k=0.999908 +x_0=155000 +y_0=463000 +ellps=bessel +towgs84=565.237,50.0087,465.658,-0.406857,0.350733,-1.87035,4.0812 +units=m +no_defs " -- 1.3.6

[pramsey]

You're absolutely right. Using the old proj4text produces the correct answer on the new PostGIS 1.5.

[pramsey]

http://trac.osgeo.org/postgis/changeset/2295

[pramsey]

Add towgs84 line into proj4text for srid = 28992, r5137 trunk, r5138 1.4 branch