<HTML dir=ltr><HEAD></HEAD>
<BODY ocsi="0" fpstyle="1">
<DIV dir=ltr id=idOWAReplyText88898>
<DIV dir=ltr><FONT color=#000000 size=2 face=Arial>Steve,</FONT></DIV>
<DIV dir=ltr><FONT size=2 face=Arial></FONT> </DIV>
<DIV dir=ltr><FONT size=2 face=Arial>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.</FONT></DIV>
<DIV dir=ltr><FONT size=2 face=Arial></FONT> </DIV>
<DIV dir=ltr><FONT size=2 face=Arial>Layne</FONT></DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> Stephen V. Mather<BR><B>Sent:</B> Tue 2/19/2013 7:46 AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Perplexed: ST_Intersects with Geography, odd results<BR></FONT><BR></DIV>
<DIV dir=ltr>
<DIV style="FONT-FAMILY: Tahoma; DIRECTION: ltr; COLOR: #000000; FONT-SIZE: 10pt">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.:
<DIV><BR></DIV>
<DIV><A href="http://blog.opengeo.org/2012/04/30/the-earth-is-not-flat-volume-2/" target=_blank>http://blog.opengeo.org/2012/04/30/the-earth-is-not-flat-volume-2/</A></DIV>
<DIV><BR></DIV>
<DIV>Best,</DIV>
<DIV>Steve<BR>
<DIV><BR>
<DIV style="FONT-FAMILY: Tahoma; FONT-SIZE: 13px">
<DIV style="FONT-FAMILY: Tahoma; FONT-SIZE: 13px">
<DIV class=WordSection1>
<P class=MsoNormal><IMG hspace=12 alt=http://sig.cmparks.net/cmp-ms-90x122.png align=left src="" width=90 height=122> <SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: #006c56; FONT-SIZE: 14pt">Stephen V. Mather<BR></SPAN><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: #006c56; FONT-SIZE: 11pt">GIS Manager<BR></SPAN><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: #006c56; FONT-SIZE: 9pt">(216) 635-3243 (Work) </SPAN><SPAN style="FONT-FAMILY: 'Calibri','sans-serif'; COLOR: #1f497d; FONT-SIZE: 11pt"><A href="http://www.clemetparks.com/" target=_blank><SPAN><BR>clevelandmetroparks.com</SPAN></A></SPAN></P>
<P class=MsoNormal></P><BR></DIV>
<DIV class=WordSection1><BR></DIV>
<DIV class=WordSection1><BR></DIV></DIV>
<DIV></DIV></DIV></DIV>
<DIV style="FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 16px">
<HR tabIndex=-1>
<DIV style="DIRECTION: ltr" id=divRpF795421><FONT color=#000000 size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org [postgis-users-bounces@lists.osgeo.org] on behalf of Jensen, Layne K CIV SPAWARSYSCEN-PACIFIC, 56210 [layne.jensen@navy.mil]<BR><B>Sent:</B> Tuesday, February 19, 2013 10:25 AM<BR><B>To:</B> postgis-users@lists.osgeo.org<BR><B>Subject:</B> [postgis-users] Perplexed: ST_Intersects with Geography, odd results<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>
<DIV><FONT color=#000000 size=2 face=Arial></FONT> </DIV><FONT color=#000000 size=2 face=Arial>
<P style="BORDER-BOTTOM: 0px; TEXT-ALIGN: left; BORDER-LEFT: 0px; PADDING-BOTTOM: 0px; WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; MARGIN: 0px 0px 1em; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT: 14px/18px 'Helvetica Neue',Helvetica,Arial,sans-serif; WORD-WRAP: break-word; WHITE-SPACE: normal; ORPHANS: 2; LETTER-SPACING: normal; COLOR: rgb(51,51,51); CLEAR: both; VERTICAL-ALIGN: baseline; BORDER-TOP: 0px; BORDER-RIGHT: 0px; WORD-SPACING: 0px; PADDING-TOP: 0px">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.</P>
<P style="BORDER-BOTTOM: 0px; TEXT-ALIGN: left; BORDER-LEFT: 0px; PADDING-BOTTOM: 0px; WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; MARGIN: 0px 0px 1em; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT: 14px/18px 'Helvetica Neue',Helvetica,Arial,sans-serif; WORD-WRAP: break-word; WHITE-SPACE: normal; ORPHANS: 2; LETTER-SPACING: normal; COLOR: rgb(51,51,51); CLEAR: both; VERTICAL-ALIGN: baseline; BORDER-TOP: 0px; BORDER-RIGHT: 0px; WORD-SPACING: 0px; PADDING-TOP: 0px">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.</P><PRE style="BORDER-BOTTOM: 0px; TEXT-ALIGN: left; BORDER-LEFT: 0px; PADDING-BOTTOM: 5px; WIDOWS: 2; TEXT-TRANSFORM: none; BACKGROUND-COLOR: rgb(238,238,238); TEXT-INDENT: 0px; MARGIN: 0px 0px 10px; PADDING-LEFT: 5px; WIDTH: auto; PADDING-RIGHT: 5px; FONT: 12px/18px Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif; ORPHANS: 2; LETTER-SPACING: normal; COLOR: rgb(51,51,51); MAX-HEIGHT: 600px; VERTICAL-ALIGN: baseline; OVERFLOW: auto; BORDER-TOP: 0px; BORDER-RIGHT: 0px; WORD-SPACING: 0px; PADDING-TOP: 5px"><CODE style="BORDER-BOTTOM: 0px; BORDER-LEFT: 0px; PADDING-BOTTOM: 0px; BACKGROUND-COLOR: rgb(238,238,238); MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; FONT-FAMILY: Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif; COLOR: rgb(34,34,34); FONT-SIZE: 12px; VERTICAL-ALIGN: baseline; BORDER-TOP: 0px; BORDER-RIGHT: 0px; PADDING-TOP: 0px">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);</CODE></PRE>
<DIV><SPAN style="TEXT-ALIGN: left; WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; DISPLAY: inline !important; FONT: 14px/18px 'Helvetica Neue',Helvetica,Arial,sans-serif; WHITE-SPACE: normal; ORPHANS: 2; FLOAT: none; LETTER-SPACING: normal; COLOR: rgb(51,51,51); WORD-SPACING: 0px">If we convert to GEOMETRY rather than GEOGRAPHY, it performs correctly. Can anyone shed light on what we are doing wrong?</SPAN></DIV>
<DIV><SPAN style="TEXT-ALIGN: left; WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; DISPLAY: inline !important; FONT: 14px/18px 'Helvetica Neue',Helvetica,Arial,sans-serif; WHITE-SPACE: normal; ORPHANS: 2; FLOAT: none; LETTER-SPACING: normal; COLOR: rgb(51,51,51); WORD-SPACING: 0px"></SPAN> </DIV></FONT>
<DIV><FONT color=#000000 size=2 face=Arial><SPAN style="TEXT-ALIGN: left; WIDOWS: 2; TEXT-TRANSFORM: none; TEXT-INDENT: 0px; DISPLAY: inline !important; FONT: 14px/18px 'Helvetica Neue',Helvetica,Arial,sans-serif; WHITE-SPACE: normal; ORPHANS: 2; FLOAT: none; LETTER-SPACING: normal; COLOR: rgb(51,51,51); WORD-SPACING: 0px">Thank you!</SPAN></FONT></DIV></DIV></DIV></DIV></DIV></DIV></BODY></HTML>