<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
@font-face
{font-family:"Trebuchet MS";
panose-1:2 11 6 3 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas",serif;
mso-ligatures:none;}
span.m-2066442463817014692gmailsignatureprefix
{mso-style-name:m_-2066442463817014692gmailsignatureprefix;}
span.gmailsignatureprefix
{mso-style-name:gmail_signature_prefix;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1855653329;
mso-list-template-ids:-1549905850;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>The format once it’s loaded in the database is no longer netcdf, so if it’s not a standard raster metadata item, it probably isn’t in the database or at the very least we don’t have a function to access it. So you’d need to read that information before it gets in the database as Tobias is doing in his example<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b>From:</b> Manaswini Ganjam <manu.ganjam@gmail.com> <br><b>Sent:</b> Friday, November 17, 2023 10:01 PM<br><b>To:</b> Regina Obe <lr@pcorp.us><br><b>Cc:</b> Tobias Schmid <schmid.tobi@gmail.com>; PostGIS Users Discussion <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-users] Extracting variable information from netcdf, imported as raster to a table<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Regina, <o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>I am sure that function does not extract offset and scale values provided with the netcdf to unpack data. The way I understand this concept is, the value in unpacked data (which is not the variable value) is used along with the scale and offset parameters provided with the netcdf to calculate the true variable values. <o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>I am thinking I can use <a href="https://www.postgresql.org/docs/9.1/plpython.html">https://www.postgresql.org/docs/9.1/plpython.html</a> extension and write a function within the database but the main problem I see here is I am unsure whether Gdal actually imports this information into the database and we don't know how to access it or does it not import these parameters into the database when we use raster2pgsql? for some reason I am certain that they would not develop a tool to actually import data but ignore a part of the dataset right? Maybe we don't know where to find that information from rasters within the database?. Any thoughts?<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thank you,<o:p></o:p></p></div><div><p class=MsoNormal>Manaswini<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Fri, 17 Nov 2023 at 18:59, Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanks Tobias. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Manaswini,<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>If you trust the netcdf order to represent real days, then might as well stick with your multiband I guess.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>If you are sure it’s the same for all bands, then yes you should be able to apply the same and assume it’s the same for all bands.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Regarding the scale factor – Tobias, in your code<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> hourlyRasterScale = hourlyRaster.GetRasterBand(1).GetScale()<br> hourlyRasterOffset = hourlyRaster.GetRasterBand(1).GetOffset()<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I guess this isn’t the same as what you get with <a href="https://postgis.net/docs/en/RT_ST_ScaleX.html" target="_blank">https://postgis.net/docs/en/RT_ST_ScaleX.html</a> ? Just assuming X and Y scale are the same which they usually are?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>And that applies across the raster.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:black'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:black'>Seems a shame you have to do this part in Python and not compute it in the database.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:black'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>If it is information that GDAL exposes as part of the Band MetaData, perhaps we can add it to this function -- <a href="https://postgis.net/docs/en/RT_ST_BandMetaData.html" target="_blank">https://postgis.net/docs/en/RT_ST_BandMetaData.html</a> as extra outputs.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Manaswin,<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>One function I really like to use before I even bother extracting data, is the ST_Histogram function to get a certain idea of if the distribution of the data makes sense in your raster tile before you even bother trying to extract pixel values from it. I’d break your data say into 10 buckets<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><a href="https://postgis.net/docs/en/RT_ST_Histogram.html" target="_blank">https://postgis.net/docs/en/RT_ST_Histogram.html</a><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b>From:</b> Manaswini Ganjam <<a href="mailto:manu.ganjam@gmail.com" target="_blank">manu.ganjam@gmail.com</a>> <br><b>Sent:</b> Friday, November 17, 2023 11:06 AM<br><b>To:</b> Tobias Schmid <<a href="mailto:schmid.tobi@gmail.com" target="_blank">schmid.tobi@gmail.com</a>><br><b>Cc:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>>; Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br><b>Subject:</b> Re: [postgis-users] Extracting variable information from netcdf, imported as raster to a table<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi, Tobias, and Regina,<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Can I implement this for multiband netcdfs, because I am pretty confident about bands and data order as my data units for time are the number of days since the first day of the year. For example days since 1950-01-01, band numbers go like 1,2,3,4....365. I can find different logic and extract the timestamp, because we have good information about this aspect from rasters in the database. Because the scale factor and offset value is same for all the bands?<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Also, I am curious to know if there is any other function that can address this in postgis?<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thank you,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Manaswini<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>On Fri, 17 Nov 2023 at 03:14, Tobias Schmid <<a href="mailto:schmid.tobi@gmail.com" target="_blank">schmid.tobi@gmail.com</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Sorry, the Screenshot was missing. <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><img border=0 width=338 height=222 style='width:3.5208in;height:2.3125in' id="m_-2066442463817014692Picture_x0020_1" src="cid:image001.png@01DA19A9.CC71FA00"><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Am Fr., 17. Nov. 2023 um 09:02 Uhr schrieb Tobias Schmid <<a href="mailto:schmid.tobi@gmail.com" target="_blank">schmid.tobi@gmail.com</a>>:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hello Manswini, <br>Hello Regina, <br>I have read your e-mails with great interest. Many of the described problems seem familiar to me. I have been working with temporally and spatially resolved (weather) raster data for over 10 years. <br><br>Here are a few comments and recommendations: <br><br>1. After some missteps I stopped using multiband-raster. Why? <o:p></o:p></p></div><div><ul type=disc><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1'>What happens if individual days (or hours) are missing? <o:p></o:p></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1'>Can I always trust the order of the bands? <o:p></o:p></li></ul></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>2. I work with 1-band grids. My database structure looks like this:<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <br><br>3. The import is using raster2pgsql. Here is snippet: <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> def raster2pgsql(outGtiffPath, dbSchema, dbTable, dbHost=conf.dbHost, dbPort=conf.dbPort, dbName=conf.dbName, dbUser=conf.dbUser):<br> try:<br> tableNotEmptySQL = f"SELECT EXISTS (SELECT * FROM {dbSchema}.{dbTable} LIMIT 1);"<br> if executeSQL(tableNotEmptySQL, fetchone=True):<br> cmd = f"raster2pgsql -a -F -n filename -s 4326 {outGtiffPath} {dbSchema}.{dbTable} | psql -h {dbHost} -p {dbPort} -d {dbName} -U {dbUser}"<br> else:<br> cmd = f"raster2pgsql -a -C -F -n filename -s 4326 {outGtiffPath} {dbSchema}.{dbTable} | psql -h {dbHost} -p {dbPort} -d {dbName} -U {dbUser}"<o:p></o:p></p></blockquote><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br> subprocess.call(cmd, shell=True)<o:p></o:p></p></blockquote><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br> return True<o:p></o:p></p></blockquote><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br> except Exception as e:<br> print(f"Importing '{outGtiffPath}' to postgres db failed.")<br> print(f"{e}\n")<br> with open(conf.failedRaster2pgsql, 'a') as file:<br> file.write(f"{datetime.now().strftime('%Y-%m-%d %X')}\t{outGtiffPath}\t{e}\n")<br> return False <o:p></o:p></p></blockquote><div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'><br>4. I read the parameters "scale" and "offset" via Python and write them to the database. <o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> hourlyRaster = gdal.Open(str(outGtiffPath))<br> hourlyRasterScale = hourlyRaster.GetRasterBand(1).GetScale()<br> hourlyRasterOffset = hourlyRaster.GetRasterBand(1).GetOffset()<br> hourlyRaster = None<o:p></o:p></p></blockquote><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></blockquote><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> insertTimestampSQL = f"SET TIMEZONE = 'UTC'; UPDATE {dataset}.{dbTable} SET timestamp_w_tz = TO_TIMESTAMP('{timeStamp}', 'YYYY-MM-DD HH24:MI'), rast_scale_factor = {hourlyRasterScale}, rast_offset = {hourlyRasterOffset} WHERE filename = '{outGtiff}';" <o:p></o:p></p></blockquote><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br>5. The query: <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Courier New";color:#0033B3'>select </span><span style='font-family:"Courier New";color:#871094'>timestamp_w_tz<br></span><span style='font-family:"Courier New";color:#080808'>, st_value(</span><span style='font-family:"Courier New";color:#871094'>rast</span><span style='font-family:"Courier New";color:#080808'>, st_setsrid(st_makepoint(</span><span style='font-family:"Courier New";color:#1750EB'>15</span><span style='font-family:"Courier New";color:#080808'>, </span><span style='font-family:"Courier New";color:#1750EB'>50</span><span style='font-family:"Courier New";color:#080808'>), </span><span style='font-family:"Courier New";color:#1750EB'>4326</span><span style='font-family:"Courier New";color:#080808'>)) * </span><span style='font-family:"Courier New";color:#871094'>rast_scale_factor </span><span style='font-family:"Courier New";color:#080808'>+ </span><span style='font-family:"Courier New";color:#871094'>rast_offset </span><span style='font-family:"Courier New";color:#0033B3'>as </span><span style='font-family:"Courier New";color:black'>value_temperature<br></span><span style='font-family:"Courier New";color:#0033B3'>from </span><span style='font-family:"Courier New";color:black'>era5</span><span style='font-family:"Courier New";color:#080808'>.</span><span style='font-family:"Courier New";color:black'>temperature_2m<br></span><span style='font-family:"Courier New";color:#0033B3'>where </span><span style='font-family:"Courier New";color:#871094'>timestamp_w_tz </span><span style='font-family:"Courier New";color:#080808'>>= </span><span style='font-family:"Courier New";color:#067D17'>'1982-09-18 00:00'<br></span><span style='font-family:"Courier New";color:#0033B3'>and </span><span style='font-family:"Courier New";color:#871094'>timestamp_w_tz </span><span style='font-family:"Courier New";color:#080808'>< </span><span style='font-family:"Courier New";color:#067D17'>'1982-09-19 00:00'</span><o:p></o:p></p></blockquote></div></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>6. The result (temperature in Kelvin. Of course.)<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Courier New"'>+---------------------------------+------------------+<br>|timestamp_w_tz |value_temperature |<br>+---------------------------------+------------------+<br>|1982-09-18 00:00:00.000000 +00:00|287.70160804659935|<br>|1982-09-18 01:00:00.000000 +00:00|287.47021164940014|<br>|1982-09-18 02:00:00.000000 +00:00|286.0640335433434 |<br>|1982-09-18 03:00:00.000000 +00:00|285.56675378590086|<br>|1982-09-18 04:00:00.000000 +00:00|285.41434365889944|<br>|1982-09-18 05:00:00.000000 +00:00|285.3075453217306 |<br>|1982-09-18 06:00:00.000000 +00:00|286.3343668343021 |<br>|1982-09-18 07:00:00.000000 +00:00|287.2543900097047 |<br>|1982-09-18 08:00:00.000000 +00:00|290.88219602540977|<br>|1982-09-18 09:00:00.000000 +00:00|292.3985099166719 |<br>|1982-09-18 10:00:00.000000 +00:00|297.1844104010519 |<br>|1982-09-18 11:00:00.000000 +00:00|297.37019500841853|<br>|1982-09-18 12:00:00.000000 +00:00|297.58267920007745|<br>|1982-09-18 13:00:00.000000 +00:00|297.85078752567847|<br>|1982-09-18 14:00:00.000000 +00:00|298.1689575718274 |<br>|1982-09-18 15:00:00.000000 +00:00|297.20666005462874|<br>|1982-09-18 16:00:00.000000 +00:00|296.9697012440353 |<br>|1982-09-18 17:00:00.000000 +00:00|294.35870439679223|<br>|1982-09-18 18:00:00.000000 +00:00|292.8045660944494 |<br>|1982-09-18 19:00:00.000000 +00:00|292.6032067295789 |<br>|1982-09-18 20:00:00.000000 +00:00|289.9332483003572 |<br>|1982-09-18 21:00:00.000000 +00:00|289.59839101402565|<br>|1982-09-18 22:00:00.000000 +00:00|287.79616907430096|<br>|1982-09-18 23:00:00.000000 +00:00|287.87181789646223|<br>+---------------------------------+------------------+</span><o:p></o:p></p></blockquote><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I know that my example does not match your problem 100%. It is also time-consuming for you to establish the 1-band grid approach. It requires you to change your database structure. Sorry about that. <br><br>Hopefully the comments will help you. You are also welcome to contact me personally. <br><br>Best regards<br>Tobias<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Am Fr., 17. Nov. 2023 um 05:32 Uhr schrieb Manaswini Ganjam via postgis-users <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>>:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Regina, <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thank you for the suggestions, the code looks clean and the computation was much better, but I still have the same issue, the values are unrealistic, this happened to me before as well (when I tried to run st_value for one raster band, with some x and y). Find the enclosed screenshot.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><img border=0 width=484 height=400 style='width:5.0416in;height:4.1666in' id="m_-2066442463817014692Picture_x0020_2" src="cid:image002.png@01DA19A9.CC71FA00"><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I have test run the code for one prcp file and the prcp values are negative, and the no data value for this dataset is -32768 (most of the values are -32767 or -32750) and I have not preprocessed this data. Do we need to manually unpack this data? If that is the case, how do I access the scale_factor and add_offset attributes packed with netcdf? <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>This information is from the climate data readme file:<span style='color:black'>The data has been packed into short integers (2 bytes instead of 4 byte reals) to save space. You must unpack that data to get the correct floating point representation of the data. Each netCDF variable that has been packed has an add_offset and scale_factor attribute associated with it. Some software automatically unpacks the data when it is read.</span><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:black'>The formula to unpack the data is:</span><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:black'>unpacked value = add_offset + ( (packed value) * scale_factor )</span><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thank you,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Manaswini<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'><br><br><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>On Thu, 16 Nov 2023 at 22:02, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>One more optimization. I think you can get rid of the FOREACH band_number too and reduce all that to this query<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So all this goes<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> band_numbers := ARRAY(SELECT generate_series(1, 366));<br><br> -- Loop through all bands within the file<br> FOREACH band_number IN ARRAY band_numbers LOOP<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>LOOP<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Use ST_NumBands instead of relying on your rasters all having 366 bands <a href="https://postgis.net/docs/RT_ST_NumBands.html" target="_blank">https://postgis.net/docs/RT_ST_NumBands.html</a><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So replace all the above with this:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, tmax, tmin, observation_time)<br> SELECT year_from_filename, n.band_number, ST_Y(sc.geom), ST_X(sc.geom), CASE WHEN variable_name = 'prcp' THEN sc.val ELSE NULL END,<br> CASE WHEN variable_name = 'tmax' THEN sc.val ELSE NULL END,<br> CASE WHEN variable_name = 'tmin' THEN sc.val ELSE NULL END, observation_time<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> FROM generate_series(1, ST_NumBands(raster_record.clipped_raster) ) AS n(band_number), ST_PixelAsCentroids(raster_record.clipped_raster, n.band_number, true) AS sc;<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>That will solve the issue of you going over the band count of your raster, which might be an issue you are running into <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b>From:</b> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> <br><b>Sent:</b> Thursday, November 16, 2023 9:46 PM<br><b>To:</b> 'Manaswini Ganjam' <<a href="mailto:manu.ganjam@gmail.com" target="_blank">manu.ganjam@gmail.com</a>><br><b>Cc:</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> RE: [postgis-users] Extracting variable information from netcdf, imported as raster to a table<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I don’t know much about netCDF but I would assume GDAL would handle the packing unraveling behind the scenes.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Some things I notice wrong<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><a href="https://postgis.net/docs/RT_ST_PixelAsCentroids.html" target="_blank">https://postgis.net/docs/RT_ST_PixelAsCentroids.html</a> is a set returning function, so you can’t just stuff it in a record variable. It has to go in a table.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So I’d get rid of this line<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'> SELECT * FROM ST_PixelAsCentroids(raster_record.clipped_raster, band_number, true) INTO pixel_data;<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I’d also scrap this, because ultimately you want to work on all centroids not the first one that falls out of the tree<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'>EXECUTE format('SELECT ST_Value($1, $2, $3, true)', raster_record.clipped_raster, band_number, centroid_point) INTO variable_value USING raster_record.clipped_raster, band_number, centroid_point;<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I’d change this<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br> -- Insert the extracted data into the extracted_data_bbox table<br> INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, tmax, tmin, observation_time)<br> VALUES (year_from_filename, band_number, ST_Y(centroid_point), ST_X(centroid_point), CASE WHEN variable_name = 'prcp' THEN variable_value ELSE NULL END,<br> CASE WHEN variable_name = 'tmax' THEN variable_value ELSE NULL END,<br> CASE WHEN variable_name = 'tmin' THEN variable_value ELSE NULL END, observation_time);<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>To this:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b> INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, tmax, tmin, observation_time)<br> SELECT year_from_filename, band_number, ST_Y(sc.geom), ST_X(sc.geom), CASE WHEN variable_name = 'prcp' THEN sc.val ELSE NULL END,<br> CASE WHEN variable_name = 'tmax' THEN sc.val ELSE NULL END,<br> CASE WHEN variable_name = 'tmin' THEN sc.val ELSE NULL END, observation_time</b><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b> FROM ST_PixelAsCentroids(raster_record.clipped_raster, band_number, true) AS sc;</b><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b>From:</b> Manaswini Ganjam <<a href="mailto:manu.ganjam@gmail.com" target="_blank">manu.ganjam@gmail.com</a>> <br><b>Sent:</b> Thursday, November 16, 2023 1:19 PM<br><b>To:</b> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br><b>Cc:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] Extracting variable information from netcdf, imported as raster to a table<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thank you, Regina,<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I apologize, I have shared the older version of my code, I have tried ST_pixelascentroid and ST_value and the issue was I messed up the parameters of centroids and that caused the errors now I corrected that as well. I think the code below is a good representation of my approach, I would like to iterate for every raster in a table, for every band and for every lat and lon and extract st value, and insert it to a table, <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>psql:/home/manaswini/MEGA/bbgc_uw_rpackage/rproject/raster2pgsql/extract_table_queryyyy.sql:66: NOTICE: Invalid band index (must use 1-based). Returning empty set for all the coordinates and bands. What could be the issue. <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Is this because my data is packed? the readme file of the data has this information: <span style='color:black'>Details about the data packing:</span><o:p></o:p></p></div><pre><span style='color:black'>The data has been packed into short integers (2 bytes instead of 4 byte reals) to save space. You must unpack that data to get the correct floating point representation of the data. Each netCDF variable that has been packed has an add_offset and scale_factor attribute associated with it. Some software automatically unpacks the data when it is read.</span><o:p></o:p></pre><pre><span style='color:black'> </span><o:p></o:p></pre><pre><span style='color:black'>The formula to unpack the data is:</span><o:p></o:p></pre><pre><span style='color:black'> </span><o:p></o:p></pre><pre><span style='color:black'>unpacked value = add_offset + ( (packed value) * scale_factor )</span><o:p></o:p></pre><pre><span style='color:black'> </span><o:p></o:p></pre><pre><span style='color:black'>For more information see here:</span><o:p></o:p></pre><pre><span style='color:black'><a href="https://www.unidata.ucar.edu/software/netcdf/workshops/2010/bestpractices/Packing.html" target="_blank">https://www.unidata.ucar.edu/software/netcdf/workshops/2010/bestpractices/Packing.html</a></span><o:p></o:p></pre><pre><span style='color:black'> </span><o:p></o:p></pre><pre><span style='color:black'>There's also another attribute called "missing_value". In this case all the -32768 values you see are missing. Only the grid points outside the downscaling domain is given the missing data value.</span><o:p></o:p></pre><pre><span style='color:black'> </span><o:p></o:p></pre><pre><span style='color:black'>The packing saves a lot of space, that is why the data is packed.</span><o:p></o:p></pre><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>-- Create the precipitation_temperature_data table<br>DROP TABLE IF EXISTS extracted_data_bbox;<br>CREATE TABLE extracted_data_bbox (<br> id SERIAL PRIMARY KEY,<br> year integer,<br> year_day integer,<br> lat double precision,<br> lon double precision,<br> prcp double precision,<br> tmax double precision,<br> tmin double precision,<br> observation_time timestamp<br>);<br><br>-- Loop through all records in gfdl_03_bbox<br>DO $$<br>DECLARE<br> raster_record RECORD;<br> year_from_filename integer;<br> observation_time timestamp;<br> centroid_point geometry;<br> variable_name text;<br> variable_value double precision;<br> band_number integer;<br> band_numbers integer[];<br> pixel_data RECORD;<br>BEGIN<br> FOR raster_record IN (SELECT * FROM gfdl_03_bbox) LOOP<br> SELECT regexp_replace(raster_record.filename, '.*_(\d{4})[^0-9]+', '\1')::integer INTO year_from_filename;<br><br> -- Determine the variable name based on the filename<br> IF raster_record.filename ~ 'prcp' THEN<br> variable_name := 'prcp';<br> ELSIF raster_record.filename ~ 'tmax' THEN<br> variable_name := 'tmax';<br> ELSIF raster_record.filename ~ 'tmin' THEN<br> variable_name := 'tmin';<br> ELSE<br> RAISE EXCEPTION 'Unknown variable in filename: %', raster_record.filename;<br> END IF;<br><br> band_numbers := ARRAY(SELECT generate_series(1, 366));<br><br> -- Loop through all bands within the file<br> FOREACH band_number IN ARRAY band_numbers LOOP<br> -- Print the band number to the PostgreSQL log<br> RAISE NOTICE 'Band Number: %', band_number;<br><br> observation_time := MAKE_DATE(year_from_filename, 1, 1) + (band_number - 1) * interval '1 day';<br><br> SELECT * FROM ST_PixelAsCentroids(raster_record.clipped_raster, band_number, true) INTO pixel_data;<br><br> -- Extract the centroid point from pixel_data<br> centroid_point := pixel_data.geom;<br><br> -- Extract the variable value based on the variable name<br> EXECUTE format('SELECT ST_Value($1, $2, $3, true)', raster_record.clipped_raster, band_number, centroid_point) INTO variable_value USING raster_record.clipped_raster, band_number, centroid_point;<br><br> -- Insert the extracted data into the extracted_data_bbox table<br> INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, tmax, tmin, observation_time)<br> VALUES (year_from_filename, band_number, ST_Y(centroid_point), ST_X(centroid_point), CASE WHEN variable_name = 'prcp' THEN variable_value ELSE NULL END,<br> CASE WHEN variable_name = 'tmax' THEN variable_value ELSE NULL END,<br> CASE WHEN variable_name = 'tmin' THEN variable_value ELSE NULL END, observation_time);<br> END LOOP;<br> END LOOP;<br>END;<br>$$;<br><br>I have tried the following query to check values for a raster band : the output is again null psql:/home/manaswini/MEGA/bbgc_uw_rpackage/rproject/raster2pgsql/test_query_fff.sql:22: NOTICE: Band: 20, X: 1, Y: 135, Value: <NULL><o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>:<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>DO $$<br>DECLARE<br> band_number integer := 20; -- Replace with the desired band number<br> x_coord integer;<br> y_coord integer;<br> pixel_value double precision;<br> srid integer := 4326; -- Replace with the correct SRID for your data<br>BEGIN<br> -- Loop through all x and y coordinates in the raster band<br> FOR x_coord IN (SELECT generate_series(1, ST_Width(rast.clipped_raster)) FROM gfdl_03_bbox AS rast WHERE rast.filename = '<a href="http://prcp_03_2034.nc" target="_blank">prcp_03_2034.nc</a>') LOOP<br> FOR y_coord IN (SELECT generate_series(1, ST_Height(rast.clipped_raster)) FROM gfdl_03_bbox AS rast WHERE rast.filename = '<a href="http://prcp_03_2034.nc" target="_blank">prcp_03_2034.nc</a>') LOOP<br> -- Get the pixel value at the current x and y coordinates<br> SELECT ST_Value(ST_SetSRID(rast.clipped_raster, srid), band_number, ST_SetSRID(ST_Point(x_coord, y_coord), srid)) INTO pixel_value<br> FROM gfdl_03_bbox AS rast<br> WHERE rast.filename = '<a href="http://prcp_03_2034.nc" target="_blank">prcp_03_2034.nc</a>';<br><br> -- Print or use the pixel value as needed<br> RAISE NOTICE 'Band: %, X: %, Y: %, Value: %', band_number, x_coord, y_coord, pixel_value;<br> END LOOP;<br> END LOOP;<br>END;<br><br>Thank you,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Manaswini<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>On Wed, 15 Nov 2023 at 20:42, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I didn’t realize that netCDF also has a vector component.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><a href="https://gdal.org/drivers/vector/netcdf.html#vector-netcdf" target="_blank">https://gdal.org/drivers/vector/netcdf.html#vector-netcdf</a><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>To read the vector component, you’d use the ogr_fdw extension to read that rather than postgis_raster extension.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Details here - <a href="https://github.com/pramsey/pgsql-ogr-fdw" target="_blank">https://github.com/pramsey/pgsql-ogr-fdw</a><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So perhaps that is what your python is doing reading the vector component. I don’t know if netcdf mixes those in one data set or not since I have no experience using netcdf.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I recall you said you are using the OSGeo Live 16 distribution. I just checked the OSGeoLive 16 does include ogr_fdw <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So do <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>CREATE EXTENSION ogr_fdw;<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>The list of supported formats you can see with this query:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>SELECT name FROM unnest(ogr_fdw_drivers()) AS f(name) ORDER BY name;<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Which for osgeolive 16, I see netCDF listed<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b>From:</b> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> <br><b>Sent:</b> Wednesday, November 15, 2023 6:19 PM<br><b>To:</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Cc:</b> 'Manaswini Ganjam' <<a href="mailto:manu.ganjam@gmail.com" target="_blank">manu.ganjam@gmail.com</a>><br><b>Subject:</b> RE: [postgis-users] Extracting variable information from netcdf, imported as raster to a table<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Just confirming some stuff, since I’m not completely following:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Raster_record.rast column is of type raster correct? IF so ST_X and ST_Y won’t work since those are for geometry types.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Also ST_Value(raster_record.rast, band_number), won’t work either since that expects as input a geometry or x,y on the raster you want the value you.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I would think you would have gotten an error with that, which makes me feel I’m missing something critical.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>If you want to extract all the pixels in a raster, you’d do something like <a href="https://postgis.net/docs/RT_ST_PixelAsPoints.html" target="_blank">https://postgis.net/docs/RT_ST_PixelAsPoints.html</a><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>SELECT pp.x, pp.y, pp.val, ST_X(pp.geom) AS lon, ST_Y(pp.geom) AS lat<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>FROM raster_record, <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>ST_PixelAsPoints(raster_record.rast, 1) AS pp<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b>From:</b> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>> <b>On Behalf Of </b>Manaswini Ganjam via postgis-users<br><b>Sent:</b> Wednesday, November 15, 2023 2:01 PM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Cc:</b> Manaswini Ganjam <<a href="mailto:manu.ganjam@gmail.com" target="_blank">manu.ganjam@gmail.com</a>><br><b>Subject:</b> [postgis-users] Extracting variable information from netcdf, imported as raster to a table<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi, <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I have been trying to download s3 cloud stored gridded climate data and generate tables with variables, lat, lon and timestamp (year, yearday). To achieve this I used raster2pgsql and imported multiple netcdf files into a database table. <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Question: How to achieve the extraction of variables using postgis? I tried using ST_value, ST_pixelaspoints but I was getting errors, mainly due to the format in which netcdfs are stored in the database (the error says can't load some characters like 00E30100082...), I even tried changing the datatype to float but still did not work. I mean it is probably not simple like selecting a variable from the netcdf. I have enclosed my sql query below: <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> -- Iterate through all raster files in the table<br> FOR raster_record IN (SELECT * FROM gfdl_03_prcp) LOOP<br> -- Determine the year from the raster file name, assuming the format is '<a href="http://prcp_03_1950.nc" target="_blank">prcp_03_1950.nc</a>'<br> SELECT<br> regexp_replace(raster_record.filename, '.*_(\d{4})\.nc', '\1')::integer<br> INTO<br> year;<br> <br> -- Calculate the start date of the year<br> year_start := (year || '-01-01')::date;<br> <br> -- Determine if the year is a leap year<br> is_leap_year := EXTRACT(ISODOW FROM (year_start + interval '1 year')) = 7;<br> <br> -- Set the number of bands for the year (365 for non-leap years, 366 for leap years)<br> FOR band_number IN 1..(CASE WHEN is_leap_year THEN 366 ELSE 365 END) LOOP<br> -- Calculate the observation_time using the year and band number<br> observation_time := year_start + (band_number - 1) * interval '1 day';<br> <br> -- Extract X (lon) and Y (lat) coordinates from the raster<br> SELECT<br> ST_X(raster_record.rast) AS lon,<br> ST_Y(raster_record.rast) AS lat<br> INTO<br> lon,<br> lat;<br> <br> -- Insert the lat, lon, prcp, and observation_time into the extracted_values table<br> INSERT INTO extracted_values (lat, lon, prcp, observation_time)<br> VALUES<br> (lat, lon, ST_Value(raster_record.rast, band_number), observation_time);<br> <br> -- Increment the counter<br> counter := counter + 1;<br> <br> -- Commit the transaction periodically in batches<br> IF counter % batch_size = 0 THEN<br> COMMIT;<br> END IF;<br> END LOOP;<br> END LOOP;<br> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>The metadata for the two files is as follows: <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>File from database:<o:p></o:p></p></div><div><div><div><div><div><div><pre>{'NC_GLOBAL#Conventions': 'CF-1.5',<o:p></o:p></pre><pre> 'NC_GLOBAL#GDAL': 'GDAL 3.6.4, released 2023/04/17',<o:p></o:p></pre><pre> 'NC_GLOBAL#history': 'Wed Nov 15 13:32:13 2023: GDAL CreateCopy( <a href="http://not_clipped_prcp.nc" target="_blank">not_clipped_prcp.nc</a>, ... )'}<o:p></o:p></pre></div></div></div></div></div><div><pre>File before loading into the database:<o:p></o:p></pre><pre>{'lat#units': 'degrees_north',<o:p></o:p></pre><pre> 'lon#units': 'degrees_east',<o:p></o:p></pre><pre> 'NC_GLOBAL#title': 'Daily statistically downscaled CMIP5 data for the United States and southern Canada east of the Rocky Mountains, version 1.0, realization 1, 0.1x0.1 degree spatial resolution.',<o:p></o:p></pre><pre> 'NETCDF_DIM_EXTRA': '{time}',<o:p></o:p></pre><pre> 'NETCDF_DIM_time_DEF': '{366,4}',<o:p></o:p></pre><pre> 'NETCDF_DIM_time_VALUES': '{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14....362,363,364,365}',<o:p></o:p></pre><pre> 'prcp#add_offset': '819.17499',<o:p></o:p></pre><pre> 'prcp#long_name': 'daily precipitation accumulation',<o:p></o:p></pre><pre> 'prcp#missing_value': '-32768',<o:p></o:p></pre><pre> 'prcp#scale_factor': '0.025',<o:p></o:p></pre><pre> 'prcp#units': 'mm',<o:p></o:p></pre><pre> 'prcp#_FillValue': '-32768',<o:p></o:p></pre><pre> 'time#units': 'days since 1952-1-1 0:0:0.0'}<o:p></o:p></pre><pre> <o:p></o:p></pre><pre><span style='font-family:"Arial",sans-serif'>In case this information is useful: Previously I used python to extract variable information and generate a csv or table using this variable information, and the code is enclosed below. In the code I extracted variable values using lon = dataset.variables['lon'][:] and iterated for loops to write them all in csv. </span><o:p></o:p></pre></div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Python code:<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>import netCDF4 as nc<br><br># Step 1: Read the NetCDF file<br>filename = "/home/manaswini/<a href="http://prcp_03_1950.nc" target="_blank">prcp_03_1950.nc</a>"<br>dataset = nc.Dataset(filename)<br>dataset.set_auto_mask(False)<br>dataset.set_auto_scale(True)<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>lon = dataset.variables['lon'][:]<br>lat = dataset.variables['lat'][:]<br>time = dataset.variables['time'][:]<br>prcp = dataset.variables['prcp'][:]<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>import numpy as np<br>import csv<br><br># csv_buffer<br>csv_buffer = open('output.csv', 'w', newline='')<br>csv_writer = csv.writer(csv_buffer)<br><br># Iterate through grid points and write to CSV buffer<br>for i in enumerate(lon):<br> for j in enumerate(lat):<br> for k in enumerate(time):<br> csv_writer.writerow([lat[j], lon[i], prcp[i][j][k], year[k], yearday[k]])<br><br><br># Close the CSV buffer<br>csv_buffer.close()<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thank you, <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-family:"Trebuchet MS",sans-serif'>Manaswini Ganjam</span><o:p></o:p></p></div></div></div></div></div></div></div></div></div></div></div></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br clear=all><o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>-- <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt;font-family:"Trebuchet MS",sans-serif'>Manaswini Ganjam</span><o:p></o:p></p></div></div></div></div></div></div></div></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br clear=all><o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=m-2066442463817014692gmailsignatureprefix>-- </span><o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt;font-family:"Trebuchet MS",sans-serif'>Manaswini Ganjam</span><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p></blockquote></div></blockquote></div></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><br clear=all><o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=m-2066442463817014692gmailsignatureprefix>-- </span><o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt;font-family:"Trebuchet MS",sans-serif'>Manaswini Ganjam</span><o:p></o:p></p></div></div></div></div></div></div></blockquote></div><p class=MsoNormal><br clear=all><o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><span class=gmailsignatureprefix>-- </span><o:p></o:p></p><div><div><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Trebuchet MS",sans-serif'>Manaswini Ganjam</span><o:p></o:p></p></div></div></div></div></body></html>