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

Jensen, Layne K CIV SPAWARSYSCEN-PACIFIC, 56210 layne.jensen at navy.mil
Tue Feb 19 09:05:41 PST 2013

```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>
```