SQLData Manipulation (DML)System Table Functions

tf_geo_rasterize

View as Markdown

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

ParameterDescriptionData TypesDescription
xX-coordinate column or expressionColumn<FLOAT | DOUBLE>
yY-coordinate column or expressionColumn<FLOAT | DOUBLE>
zZ-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_typeThe 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_metersThe 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.DOUBLEBinned dimension, in meters.
geographic_coordsIf 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.BOOLEANGeographic coordinates.
neighborhood_fill_radiusThe 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_nullsSpecifies 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

NameDescriptionData Types
xThe 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)
zThe maximum z-coordinate of all input data assigned to a given spatial bin.Column<FLOAT | DOUBLE> (same as input z-coordinate column/expression)

Example

1/* Bin 10cm USGS LiDAR from Tallahassee to 1 meter, taking the minimum z-value
2for each xy-bin. Then for each xy-bin, perform a Gaussian-average over the neighboring
3100 xy-bins. This query yields the approximate terrain for an area after removing human-made
4structures (due to the wide 100-bin Gaussian-average window), as can be seen in the
5right-hand render result in the screenshot below. Note that the LIMIT was only
6applied to this SQL query and is not used in the rendered-screenshot below. */
7
8SELECT
9  x,
10  y,
11  z
12FROM
13  TABLE(
14    tf_geo_rasterize(
15      raster => CURSOR(
16        SELECT
17          ST_X(pt),
18          ST_Y(pt),
19          z
20        FROM
21          USGS_LPC_FL_LeonCo_2018_049377_N_LAS_2019
22      ),
23      bin_dim_meters => 1,
24      geographic_coords => TRUE,
25      neighborhood_fill_radius => 100,
26      fill_only_nulls => FALSE,
27      agg_type => 'MIN',
28      fill_agg_type => 'GAUSS_AVG'
29    )
30  ) limit 20;
31
32x|y|z
33-84.29857764791747|30.40240526206634|-15.30264
34-84.29086331121893|30.40264801040913|-17.25718
35-84.29856722313815|30.40240526206634|-15.31047
36-84.29855679835883|30.40240526206634|-15.31835
37-84.29085288643959|30.40264801040913|-17.25859
38-84.2985463735795|30.40240526206634|-15.32627
39-84.30278925876371|30.402198476441|-17.09047
40-84.29084246166028|30.40264801040913|-17.25993
41-84.30277883398438|30.402198476441|-17.10194
42-84.29853594880018|30.40240526206634|-15.33422
43-84.30276840920506|30.402198476441|-17.11329
44-84.29083203688096|30.40264801040913|-17.26122
45-84.30275798442574|30.402198476441|-17.12446
46-84.29852552402086|30.40240526206634|-15.34223
47-84.30274755964642|30.402198476441|-17.1354
48-84.29878614350392|30.40263002905041|-14.74146
49-84.29119690415723|30.40236030866953|-17.22919
50-84.30449892257258|30.40238728070761|-15.9867
51-84.29328186002171|30.40223443915845|-17.63177
52-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.