[postgis-users] Incorrect results from queries specifying an SRID

['strk']

On Thu, Jun 03, 2004 at 09:24:51AM -0700, Paul Ramsey wrote:
> Actually, this is a slightly user-hostile annoyance I ran across last 
> night while writing the PostGIS workshop for MUM (I hope everyone is 
> coming, I expect to see all 450 of you there (and expect all of you to 
> buy me a beer (the workshop might be a little disjointed))).

I'd like to come, but I'm about 133dd far away :)

> 
> Since box3d's cannot hold an SRID, if you ever get a cast into box3d, 
> you lose your SRID, which results in ugly SQL like this:
> 
> WHERE the_geom && setsrid(expand(geometryfromtext('POINT(100000 
> 100000)',42102),100),42102)
> 
> (I recognize that I can shrink the lenght of it a bit by not initially 
> declaring an SRID, it will get stripped later, after all.) To the user 
> it is not immediately obvious why the more straightforward SQL does not 
> work:
> 
> WHERE the_geom && expand(geometryfromtext('POINT(100000 
> 100000)',42102),100)
> 
> ERROR:  Operation on two GEOMETRIES with different SRIDs
> 
> You make a geometry, and expand it, seems like a nice obvious piece of 
> SQL, but the error message you get says your SRIDs mismatch. But you 
> set an SRID, it's right there in the SQL! Confusion reigns.

I've added an expand(geometry, int8).
Result will be a polygon instead of a box3d with SRID and frills,
but topologically a box anyway..

This is what you need to make use of it:

CREATE OR REPLACE FUNCTION expand(geometry,float8)
        RETURNS geometry
        AS '/usr/src/postgis/postgis/libpostgis.so.0.8','expand_geometry'
        LANGUAGE 'C' WITH (iscachable,isstrict);

Have fun, and spool a beer :)

--strk;

> 
> On Thursday, June 3, 2004, at 01:36 AM, Mark Cave-Ayland wrote:
> >
> >Hmmm..... I can't see why this one doesn't work. All I can think of is
> >that the BOX3D cast causes the SRID not be set or set incorrectly. I'll
> >try and have a look at this in the near future, however I'm not sure
> >exactly when I'll be able to get around to it.
> 
>      Paul Ramsey
>      Refractions Research
>      Email: pramsey at refractions.net
>      Phone: (250) 885-0632