Skip site navigation (1) Skip section navigation (2)

Using Bounding Box as a where predicate

From: "Okeh, Sam" <Sam(dot)Okeh(at)co(dot)fulton(dot)ga(dot)us>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Using Bounding Box as a where predicate
Date: 2004-03-25 19:21:36
Message-ID: 67BEB4B05CD89845B64FAED30F4935B1050480BE@fcgctex02.co.fulton.ga.us (view raw or flat)
Thread:
Lists: pgsql-sql
I have been trying to get my code that does a proximity search to work.

The way it is intended to work is, a user enters x/y coordinate pair for
a search.

The search retrieves the closest address to the point.

So far, that code is working. Below is the working version.


  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

This works fine as far as retrieving the closest address to point.

There is however, a slight problem.
Retrieval is very slow due to the table being very large

This is where bounding boxes come in and I have attempted to use it but
it is blowing up real big.

Here is the ugly code with a "where" predicate added.

Does anyone have a clue as to what needs to change here for this to
work.

  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
  WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
  AND distance( the_geom( 'POINT(2227284.1963667543
1435389.6730164126)', -1 ) ) < 100

First I don't know how this part of the code:
'BOX3D(90900 190900, 100100 200100)'::box3d
is derived. I mean the values 90900 190900, 100100 200100.
Second, obviously the syntax is wrong or else it would have worked.

Thanks in advance for your assistance

*************************************************************

Select * From Population WHERE skillset > average

*************************************************************

Have a great day!

Sam Okeh

Systems Manager

Dept Of Public Works

Phone: 404-224-0484

Email Address: sam(dot)okeh(at)co(dot)fulton(dot)ga(dot)us

Pager: samokeh(at)imcingular(dot)com

On the web at www.fcpublicworks.com

 

pgsql-sql by date

Next:From: Kemin ZhouDate: 2004-03-26 01:25:15
Subject: how to turn off autocommit in psql
Previous:From: Gregor RotDate: 2004-03-25 13:23:00
Subject: order of results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group