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

[Stephen V. Mather]

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 [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/98bc8454/attachment.html>