<div dir="ltr"><div><br></div>Trying to exclude possible sources of errors here :<div>it tried to convert points to 2D, no sucess.</div><div><br></div><div>Cheers,</div><div>Rémi-C</div><div><br></div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">2013/12/19 Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Oups,<div>I figured it would be of no use.</div><div><br></div><div>I of course works on pure geometry, raster functions only sees postgis points and ints.<br>Here few lines of the point table</div><div><br>
</div><div>qgis_id, st_astext(geom) AS geom,z,reflectance<br></div><div><div>1<span style="white-space:pre-wrap"> </span>POINT Z (2248.03170000003 20680.0229999999 49.02441796875)<span style="white-space:pre-wrap"> </span>49024<span style="white-space:pre-wrap"> </span>8110</div>
<div>2<span style="white-space:pre-wrap"> </span>POINT Z (2247.76560000001 20680.2519999999 49.<a href="tel:0198828125" value="+33198828125" target="_blank">0198828125</a>)<span style="white-space:pre-wrap"> </span>49020<span style="white-space:pre-wrap"> </span>7890</div>
<div>
3<span style="white-space:pre-wrap"> </span>POINT Z (2248.25289999996 20680.2479999999 49.02616015625)<span style="white-space:pre-wrap"> </span>49026<span style="white-space:pre-wrap"> </span>8404</div></div>
<div><br></div><div><br></div><div>Is there any function to work on pixel sets , (like read/write many at a time) </div><div><br></div><div>Now the code : </div><div>(I can make a test case out of this if you want)</div>
<div>
<br></div><div>Thanks,</div><div><br></div><div>Rémi-C</div><div><br></div><div>///////////////////////////////</div><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div><div><br></div><div>SELECT postgis_full_version();</div><div>--POSTGIS="2.1.0 r11822" GEOS="3.5.0dev-CAPI-1.9.0 r3963" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.8.0" RASTER</div>
<div><br></div><div><br></div><div><br></div><div><br></div><div>----converting a patch to raster</div><div><span style="white-space:pre-wrap"> </span>--get patch extend</div>
<div><span style="white-space:pre-wrap"> </span>--create a raster with this extend</div><div><br></div><div><span style="white-space:pre-wrap"> </span>--explode patch to points</div><div>
<span style="white-space:pre-wrap"> </span>--set pixel value to point intersecting it.</div><div><br></div><div><br></div><div>--preparing raster :</div><div><span style="white-space:pre-wrap"> </span>--creating the raster table</div>
<div><span style="white-space:pre-wrap"> </span>DROP TABLE IF EXISTS patch_to_raster;</div><div><span style="white-space:pre-wrap"> </span>CREATE TABLE patch_to_raster(rid serial primary key</div>
<div><span style="white-space:pre-wrap"> </span>, rast raster);</div><div><br></div><div><span style="white-space:pre-wrap"> </span>--</div><div><span style="white-space:pre-wrap"> </span>--DELETE from patch_to_raster</div>
<div><span style="white-space:pre-wrap"> </span>--inserting an empty raster</div><div><span style="white-space:pre-wrap"> </span>INSERT INTO patch_to_raster (rast) VALUES (</div><div><span style="white-space:pre-wrap"> </span>ST_MakeEmptyRaster(</div>
<div><span style="white-space:pre-wrap"> </span>50</div><div><span style="white-space:pre-wrap"> </span>,50</div><div><span style="white-space:pre-wrap"> </span>,upperleftx:=0</div>
<div><span style="white-space:pre-wrap"> </span>,upperlefty:=0</div><div><span style="white-space:pre-wrap"> </span>,scalex:=1</div><div><span style="white-space:pre-wrap"> </span>, scaley:=1</div>
<div><span style="white-space:pre-wrap"> </span>, skewx:=0</div><div><span style="white-space:pre-wrap"> </span>,skewy:=0</div><div><span style="white-space:pre-wrap"> </span>, srid:=932011</div>
<div><span style="white-space:pre-wrap"> </span>) --srid of translated lambert 93 to match laser referential</div><div><span style="white-space:pre-wrap"> </span>);</div><div><span style="white-space:pre-wrap"> </span></div>
<div><span style="white-space:pre-wrap"> </span>--setting the correct pixel size</div><div><span style="white-space:pre-wrap"> </span>UPDATE patch_to_raster SET rast = ST_SetScale(rast,0.02,0.02);</div>
<div><br></div><div><span style="white-space:pre-wrap"> </span>--checking the content:</div><div><span style="white-space:pre-wrap"> </span>SELECT ST_Summary(rast)</div><div><span style="white-space:pre-wrap"> </span>FROM patch_to_raster;</div>
<div><span style="white-space:pre-wrap"> </span>--Raster of 50x50 pixels has 0 bands and extent of BOX(0 0,1 1)</div><div><br></div><div><br></div><div><span style="white-space:pre-wrap"> </span>--adding band </div>
<div><span style="white-space:pre-wrap"> </span>--first band is Z : float</div><div><span style="white-space:pre-wrap"> </span>--second band is reflectance : float</div><div><span style="white-space:pre-wrap"> </span>UPDATE patch_to_raster SET rast = ST_AddBand( --1'st band, Z</div>
<div><span style="white-space:pre-wrap"> </span>rast</div><div><span style="white-space:pre-wrap"> </span>,pixeltype:='32BF' -- '32BUI'</div><div><span style="white-space:pre-wrap"> </span>, initialvalue:=NULL</div>
<div><span style="white-space:pre-wrap"> </span>, nodataval:=NULL</div><div><span style="white-space:pre-wrap"> </span>);</div><div><span style="white-space:pre-wrap"> </span>UPDATE patch_to_raster SET rast = ST_AddBand( --2nd band, reflectance</div>
<div><span style="white-space:pre-wrap"> </span>rast</div><div><span style="white-space:pre-wrap"> </span>, pixeltype:='32BF' --'32BUI' </div><div><span style="white-space:pre-wrap"> </span>, initialvalue:=NULL</div>
<div><span style="white-space:pre-wrap"> </span>, nodataval:=NULL</div><div><span style="white-space:pre-wrap"> </span>);</div><div><br></div><div><br></div><div><span style="white-space:pre-wrap"> </span>--checking the content:</div>
<div><span style="white-space:pre-wrap"> </span>SELECT ST_Summary(rast) FROM patch_to_raster;</div><div><span style="white-space:pre-wrap"> </span>--Raster of 50x50 pixels has 2 bands and extent of BOX(0 0,1 1)</div>
<div><span style="white-space:pre-wrap"> </span>--band 1 of pixtype 32BF is in-db with no NODATA value</div><div><span style="white-space:pre-wrap"> </span>--band 2 of pixtype 32BF is in-db with no NODATA value</div>
<div><br></div><div><span style="white-space:pre-wrap"> </span>--getting a patch and creating a table with it </div><div><span style="white-space:pre-wrap"> </span>DROP TABLE IF EXISTS one_patch_into_points;</div>
<div><span style="white-space:pre-wrap"> </span>CREATE TABLE one_patch_into_points AS </div><div><span style="white-space:pre-wrap"> </span>WITH patch AS (</div><div><span style="white-space:pre-wrap"> </span>SELECT gid,patch</div>
<div><span style="white-space:pre-wrap"> </span>FROM acquisition_tmob_012013.riegl_pcpatch_space</div><div><span style="white-space:pre-wrap"> </span>WHERE gid = 87263</div><div><span style="white-space:pre-wrap"> </span>)</div>
<div><span style="white-space:pre-wrap"> </span>,point AS (</div><div><span style="white-space:pre-wrap"> </span>SELECT PC_Explode(patch) AS point</div><div><span style="white-space:pre-wrap"> </span>FROM patch</div>
<div><span style="white-space:pre-wrap"> </span>)</div><div><span style="white-space:pre-wrap"> </span>SELECT row_number() over() AS qgis_id, ST_SetSRID(point::geometry,932011) AS geom, @(PC_Get(point, 'z')*1000)::int AS z, @((PC_Get(point, 'reflectance')+50)*200)::int aS reflectance</div>
<div><span style="white-space:pre-wrap"> </span>FROM point;</div><div><span style="white-space:pre-wrap"> </span>--2746 points</div><div><br></div><div><span style="white-space:pre-wrap"> </span>--checking</div>
<div><span style="white-space:pre-wrap"> </span>SELECT *</div><div><span style="white-space:pre-wrap"> </span>FROM one_patch_into_points</div><div><span style="white-space:pre-wrap"> </span>--1<span style="white-space:pre-wrap"> </span>01010000A0AB380E00DC7EFB3A1090A140CDFDD4780132D440A69BC42020834840<span style="white-space:pre-wrap"> </span>49024<span style="white-space:pre-wrap"> </span>8110</div>
<div><span style="white-space:pre-wrap"> </span>--2<span style="white-space:pre-wrap"> </span>POINT Z (2247.76560000001 20680.2519999999 49.<a href="tel:0198828125" value="+33198828125" target="_blank">0198828125</a>)<span style="white-space:pre-wrap"> </span>49020<span style="white-space:pre-wrap"> </span>7890</div>
<div><span style="white-space:pre-wrap"> </span></div><div><br></div><div><span style="white-space:pre-wrap"> </span>--updating the raster to set it on the patch place.</div><div>
<span style="white-space:pre-wrap"> </span>--we get the upper left coordinate of patch</div><div><span style="white-space:pre-wrap"> </span>WITH patch AS (</div><div><span style="white-space:pre-wrap"> </span>SELECT gid,patch, ST_AsText(patch::geometry)</div>
<div><span style="white-space:pre-wrap"> </span>FROM acquisition_tmob_012013.riegl_pcpatch_space</div><div><span style="white-space:pre-wrap"> </span>WHERE gid = 87263</div><div><span style="white-space:pre-wrap"> </span>)</div>
<div><span style="white-space:pre-wrap"> </span>,centroid AS (</div><div><span style="white-space:pre-wrap"> </span>SELECT ST_Centroid(patch::geometry) AS centroid</div><div><span style="white-space:pre-wrap"> </span>FROM patch</div>
<div><span style="white-space:pre-wrap"> </span>)</div><div><span style="white-space:pre-wrap"> </span>,upperleft AS (</div><div><span style="white-space:pre-wrap"> </span>SELECT round(ST_X(centroid)) -0.5 AS upper_left_x, round(ST_y(centroid))-0.5 AS upper_left_y</div>
<div><span style="white-space:pre-wrap"> </span>FROM centroid</div><div><span style="white-space:pre-wrap"> </span>)--updating the raster with it</div><div><span style="white-space:pre-wrap"> </span>UPDATE patch_to_raster SET rast = ST_SetUpperLeft(rast,upper_left_x,upper_left_y)</div>
<div><span style="white-space:pre-wrap"> </span>FROM upperleft;</div><div><br></div><div><span style="white-space:pre-wrap"> </span>--checking the raster</div><div><span style="white-space:pre-wrap"> </span>SELECT ST_Summary(rast) FROM patch_to_raster;</div>
<div><span style="white-space:pre-wrap"> </span>--Raster of 50x50 pixels has 2 bands and extent of BOX(2247.5 20679.5,2248.5 20680.5) </div><div><span style="white-space:pre-wrap"> </span>--note : the patch bbox : POLYGON((2247.51 20679.50,2247.51 20680.49,2248.49 20680.49,2248.49 20679.50,2247.51 20679.50)), some pixels will be empty</div>
<div><br></div><div><br></div><div><br></div><div><span style="white-space:pre-wrap"> </span>--updating the raster with pixel value</div><div><span style="white-space:pre-wrap"> </span>--using postgis_addons function : <a href="https://raw.github.com/pedrogit/postgisaddons/master/postgis_addons.sql" target="_blank">https://raw.github.com/pedrogit/postgisaddons/master/postgis_addons.sql</a></div>
<div><span style="white-space:pre-wrap"> </span>--</div><div><span style="white-space:pre-wrap"> </span>UPDATE patch_to_raster SET rast = </div><div><span style="white-space:pre-wrap"> </span>ST_ExtractToRaster(</div>
<div><span style="white-space:pre-wrap"> </span>rast </div><div><span style="white-space:pre-wrap"> </span>,band:=1</div><div><span style="white-space:pre-wrap"> </span>,schemaname:= 'test_raster'</div>
<div><span style="white-space:pre-wrap"> </span>,tablename:= 'one_patch_into_points'</div><div><span style="white-space:pre-wrap"> </span>,geomrastcolumnname:= 'geom'</div><div>
<span style="white-space:pre-wrap"> </span>,valuecolumnname:='z'</div><div><span style="white-space:pre-wrap"> </span>);</div><div><span style="white-space:pre-wrap"> </span>--5 sec : way too long!</div>
<div><span style="white-space:pre-wrap"> </span></div><div><span style="white-space:pre-wrap"> </span>UPDATE patch_to_raster SET rast = </div><div><span style="white-space:pre-wrap"> </span>ST_ExtractToRaster(</div>
<div><span style="white-space:pre-wrap"> </span>rast </div><div><span style="white-space:pre-wrap"> </span>,band:=2</div><div><span style="white-space:pre-wrap"> </span>,schemaname:= 'test_raster'</div>
<div><span style="white-space:pre-wrap"> </span>,tablename:= 'one_patch_into_points'</div><div><span style="white-space:pre-wrap"> </span>,geomrastcolumnname:= 'geom'</div><div>
<span style="white-space:pre-wrap"> </span>,valuecolumnname:='reflectance'</div><div><span style="white-space:pre-wrap"> </span>,method:='MEAN_OF_VALUES_AT_PIXEL_CENTROID'</div><div>
<span style="white-space:pre-wrap"> </span>);</div><div><span style="white-space:pre-wrap"> </span>--6 sec, way too long !</div><div><br></div><div><br></div><div><span style="white-space:pre-wrap"> </span>--checking the value of pixel in both band (no shortcut to get several bands at a time??)</div>
<div><span style="white-space:pre-wrap"> </span>SELECT s1 as x, s2 as y, ST_Value(rast, 1,s1, s2, exclude_nodata_value:=true) AS value_band_1, ST_Value(rast, 2,s1, s2, exclude_nodata_value:=true) AS value_band_2</div>
<div><span style="white-space:pre-wrap"> </span>FROM generate_series(1,50) as s1, generate_series(1,50) as s2, patch_to_raster;</div><div><span style="white-space:pre-wrap"> </span>--values are NULL! </div>
<div><span style="white-space:pre-wrap"> </span></div><div><br></div><div><span style="white-space:pre-wrap"> </span>--checking the histogram</div><div><span style="white-space:pre-wrap"> </span>SELECT ST_Histogram( rast, 1 ) --, boolean exclude_nodata_value=true</div>
<div><span style="white-space:pre-wrap"> </span>FROM patch_to_raster;</div><div><span style="white-space:pre-wrap"> </span>--no line returned</div><div><br></div><div><span style="white-space:pre-wrap"> </span>--checking result </div>
<div><span style="white-space:pre-wrap"> </span>SELECT ST_Summary(rast) FROM patch_to_raster;</div><div><span style="white-space:pre-wrap"> </span>--Raster of 50x50 pixels has 2 bands and extent of BOX(2247.5 20679.5,2248.5 20680.5)</div>
<div><span style="white-space:pre-wrap"> </span>-- band 1 of pixtype 32BF is in-db with NODATA value of -3.40282346638529e+38</div><div><span style="white-space:pre-wrap"> </span>-- band 2 of pixtype 32BF is in-db with NODATA value of -3.40282346638529e+38</div>
<div>////////////////////////////////</div></div></blockquote></div><br></div></div>
</blockquote></div><br></div>