tf_geo_rasterize

Aggregate point data into x/y bins of a given size in meters to form a dense spatial grid, with taking the maximum z value across all points in each bin as the output value for the bin. The aggregate performed to compute the value for each bin is specified by agg_type, with allowed aggregate types of AVG, COUNT, SUM, MIN, and MAX. If neighborhood_fill_radius is set greater than 0, a blur pass/kernel will be computed on top of the results according to the optionally-specified fill_agg_type, with allowed types of GAUSS_AVG, BOX_AVG, COUNT, SUM, MIN, and MAX (if not specified, defaults to GAUSS_AVG, or a Gaussian-average kernel). if fill_only_nulls is set to true, only null bins from the first aggregate step will have final output values computed from the blur pass, otherwise if false all values will be affected by the blur pass.

Note that the arguments to bound the spatial output grid (x_min, x_max, y_min, y_max) are optional, however either all or none of these arguments must be supplied. If the arguments are not supplied, the bounds of the spatial output grid will be bounded by the x/y range of the input query, and if SQL filters are applied on the output of the tf_geo_rasterize table function, these filters will also constrain the output range.

SELECT * FROM TABLE(
  tf_geo_rasterize(
      raster => CURSOR(
        SELECT 
           x, y, z FROM table
      ),
      agg_type => <'AVG'|'COUNT'|'SUM'|'MIN'|'MAX'>,
      /* fill_agg_type is optional */
      [<fill_agg_type> => <'AVG'|'COUNT'|'SUM'|'MIN'|'MAX'|'GAUSS_AVG'|'BOX_AVG'>,] 
      bin_dim_meters => <meters>, 
      geographic_coords => <true/false>, 
      neighborhood_fill_radius => <radius in bins>,
      fill_only_nulls => <true/false> [,
      <x_min> => <minimum output x-coordinate>,
      <x_max> => <maximum output x-coordinate>,
      <y_min> => <minimum output y-coordinate>,
      <y_max> => <maximum output y-coordinate>]
    ) 
  )...

Input Arguments

Parameter	Description	Data Types
x	X-coordinate column or expression	Column<FLOAT | DOUBLE>
y	Y-coordinate column or expression	Column<FLOAT | DOUBLE>
z	Z-coordinate column or expression. The output bin is computed as the maximum z-value for all points falling in each bin.	Column<FLOAT | DOUBLE>
agg_type	The aggregate to be performed to compute the output z-column. Should be one of 'AVG', 'COUNT', 'SUM', 'MIN', or 'MAX'.	TEXT ENCODING NONE
fill_agg_type (optional)	The aggregate to be performed when computing the blur pass on the output bins. Should be one of 'AVG', 'COUNT', 'SUM', 'MIN', 'MAX', ' 'AVG', 'COUNT', 'SUM', 'GAUSS_AVG', or 'BOX_AVG'. Note that AVG is synonymous with GAUSS_AVG in this context, and the default fill_agg_type if not specified is GAUSS_AVG.	TEXT ENCODING NONE
bin_dim_meters	The width and height of each x/y bin in meters. If geographic_coords is not set to true, the input x/y units are already assumed to be in meters.	DOUBLE
geographic_coords	If true, specifies that the input x/y coordinates are in lon/lat degrees. The function will then compute a mapping of degrees to meters based on the center coordinate between x_min/x_max and y_min/y_max.	BOOLEAN
neighborhood_fill_radius	The radius in bins to compute the box blur/filter over, such that each output bin will be the average value of all bins within neighborhood_fill_radius bins.	DOUBLE
fill_only_nulls	Specifies that the box blur should only be used to provide output values for null output bins (i.e. bins that contained no data points or had only data points with null Z-values).	BOOLEAN
x_min (optional)	Min x-coordinate value (in input units) for the spatial output grid.	DOUBLE
x_max (optional)	Max x-coordinate value (in input units) for the spatial output grid.	DOUBLE
y_min (optional)	Min y-coordinate value (in input units) for the spatial output grid.	DOUBLE
y_max (optional)	Max y-coordinate value (in input units) for the spatial output grid.	DOUBLE

Output Columns

Name	Description	Data Types
x	The x-coordinates for the centroids of the output spatial bins.	Column<FLOAT | DOUBLE> (same as input x-coordinate column/expression)
y	The y-coordinates for the centroids of the output spatial bins.	Column<FLOAT | DOUBLE> (same as input y-coordinate column/expression)
z	The maximum z-coordinate of all input data assigned to a given spatial bin.	Column<FLOAT | DOUBLE> (same as input z-coordinate column/expression)

Example

/* Bin 10cm USGS LiDAR from Tallahassee to 1 meter, taking the minimum z-value
for each xy-bin. Then for each xy-bin, perform a Gaussian-average over the neighboring
100 xy-bins. This query yields the approximate terrain for an area after removing human-made
structures (due to the wide 100-bin Gaussian-average window), as can be seen in the 
right-hand render result in the screenshot below. Note that the LIMIT was only
applied to this SQL query and is not used in the rendered-screenshot below. */

SELECT
  x,
  y,
  z
FROM
  TABLE(
    tf_geo_rasterize(
      raster => CURSOR(
        SELECT
          ST_X(pt),
          ST_Y(pt),
          z
        FROM
          USGS_LPC_FL_LeonCo_2018_049377_N_LAS_2019
      ),
      bin_dim_meters => 1,
      geographic_coords => TRUE,
      neighborhood_fill_radius => 100,
      fill_only_nulls => FALSE,
      agg_type => 'MIN',
      fill_agg_type => 'GAUSS_AVG'
    )
  ) limit 20;
  
x|y|z
-84.29857764791747|30.40240526206634|-15.30264
-84.29086331121893|30.40264801040913|-17.25718
-84.29856722313815|30.40240526206634|-15.31047
-84.29855679835883|30.40240526206634|-15.31835
-84.29085288643959|30.40264801040913|-17.25859
-84.2985463735795|30.40240526206634|-15.32627
-84.30278925876371|30.402198476441|-17.09047
-84.29084246166028|30.40264801040913|-17.25993
-84.30277883398438|30.402198476441|-17.10194
-84.29853594880018|30.40240526206634|-15.33422
-84.30276840920506|30.402198476441|-17.11329
-84.29083203688096|30.40264801040913|-17.26122
-84.30275798442574|30.402198476441|-17.12446
-84.29852552402086|30.40240526206634|-15.34223
-84.30274755964642|30.402198476441|-17.1354
-84.29878614350392|30.40263002905041|-14.74146
-84.29119690415723|30.40236030866953|-17.22919
-84.30449892257258|30.40238728070761|-15.9867
-84.29328186002171|30.40223443915845|-17.63177
-84.29432433795395|30.40263901972977|-17.85748  

Heavy Immerse dashboard showing a raw Tallahasse LiDAR dataset on the left, and a smoothed version on the right using the min z values for each 1-meter binned cell of the LiDAR data, Gaussian-smoothed over the neighboring 100 cells.