[postgis-users] list of the closest borders

[Kevin Neufeld]

Ok, yikes.  This is suddenly getting much more complicated than I 
originally thought.

Yes, obviously the boundary approach does not take into account if the 
point is inside a country polygon.

Also, the problem with your second attempt was that you were grabbing 
only the first polygon out of a multipolygon and using that to test your 
distances against.

This is a very quick hack that I'm sure you can clean up, but it may get 
you closer to what you're after:



-- Sort and limit the final result
SELECT * FROM (

-- Filter out duplicate entries
SELECT DISTINCT ON (name) *
FROM (

-- Select the country we are inside
SELECT name, gmi_cntry, 0::double precision AS distance
FROM world_countries a
WHERE ST_Contains(
   a.the_geom,
   ST_GeomFromText('Point(-107.753906 48.400032)', srid(the_geom)))

UNION ALL

-- Select all countries and their respective distances
SELECT name, gmi_cntry,
     distance_sphere(
         line_interpolate_point( a.geom, line_locate_point( a.geom, 
b.pt) ),
         b.pt
     ) / 1609.344 AS distance
FROM (
   SELECT name, gmi_cntry, (ST_Dump(ST_Boundary(the_geom))).geom
   FROM world_countries ) AS a,
   (SELECT ST_GeomFromText('Point(-107.753906 48.400032)', 4326) AS pt) AS b

) AS foo
ORDER BY name, distance) AS foo

ORDER BY distance
LIMIT 5;



      name      | gmi_cntry |     distance
---------------+-----------+------------------
  United States | USA       |                0
  Canada        | CAN       | 41.4484753115909
  Mexico        | MEX       | 1148.15027501245
  Bahamas, The  | BHS       | 2165.10853394356
  Cuba          | CUB       | 2200.33855861009
(5 rows)


Cheers,
Kevin

Doug Fischer wrote:
> 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
>>
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users