Problem with finding intersections with geography types

[infinitynsk]

I have a database with a column of type "geography" that contains geographic coordinates of points. I query the database which counts number of points that are contained in a given polygon. But I encountered with situation when I get that a point isn't contained in a polygon but it is contained in another polygon that is a part of the first one.

Point in database: POINT(147.88594 70.624908)

I performed three queries:

—select number of points which are contained in polygon

—1

SELECT count(*) from geotest where point && 'srid=4326;polygon((146.25 72.3957, 151.875 72.3957, 151.875 70.6126, 146.25 70.6126, 146.25 72.3957))'::geography and st_intersects(point, 'srid=4326;polygon((146.25 72.3957, 151.875 72.3957, 151.875 70.6126, 146.25 70.6126, 146.25 72.3957))'::geography);

—select points which are contained in another polygon; the polygon is contained in polygon

—from previous select, what is confirmed by third select

—2

SELECT asewkt(point::geometry) from geotest where point && 'srid=4326;polygon((146.25 71.5249, 149.0625 71.5246, 149.0625 70.6126, 146.25 70.6126, 146.25 71.5249))'::geography and st_intersects(point, 'srid=4326;polygon((146.25 71.5249, 149.0625 71.5246, 149.0625 70.6126, 146.25 70.6126, 146.25 71.5249))'::geography);

—3

select st_contains('srid=4326;polygon((146.25 72.3957, 151.875 72.3957, 151.875 70.6126, 146.25 70.6126, 146.25 72.3957))'::geometry, 'srid=4326;polygon((146.25 71.5249, 149.0625 71.5246, 149.0625 70.6126, 146.25 70.6126, 146.25 71.5249))'::geometry);

And I got:

count

---

0

(1 row)

asewkt

---

SRID=4326;POINT(147.88594 70.624908)

(1 row)

st_contains

---

t

(1 row)

I use postgresql 8.4.5 and postgis 1.5.1

Dump of database with this point is in attachment.

[infinitynsk]

dump of test database

[pramsey]

boxes aren't actually contained

[pramsey]

point is actually only in one box

[pramsey]

You can't just run a "contains" test in geometry and expect it to return the right answer, because lines in geometry are not in the same place as in geography. Geometry lines are linear interpolations in platte carre. Geography lines are great circle arcs over a sphere. They don't go over the same places.

[pramsey]

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