Using Bounding Box as a where predicate - Mailing list pgsql-sql

<div class="Section1"><p><font color="black" face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">I have been trying to get my code that does a proximity search to work.</span></font><p><font
color="black"face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">The way it is intended to work is, a user enters x/y coordinate pair for a
search.</span></font><p><fontcolor="black" face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">The search retrieves the closest address to the point.</span></font><p><font color="black"
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial">So far, that code is working. Below is the working version.</span></font><p><font color="black"
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial"><br />   SELECT str_num||' ' ||name as full_address,<br />  
distance(the_geom,'POINT(2227284.19636675431435389.6730164126)'::geometry) as dist<br />   FROM address order by dist
limit1</span></font><p><font color="black" face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">This works fine as far as retrieving the closest address to point.</span></font><p><font
color="black"face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">There is however, a slight problem.<br /> Retrieval is very slow due to the table being very
large</span></font><p><fontcolor="black" face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">This is where bounding boxes come in and I have attempted to use it but it is blowing up real
big.</span></font><p><fontcolor="black" face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">Here is the ugly code with a “where” predicate added.</span></font><p
style="margin-bottom:12.0pt"><fontcolor="black" face="Arial" size="3"><span
style="font-size:12.0pt;font-family:Arial">Doesanyone have a clue as to what needs to change here for this to
work.</span></font><p><fontcolor="black" face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">  SELECT str_num||' ' ||name as full_address,<br />   distance(the_geom,'POINT(2227284.1963667543
1435389.6730164126)'::geometry)as dist<br />   FROM address order by dist limit 1<br />   WHERE the_geom &&
'BOX3D(90900190900, 100100 200100)'::box3d<br />   AND distance( the_geom( 'POINT(2227284.1963667543
1435389.6730164126)',-1 ) ) < 100</span></font><p><font color="black" face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial">First I don't know how this part of the code:<br /> 'BOX3D(90900 190900, 100100 200100)'::box3d<br
/>is derived. I mean the values 90900 190900, 100100 200100.<br /> Second, obviously the syntax is wrong or else it
wouldhave worked.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Thanks in advance for your assistance</span></font><p class="MsoNormal"><font color="green"
face="Arial"size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:green">*************************************************************</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Select * From Population WHERE skillset > average</span></font><p class="MsoNormal"><font
color="green"face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:green">*************************************************************</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Have a great day!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Sam Okeh</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Systems Manager</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Dept Of </span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">PublicWorks</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">Phone: 404-224-0484</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Email Address: <a
href="mailto:sam.okeh@co.fulton.ga.us">sam.okeh@co.fulton.ga.us</a></span></font><pclass="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">Pager: <a href="mailto:samokeh@imcingular.com">samokeh@imcingular.com</a></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">On the web at <a href="http://www.fcpublicworks.com">www.fcpublicworks.com</a></span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span style="font-size: 
12.0pt"> </span></font></div>