[postgis-users] Perplexed: ST_Intersects with Geography, odd results

[Stephen V. Mather]

I've just been following this board long enough to have seen others ask the same.  Ah, spherical trig... .

Best,
Steve



On Feb 19, 2013, at 12:07 PM, "Jensen, Layne K CIV SPAWARSYSCEN-PACIFIC, 56210" <layne.jensen at navy.mil<mailto:layne.jensen at navy.mil>> wrote:

Steve,

Of course, you're absolutely correct.  I knew there had to be a great big "Duh!" in there somewhere, and was prepared to be embarrassed.  I hadn't visualized the polygon as a series of great circles.  Thank you for bringing me back to reality.

Layne

________________________________
From: Stephen V. Mather
Sent: Tue 2/19/2013 7:46 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Perplexed: ST_Intersects with Geography, odd results

Having not looked at your particular use case in detail, my guess is it's related to the world not being flat, see e.g.:

http://blog.opengeo.org/2012/04/30/the-earth-is-not-flat-volume-2/

Best,
Steve

[http://sig.cmparks.net/cmp-ms-90x122.png] Stephen V. Mather
GIS Manager
(216) 635-3243 (Work)
clevelandmetroparks.com<http://www.clemetparks.com/>



________________________________
From: postgis-users-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org> [postgis-users-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org>] on behalf of Jensen, Layne K CIV SPAWARSYSCEN-PACIFIC, 56210 [layne.jensen at navy.mil<mailto:layne.jensen at navy.mil>]
Sent: Tuesday, February 19, 2013 10:25 AM
To: postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
Subject: [postgis-users] Perplexed: ST_Intersects with Geography, odd results



We are using PostgreSQL version 9.2.2 with PostGIS 2.0,and need to do simple queries to find points that are within or intersect with a polygon.

The following test case returns only partial results for an unknown reason. If the minimum longitude value of the polygon is changed from 0 to 1 to 2 to 5 to 20 (leaving the latitudes limits as they are), different numbers of the points are returned, even though all of the points fall into the polygon for all these values.

CREATE TABLE testing.tsttab
(
  lat real,
  lon real,
  tstpoint geography(Point,4326)
);

INSERT INTO testing.tsttab (lat, lon, tstpoint) values (36.1, 22.1, ST_GeographyFromText('POINT(22.1 36.1)'));
INSERT INTO testing.tsttab (lat, lon, tstpoint) values (36.2, 22.2, ST_GeographyFromText('POINT(22.2 36.2)'));
INSERT INTO testing.tsttab (lat, lon, tstpoint) values (36.3, 22.3, ST_GeographyFromText('POINT(22.3 36.3)'));
INSERT INTO testing.tsttab (lat, lon, tstpoint) values (36.4, 22.4, ST_GeographyFromText('POINT(22.4 36.4)'));
INSERT INTO testing.tsttab (lat, lon, tstpoint) values (36.5, 22.5, ST_GeographyFromText('POINT(22.5 36.5)'));
INSERT INTO testing.tsttab (lat, lon, tstpoint) values (36.6, 23.1, ST_GeographyFromText('POINT(23.1 36.6)'));
INSERT INTO testing.tsttab (lat, lon, tstpoint) values (36.7, 23.2, ST_GeographyFromText('POINT(23.2 36.7)'));

select * from testing.tsttab
where st_intersects(
st_geogfromtext('POLYGON((2 35, 2 37, 40 37, 40 35, 2 35))'),tstpoint);

If we convert to GEOMETRY rather than GEOGRAPHY, it performs correctly. Can anyone shed light on what we are doing wrong?

Thank you!
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130220/a7c19f2f/attachment.html>