Forum OpenACS Q&A: Address Search

Collapse
Posted by Sam Dau on
I believe I have come to the right place, at least I hope so.
Greetings all.

I have been trying unsuccessfully to get the address closest to the x/y coordinate pair.
I have created this function in transact-SQL but unfortunately, since we will making spatial search, the stored proc didn't help me that much.
I was wondering if I could get someone to get me a head start.

A user enters x/y coordinate pairs say x=0234.22 and y=236541.21

Based on the x/y coordinate pair, I will like to return the closest address within a 500 ft or 1 mile radius of the x/y coordinate pair.
I don't care how far the closest address is from the x/y coordinate pair as long as it is the closest.

The table structure is more like:
str_num , --street number
str_name, -- street name

x(the_geom) --x coordinate
y(the_geom) -- y coordinate

I tried my best rendition of Jerry's script below but to no avail.

select str_num||' '||name from (select str_num||' '||name, distance(loc, '$X, $Y) as address
                      from roads
                  order by address
                    limit 1) as tbl;

Does anyone know how to approach this?
Thanks in advance