SQLData Manipulation (DML)System Table Functions

tf_raster_graph_shortest_slope_weighted_path

View as Markdown

Aggregate 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 graph shortest path is then computed between an origin point on the grid specified by origin_x and origin_y and a destination point on the grid specified by destination_x and destination_y, where the shortest path is weighted by the nth exponent of the computed slope between a bin and its neighbors, with the nth exponent being specified by slope_weighted_exponent. A max allowed traversable slope can be specified by slope_pct_max, such that no traversal is considered or allowed between bins with absolute computed slopes greater than the percentage specified by slope_pct_max.

SELECT * FROM TABLE(
    tf_raster_graph_shortest_slope_weighted_path(
        raster => CURSOR(
            SELECT x, y, z FROM table
        ),
        agg_type => <'AVG'|'COUNT'|'SUM'|'MIN'|'MAX'>,
        bin_dim => <meters>,
        geographic_coords => <true/false>,
        neighborhood_fill_radius => <num bins>,
        fill_only_nulls => <true/false>,
        origin_x => <origin x coordinate>,
        origin_y => <origin y coordinate>,
        destination_x => <destination x coordinate>,
        destination_y => <destination y coordinate>,
        slope_weighted_exponent => <exponent>,
        slope_pct_max => <max pct slope>
    )

Input Arguments

ParameterDescriptionData Types
xInput x-coordinate column or expression of the data to be rasterized.Column <FLOAT | DOUBLE>
yInput y-coordinate column or expression of the data to be rasterized.Column <FLOAT | DOUBLE> (must be the same type as x)
zInput z-coordinate column or expression of the data to be rasterized.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_dimThe width and height of each x/y bin . If geographic_coords is true, the input x/y units will be translated to meters according to a local coordinate transform appropriate for the x/y bounds of the data.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_bin_radiusThe radius in bins to compute the gaussian 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 gaussian 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
origin_xThe x-coordinate for the starting point for the graph traversal, in input (not bin) units.DOUBLE
origin_yThe y-coordinate for the starting point for the graph traversal, in input (not bin) units.DOUBLE
destination_xThe x-coordinate for the destination point for the graph traversal, in input (not bin) units.DOUBLE
destination_yThe y-coordinate for the destination point for the graph traversal, in input (not bin) units.DOUBLE
slope_weighted_exponentThe slope weight between neighboring raster cells will be weighted by the slope_weighted_exponent power. A value of 1 signifies that the raw slopes between neighboring cells should be used, increasing this value from 1 will more heavily penalize paths that traverse steep slopes.DOUBLE
slope_pct_maxThe max absolute value of slopes (measured in percentages) between neighboring raster cells that will be considered for traversal. A neighboring graph cell with an absolute slope greater than this amount will not be considered in the shortest slope-weighted path graph traversalDOUBLE

Output Columns

/* Compute the shortest slope weighted path over a 30m Copernicus 
Digital Elevation Model (DEM) input raster comprising the area around Mt. Everest,
to compute the shorest slope-weighted path from the plains of Nepal to the peak */
create table mt_everest_climb as
select
  path_step,
  st_setsrid(st_point(x, y), 4326) as path_pt
from
  table(
    tf_raster_graph_shortest_slope_weighted_path(
      raster => cursor(
        select
          st_x(raster_point),
          st_y(raster_point),
          z
        from
          copernicus_30m_mt_everest
      ),
      agg_type => 'AVG',
      bin_dim => 30,
      geographic_coords => TRUE,
      neighborhood_fill_radius => 1,
      fill_only_nulls => FALSE,
      origin_x => 86.01,
      origin_y => 27.01,
      destination_x => 86.9250,
      destination_y => 27.9881,
      slope_weight_exponent => 4,
      slope_pct_max => 50
    )
  );

Result of the example query above, showing the shortest slope-weighted path between the Nepali planes and the peak of Mt. Everest. The path closely mirrors the actual climbing route used.