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: [EMAIL PROTECTED] [mailto:postgis-
[EMAIL PROTECTED] 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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
