[postgis-users] list of the closest borders

[Randall, Eric]

Hi Doug,
 
If your point is in a table points and your states are in a table states and your distances are in feet
then one way might be something like:
 
 
select states.stateabbrev, st_distance(states.geom,points.geom)/5280
from points, states
where points.point_id = your_PA_point_id
and stateabbrev in ('OH','MD',''WV','VA')
order by st_distance(states.geom,points.geom)
 
 
If your point is an xy location then similarly (using example point in Greene County)
 
select states.stateabbrev, st_distance(states.geom,makepoint(1263197,192349))/5280
from states
where stateabbrev in ('OH','MD',''WV','VA')
order by st_distance(states.geom,makepoint(1263197,192349))
 
 
 
If you are talking about the SW corner point on the PA Boundary then you'll need to get the 
point on PA Boundary nearest the SW point (always the first point I believe)  on the exteriorring of the envelope of the PA Boundary which would be:
 
 
select st_line_interpolate_point(st_exteriorring(st_geometryn(geom,1)),st_line_locate_point(st_exteriorring(st_geometryn(geom,1)),st_pointn(st_exteriorring(st_envelope(geom)),1)))
from states where stateabbrev = 'PA'
 
 
and use that point in the query(s) above.  There are other ways, probably better ways too.  
 
 
 
-Eric
 
 
 
 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of Doug Fischer
Sent: Tuesday, September 09, 2008 10:55 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] list of the closest borders



Sorry but I just noticed that I made an error in my post.  What I meant to say was the SW corner of PA and in the table the distance to PA should be 0 because the Point is in PA. 

Thanks,
Doug

On Sep 9, 2008, at 8:52 AM, Doug Fischer wrote:


I am new to GIS / PostGIS so please forgive me if this is a simple question.

Assuming that I have a database populated with all of the proper data, I need to do determine a list of the closest borders from a point including distances.

ex. 
If I have a point located in the SE corner of PA, I would like to run a query that will return a list containing something like the following:



Border
Distance in miles

PA
A

OH
B

MD
C

WV
D

VA
E


Is there a function in PostGIS that can do this type of thing? Does anyone have an example?

Thank you very much. 
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080910/0127a10e/attachment.html>