Different output from Transform function when used in a function vs. directly - redirected here from postgreSQL bug reporting

[md]

The follwoing sql creates a table to store coordinates and location names, then takes input from arrays and populates the table with transformed coordinates: begin;

create temporary table z_coo (

x_t double precision, y_t double precision, location text);

CREATE OR REPLACE FUNCTION insert_coords(x_ar double precision[],y_ar double precision[], loc_ar text[],srid_from integer,srid_to integer) RETURNS void AS $$ BEGIN FOR i IN 1..array_upper(x_ar,1) LOOP

INSERT INTO z_coo VALUES(X(Transform(GeomFromEWKT('SRID='

srid_from

';POINT('

x_ar[i]

' '

y_ar[i]

')'),srid_to)),

Y(Transform(GeomFromEWKT('SRID='

srid_from

';POINT('

x_ar[i]

' '

y_ar[i]

')'),srid_to)),

loc_ar[i]); END LOOP; END; $$ LANGUAGE 'plpgsql' VOLATILE STRICT;

select insert_coords(ARRAY[2093904.76854,2169323.2479,2246585.31659,2280791.02076,2 282685.64095], ARRAY[357063.46674,395585.38205,317382.48154,486791.34705,400196.04792], ARRAY['aaa',bbb',ccc',ddd',eee'], 2285,26911); commit;

select * from z_coo; *

The transformed coordinates obtained that way for location 'aaa' are: x = 346653.25322388 y = 5205506.92495088

Hoewever, if the following sql is run separately for location 'aaa': * select

X(Transform(GeomFromEWKT('SRID=2285;POINT(2093904.76854

357063.46674)'),26911)),

Y(Transform(GeomFromEWKT('SRID=2285;POINT(2093904.76854

357063.46674)'),26911)); then the transformed coordinates for location 'aaa' are: x = 352043.233452746 y = 5314191.37291459

This is a disturbingly huge differnece.

[kneufeld]

I can't duplicate this problem. Running the above code sample, I get the same results:

SELECT * FROM z_coo WHERE location = 'aaa';
       x_t        |       y_t        | location 
------------------+------------------+----------
 352043.233452746 | 5314191.37291459 | aaa
(1 row)

SELECT 
  X(Transform(GeomFromEWKT('SRID=2285;POINT(2093904.76854 357063.46674)'),26911)),
  Y(Transform(GeomFromEWKT('SRID=2285;POINT(2093904.76854 357063.46674)'),26911));                                                                                                                
        x         |        y         
------------------+------------------
 352043.233452746 | 5314191.37291459
(1 row)

Mind you, I had to correct some syntax errors to make it run at all:

ARRAY['aaa',bbb',ccc',ddd',eee'], 2285,26911); commit; 

should be:

ARRAY['aaa','bbb','ccc','ddd','eee'], 2285,26911); commit; 

So, this is not an issue running:

                                postgis_full_version                                 
-------------------------------------------------------------------------------------
 POSTGIS="1.4.0SVN" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS
(1 row)

— Kevin

[robe]

I tried duplicating as well on PostgreSQL 8.4 beta2, "POSTGIS="1.4.0SVN" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS"

as well as PostgreSQL 8.2 — "POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS"

and was unable to duplicate — both approaches yielded the (with direct transform as well as what is in the table)

352043.233452746;5314191.37291458

[mcayland]

Note that the 1.4 PROJ.4 code got re-written as part of the code sprint since the old code made us of internal PROJ.4 APIs rather than the official external API. It would be interesting to see if there is any difference when using 1.3.

ATB,

Mark.

[robe]

Hmm Mark — if you see I did test on a 1.3.3 and saw no difference. I'm wondering if this is some sort of cooky casting behavior and it could be a custom cast this person has in place that we don't have. the only difference I see is that in the function the double_precision would need to be cast to text and if that is done wrong who knows what kind of chaos insues.

[kneufeld]

I just did a test on version 1.1.6 just to be sure … same results.

POSTGIS="1.1.6" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS

I think we can safely close this ticket. — Kevin

[mcayland]

I agree with Kevin: if no-one can reproduce this on current versions of PostGIS/PROJ then I don't think there's much here we can do. Paul?

ATB,

Mark.