[postgis-users] Obtaining an address closest based on x/y coordinate pair
iheanyi Okeh
simflex at hotmail.com
Fri Mar 19 13:03:16 PST 2004
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
More information about the postgis-users
mailing list