[postgis-users] Bounding Box Query

[iheanyi Okeh]

Yes, I do to both questions.

normally, In PostGIS you get the x and y of each point shape using the 
x(geometry) and y(geometry) functions.  But In our database, the geometry is 
in the field called the_geom, so you would get the x and y for each point 
using x(the_geom) and y(the_geom).

So the query below works except it is very sloooooooow.

SELECT str_num || ' ' || name as full_address,
> > >    distance(the_geom,'POINT(2227284.1963667543
> > > 1435389.6730164126)'::geometry) as dist
> > >    FROM address
> > > ORDER BY dist limit 1

It is only when I try using the overlap operator as a where predicate that 
it crashes.


>From: Tyler Mitchell <TMitchell at lignum.com>
>Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>Subject: Re: [postgis-users] Bounding Box Query
>Date: Mon, 29 Mar 2004 09:15:53 -0800
>
>Do you have the x,y coordinate pair in a table in the database?
>Do you have the road data in a table too?
>
>postgis-users-bounces at postgis.refractions.net wrote on 03/29/2004 08:40:07
>AM:
>
> > Hi Tyler,
> > This is the first time I am getting a response from member listers.
> > Thanks for inquiring.,
> > Can you repost your response, please?
> >
> >
> > >From: Tyler Mitchell <TMitchell at lignum.com>
> > >Reply-To: PostGIS Users Discussion
><postgis-users at postgis.refractions.net>
> > >To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> > >CC:
> > >postgis-users at postgis.refractions.net,postgis-users-
> > bounces at postgis.refractions.net
> > >Subject: Re: [postgis-users] Bounding Box Query
> > >Date: Mon, 29 Mar 2004 08:36:30 -0800
> > >
> > >Are you getting email from the list okay?  A couple of us had responded
>a
> > >week or so ago...
> > >
> > >postgis-users-bounces at postgis.refractions.net wrote on 03/29/2004
>05:51:08
> > >AM:
> > >
> > > > I had posted this question here before but didn't get any response.
> > > > I was encouraged to try it again.
> > > >
> > > > I have a query that retrieves an address closest to a point given an
>x/y
> > >
> > > > coordinate pair.
> > > >
> > > > This is that query:
> > > >
> > > > SELECT str_num || ' ' || name as full_address,
> > > >    distance(the_geom,'POINT(2227284.1963667543
> > > > 1435389.6730164126)'::geometry) as dist
> > > >    FROM address
> > > > ORDER BY dist limit 1
> > > >
> > > > The above query works as far as returning the closest address to a
>point
> > >
> > > > based on x/y coordinate pair.
> > > >
> > > > Hower, performance is really bad as it takes quite sometime for this
> > >query
> > > > to retrieve a record.
> > > >
> > > > I have a about bounding boxes or overlap operator (&&) as a means of
> > > > improving performance.
> > > >
> > > > I don't quite understand it how works.
> > > > For instance, given the below query (which doesn't work by the way),
> > > >
> > > > SELECT str_num || ' ' || name as full_address,
> > > >    distance(the_geom,'POINT(2227284.1963667543
> > > > 1435389.6730164126)'::geometry) as dist
> > > >    FROM address
> > > >    WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
> > > >    AND distance( the_geom( 'POINT(2227284.1963667543
> > >1435389.6730164126)',
> > > > -1 ) ) < 100
> > > > ORDER BY dist limit 1
> > > >
> > > > My questions are:
> > > > 1, how are the arguments in bo3d shown below generated? I just
>filled in
> > >
> > > > those numbers without knowing where they come from or how they are
> > > > generated.
> > > >
> > > >    WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
> > > >    AND distance( the_geom( 'POINT(2227284.1963667543
> > >1435389.6730164126)',
> > > > -1 ) ) < 100
> > > >
> > > > 2, how can I apply the information on question 1 to with the query
>above
> > >
> > > > (the one indicated as a working code)?
> > > >
> > > > All research I have done so far have not helped me.
> > > > Your assistance is greatly appreciated.
> > > >
> > > > _________________________________________________________________
> > > > All the action. All the drama. Get NCAA hoops coverage at MSN Sports
>by
> > > > ESPN. http://msn.espn.go.com/index.html?partnersite=espn
> > > >
> > > > _______________________________________________
> > > > 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
> >
> > _________________________________________________________________
> > Get rid of annoying pop-up ads with the new MSN Toolbar – FREE!
> > http://toolbar.msn.com/go/onm00200414ave/direct/01/
> >
> > _______________________________________________
> > 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

_________________________________________________________________
Get rid of annoying pop-up ads with the new MSN Toolbar – FREE! 
http://clk.atdmt.com/AVE/go/onm00200414ave/direct/01/