<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body dir="auto">
<div>I've just been following this board long enough to have seen others ask the same. Ah, spherical trig... .</div>
<div><br>
</div>
<div>Best,</div>
<div>Steve<br>
<br>
<br>
</div>
<div><br>
On Feb 19, 2013, at 12:07 PM, "Jensen, Layne K CIV SPAWARSYSCEN-PACIFIC, 56210" <<a href="mailto:layne.jensen@navy.mil">layne.jensen@navy.mil</a>> wrote:<br>
<br>
</div>
<blockquote type="cite">
<div>
<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="cid:" 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>
<a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a> [<a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a>] on behalf of Jensen, Layne K CIV SPAWARSYSCEN-PACIFIC, 56210
[<a href="mailto:layne.jensen@navy.mil">layne.jensen@navy.mil</a>]<br>
<b>Sent:</b> Tuesday, February 19, 2013 10:25 AM<br>
<b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><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>
</div>
</blockquote>
<blockquote type="cite">
<div><span>_______________________________________________</span><br>
<span>postgis-users mailing list</span><br>
<span><a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a></span><br>
<span><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></span><br>
</div>
</blockquote>
</body>
</html>