[postgis-users] list of the closest borders

[Doug Fischer]

Kevin,

I have played with these functions and created a query however it  
does not seem to be producing the correct results.

What I have done is the following (just so you understand the data  
that I am working with).

I have downloaded the World shapefile from http://www.cipotato.org/ 
diva/data/MoreData.htm

I used shp2pgsql to create the inserts for PostGIS into a table  
called world_countries with the following definition:

gid - integer
name - character varying(40)
gmi_cntry - character varying(3)
region - character varying(25)
the_geom - geometry

I selected a point close to the canadian border in Montana (lat:  
48.400032 long: -107.753906) and ran the following queries

1)
select name, gmi_cntry,
	distance(the_geom,
		GeomFromText('Point(-107.753906 48.400032)', srid(the_geom))) *  
69.046767 as distance
	from worl_countries order by distance limit 5;

2)
select name, gmi_cntry,
	distance_sphere(
		line_interpolate_point(
			ExteriorRing(GeometryN(the_geom, 1)),
			line_locate_point(
				ExteriorRing(GeometryN(the_geom, 1)),
				GeomFromText('Point(-107.753906 48.400032)', srid(the_geom))
			)
		),
		GeomFromText('Point(-107.753906 48.400032)', srid(the_geom))
	) / 1609.344 as distance
	from world_countries order by distance limit 5;

Results of query 1:

United States 	|	USA	|	0
Canada			|	CAN	|	41.42...
Mexico			|	MEX	|	1147.37...
Guatemala		|	GTM	|	2408.14...
Cuba			|	CUB	|	2413.21...

Results of query 2:

United States 	|	USA	|	1277.33...
Mexico			|	MEX	|	1336.31...
Canada			|	CAN	|	1509.44...
Cuba			|	CUB	|	2283.77...
Guatemala		|	GTM	|	2313.55...

As you can see from the results of the 2 queries, they are not even  
close.  I understand that the first query is not going to be all that  
accuate, but I tthought that the two would at least be in the same  
ballpark.  The results from query 1 are more like what I need, I was  
just trying to get a little more accurate.  Query 2 seems to be  
better when the Point that I am using in the query is not inside of a  
country already.

Any ideas?  suggestions?  Anything would be appreciated.

thank you very much,
Doug

On Sep 10, 2008, at 11:34 AM, Kevin Neufeld wrote:

> Yes.  Experiment with
>
> - ST_Line_Locate_Point(linestring, Point) - which returns a  
> percentage along the linestring the point occurs.
>
> - ST_Line_Interpolate_Point(linestring, location) - which accepts a  
> "percentage along" and returns the interpolated point along the  
> linestring.
>
> Since these functions accept a linestring, you could extract the  
> exterior ring of your country polygon.
>
> Something like:
> SELECT
>   ST_Line_Interpolate_Point(
>     ST_Exterior_Ring(the_geom),
>     ST_Line_Locate_Point(ST_Exterior_Ring(the_geom), gps_pt)
>   )
> FROM ...
>
> Cheers,
> Kevin
>
> Doug Fischer wrote:
>> ...
>> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>