|
|
| GeoCommunity Mailing List |
| |
| Mailing List Archives |
| Subject: | [gislist] Obtaining an based on x/y coordinate pair |
| Date: |
03/22/2004 08:15:00 AM |
| From: |
iheanyi Okeh |
|
|
This is a repost since I inadvertently omitted title on my first post
I have been trying desperately but unsuccessfully to get the address closest
to the x/y coordinate pair.
A user enters x/y coordinate pair say x=0234.22 and y=236541.21
Based on the x/y coordinate pair, I will like to return the closest address
The table structure looks like this:
str_num , --street number
str_name, -- street name
the_geom -- contains x/y coordinate values
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
_________________________________________________________________
MSN Toolbar provides one-click access to Hotmail from any Web page – FREE
download! http://clk.atdmt.com/AVE/go/onm00200413ave/direct/01/
_______________________________________________
gislist mailing list
gislist@lists.geocomm.com
http://lists.geocomm.com/mailman/listinfo/gislist
_________________________________
This list is brought to you by
The GeoCommunity
http://www.geocomm.com/
Get Access to the latest GIS & Geospatial Industry RFPs and bids
http://www.geobids.com
|
|
Sponsored by:
For information
regarding
advertising rates
Click Here!
|
