<html><head><style type='text/css'>p { margin: 0; }</style></head><body><div style='font-family: arial,helvetica,sans-serif; font-size: 10pt; color: #000000'>Thanks a lot Bborie,<br><br>As you suggested there was a null geometry in my table and the second "error" you discovered was that I used non tiled raster in the database.<br>
http://si.cenlr.org/2012/08/01/postgis-raster-suite<br><br>I forgot to "reply to all" so I put our exchange below<br><br>Thanks again,<br><br>Mathieu<br><br><div><span name="x"></span>--<br><font style="color: rgb(102, 102, 102);" size="1"><em>Mathieu BOSSAERT</em><br style="font-style: italic;"><span style="font-style: italic;">Responsable du </span><a style="font-style: italic;" href="http://si.cenlr.org/">système d'information</a></font><font style="font-weight: bold; font-style: italic; color: rgb(102, 102, 102);" size="1"> du CEN L-R</font><br><font style="font-style: italic; color: rgb(102, 102, 102);" size="1">04 67 29 90 65 - sig@cenlr.org</font><br><img src="http://sig.cenlr.org/_media/signature-courriel_mathieu.jpg"><br><span name="x"></span><span name="x"></span><br><hr id="zwchr"><b>De: </b>"Bborie Park" <bkpark@ucdavis.edu><br><b>À: </b>"Mathieu Bossaert (CEN L-R)" <sig@cenlr.org><br><b>Envoyé: </b>Mercredi 1 Août 2012 19:05:47<br><b>Objet: </b>Re: [postgis-users] st_value/st_world2rastercoordx error<br><br>Ah. If each table only has one row, then definitely do not use<br>ST_Intersects. ST_Intersects should be used in the normal situation (1<br>table = many rows). Also, one thing to keep in mind is that a single<br>field in PostgreSQL can only hold 1 GB of data. I don't know if your<br>rasters exceed 1GB in size, but that is something to keep in mind.<br><br>I'll have to add a check for when the geometry is null in that<br>function... I'll ticket it.<br><br>-bborie<br><br>On 08/01/2012 03:16 AM, SIG CEN L-R wrote:<br>Hi bborie, <br><br>Thanks for the answer. It helps me a lot for this problem and generally with postgis ratser. <br><br>Basically my ratsers were stored in the database as only one tile (one raster = one table with one row) <br><br>=> when i tried to enhance my query by using the st-intersects() operator, the execution grew up! <br><br>So now I store my rasters with indexed tile (100x100px). The query is now very fast! <br><br>For the second problem, you were right, one of my row had a null geometry value! <br><br>Thanks again, <br><br><br><br>Mathieu Bossaert<br><br><span></span>--<br><font style="color:" size="1"><i>Mathieu BOSSAERT</i><br style="font-style:italic"><i>Responsable du </i><a style="font-style:italic" href="http://si.cenlr.org/" target="_blank">système d'information</a></font><font style="font-weight:" size="1"> du CEN L-R</font><br>
<font style="font-style:" size="1">04 67 29 90 65 - <a href="mailto:sig@cenlr.org" target="_blank">sig@cenlr.org</a></font><br><img src="http://mail.espaces-naturels.fr/zimbra/#"><br><br><br><span class="Object" id="OBJ_PREFIX_DWT306"></span></div><hr id="zwchr"><b>De: </b>"Bborie Park" <bkpark@ucdavis.edu><br><b>À: </b>"Mathieu Bossaert (CEN L-R)" <sig@cenlr.org>, "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br><b>Envoyé: </b>Mardi 31 Juillet 2012 16:06:56<br><b>Objet: </b>Re: [postgis-users] st_value/st_world2rastercoordx error<br><br>Can you isolate the geometry that is causing the NaN? Maybe something like<br><br>SELECT<br> rowid,<br> ST_X(geometry)<br> ST_Y(geometry)<br>FROM mygeometrytable<br><br>The error message indicates that somehow a NaN is being passed to ST_World2RasterCoordX...<br>
<br>Also, your query isn't ideal. You should add another WHERE clause dealing with the intersection of the rast and the geometry.<br><br>UPDATE export.tous_point_espece_selon_format_esri SET<br>pente_mnt = ST_VALUE(rast, geometrie, true)<br>
<div>
FROM raster.pente_mnt30 <br>
WHERE code_insee LIKE '30%'<br>AND ST_Intersects(geometrie, rast)<br></div><br>-bborie<br><br><div class="gmail_quote">On Tue, Jul 31, 2012 at 2:09 AM, Mathieu Bossaert (CEN L-R) <span dir="ltr"><<a href="mailto:sig@cenlr.org" target="_blank">sig@cenlr.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="font-size:10pt;font-family:arial,helvetica,sans-serif"><div style="font-size:10pt;font-family:arial,helvetica,sans-serif">
Good morning,<br><br>first of all I want to thank the PostGIS community for the great job she does.<br><br>I am a french user of postgis since 2006 and it helps us a lot in our mission to preserve landscapes and biodiversity.<br>
Since a few month we use rasters function in order to characterize species distribution.<br><br>I have a problem with 1 of 5 raster table containing a dem.<br>When I try to populate an elevation attribute of a point layer from this raster I get an error. To workaround this problem I use pg-script to run this update line after line. <br>
<br>Is there a way to run this command, skipping the error.<br><br>Here is the query : <br><br>UPDATE export.tous_point_espece_selon_format_esri SET pente_mnt = ST_VALUE(rast, geometrie, true)<br>
FROM raster.pente_mnt30 <br>
WHERE code_insee LIKE '30%'<br><br>And here is the error (sorry it's in french)<br><br>ERREUR: syntaxe en entrée invalide pour l'entier : « NaN » <br>CONTEXTE : PL/pgSQL function "st_world2rastercoordx" lors de la conversion de la valeur de retour au type de retour de la fonction PL/pgSQL function "st_value" line 13 at RETURN <br>
<br>Thanks again,<br><br>Mathieu Bossaert<br><br><div><span></span>--<br><font style="color:" size="1"><i>Mathieu BOSSAERT</i><br style="font-style:italic"><i>Responsable du </i><a style="font-style:italic" href="http://si.cenlr.org/" target="_blank">système d'information</a></font><font style="font-weight:" size="1"> du CEN L-R</font><br>
<font style="font-style:" size="1">04 67 29 90 65 - <a href="mailto:sig@cenlr.org" target="_blank">sig@cenlr.org</a></font><br><img src="#" dfsrc="#"><br><span></span><br></div></div></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br>Bborie Park<br>Programmer<br>Center for Vectorborne Diseases<br>UC Davis<br>530-752-8380<br><a href="mailto:bkpark@ucdavis.edu" target="_blank">bkpark@ucdavis.edu</a><br>
</div></body></html>