[postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax

Thu Jan 8 07:35:33 PST 2015

Hi,

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:

ST_Intersects(ST_Transform(ST_GeomFromGeoJSON($var),XXXX),geom)

Where XXXX is the SRID you want to use. 

Regards,

Hug

-------- Original Message --------
From: Joseph Spenner <joseph85750 at yahoo.com>
Sent: Thursday, January 8, 2015 04:06 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax

Hugues:
  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):
{"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]]]]}

DBD::Pg::st execute failed: ERROR:  Operation on mixed SRID geometries at ./alertPoly2.cgi line 109.
DBD::Pg::st execute failed: ERROR:  Operation on mixed SRID geometries at ./alertPoly2.cgi line 109.

(line 109 is the: $sth->execute();  )
The above query should return some rows.
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.
Thanks again for your help.  I think it is very close!
Regards,Joseph Spenner

      From: Hugues François <hugues.francois at irstea.fr>
 To: Joseph Spenner <joseph85750 at yahoo.com>; PostGIS Users Discussion <postgis-users at lists.osgeo.org> 
 Sent: Thursday, January 8, 2015 1:44 AM
 Subject: Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax

#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 : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Joseph Spenner
Envoyé : mercredi 7 janvier 2015 17:30
À : PostGIS Users Discussion
Objet : Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax  

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");

When I run with my new select statement, I get errors:  -----

DBD::Pg::st execute failed: ERROR:  function st_intersects(text, geometry) is not unique
LINE 1: ...ertseverity,ST_Asgeojson( geom ) from polys where ST_Interse...
                                                             ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts. at ./alertPoly2.cgi line 103.
DBD::Pg::st execute failed: ERROR:  function st_intersects(text, geometry) is not unique
LINE 1: ...ertseverity,ST_Asgeojson( geom ) from polys where ST_Interse...
                                                             ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts. at ./alertPoly2.cgi line 103.
------

Any help would be great.  Thanks!        From: Hugues François <hugues.francois at irstea.fr>
To: Joseph Spenner <joseph85750 at yahoo.com>; PostGIS Users Discussion <postgis-users at lists.osgeo.org> 
Sent: Wednesday, January 7, 2015 1:17 AM
Subject: Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax
Hello,

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.

HTH

Hugues.

-------- Message d'origine--------
De: postgis-users-bounces at lists.osgeo.org de la part de Joseph Spenner
Date: mar. 06/01/2015 20:31
À: PostGIS Users Discussion
Objet : [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax

Hello,I currently have a select which works correctly:
$sth = $dbh->prepare("select ST_Asgeojson( geom ) from polys where ST_Intersects(ST_GeomFromText('POLYGON(($textPoly, $closingLon $closingLat))', 4269), geom)=TRUE");

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:
$sth = $dbh->prepare("select ST_Asgeojson( geom ) from polys where ST_Intersects(ST_GeomFromGeoJSON('POLYGON(($jsonPolys))', 4269), geom)=TRUE");

 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.
Any ideas?
Thanks!
Regards,Joseph Spenner