[postgis-users] Obtaining an address closest based on x/y coordinate pair

[Tyler Mitchell]

> I have been trying desperately but unsuccessfully to get the address 
closest

Unless I'm missing something, this should be straightforward with the use 
of a couple more functions.  Don't be scared of them, they'll help you a 
lot :)  Specifically, you want to use the Distance() function in PostGIS. 
It takes in two geometry fields and will compute the distance for you.

If there are way you can have the coordinates that the user enters get put 
into a postgis table in a geometry column?  If you can get that setup it 
will make discussion even easier.  Let's pretend that you get that working 
and that your point is in a table called UserPoint and it has a the_geom 
field.  Here is how you would do your query:

SELECT
distance(UserPoint.the_geom, address.the_geom),
str_num ||" "|| name AS address
FROM
UserPoint,
address
WHERE
Distance(UserPoint.the_geom, address.the_geom) < 1000;

-You don't need to a UserPoint table with a geometry field, you could 
create a string that represents the geometry too.  Let us know if you need 
help doing this.
-The "< 1000" is the distance threshold I set - change it to whatever you 
want.
-If you have a lot of points, this probably won't be very efficient.  But 
try it out with a few pieces first to see. :)
-When you are ready to optimize things, let us know and we'll introduce 
you to && operator and Expand() function.

Is this helpful?

Tyler