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

[Jensen, Layne K CIV SPAWARSYSCEN-PACIFIC, 56210]

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



 Stephen V. Mather
GIS Manager
(216) 635-3243 (Work) 
clevelandmetroparks.com









From: postgis-users-bounces at lists.osgeo.org [postgis-users-bounces at lists.osgeo.org] on behalf of Jensen, Layne K CIV SPAWARSYSCEN-PACIFIC, 56210 [layne.jensen at navy.mil]
Sent: Tuesday, February 19, 2013 10:25 AM
To: 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!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130219/764e9676/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3271 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130219/764e9676/attachment.bin>