[postgis-users] list of the closest borders

[Doug Fischer]

Thank you for your response.

Let me try to clarify what I am trying to, now that I have found some  
actual data.

I have a table with the following structure.

countries
___________________________________
country_name	|	character varying(40)
the_geom		|	geometry
___________________________________

This table contains all of the countries.  What I need to do is  
determine the distance, in meters, between a point (collected by a  
GPS) and say the 5 closest countries' borders.

  countries.the_geom SRID = 4269

When I run the following query, I get back a result that seems to be  
correct to me, however the distances are in degrees.

select country_name, distance(the_geom, GeomFromText('Point(-79.68433  
40.58954)', 4269) as distance
	from countries
	order by distance
	limit 5;

Result:
__________________________________
United States	|	0
Canada			|	1.8533
Bahamas, The	|	13.7850
Bermuda		|	16.9754
Cuba			|	17.4070

Is it reasonable to simply multiply the distance by 69 (the  
approximate distance in miles between degrees)?  How accurate is  
this?  I don't need to be all that precise, but I do need to at least  
be in the ballpark.

Is there some way to determine the closest point in the border of the  
country to the point collected by the GPS in order to use the  
distance_sphere() or distance_spheroid() functions for a more precise  
measurement?

Thank you very much for any help.


On Sep 10, 2008, at 9:20 AM, Randall, Eric wrote:

> 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
>
> _______________________________________________
> 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/1541595b/attachment.html>