Opened 15 years ago

Closed 14 years ago

#194 closed defect (worksforme)

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

Reported by: md Owned by: pramsey
Priority: medium Milestone:
Component: postgis Version:
Keywords: Cc:


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='
';POINT(' x_ar[i] ' ' y_ar[i] ')'),srid_to)),
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




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

This is a disturbingly huge differnece.

Change History (7)

comment:1 by kneufeld, 15 years ago

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)

  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="1.4.0SVN" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS
(1 row)

— Kevin

comment:2 by robe, 15 years ago

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)


comment:3 by mcayland, 15 years ago

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.



comment:4 by robe, 15 years ago

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.

comment:5 by kneufeld, 15 years ago

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

comment:6 by mcayland, 15 years ago

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?



comment:7 by pramsey, 14 years ago

Resolution: worksforme
Status: newclosed
Note: See TracTickets for help on using tickets.