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
order by address
limit 1) as tbl;
Does anyone know how to approach this?
Thanks in advance