Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am using PostgreSQL version 9.2.2 with PostGIS 2.0.2 (or just 2.0, not sure), and need to do simple queries to find points that 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 I convert to GEOMETRY rather than GEOGRAPHY, it performs correctly. Am I mis-using something?

share|improve this question
    
Quick note, to find your exact version you can use SELECT postgis_version(); or to find your version and the versions of all of PostGIS's dependencies you can use SELECT postgis_full_version(); –  HeyOverThere Feb 13 '13 at 19:17
add comment

1 Answer

What you think is the right answer may not be the right answer. You're using geography, so you're on the sphere now, not the plane.

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.