Thanks for the response and code modification.
It seems to be having problem with certain aspects of it.
For instance, looking at the table structure I posted earlier,
the current code I am using looks like this:
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.
