Forum OpenACS Q&A: Closest Address Calculation

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

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

Notice the the syntax x(the_geom)

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 question becomes, how can I blend your part of the script: select (:x0 - x)^2 + (:y0 - y)^2 as distance into this: x(the_geom) and y(the_geom)?
I tried (:x0(the_geom) - x(the_geom))^2 + (:y0(the_geom) - y(the_geom))^2 as distance but it is error-ying out on both the :(colon) and the 0.

I must point out that if I use the code this way:

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

(Without the colon and 0), it does return an address but I am afraid something significant is missing.

Any further assistance would be appreciated.

select str_num || ' ' || name as full_address,
       point_distance(the_geom, '$X,$Y' ) as dist
  from address
 order by dist
 limit 1

Where the $X and $Y appear is where the input coordinates from your user should go - it doesn't sound like you're using OpenACS so I can't tell you how to do that in your environment (we would write them as :X and :Y ).

If you have many rows in the address table this will be a slow query. There are several optimisations available (using a bounding box to restrict the rows you consider candidates, replacing point_distance with your own function that doesn't bother taking the square root of the distance) but I'd think you should avoid that sort of thing until you can get the simple but slow query working in your environment.

the point_distance function takes 2 arguments of type point. What you have there is 3 arguments where the first is indeed a point but the other two are plain numbers - what you need to do is tell Postgres that the 2 real numbers are actually the coordinates of a single point. The way to do this is to enclose the pair on numbers in single quote marks. So what you actually want is this -

select
str_num ||' '|| name AS full_address,
point_distance(the_geom, '2227284.1963667543,1435389.6730164126') as dist
from address order by dist limit 1

hope this helps you out...

                    List of functions
 Result data type |      Name      | Argument data types 
------------------+----------------+---------------------
 double precision | point_distance | point, point
(1 row)

There's an example of restricting the query with a bounding box in the thread referenced above but it's for plain Postgres not PostGIS, and since you're using the PostGIS extensions it won't really help. You really should speak to the people on the PostGIS mailing lists since they know the extensions you're using.

http://postgis.refractions.net/mailman/listinfo/postgis-users