<html><head><style>body{font-family:Helvetica,Arial;font-size:13px}</style></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Thanks Paul,</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">You’re absolutely right that the intersect query should always have returned touching polygons. I’m not sure how we were ever getting the desired results. That’s what I’m looking into now.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Interestingly, the critical difference seems to be the use of binary geometry.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">The land parcel geometry is passed into the URL, and used as the basis for the intersect query:</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">http://my-geoserver.example.com/geoserver/workspace/wfs?service=WFS&version=2.0.0&request=GetFeature&typeName=workspace:zones&outputFormat=json&cql_filter=INTERSECTS(geom,POLYGON((-37.79397716231438%20145.0752696285534,-37.79395266253229%20145.07506684589112,-37.79391636418757%20145.07507518332602,-37.79354819089359%20145.07515979038527,-37.79358130702303%20145.0753488489579,-37.79397716231438%20145.0752696285534)))</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Looking at my Postgres query logs, this generates the following query:</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="margin: 0px;"><div id="bloop_customfont" style="margin: 0px;">SELECT</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"feature_id",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"scheme_code",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"zone_status",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"zone_code",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"updated_at",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>encode(ST_AsEWKB("geom"),'base64') as "geom"</div><div id="bloop_customfont" style="margin: 0px;">FROM</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"public"."planning_zones"</div><div id="bloop_customfont" style="margin: 0px;">WHERE</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"geom" && ST_GeomFromWKB('\x00000000030000000100000006406222689bda209bc042e5a10b2cf4a040622266f29603b8c042e5a03da812fc406222670412240cc042e59f0d29f8f040622267b58135bac042e592fcb3d80e4062226941fd4218c042e594128030c0406222689bda209bc042e5a10b2cf4a0', 4326)</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>AND ST_Intersects("geom", ST_GeomFromWKB('\x00000000030000000100000006406222689bda209bc042e5a10b2cf4a040622266f29603b8c042e5a03da812fc406222670412240cc042e59f0d29f8f040622267b58135bac042e592fcb3d80e4062226941fd4218c042e594128030c0406222689bda209bc042e5a10b2cf4a0', 4326)) LIMIT 1000000;</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">This query provides the current behaviour - returning a single zone polygon that intersects with the parcel polygon.</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">However, if I take that same polygon string, and run the query using ST_AsText:</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;"><div id="bloop_customfont" style="margin: 0px;">SELECT</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"feature_id",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"scheme_code",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"zone_status",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"zone_code",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"updated_at",</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>encode(ST_AsEWKB("geom"),'base64') as "geom"</div><div id="bloop_customfont" style="margin: 0px;">FROM</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"public"."planning_zones"</div><div id="bloop_customfont" style="margin: 0px;">WHERE</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>"geom" && ST_GeomFromText('POLYGON((-37.79397716231438 145.0752696285534,-37.79395266253229 145.07506684589112,-37.79391636418757 145.07507518332602,-37.79354819089359 145.07515979038527,-37.79358130702303 145.0753488489579,-37.79397716231438 145.0752696285534))', 4326)</div><div id="bloop_customfont" style="margin: 0px;"><span class="Apple-tab-span" style="white-space:pre"> </span>AND ST_Intersects("geom", (ST_GeomFromText('POLYGON((-37.79397716231438 145.0752696285534,-37.79395266253229 145.07506684589112,-37.79391636418757 145.07507518332602,-37.79354819089359 145.07515979038527,-37.79358130702303 145.0753488489579,-37.79397716231438 145.0752696285534))', 4326))) LIMIT 1000000;</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">Then I get zero results.</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">The parcel geometry in the URL comes from the same database. If I rewrite this query to use a spatial join, as detailed in my previous email, I get 3 intersecting zone polygons, which sounds like the correct behaviour.</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">Is there a tolerance / precision difference between text and binary geometry?</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">I’m now looking for workarounds or solutions - stop me if I’m heading in the wrong direction:</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">- workaround: take the zone polygon with the largest intersecting area</div><div id="bloop_customfont" style="margin: 0px;">- workaround: filter out polygons below an arbitrary intersection area (i.e. anything with <1m2 area isn’t useful)</div><div id="bloop_customfont" style="margin: 0px;">- solution: snap my zone polygons to my parcel polygon, and filter out results that only share a border</div><div id="bloop_customfont" style="margin: 0px;"> - I’m not experienced in doing this</div><div id="bloop_customfont" style="margin: 0px;"> - I’m experimenting with ST_Snap and filtering out ST_Touches, but it still returning the same 3 results </div></div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">Thanks for all the help</div></div> <br> <div id="bloop_sign_1464138985676204800" class="bloop_sign"><div><!--?xml version="1.0" encoding="UTF-8" standalone="no"?-->
<font face="Georgia"><span style="line-height: normal;">▬▬▬ι═══════</span></font><span style="font-family: Georgia; line-height: normal;">═</span><span style="line-height: normal; font-family: Georgia;">═══ﺤ</span></div><div><font face="Georgia"><span style="line-height: normal;">Timothy Asquith // Red Ronin</span><br style="line-height: normal;"><a href="mailto:tim@redronin.io" style="line-height: normal;">tim@redronin.io</a><br style="line-height: normal;"><a href="http://www.redronin.io/" style="line-height: normal;">www.redronin.io</a></font></div></div> <br><p class="airmail_on">On 25 May 2016 at 5:00:52 AM, postgis-users-request@lists.osgeo.org (<a href="mailto:postgis-users-request@lists.osgeo.org">postgis-users-request@lists.osgeo.org</a>) wrote:</p> <blockquote type="cite" class="clean_bq"><span><div><span style="color: rgb(0, 0, 0); font-family: 'helvetica Neue', helvetica; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;">Re: ST_Intersects differing results</span></div></span></blockquote></body></html>