<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>It’s hard to tell from below if there is an issue or not because something intersects in planar space or spheroidal space doesn’t guarantee the other<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Especially when you are talking about fairly large areas.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Can you provide a sample of the data set you are working with and I can test with a newer version of PostGIS.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>We have run into issues with geography in the past and usually the culprit is tolerance or some issue with distancetree vs. uncached answers.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So one way to check is to compare what ST_Distance, _<i>ST</i>_DistanceTree and _<i>ST</i>_DistanceUncached return as answers. They should all return the same answer.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Geography ST_Intersects just uses Distance with a very small tolerance as discussed in these tickets, https://trac.osgeo.org/postgis/ticket/4223 , <a href="https://trac.osgeo.org/postgis/ticket/4932">https://trac.osgeo.org/postgis/ticket/4932</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>All which have been fixed since your release version.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Geometry on the other hand uses DE9I matrix - <a href="https://postgis.net/docs/using_postgis_query.html#DE-9IM">https://postgis.net/docs/using_postgis_query.html#DE-9IM</a> but that doesn’t mean the answer geometry gives you is right either since it is assuming straight lines. One other option to confirm is to transform your data to a suitable space preserving California planar (meter or foot) spatial ref sys that covers your fload zone and confirm the answers are still the same.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Claire Palmer<br><b>Sent:</b> Saturday, July 1, 2023 7:18 PM<br><b>To:</b> postgis-users@lists.osgeo.org<br><b>Subject:</b> [postgis-users] ST_Intersects on geography in WHERE clause returns wrong results<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Dear PostGIS users,<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal>I am running a simple point-in-polygon analysis on Orange County flood zone polygons. I noticed that I'm getting some false positives when running ST_Intersects on geography in the where clause. <o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>My postgis_full_version is:<o:p></o:p></p></div><div><p class=MsoNormal>POSTGIS-"2.4.3 R16312" PGSQL=100 GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20 GDAL_DATA not found" LIBXML="2.7.8" LIBJSON="0.12" LIBROTOBUF="1.2.1" RASTER<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>The following query should return one flood zone, but instead it's returning two:<o:p></o:p></p></div><div><p class=MsoNormal><span style='font-family:"Courier New"'>SELECT gid, fld_zone, ST_Intersects(geog_nad83, 'SRID=4269;POINT(-117.876941 33.644253)'::geography)<br>FROM nfhl.orange_county_s_fld_haz_ar<br>WHERE ST_Intersects(geog_nad83, 'SRID=4269;POINT(-117.876941 33.644253)'::geography);</span><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>It's selecting the polygon the point intersects with, and also the adjacent polygon.<o:p></o:p></p></div><div><p class=MsoNormal>Here's a visual of the two polygons together with the test point:<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>If I remove the where clause and query ST_Intersects for the adjacent polygon, the result is correctly FALSE:<o:p></o:p></p></div><div><div><p class=MsoNormal><span style='font-family:"Courier New"'>SELECT gid, fld_zone, ST_Intersects(geog_nad83, 'SRID=4269;POINT(-117.876941 33.644253)'::geography)<br>FROM nfhl.orange_county_s_fld_haz_ar<br>WHERE gid = 224482;</span><o:p></o:p></p></div></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>To test further, I generated 1000 points on a 200 meter buffer around the test point. The results can be seen in the following visual. Some points return only the polygon they intersect with (grey, green) but others return an additional adjacent polygon (blue, red):<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal> <o:p></o:p></p></div><div><p class=MsoNormal>Also, I should note that running the same query on geometry does not introduce any errors. We'd like to stick with using geography if possible and were a bit surprised with these false positives.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Does anyone know what's going on? Does it have to do with the earlier PostGIS version? We are planning an upgrade to the latest version, but our API is stuck for now with the version I posted above. <o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Any insight as to why the false positives are occurring would be appreciated!<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thanks,<o:p></o:p></p></div><div><p class=MsoNormal>Claire<o:p></o:p></p></div></div></div></div></body></html>