<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
Hi All,
<div><br>
</div>
<div>I am hoping someone here has experience with the </div>
<div>issue I am having…</div>
<div><br>
</div>
<div><br>
</div>
<div>I am using:</div>
<div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 10241357 Oct 8 22:04 <span style="color: #b12512">
<b>gdal-1.10.1.tar.gz</b></span></div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 1813726 Oct 8 22:04 <span style="color: #b12512">
<b>geos-3.4.2.tar.bz2</b></span></div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 340953 Oct 8 22:04 <span style="color: #b12512">
<b>json-c-0.9.tar.gz</b></span></div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 5161069 Oct 8 22:04 <span style="color: #b12512">
<b>libxml2-2.9.0.tar.gz</b></span></div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 16930885 Oct 8 22:04 <span style="color: rgb(177, 37, 18);">
<b>perl-5.16.3.tar.gz</b></span></div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 195442 Oct 8 22:04 <span style="color: #b12512">
<b>pgtap-0.94.0.tar.gz</b></span></div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 6518378 Oct 8 22:04 <span style="color: #b12512">
<b>postgis-2.1.0.tar.gz</b></span></div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 21865589 Oct 8 22:04 <span style="color: #b12512">
<b>postgresql-9.3.4.tar.gz</b></span></div>
<div style="margin: 0px; font-size: 13px; background-color: rgb(218, 218, 218);">
-rw-r--r-- 1 postgres postgres 785279 Oct 8 22:04 <span style="color: #b12512">
<b>proj-4.8.0.tar.gz</b></span></div>
</div>
<div><span style="color: #b12512"><b><br>
</b></span></div>
<div>I have a table that contains a geography column. </div>
<div>The column contains “multipolygon” values.</div>
<div><br>
</div>
<div>When I do a query that uses the ST_Intersects() function it </div>
<div>seems to return the wrong results if the underlying plan uses</div>
<div>the _st_distance() function and it returns the correct results if</div>
<div>the plan uses the st_intersects() function.</div>
<div><br>
</div>
<div>I am having the issue with a multipolygon column but it might</div>
<div>happen of other types as well.</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>Here is an small example of the issue:</div>
<div><br>
</div>
<div>
<div>=# create table multipolygon</div>
<div>(</div>
<div> mpid bigint not null,</div>
<div> multip Geography(multipolygon, 4326) not null</div>
<div>);</div>
<div>CREATE TABLE</div>
<div><br>
</div>
<div><br>
</div>
<div>=# alter table multipolygon add constraint PK_POLYGON primary key (mpid);</div>
<div>ALTER TABLE</div>
<div><br>
</div>
<div><br>
</div>
<div>Now I will add two small “rectangles” into the table. I don’t really need to</div>
<div>use multiple polygons to reproduce the issue so I will use a single polygon with</div>
<div>points to approximate lines of constant latitude (just trying to avoid the great</div>
<div>circle arc).</div>
<div><br>
</div>
<div>=# insert into multipolygon </div>
<div>values (1, ST_GeographyFromText('SRID=4326;multipolygon(((20 10, 21 10, 22 10, 23 10, 24 10, 25 10, 26 10, 27 10, 28 10, 29 10, 30 10, 30 20, 29 20, 28 20, 27 20, 26 20, 25 20, 24 20, 23 20, 22 20, 21 20, 20 20, 20 10)))'));</div>
<div>INSERT 0 1</div>
<div><br>
</div>
<div>=# insert into multipolygon</div>
<div>values (2, ST_GeographyFromText('SRID=4326;multipolygon(((25 15, 26 15, 27 15, 28 15, 29 15, 30 15, 31 15, 32 15, 33 15, 34 15, 35 15, 35 25, 34 25, 33 25, 32 25, 31 25, 30 25, 29 25, 28 25, 27 25, 26 25, 25 25, 25 15)))'));</div>
<div>INSERT 0 1</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>Now I run a query that should return both rows.</div>
<div><br>
</div>
<div><br>
</div>
<div>
<div>=# select m1.mpid from multipolygon m1 where st_intersects(m1.multip ,'POLYGON((27 17, 28 17, 28 19, 27 19, 27 17))'::geography);</div>
<div> mpid </div>
<div>------</div>
<div> 1</div>
<div>(1 row)</div>
<div><br>
</div>
<div>=# select m1.mpid from multipolygon m1 where st_intersects((select multip from multipolygon m2 where m2.mpid = m1.mpid ),'POLYGON((27 17, 28 17, 28 19, 27 19, 27 17))'::geography );</div>
<div> mpid </div>
<div>------</div>
<div> 1</div>
<div> 2</div>
<div>(2 rows)</div>
<div><br>
</div>
<div><br>
</div>
<div>You can see the two queries seem to be logically the same but they return different results</div>
<div>Next I will look at the plans for the two queries</div>
<div><br>
</div>
<div><br>
</div>
<div>=# explain select m1.mpid from multipolygon m1 where st_intersects(m1.multip ,'POLYGON((27 17, 28 17, 28 19, 27 19, 27 17))'::geography);</div>
<div>
QUERY PLAN
</div>
<div>-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</div>
<div> Seq Scan on multipolygon m1 (cost=0.00..317.40 rows=77 width=8)</div>
<div> Filter: ((multip && '0103000020E610000001000000050000000000000000003B4000000000000031400000000000003C4000000000000031400000000000003C4000000000000033400000000000003B4000000000000033400000000000003B400000000000003140'::geography) AND (_st_distance(multip,
'0103000020E610000001000000050000000000000000003B4000000000000031400000000000003C4000000000000031400000000000003C4000000000000033400000000000003B4000000000000033400000000000003B400000000000003140'::geography, 0::double precision, false) < 1e-05::double precision))</div>
<div>(2 rows)</div>
<div><br>
</div>
<div><br>
</div>
<div>=# explain select m1.mpid from multipolygon m1 where st_intersects((select multip from multipolygon m2 where m2.mpid = m1.mpid ),'POLYGON((27 17, 28 17, 28 19, 27 19, 27 17))'::geography );</div>
<div> QUERY PLAN </div>
<div>-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</div>
<div> Seq Scan on multipolygon m1 (cost=0.00..9788.80 rows=387 width=8)</div>
<div> Filter: st_intersects((SubPlan 1), '0103000020E610000001000000050000000000000000003B4000000000000031400000000000003C4000000000000031400000000000003C4000000000000033400000000000003B4000000000000033400000000000003B400000000000003140'::geography)</div>
<div> SubPlan 1</div>
<div> -> Index Scan using pk_polygon on multipolygon m2 (cost=0.15..8.17 rows=1 width=32)</div>
<div> Index Cond: (mpid = m1.mpid)</div>
<div>(5 rows)</div>
</div>
<div><br>
</div>
<div>You can see that the query using _st_distance is the one that returns a bad result.</div>
<div>However I can also show _st_distance() works if it is limited to finding just one</div>
<div>result. Here is the same query that didn’t work, however this time I add a constraint</div>
<div>to limit the result set to one row.</div>
<div><br>
</div>
<div><br>
</div>
<div>
<div>=# select m1.mpid from multipolygon m1 where st_intersects(m1.multip ,'POLYGON((27 17, 28 17, 28 19, 27 19, 27 17))'::geography) and m1.mpid = 1;</div>
<div> mpid </div>
<div>------</div>
<div> 1</div>
<div>(1 row)</div>
<div><br>
</div>
<div>=# select m1.mpid from multipolygon m1 where st_intersects(m1.multip ,'POLYGON((27 17, 28 17, 28 19, 27 19, 27 17))'::geography) and m1.mpid = 2;</div>
<div> mpid </div>
<div>------</div>
<div> 2</div>
<div>(1 row)</div>
<div><br>
</div>
<div>=# explain select m1.mpid from multipolygon m1 where st_intersects(m1.multip ,'POLYGON((27 17, 28 17, 28 19, 27 19, 27 17))'::geography) and m1.mpid = 2;</div>
<div>
QUERY PLAN
</div>
<div>-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</div>
<div> Index Scan using pk_polygon on multipolygon m1 (cost=0.15..8.43 rows=1 width=8)</div>
<div> Index Cond: (mpid = 2)</div>
<div> Filter: ((multip && '0103000020E610000001000000050000000000000000003B4000000000000031400000000000003C4000000000000031400000000000003C4000000000000033400000000000003B4000000000000033400000000000003B400000000000003140'::geography) AND (_st_distance(multip,
'0103000020E610000001000000050000000000000000003B4000000000000031400000000000003C4000000000000031400000000000003C4000000000000033400000000000003B4000000000000033400000000000003B400000000000003140'::geography, 0::double precision, false) < 1e-05::double precision))</div>
<div>(3 rows)</div>
</div>
<div><br>
</div>
<div><br>
</div>
</div>
<div>This might be the wrong conclusion but it appears that </div>
<div>_st_distance() doesn’t work if the result set contains more</div>
<div>than one row.</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>Thanks in advance,</div>
<div>Bob</div>
<div>
<div apple-content-edited="true"><span class="Apple-style-span" style="border-collapse: separate; font-family: Helvetica; border-spacing: 0px;"><tt><font size="2">-----------------------------------------------------------------<br>
Robert Hartranft<br>
Mission Operations and Services<br>
Intelligence & Information System<br>
Raytheon Company<br>
301-851-8197<br>
<a href="mailto:robert_m_hartranft@raytheon.com">robert_m_hartranft@raytheon.com</a><br>
robert.m.hartranft@nasa.gov<br>
5700 Rivertech Court<br>
Riverdale, MD 20737<br>
-----------------------------------------------------------------<br>
</font></tt></span></div>
<br>
</div>
</body>
</html>