[postgis-users] Getting inconsistent results with function in select vs in a join
Stephen Woodbridge
woodbri at swoodbridge.com
Mon Oct 24 19:22:21 PDT 2011
Argh,
It looks like this problem is related to casts, or at least adding some
explicit casts seems to change the behavior. But I'm not getting the
pattern as to what or why.
So here is a test query that I have been playing with:
select (b).*, * from (
select imt_rgeo_countyzip(x, y) as b, c.* from (
--select -79.2723008572695::real as x, 41.2393432049441::real
as y -- BAD result
--select -79.2723008572695::double precision as x,
41.2393432049441::double precision as y -- GOOD result
--select -79.2723008572695::numeric as x,
41.2393432049441::numeric as y -- GOOD result
--select * from testdata a where gid=552
-- BAD result
--select x::numeric, y::numeric from testdata a where gid=552
-- GOOD result
--select x::real, y::real from testdata a where gid=552
-- BAD result
--select x::double precision, y::double precision from testdata
a where gid=552 -- BAD result
) as c offset 0
) as d;
if you uncomment one of the commented sub selects you get a GOOD or BAD
result as indicated. The function is declared as:
CREATE OR REPLACE FUNCTION imt_rgeo_countyzip(double precision, double
precision)
RETURNS rgeo_out AS
$BODY$
...
I just changed the test query and put a cast on x,y getting passed into
the function and this give more consistent good behavior except of the
values are x,y are already double precision.
select (b).*, * from (
select imt_rgeo_countyzip(x::numeric, y::numeric) as b, c.* from (
--select -79.2723008572695 as x, 41.2393432049441 as y
-- GOOD result
--select -79.2723008572695::real as x, 41.2393432049441::real
as y -- GOOD result
--select -79.2723008572695::double precision as x,
41.2393432049441::double precision as y -- GOOD result
--select -79.2723008572695::numeric as x,
41.2393432049441::numeric as y -- GOOD result
--select * from testdata a where gid=552
-- GOOD result
--select x, y from testdata a where gid=552
-- GOOD result
--select x::real, y::real from testdata a where gid=552
-- GOOD result
--select x::double precision, y::double precision from testdata
a where gid=552 -- BAD result
--select x::numeric, y::numeric from testdata a where gid=552
-- GOOD result
) as c offset 0
) as d;
Sorry for the line wrapping it makes this a little harder to read. I
also tried changing the function definition to these:
CREATE OR REPLACE FUNCTION imt_rgeo_countyzip(numeric, numeric)
CREATE OR REPLACE FUNCTION imt_rgeo_countyzip(real, real)
And these threw errors when the arguments did not explicitly match real
or numeric.
So is this a postgis bug? I do not understand why passing double
precisions to a double precision function would give the wrong results
nor how casting it to another type only to have it implicitly cast back
fixes something.
Now more confused than ever! in spite of having a work-around to the
problem. Clearly I'm missing something.
-Steve
On 10/24/2011 2:27 PM, Stephen Woodbridge wrote:
> Hi all,
>
> I have a query that gives me one result when I just call the stored
> procedure directly and a different result when it gets call with a join.
> I can not for the life of me figure out what is going on.
>
> So here is the basic query:
> select * from imt_rgeo_countyzip(-79.2723,41.2393);
> "Clarion";"PA";"42031";"16225";0;0
>
> Here is the row that I will join the above with:
> select * from testdata where gid=552;
> -79.2723;41.2393;"Z";552;"Clarion";"PA";"42031";"16225";0;0
>
> Here is the joined results:
> select *, imt_rgeo_countyzip(a.x,a.y) from testdata a where gid=552;
> -79.2723;41.2393;"Z";552;"Clarion";"PA";"42031";"16225";0;0;"(Clarion,PA,42031,16225,5242.59489074449,0)"
>
>
> Notice the 5242.59489074449 as the 2nd to last result to the
> imt_rgeo_countyzip() call here but in the reference query above it is 0.
> I have also verified the I'm selecting the same polygon in both of these
> cases. Is this a bug in PostGIS? Specifically in:
> ST_Distance_Spheroid(pnt, the_geom,
> 'SPHEROID["WGS 84",6378137,298.257223563]')
>
> My stored procedure definition is here: http://codepad.org/jt9WVoi7
>
> "PostgreSQL 8.3.14 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
> (Debian 4.3.2-1.1) 4.3.2"
>
> "POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August
> 2008" LIBXML="2.6.32" USE_STATS"
>
> Confused again,
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list