Search…
SQL Capabilities
DML allows you to update and query data stored in HEAVY.AI.
See Using Geospatial Objects: Geospatial Functions for details on geospatial functions.

INSERT

Use for single-row ad hoc inserts. (When inserting many rows, use the more efficient COPY command.)
1
INSERT INTO <destination_table> VALUES (<value>, ...);
Copied!
1
INSERT INTO <table> (<column>, ...) VALUES (value, ...);
Copied!

Examples

1
CREATE TABLE foo (a INT, b FLOAT, c TEXT, d TIMESTAMP);
2
INSERT INTO foo VALUES (NULL, 3.1415, 'xyz', '2015-05-11 211720');
Copied!
You can also insert into a table as SELECT, as shown in the following examples:
Example 1
Example 2
Example 3
Example 4
1
INSERT INTO destination_table SELECT * FROM source_table;
Copied!
1
INSERT INTO destination_table (id, name, age, gender) SELECT * FROM source_table;
Copied!
1
INSERT INTO destination_table (name, gender, age, id) SELECT name, gender, age, id FROM source_table;
Copied!
1
INSERT INTO votes_summary (vote_id, vote_count) SELECT vote_id, sum(*) FROM votes GROUP_BY vote_id;
Copied!
You can insert array literals into array columns. The inserts in the following example each have three array values, and demonstrate how you can:
  • Create a table with variable-length and fixed-length array columns.
  • Insert NULL arrays into these colums.
  • Specify and insert array literals using {...} or ARRAY[...] syntax.
  • Insert empty variable-length arrays using{} and ARRAY[] syntax.
  • Insert array values that contain NULL elements.
1
CREATE TABLE ar (ai INT[], af FLOAT[], ad2 DOUBLE[2]);
2
INSERT INTO ar VALUES ({1,2,3},{4.0,5.0},{1.2,3.4});
3
INSERT INTO ar VALUES (ARRAY[NULL,2],NULL,NULL);
4
INSERT INTO ar VALUES (NULL,{},{2.0,NULL});
Copied!

Default Values

If you create a table with column that has a default value, or alter a table to add a column with a default value, using the INSERT command creates a record that includes the default value if it is omitted from the INSERT. For example, assume a table created as follows:
1
CREATE TABLE tbl (
2
id INTEGER NOT NULL,
3
name TEXT NOT NULL DEFAULT 'John Doe',
4
age SMALLINT NOT NULL);
Copied!
If you omit the name column from an INSERT or INSERT FROM SELECT statement, the missing value for column name is set to 'John Doe'.
INSERT INTO tbl (id, age) VALUES (1, 36); creates the record 1|'John Doe'|36 .
INSERT INTO tbl (id, age) SELECT id, age FROM old_tbl; also sets all the name values to John Doe .

SELECT

1
query:
2
| WITH withItem [ , withItem ]* query
3
| {
4
select
5
}
6
[ ORDER BY orderItem [, orderItem ]* ]
7
[ LIMIT [ start, ] { count | ALL } ]
8
[ OFFSET start { ROW | ROWS } ]
9
10
withItem:
11
name
12
[ '(' column [, column ]* ')' ]
13
AS '(' query ')'
14
15
orderItem:
16
expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
17
18
select:
19
SELECT [ DISTINCT ] [/*+ hints */]
20
{ * | projectItem [, projectItem ]* }
21
FROM tableExpression
22
[ WHERE booleanExpression ]
23
[ GROUP BY { groupItem [, groupItem ]* } ]
24
[ HAVING booleanExpression ]
25
[ WINDOW window_name AS ( window_definition ) [, ...] ]
26
27
projectItem:
28
expression [ [ AS ] columnAlias ]
29
| tableAlias . *
30
31
tableExpression:
32
tableReference [, tableReference ]*
33
| tableExpression [ ( LEFT ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
34
35
joinCondition:
36
ON booleanExpression
37
| USING '(' column [, column ]* ')'
38
39
tableReference:
40
tablePrimary
41
[ [ AS ] alias ]
42
43
tablePrimary:
44
[ catalogName . ] tableName
45
| '(' query ')'
46
47
groupItem:
48
expression
49
| '(' expression [, expression ]* ')'
Copied!
For more information, see SELECT.

ORDER BY

  • Sort order defaults to ascending (ASC).
  • Sorts null values after non-null values by default in an ascending sort, before non-null values in a descending sort. For any query, you can use NULLS FIRST to sort null values to the top of the results or NULLS LAST to sort null values to the bottom of the results.
  • Allows you to use a positional reference to choose the sort column. For example, the command SELECT colA,colB FROM table1 ORDER BY 2 sorts the results on colB because it is in position 2.

SELECT Hints

HEAVY.AI provides various query hints for controlling the behavior of the query execution engine.

Syntax

1
SELECT /*+ hint */ FROM ...;
Copied!
SELECT hints must appear first, immediately after the SELECT statement; otherwise, the query fails.
By default, a hint is applied to the query step in which it is defined. If you have multiple SELECT clauses and define a query hint in one of those clauses, the hint is applied only to the specific query step; the rest of the query steps are unaffected. For example, applying the /* cpu_mode */ hint affects only the SELECT clause in which it exists.
You can define a hint to apply to all query steps by prepending g_ to the query hint. For example, if you define /*+ g_cpu_mode */, CPU execution is applied to all query steps.
HEAVY.AI supports the following query hints.
Marker
Key-Value pair
The marker hint type represents a Boolean flag.
Hint
Details
Example
cpu_mode
Force CPU execution mode.
SELECT /*+ cpu_mode */ ...
columnar_output
Enable columnar output for the input query.
SELECT /+* columnar_output */ ...
keep_result
Add result set of the input query to the result set cache.
SELECT /+* keep_result */ ...
keep_table_function_result
Add result set of the table function query to the result set cache.
SELECT /+* keep_table_function_result */ ...
overlaps_allow_gpu_build
Use GPU (if available) to build an overlaps join hash table. (CPU is used by default.)
SELECT /+* overlaps_allow_gpu_build */ ...
overlaps_no_cache
Skip adding an overlaps join hash table to the hash table cache.
SELECT /+* overlaps_no_cache */ ...
rowwise_output
Enable row-wise output for the input query.
SELECT /+* rowwise_output */ ...
The key-value pair type is a hint name and its value.
Hint
Details
Example
overlaps_bucket_threshold
Set the overlaps bucket threshold.
  • Value type: DOUBLE
  • Range: 0-90
Set the overlaps threshold to 10:
SELECT /*+ overlaps_bucket_threshold(10.0) */ ...
overlaps_max_size
Set the maximum overlaps size.
  • Value type: INTEGER
  • Range: >=0
Set the maximum overlap to 10: SELECT /*+ overlaps_max_size(10.0) */ ...
overlaps_keys_per_bin
Set the number of overlaps keys per bin.
  • Value type: DOUBLE
  • Range: 0.0 < x < double::max
SELECT /+* overlaps_keys_per_bin(0.1) */ ...

System Table Functions

HEAVY.AI provides access to a set system-provided table functions, also known as table-valued functions (TVS). System table functions, like user-defined table functions, support execution of queries on both CPU and GPU over one or more SQL result-set inputs. Table function support in HEAVY.AI can be split into two broad categories: system table functions and user-defined table functions (UDTFs). System table functions are built-in to the HEAVY.AI server, while UDTFs can be declared dynamically at run-time by specifying them in Numba subset of the Python language. For more information on UDTFs, see User-Defined Table Functions.
To improve performance, table functions can be optionally declared to enable filter pushdown optimization, which allows the Calcite optimizer to "push down" filters on the output(s) of a table functions to its input(s) when the inputs and outputs are declared to be semantically equivalent (for example, a longitude variable that is inputted and outputted from a table function). This can significantly increase performance in cases where only a small portion of one or more input tables are required to compute the filtered output of a table function.
Whether system- or user-provided, table functions can execute over one or more result sets specified by subqueries, and can also take any number of additional constant literal arguments specified in the function definition. The following example function takes two input subqueries, which are required to be wrapped in CURSOR arguments, and four literal (constant) arguments. SQL subquery inputs can consist of any SQL expression (including multiple subqueries, joins, and so on) allowed by HeavyDB, and the output can be filtered, grouped by, joined, and so on like a normal SQL subquery, including being inputted into additional table functions by wrapping in a CURSOR argument. The number and types of input arguments, as well as the number and types of output arguments, are specified in the table function definition itself.

Filter Push Down

1
SELECT * FROM (TABLE(my_table_function /* This is only an example! */ (
2
CURSOR(SELECT arg1, arg2, arg3 FROM input_1 WHERE x > 10),
3
CURSOR(SELECT arg1, AVG(arg2) FROM input_2 GROUP BY arg1 where y < 40),
4
'Fred' /* name_argument */,
5
true /* compute special result */,
6
42 /* a very special integer argument */,
7
27.3 /* another nice argument */))
8
WHERE output1 BETWEEN 32.2 AND 81.8;
Copied!
Table functions allow for the efficient execution of advanced algorithms that may be difficult or impossible to express in canonical SQL. By allowing execution of code directly over SQL result sets, leveraging the same hardware parallelism used for fast SQL execution and visualization rendering, HEAVY.AI provides orders-of-magnitude speed increases over the alternative of transporting large result sets to other systems for post-processing and then returning to HEAVY.AI for storage or downstream manipulation. You can easily invoke system-provided or user-defined algorithms directly inline with SQL and rendering calls, making prototyping and deployment of advanced analytics capabilities easier and more streamlined.
The following table functions are available in HEAVY.AI:
The TABLE command is required to wrap a table function clause; for example: select * from TABLE(generate_series(1, 10));
The CURSOR command is required to wrap any subquery inputs.

generate_random_strings

Generates random string data.
1
SELECT * FROM TABLE(generate_random_strings(<num_strings>, <string_length>/)
Copied!

Input Arguments

Parameter
Description
Data Type
<num_strings>
The number of strings to randomly generate.
BIGINT
<string_length>
Length of the generated strings.
BIGINT

Output Columns

Name
Description
Data Type
id
Integer id of output, starting at 0 and increasing monotonically
BIGINT
rand_str
Random String
TEXT ENCODING DICT
Example
1
heavysql> SELECT * FROM TABLE(generate_random_strings(10, 20);
2
id|rand_str
3
0 |He9UeknrGYIOxHzh5OZC
4
1 |Simnx7WQl1xRihLiH56u
5
2 |m5H1lBTOErpS8is00YJ
6
3 |eeDiNHfKzVQsSg0qHFS0
7
4 |JwOhUoQEI6Z0L78mj8jo
8
5 |kBTbSIMm25dvf64VMi
9
6 |W3lUUvC5ajm0W24JML
10
7 |XdtSQfdXQ85nvaIoyYUY
11
8 |iUTfGN5Jaj25LjGJhiRN
12
9 |72GUoTK2BzcBJVTgTGW
Copied!

generate_series

Generate a series of integer values.
1
SELECT * FROM TABLE(generate_series(<start_num>, <end_num>[, <increment>])
Copied!

Input Arguments

Parameter
Description
Data Type
<start_num>
Starting integer value.
BIGINT
<end_num>
Ending integer value.
BIGINT
<increment> (optional, defaults to 1)
Increment to increase or decrease and values that follow. Integer.
BIGINT

Output Columns

Name
Description
Data Type
generate_series
The integer series specified by the input arguments.
BIGINT
Example
1
heavysql> select * from table(generate_series(2, 10, 2));
2
series
3
2
4
4
5
6
6
8
7
10
8
5 rows returned.
9
10
heavysql> select * from table(generate_series(8, -4, -3));
11
series
12
8
13
5
14
2
15
-1
16
-4
17
5 rows returned.
Copied!

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. optionally applying a box filter on each point on the output grid to either fill in null values or spatially weight each output value based on the values of neighboring bins.
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.
1
SELECT * FROM TABLE(
2
tf_geo_rasterize(
3
raster => CURSOR(
4
SELECT
5
x, y, z FROM table
6
),
7
bin_dim_meters => <meters>,
8
geographic_coords => <true/false>,
9
neighborhood_fill_radius => <radius in bins>,
10
fill_only_nulls => <true/false> [,
11
<x_min> => <minimum output x-coordinate>,
12
<x_max> => <maximum output x-coordinate>,
13
<y_min> => <minimum output y-coordinate>,
14
<y_max> => <maximum output y-coordinate>]
15
)
16
)...
Copied!

Input Arguments

Parameter
Description
Data Types
x
X-coordinate column or expression
FLOAT | DOUBLE
y
Y-coordinate column or expression
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.
FLOAT | DOUBLE
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 Constant
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 Constant
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 Constant
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 Constant
x_min (optional)
Min x-coordinate value (in input units) for the spatial output grid.
DOUBLE Constant
x_max (optional)
Max x-coordinate value (in input units) for the spatial output grid.
DOUBLE Constant
y_min (optional)
Min y-coordinate value (in input units) for the spatial output grid.
DOUBLE Constant
y_max (optional)
Max y-coordinate value (in input units) for the spatial output grid.
DOUBLE Constant

Output Columns

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

tf_geo_rasterize_slope

Similar to tf_geo_rasterize, but also computes the slope and aspect per output bin. 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.
1
SELECT * FROM TABLE(
2
tf_geo_rasterize_slope(
3
raster => CURSOR(
4
SELECT
5
x, y, z FROM table
6
),
7
bin_dim_meters => <meters>,
8
geographic_coords => <true/false>,
9
neighborhood_fill_radius => <radius in bins>,
10
fill_only_nulls => <true/false>,
11
compute_slope_in_degrees => <true/false>
12
)
13
)
Copied!

Input Arguments

Parameter
Description
Data Types
x
Input x-coordinate column or expression.
FLOAT | DOUBLE
y
Input y-coordinate column or expression.
FLOAT | DOUBLE
z
Input z-coordinate column or expression. The output bin is computed as the maximum z-value for all points falling in each bin.
FLOAT | DOUBLE
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 Constant
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 Constant
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.
BIGINT Constant
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 Constant
compute_slope_in_degrees
If 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 Constant

Output Columns

Name
Description
Data Types
x
The x-coordinates for the centroids of the output spatial bins.
FLOAT | DOUBLE (same as input x column/expression)
y
The y-coordinates for the centroids of the output spatial bins.
FLOAT | DOUBLE (same as input y column/expression)
z
The maximum z-coordinate of all input data assigned to a given spatial bin.
FLOAT | DOUBLE (same as input z column/expression)
slope
The average slope of an output grid cell (in degrees of a fraction between 0 and 1, depending on the argument to compute_slope_in_degrees).
FLOAT | DOUBLE (same as input z column/expression)
aspect
The 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°).
FLOAT | DOUBLE (same as input z column/expression)

tf_mandelbrot

1
SELECT * FROM TABLE(
2
tf_mandelbrot( <x_pixels>, <y_pixels>, <x_min>, <x_max>, <y_min>, <y_max>, <max_iterations>
3
)
4
)
Copied!
Parameter
Data Type
x_pixels
32-bit integer
y_pixels
32-bit integer
x_min
DOUBLE
x_max
DOUBLE
y_min
DOUBLE
y_max
DOUBLE
max_iterations
32-bit integer

tf_mandelbrot_cuda

1
SELECT * FROM TABLE(
2
tf_mandelbrot_cuda( <x_pixels>, <y_pixels>, <x_min>, <x_max>, <y_min>, <y_max>, <max_iterations>
3
)
4
)
Copied!
Parameter
Data Type
x_pixels
32-bit integer
y_pixels
32-bit integer
x_min
DOUBLE
x_max
DOUBLE
y_min
DOUBLE
y_max
DOUBLE
max_iterations
32-bit integer

tf_mandelbrot_cuda_float

1
SELECT * FROM TABLE(
2
tf_mandelbrot_cuda_float( <x_pixels>, <y_pixels>, <x_min>, <x_max>, <y_min>, <y_max>, <max_iterations>
3
)
4
)
Copied!
Parameter
Data Type
x_pixels
32-bit integer
y_pixels
32-bit integer
x_min
DOUBLE
x_max
DOUBLE
y_min
DOUBLE
y_max
DOUBLE
max_iterations
32-bit integer

tf_mandelbrot_float

1
SELECT * FROM TABLE(
2
tf_mandelbrot_float(<x_pixels>, <y_pixels>, <x_min>, <x_max>, <y_min>, <y_max>, <max_iterations>
3
)
4
)
Copied!
Parameter
Data Type
x_pixels
32-bit integer
y_pixels
32-bit integer
x_min
DOUBLE
x_max
DOUBLE
y_min
DOUBLE
y_max
DOUBLE
max_iterations
32-bit integer

tf_rf_prop

1
SELECT * FROM TABLE(
2
tf_rf_prop(
3
data => CURSOR(
4
SELECT
5
rf_source_id, x, y, repeater_height_meters
6
),
7
rf_source_z_is_relative_to_terrain,
8
rf_source_signal_strength_dbm,
9
rf_source_signal_frequency_mhz
10
data => CURSOR(
11
SELECT
12
rf_source_id, x, y, elevation_amsl_meters
13
),
14
bin_dim_meters,
15
strongest_k_sources_per_terrain_bin
16
max_ray_travel_meters,
17
num_rays_per_source,
18
min_receiver_signal_strength_dbm
19
default_source_height_agl_meters
20
ray_step_bin_multiple
21
loop_grain_size
22
)
23
)
Copied!
Short Version
Long Version

Input Arguments

Parameter
Details
Data Type
RF sources
Consists of rf_source_id, x (RF sources), y (RF sources), and z_meters.
CURSOR
rf_source_id
ID of the RF source. Used in the output for each grid cell to denote the RF source contributing the strongest signal.
INT | BIGINT | TEXT ENCODING DICT
x (RF sources)
x-coordinate of the RF source, assumed to be in longitude degrees. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
y (RF sources)
y-coordinate of the RF source, assumed to be in latitude degrees. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
repeater_height_meters
.
FLOAT | DOUBLE
rf_source_z_is_relative_to_terrain
true specifies that z_meters values are the height in meters above ground level. false indicates the values are height in meters above sea level.
BOOLEAN
rf_source_signal_strength_dbm
Signal strength in dBm for all RF sources.
DOUBLE
rf_source_signal_frequency_mhz
Frequency in MHZ of the RF sources.
DOUBLE
Terrain elevations
Consists of x (terrain elevations), y (terrain elevations), and elevation_amsl_meters.
CURSOR
x (terrain elevations)
x-coordinate of the terrain/building height, assumed to be in longitude degrees. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
y (terrain elevations)
y-coordinate of the terrain/building height, assumed to be in latitude degrees. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
elevation_amsl_meters
Elevation of the point, in meters above sea level.
FLOAT | DOUBLE
bin_dim_meters
Width and height of each binned terrain cell, which is the unit of granularity over which the RF propagation algorithm is executed.
DOUBLE
num_top_sources_per_terrain_bin
max_ray_travel_meters
Maximum meters for which to model RF propagation from any given source. Lower values can increase performance, buy potentially misses the strongest repeater for a terrain cell that is further away than the value set.
DOUBLE
num_rays_per_source
Number of 2.5D rays to propagate from each source. Higher numbers potentially provide more accurate results but can decrease performance.
BIGINT
min_receiver_signal_strength_dbm
The minimum signal strength threshold in dBm to continue propagating a signal ray from a source. Rays stop being propagated when either the distance from the source exceeds the limit set by max_ray_travel_meters, or the signal strength (as measured by free-space power loss, assuming no obstructions in the path) drops below the value set by min_receiver_signal_strength_dbm.
DOUBLE

Outputs

A table of grid cells of width and height bin_dim_meters along with the maximum signal strength from any one repeater at that cell, and the ID of the repeater with the strongest signal.
Parameter
Details
Text
terrain_bin_id
x
x-centroid of the output bin, in lon/lat degrees.
FLOAT | DOUBLE (same type as RF sources input)
y
y-centroid of the output bin, in lon/lat degrees.
FLOAT | DOUBLE(same type as RF sources input)
elevation_amsl_meters
Maximum elevation of all input terrain data falling in the output bin, in meters above sea level.
FLOAT | DOUBLE (same type as terrain elevations input)
rf_source_id
ID of the repeater with the strongest signal for the grid cell.
FLOAT | DOUBLE(same type as RF sources ID input)
rf_signal_strength_dbm
The signal strength in dBm for a given terrain bin.
FLOAT | DOUBLE (same type as RF sources coordinates input)
rf_signal_z_angle_degrees
rf_source_distance_meters

Input Arguments

Parameter
Details
Data Type
RF sources
Consists of rf_source_id, x (RF sources), y (RF sources), and z_meters.
CURSOR
rf_source_id
ID of the RF source. Used in the output for each grid cell to denote the RF source contributing the strongest signal.
INT | BIGINT | TEXT ENCODING DICT
x (RF sources)
x-coordinate of the RF source. If geographic_coords is set to true, assumed to be longitude degrees; otherwise, an arbitrary meters-coordinate system. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
y (RF sources)
y-coordinate of the RF source. If geographic_coords is set to true, assumed to be latitude degrees; otherwise, an arbitrary meters-coordinate system. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
repeater_height_meters
FLOAT | DOUBLE
rf_source_z_is_relative_to_terrain
true specifies that z_meters values are the height in meters above ground level. false indicates the values are height in meters above sea level.
BOOLEAN
rf_source_signal_strength_dbm
Signal strength in dBm for all RF sources.
DOUBLE
rf_source_signal_frequency_mhz
Frequency in MHZ of the RF sources.
DOUBLE
Terrain elevations
Consists of x (terrain elevations), y (terrain elevations), and elevation_amsl_meters.
CURSOR
x (terrain elevations)
x-coordinate of the terrain/building height. If geographic_coords is set to true, assumed to be longitude degrees; otherwise, an arbitrary meters-coordinate system. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
y (terrain elevations)
y-coordinate of the terrain/building height. If geographic_coords is set to true, assumed to be latitude degrees; otherwise, an arbitrary meters-coordinate system. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
elevation_amsl_meters
Elevation of the point, in meters above sea level.
FLOAT | DOUBLE
bin_dim_meters
Width and height of each binned terrain cell, which is the unit of granularity over which the RF propagation algorithm is executed.
DOUBLE
strongest_k_sources_per_terrain_bin
max_ray_travel_meters
Maximum meters for which to model RF propagation from any given source. Lower values can increase performance, but potentially misses the strongest repeater for a terrain cell that is further away than the value set.
DOUBLE
num_rays_per_source
Number of 2.5D rays to propagate from each source. Higher numbers potentially provide more accurate results but can decrease performance.
BIGINT
min_receiver_signal_strength_dbm
The minimum signal strength threshold in dBm to continue propagating a signal ray from a source. Rays stop being propagated when either the distance from the source exceeds the limit set by max_ray_travel_meters, or the signal strength (as measured by free-space power loss, assuming no obstructions in the path) drops below the value set by min_receiver_signal_strength_dbm.
DOUBLE
default_source_height_agl_meters
The default height in meters above ground level for a source if there is no input data in the terrain_elevations for the terrain bin in which a given rf_source lies.
DOUBLE
ray_step_bin_multiple
The step used in number of bins (can be fractional) for the RF propagation simulation. Lower values of this (below 1.0) can increase accuracy of the simulation, but decrease performance. Values above 1.0 are not recommended because they can cause gaps in the output.
DOUBLE
loop_grain_size
The size in number of rays at which to parallelize over; threads will process rays in groups of at least this size. The default value is 40; performance may improve by changing this number, depending on hardware setup.
BIGINT

Output Columns

A table of grid cells of width and height bin_dim_meters along with the maximum signal strength from any one repeater at that cell, and the ID of the repeater with the strongest signal.
Parameter
Details
Data Type
terrain_bin_id
x
x-centroid of the output bin, in lon/lat degrees.
FLOAT | DOUBLE (same type as RF sources input)
y
y-centroid of the output bin, in lon/lat degrees.
FLOAT | DOUBLE(same type as RF sources input)
elevation_amsl_meters
Maximum elevation of all input terrain data falling in the output bin, in meters above sea level.
FLOAT | DOUBLE (same type as terrain elevations input)
rf_source_id
ID of the repeater with the strongest signal for the grid cell.
FLOAT | DOUBLE(same type as RF sources ID input)
rf_signal_strength_dbm
The signal strength in dBm for a given terrain bin.
FLOAT | DOUBLE (same type as RF sources coordinates input)
rf_signal_z_angle_degrees
rf_source_distance_meters

tf_rf_prop_max_signal

1
SELECT * FROM TABLE(
2
tf_rf_prop(
3
data => CURSOR(
4
SELECT
5
rf_source_id, x, y, z_meters
6
),
7
rf_source_z_is_relative_to_terrain,
8
rf_source_signal_strength_dbm,
9
rf_source_signal_frequency_mhz
10
data => CURSOR(
11
SELECT
12
x, y, elevation_amsl_meters
13
),
14
geographic_coords,
15
bin_dim_meters,
16
max_ray_travel_meters,
17
num_rays_per_source,
18
min_receiver_signal_strength_dbm,
19
default_source_height_agl_meters,
20
ray_step_bin_multiple,
21
loop_grain_size
22
)
23
)
Copied!
Taking a set of point elevations and a set of signal source locations as input, tf_rf_prop_max_signal executes line-of-sight 2.5D RF signal propagation from the provided sources over a binned 2.5D elevation grid derived from the provided point locations, calculating the max signal in dBm at each grid cell, using the formula for free-space power loss. The RF source ID contributing the strongest signal is also outputted.
For the short version, where geographic_coords defaults to true, or for the long version where geographic_coords is explicitly set to true, x and y input coordinates are assumed to be lon/lat degrees. A best-fit conversion to a meters coordinate system is executed using the change in meters per lon/lat degree at the centroid of the input terrain, using the haversine distance formula. For convenience and optimal performance, tf_rf_prop_max_signal enables filter push-down by default, such that filters on the outputs rf_source_id, x, y, and elevation_amsl_meters are pushed down to the respective inputs in the rf_sources and terrain_elevations cursor subqueries.
Short Version
Long Version

Input Arguments

Parameter
Details
Data Type
RF sources
Consists of rf_source_id, x (RF sources), y (RF sources), and z_meters.
CURSOR
rf_source_id
ID of the RF source. Used in the output for each grid cell to denote the RF source contributing the strongest signal.
INT | BIGINT | TEXT ENCODING DICT
x (RF sources)
x-coordinate of the RF source, assumed to be in longitude degrees. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
y (RF sources)
y-coordinate of the RF source, assumed to be in latitude degrees. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
z_meters
z-coordinate of the RF source, in meters. If rf_source_is_relative_to_terrain is set to true, meters are relative to the terrain height; for example, an input of 20 is 20 meters above ground level. Otherwise, it is absolute height above sea level; 20 is 20 meters above mean sea level (AMSL).
FLOAT | DOUBLE
rf_source_z_is_relative_to_terrain
true specifies that z_meters values are the height in meters above ground level. false indicates the values are height in meters above sea level.
BOOLEAN
rf_source_signal_strength_dbm
Signal strength in dBm for all RF sources.
DOUBLE
rf_source_signal_frequency_mhz
Frequency in MHZ of the RF sources.
DOUBLE
Terrain elevations
Consists of x (terrain elevations), y (terrain elevations), and elevation_amsl_meters.
CURSOR
x (terrain elevations)
x-coordinate of the terrain/building height, assumed to be in longitude degrees. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
y (terrain elevations)
y-coordinate of the terrain/building height, assumed to be in latitude degrees. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
elevation_amsl_meters
Elevation of the point, in meters above sea level.
FLOAT | DOUBLE
geographic_coords
true specifies that the input x and y arguments for RF sources and terrain elevations are in lon/lat degrees. false specifies that the data is already in a local meter coordinate system.
BOOLEAN
bin_dim_meters
Width and height of each binned terrain cell, which is the unit of granularity over which the RF propagation algorithm is executed.
DOUBLE
max_ray_travel_meters
Maximum meters for which to model RF propagation from any given source. Lower values can increase performance, buy potentially misses the strongest repeater for a terrain cell that is further away than the value set.
DOUBLE
num_rays_per_source
Number of 2.5D rays to propagate from each source. Higher numbers potentially provide more accurate results but can decrease performance.
BIGINT
min_receiver_signal_strength_dbm
The minimum signal strength threshold in dBm to continue propagating a signal ray from a source. Rays stop being propagated when either the distance from the source exceeds the limit set by max_ray_travel_meters, or the signal strength (as measured by free-space power loss, assuming no obstructions in the path) drops below the value set by min_receiver_signal_strength_dbm.
DOUBLE

Outputs

A table of grid cells of width and height bin_dim_meters along with the maximum signal strength from any one repeater at that cell, and the ID of the repeater with the strongest signal.
Parameter
Details
Text
x
x-centroid of the output bin, in lon/lat degrees.
FLOAT | DOUBLE (same type as RF sources input)
y
y-centroid of the output bin, in lon/lat degrees.
FLOAT | DOUBLE(same type as RF sources input)
elevation_amsl_meters
Maximum elevation of all input terrain data falling in the output bin, in meters above sea level.
FLOAT | DOUBLE (same type as terrain elevations input)
rf_source_id
ID of the repeater with the strongest signal for the grid cell.
FLOAT | DOUBLE(same type as RF sources ID input)
max_signal_strength_dbm
The maximum signal strength in dBm for a given terrain bin.
FLOAT | DOUBLE (same type as RF sources coordinates input)

Input Arguments

Parameter
Details
Text
RF sources
Consists of rf_source_id, x (RF sources), y (RF sources), and z_meters.
CURSOR
rf_source_id
ID of the RF source. Used in the output for each grid cell to denote the RF source contributing the strongest signal.
INT | BIGINT | TEXT ENCODING DICT
x (RF sources)
x-coordinate of the RF source. If geographic_coords is set to true, assumed to be longitude degrees; otherwise, an arbitrary meters-coordinate system. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
y (RF sources)
y-coordinate of the RF source. If geographic_coords is set to true, assumed to be latitude degrees; otherwise, an arbitrary meters-coordinate system. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
z_meters
z-coordinate of the RF source, in meters. If rf_source_is_relative_to_terrain is set to true, meters are relative to the terrain height; for example, an input of 20 is 20 meters above ground level. Otherwise, it is absolute height above sea level; 20 is 20 meters above mean sea level (AMSL).
FLOAT | DOUBLE
rf_source_z_is_relative_to_terrain
true specifies that z_meters values are the height in meters above ground level. false indicates the values are height in meters above sea level.
BOOLEAN
rf_source_signal_strength_dbm
Signal strength in dBm for all RF sources.
DOUBLE
rf_source_signal_frequency_mhz
Frequency in MHZ of the RF sources.
DOUBLE
Terrain elevations
Consists of x (terrain elevations), y (terrain elevations), and elevation_amsl_meters.
CURSOR
x (terrain elevations)
x-coordinate of the terrain/building height. If geographic_coords is set to true, assumed to be longitude degrees; otherwise, an arbitrary meters-coordinate system. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
y (terrain elevations)
y-coordinate of the terrain/building height. If geographic_coords is set to true, assumed to be latitude degrees; otherwise, an arbitrary meters-coordinate system. Whether FLOAT or DOUBLE, x and y must be the same type.
FLOAT | DOUBLE
elevation_amsl_meters
Elevation of the point, in meters above sea level.
FLOAT | DOUBLE
geographic_coords
true specifies that the input x and y arguments for RF sources and terrain elevations are in lon/lat degrees. false specifies that the data is already in a local meter coordinate system.
BOOLEAN
bin_dim_meters
Width and height of each binned terrain cell, which is the unit of granularity over which the RF propagation algorithm is executed.
DOUBLE
max_ray_travel_meters
Maximum meters for which to model RF propagation from any given source. Lower values can increase performance, but potentially misses the strongest repeater for a terrain cell that is further away than the value set.
DOUBLE
num_rays_per_source
Number of 2.5D rays to propagate from each source. Higher numbers potentially provide more accurate results but can decrease performance.
BIGINT
min_receiver_signal_strength_dbm
The minimum signal strength threshold in dBm to continue propagating a signal ray from a source. Rays stop being propagated when either the distance from the source exceeds the limit set by max_ray_travel_meters, or the signal strength (as measured by free-space power loss, assuming no obstructions in the path) drops below the value set by min_receiver_signal_strength_dbm.
DOUBLE
default_source_height_agl_meters
The default height in meters above ground level for a source if there is no input data in the terrain_elevations for the terrain bin in which a given rf_source lies.
DOUBLE
ray_step_bin_multiple
The step used in number of bins (can be fractional) for the RF propagation simulation. Lower values of this (below 1.0) can increase accuracy of the simulation, but decrease performance. Values above 1.0 are not recommended because they can cause gaps in the output.
DOUBLE
loop_grain_size
The size in number of rays at which to parallelize over; threads will process rays in groups of at least this size. The default value is 40; performance may improve by changing this number, depending on hardware setup.
BIGINT

Output Columns

A table of grid cells of width and height bin_dim_meters along with the maximum signal strength from any one repeater at that cell, and the ID of the repeater with the strongest signal.
Parameter
Type
Details
Text
x
x-centroid of the output bin, in lon/lat degrees.
FLOAT | DOUBLE (same type as RF sources input)
y
y-centroid of the output bin, in lon/lat degrees.
FLOAT | DOUBLE(same type as RF sources input)
elevation_amsl_meters
Maximum elevation of all input terrain data falling in the output bin, in meters above sea level.
FLOAT | DOUBLE (same type as terrain elevations input)
rf_source_id
ID of the repeater with the strongest signal for the grid cell.
FLOAT | DOUBLE(same type as RF sources ID input)
max_signal_strength_dbm
The maximum signal strength in dBm for a given terrain bin.
FLOAT | DOUBLE (same type as RF sources coordinates input)

UPDATE

1
UPDATE table_name SET assign [, assign ]* [ WHERE booleanExpression ]
Copied!
Changes the values of the specified columns based on the assign argument (identifier=expression) in all rows that satisfy the condition in the WHERE clause.

Example

1
UPDATE UFOs SET shape='ovate' where shape='eggish';
Copied!
Currently, HEAVY.AI does not support updating a geo column type (POINT, LINESTRING, POLYGON, or MULTIPOLYGON) in a table.

Update Via Subquery

You can update a table via subquery, which allows you to update based on calculations performed on another table.
Examples
Example 1
Example 2
Example 3
1
UPDATE test_facts SET lookup_id = (SELECT SAMPLE(test_lookup.id)
2
FROM test_lookup WHERE test_lookup.val = test_facts.val);
Copied!
1
UPDATE test_facts SET val = val+1, lookup_id = (SELECT SAMPLE(test_lookup.id)
2
FROM test_lookup WHERE test_lookup.val = test_facts.val);
Copied!
1
UPDATE test_facts SET lookup_id = (SELECT SAMPLE(test_lookup.id)
2
FROM test_lookup WHERE test_lookup.val = test_facts.val) WHERE id < 10;
Copied!

DELETE

1
DELETE FROM table_name [ * ] [ [ AS ] alias ]
2
[ WHERE condition ]
Copied!