st_value/st_world2rastercoordx error

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

st_value/st_world2rastercoordx error

Mathieu Bossaert (CEN L-R)
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 - [hidden email]



_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_value/st_world2rastercoordx error

Bborie Park
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) <[hidden email]> 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 - [hidden email]



_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users




--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
[hidden email]

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: st_value/st_world2rastercoordx error

Mathieu Bossaert (CEN L-R)
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 - [hidden email]



De: "Bborie Park" <[hidden email]>
À: "Mathieu Bossaert (CEN L-R)" <[hidden email]>
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 - [hidden email]




De: "Bborie Park" <[hidden email]>
À: "Mathieu Bossaert (CEN L-R)" <[hidden email]>, "PostGIS Users Discussion" <[hidden email]>
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) <[hidden email]> 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 - [hidden email]



_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users




--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
[hidden email]

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users