[postgis-users] Re: problem with BOX3D query

Randy George rkgeorge at cadmaps.com
Mon May 8 14:04:40 PDT 2006 

Hi Patricio,
	
	Thank you very much! Mystery solved! 

I had copy/pasted Box3D from the PostGIS pdf manual and failed to notice
that the apostrophe changes:
pdf ’ = hex 92 instead of hex 27 '
once I change to hex 27 apostrophe everything works as expected.

>From pdf manual:
SELECT
AsText(GEOM) AS GEOM
FROM ROADS_GEOM
WHERE
GEOM && GeomFromText(’BOX3D(191232 243117,191232 243119)’::box3d,-1);

Note the apostrophe!

SELECT the_geom FROM streets
WHERE the_geom && SetSRID(’BOX3D(-111.80 33.30,-111.75 33.35)’::box3d,4269);
will not work

but
SELECT the_geom FROM streets
WHERE the_geom && SetSRID('BOX3D(-111.80 33.30,-111.75 33.35)'::box3d,4269);
will work as expected.

Interestingly - 

SELECT the_geom FROM streets
WHERE the_geom && GeomFromText('BOX3D(-111.80 33.30,-111.75
33.35)'::box3d,4269);
Produces response:
ERROR:  function geomfromtext(box3d, integer) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.

So manual may need to be updated?

Thanks
Randy


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Patricio
Cifuentes Ithal
Sent: Monday, May 08, 2006 2:18 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Re: problem with BOX3D query

Hi Randy,

sorry I'am very explicyt... so now send example much.

example
SELECT * FROM table WHERE SetSRID('BOX3D(111.80 33.30,-111.75
33.350)'::box3d,4269) && the_geom

-- 

Patricio Cifuentes Ithal
Ingeniero en Informática

Informática
SIIGSA
(56-2) 204 60 22
----- Original Message ----- 
From: "Randy George" <rkgeorge at cadmaps.com>
To: "'PostGIS Users Discussion'" <postgis-users at postgis.refractions.net>
Sent: Monday, May 08, 2006 3:52 PM
Subject: RE: [postgis-users] Re: problem with BOX3D query


> Hi Patricio,
>
> Perhaps I'm not understanding your suggestion correctly but
> reversing fields:
> SELECT the_geom FROM streets
> WHERE SetSRID('BOX3D(-111.80 33.30,-111.75 33.35)'::box3d,4269) &&
the_geom;
> Produces the same response:
> ERROR:  syntax error at or near "33.30" at character 60
>
> Thanks
> Randy
>
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Patricio
> Cifuentes Ithal
> Sent: Monday, May 08, 2006 1:07 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Re: problem with BOX3D query
>
> hi,
>
> change field the_geom position....
>
> SELECT the_geom FROM streets
> WHERE the_geom && SetSRID('BOX3D(-111.80 33.30 0.0, -111.75
> 33.350.0)'::box3d,4269);
>
>
> example
> $sqldata="the_geom from (SELECT * FROM ".$table." WHERE
> SetSRID('BOX3D(".$ext[0]." ".$ext[1].",".$ext[2]."
".$ext[3].")'::box3d,-1)
> && the_geom ) as foo using unique gid using SRID=-1";
>
>
>
> -- 
>
> Patricio Cifuentes Ithal
> Ingeniero en Informática
>
> Informática
> SIIGSA
> (56-2) 204 60 22
> ----- Original Message ----- 
> From: "Randy George" <rkgeorge at cadmaps.com>
> To: "'PostGIS Users Discussion'" <postgis-users at postgis.refractions.net>
> Sent: Monday, May 08, 2006 2:57 PM
> Subject: RE: [postgis-users] Re: problem with BOX3D query
>
>
> > Hi Marc,
> >
> > I believe I'm using one of your latest builds for PostgreSQL 8.1:
> > SELECT postgis_full_version()
> > "POSTGIS="1.1.1" GEOS="2.2.1-CAPI-1.0.1" PROJ="Rel. 4.4.9, 29 Oct 2004"
> > USE_STATS DBPROC="0.3.0" RELPROC="1.1.1" (needs proc upgrade)"
> >
> > I've tried the query with and without the space before comma and after
> > comma, but receive the same error.
> >
> > I've also tried adding zmin zmax:
> >
> > SELECT the_geom FROM streets
> > WHERE the_geom && SetSRID('BOX3D(-111.80 33.30 0.0, -111.75 33.35
> > 0.0)'::box3d,4269);
> > ERROR:  syntax error at or near "33.30" at character 72
> >
> >
> > Thanks
> > Randy
> >
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mark
> > Cave-Ayland
> > Sent: Monday, May 08, 2006 5:25 AM
> > To: 'PostGIS Users Discussion'
> > Subject: RE: [postgis-users] Re: problem with BOX3D query
> >
> > > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Randy
> > George
> > > Sent: 06 May 2006 18:21
> > > To: postgis-users at postgis.refractions.net
> > > Subject: [postgis-users] Re: problem with BOX3D query
> > >
> > > Hi,
> > >
> > > I'm trying to find my error in a BOX3D query
> > >
> > > "PostgreSQL 8.1.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
> 3.4.2
> > (mingw-
> > > special)" In Windows 2003 environment
> > >
> > >
> > > SELECT the_geom FROM streets
> > > WHERE the_geom && SetSRID('BOX3D(-111.80 33.30 , -111.75
> > 33.35)'::box3d,4269);
> > >
> > > Gives ERROR: syntax error at or near "33.30" at character 72
> > >
> > > But identical BOX3D here
> > > select AsEWKT(SetSRID('BOX3D(-111.80 33.30 , -111.75 33.35)'::box3d,
> > 4269));
> > > Works fine?
> > > "SRID=4269;POLYGON((-111.8 33.3,-111.8 33.35,-111.75 33.35,-111.75
> > 33.3,-111.8 33.3))"
> > >
> > > Substituting POLYGON
> > > SELECT the_geom FROM streets
> > > WHERE the_geom && GeomFromText('POLYGON((-111.8 33.3,-111.8
> 33.35,-111.75
> > 33.35,-111.75
> > > 33.3,-111.8 33.3))',4269)
> > > Works as expected
> > >
> > > As does he simpler LINESTRING bounding box
> > > SELECT the_geom FROM streets
> > > WHERE the_geom && GeomFromText('LINESTRING(-111.80 33.30, -111.75
> > 33.35)',4269)
> > >
> > > However I'm curious why the BOX3D isn't working?
> > >
> > > Thanks
> > > Rkgeorge
> >
> >
> > Hi Randy,
> >
> > Using PostGIS 1.0.3, I see the following behaviour:
> >
> > postgis=# SELECT geom FROM osgb_text
> > postgis-# WHERE geom && SetSRID('BOX3D(-111.80 33.30 , -111.75
> > 33.35)'::box3d,4269);
> > ERROR:  BOX3D parser - couldnt parse.  It should look like: BOX3D(xmin
> ymin
> > zmin,xmax ymax zmax) or BOX3D(xmin ymin,xmax ymax)
> > postgis=# select postgis_full_version();
> >                                          postgis_full_version
>
> --------------------------------------------------------------------------
> --
> > --------------------------
> >  POSTGIS="1.0.3" GEOS="2.1.3" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS
> > DBPROC="0.3.0" RELPROC="0.3.0"
> > (1 row)
> >
> > Removing the space before the comma in the BOX3D allows the statement to
> > execute without problems here. Which version of PostGIS are you using?
> > (SELECT postgis_full_version()). I wonder if it could be something to do
> > with the Geometry <-> BOX3D cast used to query the indices.
> >
> >
> > Kind regards,
> >
> > Mark.
> >
> > ------------------------
> > WebBased Ltd
> > 17 Research Way
> > Plymouth
> > PL6 8BT
> >
> > T: +44 (0)1752 797131
> > F: +44 (0)1752 791023
> >
> > http://www.webbased.co.uk
> > http://www.infomapper.com
> > http://www.swtc.co.uk
> >
> > This email and any attachments are confidential to the intended
recipient
> > and may also be privileged. If you are not the intended recipient please
> > delete it from your system and notify the sender. You should not copy it
> or
> > use it for any purpose nor disclose or distribute its contents to any
> other
> > person.
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
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