OK. Given your table definition, I'd use
select str_num || ' ' || name as full_address,
point_distance(the_geom, '$X,$Y' ) as dist
from address
order by dist
limit 1
This is assuming that
the_geom is defined as the Postgres
point datatype.
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.
