[postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax

Thu Jan 8 10:26:27 PST 2015

Hugues:  Yes, that was it.  When I initially created this column, I used 4269.  I was following an example, and this is what was used.I recreated the table using the default (not specifying) and did the same with my query.  This solved my issue!
Thanks for your help!   Hopefully I won't have any issues by omitting the SRID.
Regards,Joseph Spenner
      From: Hugues François <hugues.francois at irstea.fr>
 To: joseph85750 at yahoo.com; postgis-users at lists.osgeo.org 
 Sent: Thursday, January 8, 2015 8:35 AM
 Subject: Re: [postgis-users] ST_Intersects ST_GeomFromGeoJSON syntax

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150108/76732b8e/attachment-0001.html>