[postgis-users] st_value/st_world2rastercoordx error

Wed Aug 1 23:29:23 PDT 2012

Thanks a lot Bborie, 

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. 
http://si.cenlr.org/2012/08/01/postgis-raster-suite 

I forgot to "reply to all" so I put our exchange below 

Thanks again, 

Mathieu 

-- 
Mathieu BOSSAERT 
Responsable du système d'information du CEN L-R 
04 67 29 90 65 - sig at cenlr.org 

----- Mail original -----
De: "Bborie Park" <bkpark at ucdavis.edu> 
À: "Mathieu Bossaert (CEN L-R)" <sig at cenlr.org> 
Envoyé: Mercredi 1 Août 2012 19:05:47 
Objet: Re: [postgis-users] st_value/st_world2rastercoordx error 

Ah. If each table only has one row, then definitely do not use 
ST_Intersects. ST_Intersects should be used in the normal situation (1 
table = many rows). Also, one thing to keep in mind is that a single 
field in PostgreSQL can only hold 1 GB of data. I don't know if your 
rasters exceed 1GB in size, but that is something to keep in mind. 

I'll have to add a check for when the geometry is null in that 
function... I'll ticket it. 

-bborie 

On 08/01/2012 03:16 AM, SIG CEN L-R wrote: 
Hi bborie, 

Thanks for the answer. It helps me a lot for this problem and generally with postgis ratser. 

Basically my ratsers were stored in the database as only one tile (one raster = one table with one row) 

=> when i tried to enhance my query by using the st-intersects() operator, the execution grew up! 

So now I store my rasters with indexed tile (100x100px). The query is now very fast! 

For the second problem, you were right, one of my row had a null geometry value! 

Thanks again, 

Mathieu Bossaert 

-- 
Mathieu BOSSAERT 
Responsable du système d'information du CEN L-R 
04 67 29 90 65 - sig at cenlr.org 

----- Mail original -----
De: "Bborie Park" <bkpark at ucdavis.edu> 
À: "Mathieu Bossaert (CEN L-R)" <sig at cenlr.org>, "PostGIS Users Discussion" <postgis-users at postgis.refractions.net> 
Envoyé: Mardi 31 Juillet 2012 16:06:56 
Objet: Re: [postgis-users] st_value/st_world2rastercoordx error 

Can you isolate the geometry that is causing the NaN? Maybe something like 

SELECT 
rowid, 
ST_X(geometry) 
ST_Y(geometry) 
FROM mygeometrytable 

The error message indicates that somehow a NaN is being passed to ST_World2RasterCoordX... 

Also, your query isn't ideal. You should add another WHERE clause dealing with the intersection of the rast and the geometry. 

UPDATE export.tous_point_espece_selon_format_esri SET 
pente_mnt = ST_VALUE(rast, geometrie, true) 

FROM raster.pente_mnt30 
WHERE code_insee LIKE '30%' 
AND ST_Intersects(geometrie, rast) 

-bborie 

On Tue, Jul 31, 2012 at 2:09 AM, Mathieu Bossaert (CEN L-R) < sig at cenlr.org > wrote: 

Good morning, 

first of all I want to thank the PostGIS community for the great job she does. 

I am a french user of postgis since 2006 and it helps us a lot in our mission to preserve landscapes and biodiversity. 
Since a few month we use rasters function in order to characterize species distribution. 

I have a problem with 1 of 5 raster table containing a dem. 
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. 

Is there a way to run this command, skipping the error. 

Here is the query : 

UPDATE export.tous_point_espece_selon_format_esri SET pente_mnt = ST_VALUE(rast, geometrie, true) 
FROM raster.pente_mnt30 
WHERE code_insee LIKE '30%' 

And here is the error (sorry it's in french) 

ERREUR: syntaxe en entrée invalide pour l'entier : « NaN » 
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 

Thanks again, 

Mathieu Bossaert 

-- 
Mathieu BOSSAERT 
Responsable du système d'information du CEN L-R 
04 67 29 90 65 - sig at cenlr.org 

_______________________________________________ 
postgis-users mailing list 
postgis-users at postgis.refractions.net 
http://postgis.refractions.net/mailman/listinfo/postgis-users 

-- 
Bborie Park 
Programmer 
Center for Vectorborne Diseases 
UC Davis 
530-752-8380 
bkpark at ucdavis.edu 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120802/774b5c54/attachment.html>