Skip to content

UDSF: Spatial Functions

Jump to bottom
Admin edited this page May 13, 2018 · 4 revisions

Aggregation performed spatially across the study area, producing a single value.

Avg_S

  • Syntax: Avg_S(grid) returns the mean of all cell values for each passed grid.
  • Example:
/*Calculate the global hourly mean land surface temperature(TSURF) for 10/2010.*/
SELECT time, Avg_S(grid) FROM MST1NXMLD 
WHERE variable = "TSURF" AND year = 2010 AND month=10 ORDER BY time

Max_S

  • Syntax: Max_S(grid) returns the maxima of all cell values for each passed grid.
  • Example:
/*Caculate the maximum monthly liquid water convective precipitation(PRECCU) in 1998.*/	
SELECT month, Max_S(grid) FROM MATMNXINT 
WHERE variable = "PRECCU" AND year = 1998 ORDER BY month
  • Example 2:
/*
Calculate the yearly number of extreme heat days between 1980 and 2015 in Los Angeles area (AOI = REGION.BboxOnMap). 
*/
drop view if exists daily;
create view daily as
select year,month,day,if(sum(if(Max_S(grid)>308.15,1,0))>=1,1,0) as isExtreme 
from MST1NXMLD  
where variable='TSURF' and year between 1980 and 2015 
and AOI = REGION.BboxOnMap
group by year,month,day;

select year,sum(isExtreme) as extreme_days_x
from daily
group by year order by year asc
visualize as vis.TimeSeries

Try it yourself: http://gis.cas.sc.edu/scovas?id=8475969214283

Min_S

  • Syntax: Min_S(grid) returns the minima of all cell values for each passed grid.
  • Example:
/*Find the lowest land surface temperature at 6PM(UTC) for each day in July 2000.*/	
SELECT day, Min_S(grid) FROM MST1NXMLD 
WHERE variable = "TSURF" AND year = 2000 AND month=7 AND hour = 18 ORDER BY day

Sum_S

  • Syntax: Sum_S(grid) returns the sum of all cell values for each passed grid.
  • Example:
SELECT Sum_S(grid) FROM MATMNXINT
WHERE variable = "PRECCU" AND year = 1980 AND month = 9

Count_S

  • Syntax: Count_S(grid) returns the number of cells for each passed grid.
  • Example:
SELECT Count_S(grid) FROM MST1NXMLD 
WHERE variable = "TSURF" AND time = 2010100104

Corr_S

  • Syntax: Corr_S(grid1,grid2) returns a correlation coefficient value(Pearson's r) between the cell values of grid1 and grid2.
  • Example:
/*Calculate the correlation coefficient between temperature and precipitation for each month in 2015 in U.S.*/
SELECT a.month,Corr_S(Avg_T(a.grid),Avg_T(b.grid)) as PearsonR_x
From MST1NXMLD a 
	JOIN MST1NXMLD b ON (a.time = b.time)
WHERE a.variable= "PRECTOT" and b.variable='TSURF' and a.year = 2015
AND AOI = REGION.BboxOnMap
GROUP BY a.month ORDER BY a.month
VISUALIZE AS vis.TimeSeries

Try it yourself: http://gis.cas.sc.edu/scovas?id=1526173638857

Clone this wiki locally