From: | "Okeh, Sam" <Sam(dot)Okeh(at)co(dot)fulton(dot)ga(dot)us> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | |
Date: | 2004-03-24 20:11:49 |
Message-ID: | 67BEB4B05CD89845B64FAED30F4935B18974C8@fcgctex02.co.fulton.ga.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I have been trying to get my code that does a proximity search.
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.
Did I mention I am a complete newbie to openGIS/postGIS/postgreSQL? but
I am learning real fast.
Thanks in advance
*************************************************************
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-03-24 22:51:21 | Re: configure script failure with SCO 5.0.7 |
Previous Message | David P. Lurie | 2004-03-24 18:20:53 | configure script failure with SCO 5.0.7 |