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