SQLData Manipulation (DML)System Table Functions

tf_geo_rasterize_slope

View as Markdown

Similar to tf_geo_rasterize, but also computes the slope and aspect per output bin.

Aggregates point data into x/y bins of a given size in meters to form a dense spatial grid, computing the specified aggregate (using agg_type) across all points in each bin as the output value for the bin. A Gaussian average is then taken over the neighboring bins, with the number of bins specified by neighborhood_fill_radius, optionally only filling in null-valued bins if fill_only_nulls is set to true. The slope and aspect is then computed for every bin, based on the z values of that bin and its neighboring bins. The slope can be returned in degrees or as a fraction between 0 and 1, depending on the boolean argument to compute_slope_in_degrees.

Note that 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_slope table function, these filters will also constrain the output range.

SELECT * FROM TABLE(
  tf_geo_rasterize_slope(
      raster => CURSOR(
        SELECT 
           x, y, z FROM table
      ),
      agg_type => <'AVG'|'COUNT'|'SUM'|'MIN'|'MAX'>,
      bin_dim_meters => <meters>, 
      geographic_coords => <true/false>, 
      neighborhood_fill_radius => <radius in bins>,
      fill_only_nulls => <true/false>,
      compute_slope_in_degrees => <true/false>
    )
 )

Input Arguments

ParameterDescriptionData TypesDescription
xInput x-coordinate column or expression.Column<FLOAT | DOUBLE>
yInput y-coordinate column or expression.Column<FLOAT | DOUBLE>
zInput 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_typeThe aggregate to be performed to compute the output z-column. Should be one of ‘AVG’, ‘COUNT’, ‘SUM’, ‘MIN’, or ‘MAX’.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.DOUBLE
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.BOOLEAN
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.BIGINT
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
compute_slope_in_degreesIf true, specifies the slope should be computed in degrees (with 0 degrees perfectly flat and 90 degrees perfectly vertical). If false, specifies the slope should be computed as a fraction from 0 (flat) to 1 (vertical). In a future release, we are planning to move the default output to percentage slope.BOOLEAN

Output Columns

NameDescriptionData Types
xThe x-coordinates for the centroids of the output spatial bins.Column<FLOAT | DOUBLE> (same as input x column/expression)
yThe y-coordinates for the centroids of the output spatial bins.Column<FLOAT | DOUBLE> (same as input y column/expression)
zThe maximum z-coordinate of all input data assigned to a given spatial bin.Column<FLOAT | DOUBLE> (same as input z column/expression)
slopeThe average slope of an output grid cell (in degrees or a fraction between 0 and 1, depending on the argument to compute_slope_in_degrees).Column<FLOAT | DOUBLE> (same as input z column/expression)
aspectThe direction from 0 to 360 degrees pointing towards the maximum downhill gradient, with 0 degrees being due north and moving clockwise from N (0°) -> NE (45°) -> E (90°) -> SE (135°) -> S (180°) -> SW (225°) -> W (270°) -> NW (315°).Column<FLOAT | DOUBLE> (same as input z column/expression)

Example

/* Compute the slope and aspect ratio for a 30-meter Copernicus 
Digital Elevation Model (DEM) raster, binned to 90-meters */
select
  *
from
  table(
    tf_geo_rasterize_slope(
      raster => cursor(
        select
          st_x(raster_point),
          st_y(raster_point),
          CAST(z AS float)
        from
          copernicus_30m_mt_everest
      ),
      agg_type => 'AVG',
      bin_dim_meters => 90.0,
      geographic_coords => true,
      neighborhood_fill_radius => 1,
      fill_only_nulls => false,
      compute_slope_in_degrees => true
    )
  )
order by
  slope desc nulls last
limit
  20;
  
x|y|z|slope|aspect
86.96533511629579|27.96534132281817|6212.096|78.37033|18.09232
87.23751907091268|27.78489838800869|3793.584|78.17864|125.03
87.23660262662104|27.78408922686605|3929.989|78.06877|127.629
86.96625156058742|27.96534132281817|6041.277|78.00574|19.00616
87.2356861823294|27.78328006572341|3981.662|77.53327|127.3175
86.96441867200414|27.96615048396082|5869.373|77.3751|20.82031
86.95800356196267|27.96857796738875|6083.791|77.13709|29.89468
86.96350222771251|27.96615048396082|6081.35|77.08266|21.6792
87.23843551520432|27.78570754915134|3630.32|77.04676|125.2154
86.96441867200414|27.96534132281817|6378.94|76.95021|17.77107
87.22468885082972|27.81321902800121|4771.554|76.71017|253.2764
87.2356861823294|27.78247090458076|3520.049|76.63997|113.6511
87.23660262662104|27.78328006572341|3445.282|76.38319|127.2889
86.96716800487906|27.96534132281817|5864.711|76.16835|19.27573
87.23476973803776|27.78166174343812|3945.683|76.13519|102.7789
86.95708711767104|27.96857796738875|6336.072|76.13168|24.90349
87.22468885082972|27.81240986685857|4732.937|76.07494|264.7046
87.23751907091268|27.78408922686605|3367.659|76.0099|126.7463
86.9589200062543|27.9677688062461|6223.083|75.46346|26.85898
87.22377240653809|27.81402818914385|4704.619|75.41299|205.3219

Inline generation of slope-field using the above example query, showing the computed slopes over 90-meter binned Copernicus 30m DEM data. Note that this can be done in Immerse using a custom source, and optionally parametrized if desired. The direction of the slope (aspect) is indicated by the direction of the arrows.