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

[iheanyi Okeh]

This is my post here and I have come to this list, not knowing what type of
questions are asked here but I am desperate for help.



I have been trying desperately but unsuccessfully to get the address closest
to the x/y coordinate pair.

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.

The table structure looks like this:

str_num , --street number
str_name, -- street name

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


Notice the way x and y coordinates are structure.

As you know, in PostGIS we get the x and y of each point shape using the
x(geometry) and y(geometry) functions.
However, in our database, the geometry is already in the field called
the_geom, so to get the x and y for each point we use it like this:
x(the_geom) and y(the_geom).

The code I am using so far isn't working.
Infact, it always returns the same address, no matter what the x/y
coordinate pair is.

Here is that code:


select
sqrt(distance) AS distance,
str_num ||" "|| name AS address
from
(select (x(the_geom) - x(the_geom))^2 + (y(the_geom) - y(the_geom))^2 as
distance, *
from address order by distance limit 1) as r


It is important to point out that if I use a where predicate, such as this:

select
sqrt(distance) AS distance,
str_num ||" "|| name AS address
from
(select (x(the_geom) - x(the_geom))^2 + (y(the_geom) - y(the_geom))^2 as
distance, *
from address where x(the_geom) = someXValue and y(the_geom) = someYValue
ORDER BY distance limit 1) as r

the code stops returning any values at all.
Any help would be truly appreciated.

I have heard terms such as bounding box, contains and intersections but I am
extremely new to postgreSQL/postGIS and wouldn't know how to use them in
this case.
Many thanks in advance

_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfee® 
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963