<html><body><div style="color:#000; background-color:#fff; font-family:garamond, new york, times, serif;font-size:14px">Hugues:<br><div id="yui_3_16_0_1_1420727124710_16785">  Thanks for the info!  I think I'm getting close.  Now it seems to work ~sometimes~.   It will error if any rows would be returned.  But it will not throw an error if no rows would be returned.   I verified with a few queries that I know the results beforehand, and was able to determine this to be the case.  This one fails (with the following $jsonPoly):</div><div id="yui_3_16_0_1_1420727124710_16786"><br></div><div id="yui_3_16_0_1_1420727124710_11244">{"type":"MultiPolygon","coordinates":[[[[-101.426597595,42.0922126770001],[-101.415992737,42.092113495],[-101.410797119,42.092113495],[-101.402198792,42.092010498],[-101.375396729,42.0918121340001],[-101.368797302,42.092010498],[-101.364700317,42.092010498],[-101.350196838,42.0918121340001],[-101.345092773,42.0917129520001],[-101.322799683,42.09161377],[-101.280296326,42.0914115910001],[-101.256095886,42.091213226],[-101.250396729,42.091110229],[-101.23299408,42.091213226],[-101.217895508,42.091213226],[-101.212898254,42.091110229],[-101.188499451,42.0911140440001],[-101.176696777,42.0909118650001],[-101.173599243,42.0909118650001],[-101.163192749,42.090713501],[-101.144294739,42.0904121400001],[-101.125396729,42.0904121400001],[-101.117195129,42.090213776],[-101.108299255,42.090110779],[-101.103500366,42.090110779],[-101.100593567,42.0900115970001],[-101.094894409,42.0899124150001],[-101.072097778,42.0899124150001],[-101.043800354,42.089813232],[-101.036499023,42.089813232],[-101.022399902,42.0897140500001],[-101.014297485,42.089710236],[-101.000396729,42.0891113280001],[-100.939498901,42.0891113280001],[-100.91469574,42.0890121520001],[-100.888595581,42.0890121460001],[-100.875396729,42.08821106],[-100.846099854,42.08821106],[-100.846099854,42.000713348],[-100.845596313,41.9764137270001],[-100.845298767,41.9737129210001],[-100.844398499,41.963012695],[-100.84349823,41.912513733],[-100.843795776,41.8877105710001],[-100.843696597,41.8750114440001],[-100.843696597,41.8506126400001],[-100.843795776,41.838710785],[-100.843795776,41.805812836],[-100.843696594,41.783210754],[-100.842895508,41.757511139],[-100.842498779,41.7500114440001],[-100.842498779,41.7404136660001],[-100.857192993,41.7404136660001],[-100.876296997,41.7399139400001],[-100.908195496,41.7399139400001],[-100.926498413,41.740013123],[-100.973098755,41.740013123],[-101.000396729,41.7407112120001],[-101.037094116,41.7411117550001],[-101.048095703,41.7412109370001],[-101.076194763,41.7415122990001],[-101.109397888,41.741912842],[-101.125900269,41.742412567],[-101.179397583,41.742412567],[-101.215499878,41.7426109310001],[-101.250900269,41.7425117490001],[-101.319793701,41.7430114750001],[-101.332099915,41.743110657],[-101.339096069,41.743110657],[-101.342796326,41.743213654],[-101.36289978,41.7434120180001],[-101.370498657,41.743610382],[-101.373497009,41.743610382],[-101.375495911,41.7434120180001],[-101.401397705,41.743213654],[-101.406295776,41.743213654],[-101.426300049,41.7430114750001],[-101.426094055,41.7509117130001],[-101.426193237,41.782711029],[-101.426193237,41.820613861],[-101.426300049,41.8295135500001],[-101.426300049,41.8649139400001],[-101.426094055,41.8759117130001],[-101.426094055,41.8868103030001],[-101.425994873,41.9016113280001],[-101.425994873,41.911613464],[-101.426193237,41.9231109620001],[-101.426193237,41.931812286],[-101.426094055,41.9492111210001],[-101.426094055,42.0000114440001],[-101.426696777,42.0062103270001],[-101.426498413,42.0321121220001],[-101.426300049,42.060012817],[-101.426193237,42.0711135860001],[-101.426300049,42.0789108280001],[-101.426597595,42.0922126770001]]]]}<br style="" class=""><br></div><div id="yui_3_16_0_1_1420727124710_11220"><span id="yui_3_16_0_1_1420727124710_11286">DBD::Pg::st execute failed: ERROR:  Operation on mixed SRID geometries at ./alertPoly2.cgi line 109.<br style="" class="">DBD::Pg::st execute failed: ERROR:  Operation on mixed SRID geometries at ./alertPoly2.cgi line 109.<br style="" class=""></span></div><div id="yui_3_16_0_1_1420727124710_11490"><br><span id="yui_3_16_0_1_1420727124710_11286"></span></div><div id="yui_3_16_0_1_1420727124710_11487" dir="ltr"><span id="yui_3_16_0_1_1420727124710_11286">(line 109 is the: $sth->execute();  )</span></div><div id="yui_3_16_0_1_1420727124710_11489" dir="ltr"><br></div><div id="yui_3_16_0_1_1420727124710_18380" dir="ltr">The above query should return some rows.</div><div id="yui_3_16_0_1_1420727124710_18381" dir="ltr"><br></div><div id="yui_3_16_0_1_1420727124710_18382" dir="ltr">I can provide some geoJSON which doesn't throw an error, but I don't think that is the problem.  There's maybe something still wrong with the syntax.</div><div dir="ltr"><br></div><div dir="ltr">Thanks again for your help.  I think it is very close!</div><div dir="ltr"><br></div><div id="yui_3_16_0_1_1420727124710_18824" dir="ltr">Regards,</div><div id="yui_3_16_0_1_1420727124710_19337" dir="ltr">Joseph Spenner</div><div id="yui_3_16_0_1_1420727124710_18383" dir="ltr"><br><span id="yui_3_16_0_1_1420727124710_11286"></span></div><div id="yui_3_16_0_1_1420727124710_17734" dir="ltr"><span id="yui_3_16_0_1_1420727124710_11286"></span></div><div id="yui_3_16_0_1_1420727124710_11131"> <br><br><div>
<br></div></div><br>  <div id="yui_3_16_0_1_1420727124710_11231" style="font-family: garamond, new york, times, serif; font-size: 14px;"> <div id="yui_3_16_0_1_1420727124710_11230" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1420727124710_11229" dir="ltr"> <hr size="1">  <font face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> Hugues François <hugues.francois@irstea.fr><br> <b><span style="font-weight: bold;">To:</span></b> Joseph Spenner <joseph85750@yahoo.com>; PostGIS Users Discussion <postgis-users@lists.osgeo.org> <br> <b><span style="font-weight: bold;">Sent:</span></b> Thursday, January 8, 2015 1:44 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax<br> </font> </div> <div id="yui_3_16_0_1_1420727124710_11236" class="y_msg_container"><br><div id="yiv2363559141"><style>#yiv2363559141 #yiv2363559141 --
 
 _filtered #yiv2363559141 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;}
 _filtered #yiv2363559141 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;}
 _filtered #yiv2363559141 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
 _filtered #yiv2363559141 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}
 _filtered #yiv2363559141 {font-family:Garamond;panose-1:2 2 4 4 3 3 1 1 8 3;}
#yiv2363559141  
#yiv2363559141 p.yiv2363559141MsoNormal, #yiv2363559141 li.yiv2363559141MsoNormal, #yiv2363559141 div.yiv2363559141MsoNormal
        {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}
#yiv2363559141 a:link, #yiv2363559141 span.yiv2363559141MsoHyperlink
        {color:blue;text-decoration:underline;}
#yiv2363559141 a:visited, #yiv2363559141 span.yiv2363559141MsoHyperlinkFollowed
        {color:purple;text-decoration:underline;}
#yiv2363559141 p.yiv2363559141MsoAcetate, #yiv2363559141 li.yiv2363559141MsoAcetate, #yiv2363559141 div.yiv2363559141MsoAcetate
        {margin:0cm;margin-bottom:.0001pt;font-size:8.0pt;}
#yiv2363559141 span.yiv2363559141EmailStyle17
        {color:#1F497D;}
#yiv2363559141 span.yiv2363559141TextedebullesCar
        {}
#yiv2363559141 .yiv2363559141MsoChpDefault
        {font-size:10.0pt;}
 _filtered #yiv2363559141 {margin:70.85pt 70.85pt 70.85pt 70.85pt;}
#yiv2363559141 div.yiv2363559141WordSection1
        {}
#yiv2363559141 </style><div id="yui_3_16_0_1_1420727124710_11235"><div id="yui_3_16_0_1_1420727124710_11234" class="yiv2363559141WordSection1"><div id="yui_3_16_0_1_1420727124710_11233" class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;">Hi Joseph,</span></div><div id="yui_3_16_0_1_1420727124710_11237" class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;">  </span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">If you take a look at the error returned, you can read that the problem comes from the ST_Intersects function which works with geometries but not with text. I think, if your geoJSON is right formatted, you can just remove the ST_AsText part and it should work.</span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">  </span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">However it also seems that your where clause is not written correctly you can just write your query like that :</span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">  </span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">select ST_Asgeojson( geom ) from polys</span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">where ST_Intersects(ST_GeomFromGeoJSON('$jsonPoly'), geom);</span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">  </span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">HTH</span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">  </span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;" lang="EN-GB">Hug</span></div><div class="yiv2363559141MsoNormal"><span style="font-size:11.0pt;">  </span></div><div><div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm;"><div class="yiv2363559141MsoNormal"><b><span style="font-size:10.0pt;">De :</span></b><span style="font-size:10.0pt;"> postgis-users-bounces@lists.osgeo.org [mailto:postgis-users-bounces@lists.osgeo.org] <b>De la part de</b> Joseph Spenner<br clear="none"><b>Envoyé :</b> mercredi 7 janvier 2015 17:30<br clear="none"><b>À :</b> PostGIS Users Discussion<br clear="none"><b>Objet :</b> Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax</span></div></div></div><div class="yiv2363559141MsoNormal">  </div><div class="qtdSeparateBR"><br><br></div><div class="yiv2363559141yqt8146304217" id="yiv2363559141yqt00270"><div><div id="yiv2363559141yui_3_16_0_1_1420642633324_35230"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">More info.  Here are my 2 select statements.</span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_35229"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">This one works:</span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_35228"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34626"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">$sth = $dbh->prepare("select ST_Asgeojson( geom ) from polys where ST_Intersects(ST_GeomFromText('POLYGON(($textPoly, $closingLon $closingLat))', 4269), geom)=TRUE");</span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34630"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34631"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34632"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">However, below is my new one, which I can't get to work:</span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34633"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34609"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">$sth = $dbh->prepare("select ST_Asgeojson( geom ) from polys where ST_Intersects(ST_AsText(ST_GeomFromGeoJSON('$jsonPoly')), geom)=TRUE");<br clear="none"><br clear="none"></span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34646"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;"><br clear="none"><br clear="none"></span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34724"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;"><br clear="none"><br clear="none"></span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34725"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">When I run with my new select statement, I get errors:</span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34726"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34957"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">-----<br clear="none"><br clear="none"></span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34727"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">DBD::Pg::st execute failed: ERROR:  function st_intersects(text, geometry) is not unique<br clear="none">LINE 1: ...ertseverity,ST_Asgeojson( geom ) from polys where ST_Interse...<br clear="none">                                                             ^<br clear="none">HINT:  Could not choose a best candidate function. You might need to add explicit type casts. at ./alertPoly2.cgi line 103.<br clear="none">DBD::Pg::st execute failed: ERROR:  function st_intersects(text, geometry) is not unique<br clear="none">LINE 1: ...ertseverity,ST_Asgeojson( geom ) from polys where ST_Interse...<br clear="none">                                                             ^<br clear="none">HINT:  Could not choose a best candidate function. You might need to add explicit type casts. at ./alertPoly2.cgi line 103.<br clear="none">------</span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34959"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;"><br clear="none"><br clear="none"></span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34961"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">Any help would be great.</span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_35094"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_35095"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">Thanks!</span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_35321"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34612"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34625"><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div></div></div><div class="yiv2363559141MsoNormal" style="background:white;"><span style="font-size:10.5pt;">  </span></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34619"><div id="yiv2363559141yui_3_16_0_1_1420642633324_34618"><div id="yiv2363559141yui_3_16_0_1_1420642633324_34622"><div class="yiv2363559141MsoNormal" style="text-align:center;background:white;" align="center"><span style=""></span><hr align="center" size="1" width="100%"></div><div class="yiv2363559141MsoNormal" style="background:white;"><b><span style="font-size:10.0pt;">From:</span></b><span style="font-size:10.0pt;"> Hugues François <<a rel="nofollow" shape="rect" ymailto="mailto:hugues.francois@irstea.fr" target="_blank" href="mailto:hugues.francois@irstea.fr">hugues.francois@irstea.fr</a>><br clear="none"><b>To:</b> Joseph Spenner <<a rel="nofollow" shape="rect" ymailto="mailto:joseph85750@yahoo.com" target="_blank" href="mailto:joseph85750@yahoo.com">joseph85750@yahoo.com</a>>; PostGIS Users Discussion <<a rel="nofollow" shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>> <br clear="none"><b>Sent:</b> Wednesday, January 7, 2015 1:17 AM<br clear="none"><b>Subject:</b> Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax</span><span style=""></span></div></div><div id="yiv2363559141yui_3_16_0_1_1420642633324_34617"><div class="yiv2363559141MsoNormal" style="margin-bottom:12.0pt;background:white;"><span style=""><br clear="none">Hello,<br clear="none"><br clear="none">I'm not very familiar with the use of ST_GeomFromGeoJSON() but looking at the doc, it only accepts a single json string so you'd better remove all the ST_GeomFromText stuff: ST_GeomFromGeoJSON($jsonPolys). There are some examples at the bottom of the page <a rel="nofollow" shape="rect" target="_blank" href="http://www.postgis.org/docs/ST_GeomFromGeoJSON.html">http://www.postgis.org/docs/ST_GeomFromGeoJSON.html</a>.<br clear="none"><br clear="none">HTH<br clear="none"><br clear="none">Hugues.<br clear="none"><br clear="none"><br clear="none">-------- Message d'origine--------<br clear="none">De: <a rel="nofollow" shape="rect" ymailto="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank" href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a> de la part de Joseph Spenner<br clear="none">Date: mar. 06/01/2015 20:31<br clear="none">À: PostGIS Users Discussion<br clear="none">Objet : [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax<br clear="none"><br clear="none">Hello,I currently have a select which works correctly:<br clear="none">$sth = $dbh->prepare("select ST_Asgeojson( geom ) from polys where ST_Intersects(ST_GeomFromText('POLYGON(($textPoly, $closingLon $closingLat))', 4269), geom)=TRUE");<br clear="none"><br clear="none"><br clear="none">I'm trying to do the same thing, using FromGeoJSON instead of Text.  Below is my attempt, but it's not working.  This is my statement, where $jsonPolys contains the JSON:<br clear="none">$sth = $dbh->prepare("select ST_Asgeojson( geom ) from polys where ST_Intersects(ST_GeomFromGeoJSON('POLYGON(($jsonPolys))', 4269), geom)=TRUE");<br clear="none"><br clear="none"> I think the problem has something to do with the POLYGON text, since it may not be necessary.  But I can't get the syntax right.<br clear="none">Any ideas?<br clear="none">Thanks!<br clear="none">Regards,Joseph Spenner<br clear="none"><br clear="none"></span></div></div></div></div></div></div></div></div></div><br><br></div> </div> </div>  </div></body></html>