[postgis-users] list of the closest borders

[Doug Fischer]

Kevin,

This query works like a champ.  Thank you very much for your help!

Doug

On Sep 11, 2008, at 2:49 PM, Kevin Neufeld wrote:

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