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