<html><body><div style="color:#000; background-color:#fff; font-family:garamond, new york, times, serif;font-size:14px"><div id="yui_3_16_0_1_1420727124710_71848"><div dir="ltr">Hugues:</div><div id="yui_3_16_0_1_1420727124710_71887" dir="ltr"> Yes, that was it. When I initially created this column, I used 4269. I was following an example, and this is what was used.</div><div id="yui_3_16_0_1_1420727124710_71888" dir="ltr">I recreated the table using the default (not specifying) and did the same with my query. This solved my issue!</div><div id="yui_3_16_0_1_1420727124710_71889" dir="ltr"><br></div><div id="yui_3_16_0_1_1420727124710_71890" dir="ltr">Thanks for your help! Hopefully I won't have any issues by omitting the SRID.</div><div id="yui_3_16_0_1_1420727124710_71891" dir="ltr"><br></div><div id="yui_3_16_0_1_1420727124710_72141" dir="ltr">Regards,</div><div id="yui_3_16_0_1_1420727124710_72142" dir="ltr">Joseph Spenner</div><div dir="ltr"><br> </div></div><div id="yui_3_16_0_1_1420727124710_72146" style="font-family: garamond, new york, times, serif; font-size: 14px;"> <div id="yui_3_16_0_1_1420727124710_72145" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1420727124710_72144" dir="ltr"> <hr size="1"> <font id="yui_3_16_0_1_1420727124710_72143" 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> joseph85750@yahoo.com; postgis-users@lists.osgeo.org <br> <b><span style="font-weight: bold;">Sent:</span></b> Thursday, January 8, 2015 8:35 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_72147" class="y_msg_container"><br>Hi,<br clear="none"><br clear="none">You're using different srid between your geometries. It means you're using different coordinate systems. I don't know how this thing is managed by geoJSON but if your table uses a single srid (you can ensure that by using type in postgis 2 or constraint in pgis 1.5) you can use st_transform:<br clear="none"><br clear="none">ST_Intersects(ST_Transform(ST_GeomFromGeoJSON($var),XXXX),geom)<br clear="none"><br clear="none">Where XXXX is the SRID you want to use. <br clear="none"><br clear="none">Regards,<br clear="none"><br clear="none">Hug<br clear="none"><br clear="none">-------- Original Message --------<br clear="none">From: Joseph Spenner <<a shape="rect" ymailto="mailto:joseph85750@yahoo.com" href="mailto:joseph85750@yahoo.com">joseph85750@yahoo.com</a>><br clear="none">Sent: Thursday, January 8, 2015 04:06 PM<br clear="none">To: PostGIS Users Discussion <<a shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br clear="none">Subject: Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax<br clear="none"><br clear="none">Hugues:<br clear="none"> 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):<br clear="none">{"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 clear="none"><br clear="none">DBD::Pg::st execute failed: ERROR: Operation on mixed SRID geometries at ./alertPoly2.cgi line 109.<br clear="none">DBD::Pg::st execute failed: ERROR: Operation on mixed SRID geometries at ./alertPoly2.cgi line 109.<br clear="none"><br clear="none">(line 109 is the: $sth->execute(); )<br clear="none">The above query should return some rows.<br clear="none">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.<br clear="none">Thanks again for your help. I think it is very close!<br clear="none">Regards,Joseph Spenner<br clear="none"> <br clear="none"><br clear="none"><br clear="none"><br clear="none"> From: Hugues François <<a shape="rect" ymailto="mailto:hugues.francois@irstea.fr" href="mailto:hugues.francois@irstea.fr">hugues.francois@irstea.fr</a>><br clear="none"> To: Joseph Spenner <<a shape="rect" ymailto="mailto:joseph85750@yahoo.com" href="mailto:joseph85750@yahoo.com">joseph85750@yahoo.com</a>>; PostGIS Users Discussion <<a shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>> <br clear="none"> Sent: Thursday, January 8, 2015 1:44 AM<br clear="none"> Subject: Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax<br clear="none"> <br clear="none">#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 Hi Joseph, 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. However it also seems that your where clause is not written correctly you can just write your query like that : select ST_Asgeojson( geom ) from polyswhere ST_Intersects(ST_GeomFromGeoJSON('$jsonPoly'), geom); HTH Hug De : <a shape="rect" ymailto="mailto:postgis-users-bounces@lists.osgeo.org" href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a shape="rect" ymailto="mailto:postgis-users-bounces@lists.osgeo.org" href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a>] De la part de Joseph Spenner<div class="qtdSeparateBR"><br><br></div><div class="yqt6144560246" id="yqtfd78702"><br clear="none">Envoyé : mercredi 7 janvier 2015 17:30<br clear="none">À : PostGIS Users Discussion<br clear="none">Objet : Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax <br clear="none"><br clear="none">More info. Here are my 2 select statements.This one works: $sth = $dbh->prepare("select ST_Asgeojson( geom ) from polys where ST_Intersects(ST_GeomFromText('POLYGON(($textPoly, $closingLon $closingLat))', 4269), geom)=TRUE"); However, below is my new one, which I can't get to work: $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"><br clear="none"><br clear="none"><br clear="none"><br clear="none">When I run with my new select statement, I get errors: -----<br clear="none"><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">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">------<br clear="none"><br clear="none">Any help would be great. Thanks! From: Hugues François <<a shape="rect" ymailto="mailto:hugues.francois@irstea.fr" href="mailto:hugues.francois@irstea.fr">hugues.francois@irstea.fr</a>><br clear="none">To: Joseph Spenner <<a shape="rect" ymailto="mailto:joseph85750@yahoo.com" href="mailto:joseph85750@yahoo.com">joseph85750@yahoo.com</a>>; PostGIS Users Discussion <<a shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>> <br clear="none">Sent: Wednesday, January 7, 2015 1:17 AM<br clear="none">Subject: Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax<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 http://www.postgis.org/docs/ST_GeomFromGeoJSON.html.<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 shape="rect" ymailto="mailto:postgis-users-bounces@lists.osgeo.org" 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"><br clear="none"><br clear="none"> </div><br><br></div> </div> </div> </div></body></html>