Opened 9 years ago

Closed 7 years ago

Last modified 7 years ago

#621 closed defect (invalid)

Problem with finding intersections with geography types

Reported by: infinitynsk Owned by: pramsey
Priority: high Milestone: PostGIS 1.5.4
Component: postgis Version: 1.5.X
Keywords: Cc:

Description

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.

Attachments (3)

dump.sql.tar.gz (198.3 KB) - added by infinitynsk 9 years ago.
dump of test database
screenshot_01.png (670.0 KB) - added by pramsey 7 years ago.
boxes aren't actually contained
screenshot_02.png (540.6 KB) - added by pramsey 7 years ago.
point is actually only in one box

Download all attachments as: .zip

Change History (6)

Changed 9 years ago by infinitynsk

Attachment: dump.sql.tar.gz added

dump of test database

comment:1 Changed 8 years ago by robe

Milestone: PostGIS 1.5.3PostGIS 1.5.4

Changed 7 years ago by pramsey

Attachment: screenshot_01.png added

boxes aren't actually contained

Changed 7 years ago by pramsey

Attachment: screenshot_02.png added

point is actually only in one box

comment:2 Changed 7 years ago by pramsey

Resolution: invalid
Status: newclosed

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.

Note: See TracTickets for help on using tickets.