telco

This module contains procedures to perform spatial analysis to solve specific use-cases for the Telco industry.

TELCO_PATH_PROFILE

TELCO_PATH_PROFILE(tx_table, rx_table, output, options)

Description

First, the procedure computes the geodetic propagation line projected to the ground between Tx and Rx and it derives the intersection between this line and the obstacle's geometry. In this implementation, various types of obstacles can be included: buildings, vegetation, water and clutter (general other than building / vegetation or Landcover data), as well as the terrain morphology, which is sampled every 10 m (i.e. every sampled point is considered as an obstacle) to take into account the terrain elevation along the path. The user can specify which obstacles to include in the derivation of the path profile, and only those defined in the options parameter are considered. Based on the derived profile, the procedure it estimates whether the path is free of obstruction - Line of Sight (LOS) or Non-Line of Sight (NLOS). When a dataset containing the buildings footprint geometries is provided it also returns whether a receiver is inside a building or not as well as the street width at leach receiver, which represents the distance between the centroid of the last obstacle on the path and the centroid of the first obstacle in the opposite direction outside the path.

Then, for each obstacle on the path, the procedure computes the geodetic distance between Tx and the obstacle and between the obstacle and Rx and for a given frequency computes the fraction of the first Fresnel zone obstructed by the obstacle. When the terrain elevation is taken into account, the height of an object is computed as the sum of the object's height and terrain's elevation, interpolated by the values of the terrain's dataset.

As an output, this procedure produces a table containing the following information for each pair Tx-Rx:

Tx id and Rx id, along with longitudes and latitudes and heights above ground or above sea level.
Distance, downtilt angle and bearing
LOS or NLOS
Summary of obstacles (id, type and ratio of 1st Fresnel Zone obstruction)
Inside a building
Street width where the Rx is located
Optionally, the procedure returns a table with additional information on the obstacles between each pair Tx-Rx (e.g. the obstacle type, height, geometry, the intersected geometry, etc.)

Note that it is highly recommended the clutter tables provided to this procedure to be clustered by the geometry.

Input parameters

The input parameters are:

tx_query: STRING the query or the fully qualified name of the table containing the transmitters locations. It should include the following columns: id the id of the Tx, geom the geography of the Tx, height the elevation above ground (m) of the Tx and buffer the radius in meters (m) around the Tx that the Rx can be considered to served by the Tx.
rx_query: STRING the query or the fully qualified name of the table containing the receivers locations. It should include the following columns: id the id of the Rx, geom the geography of the Rx, height the elevation above ground (m) of the Rx.
output_prefix: STRING destination prefix for the output tables. It must contain the project, dataset and prefix. For example <my-project>.<my-dataset>.<output-prefix>.
options: STRING containing a valid JSON with the different options. Valid options are described in the table below.

Option

Description

buildings_query

STRING the query or the fully qualified name of the table containing the buildings footprint geometries. It should include the following columns: id the id of the building, geom the footprint's geography of the building, height the elevation above ground of the building.

vegetation_query

STRING the query or the fully qualified name of the table containing the vegetation footprint geometries. It should include the following columns: id the id of the vegetation, geom the footprint's geography of the building, height the elevation above ground of the vegetation.

water_query

STRING the query or the fully qualified name of the table containing the water sources footprint geometries. It should include the following columns: id the id of the source, geom the footprint's geography of the source, height the elevation above ground of the source.

other_clutter_query

STRING the query or the fully qualified name of the table containing a general type of clutter other than building or vegetation. It should include the following columns: id the id of the clutter, geom the clutter's footprint geography of the building, height the elevation above ground of the clutter, type the type of the obstacle.

terrain_height_query

STRING the query or the fully qualified name of the table containing the terrain morphology. It should include the following columns: geometry the point's geography , height the elevation above sea level of the point.

terrain_points

STRING the flag to specify whether or not terrain morphology should be accounted for in the path profile. It can be TRUE or FALSE. IF TRUE then the terrain_height_query is required to be defined. Default value is FALSE.

include_obstacles_table

STRING the flag to specify whether or not the optional output table with the details on the obstacles should be exported. It can be TRUE or FALSE. Default value is FALSE.

include_street_width

STRING the flag to specify whether or not the street width should be computed in the path profile. It can be TRUE or FALSE. Default value is FALSE.

terrain_sampling_resolution

FLOAT64 the sampling distance in meters for the terrain points elevation to be considered in the path profile. Default value is 10.

freqGHz

FLOAT64 the operating frequency of the links in GHz. Default value is 2.4 GHz.

Output

The procedures outputs the table output_prefix and optionally the table output_prefix+_details: The first table output_prefix has the following schema:

tx_id: STRING the id of the transmitter as it appears in the tx_table input.
rx_id: STRING the id of the receiver as it appears in the rx_table input.
lontx: FLOAT64 the longitude of the transmitter's location.
lattx: FLOAT64 the latitude of the transmitter's location.
heighttx: FLOAT64 the elevation above ground in meters of the transmitter.
lonrx: FLOAT64 the longitude of the receiver's location.
latrx: FLOAT64 the latitude of the receiver's location.
heightrx: FLOAT64 the elevation above ground in meters of the receiver.
street_width: FLOAT64 the street width in meters of where the receiver is located, if the include_street_width in the options argument is set to TRUE. Default values is 200.
geom: GEOGRAPHY the geometry of the line connecting the transmitter and the receiver.
fresnel_geom: GEOGRAPHY the geometry of the ellipse representing the First Fresnel Zone horizontally connecting the transmitter and the receiver.
los: STRING a flag that specifies whether this link is clear of obstacles or not. It can be los (Line-Of-Sight) or nlos (Non-Line-Of-Sight).
inbuilding: BOOLEAN a flag, TRUE or FALSE, that specifies whether the receiver is located inside a building. Default is FALSE. This column is optional and subject to the building query being provided.
obstacles: RECORD a list of structs of the following fields which describe the characteristics of the obstacle between the transmitter and the receiver.
- id: STRING the id of the obstacle as it appears in the respective input query (buildings, vegetation, clutter, water, terrain points),
- type: STRING the type of obstacle. This can be either building, vegetation, water, terrain or as specified in the clutter dataset.
- ratio: FLOAT64 the ratio of the obstruction of the 1st Fresnel zone. If h is the height of the link at the obstacle's location, and h_obstacle the obstacle's height, then the ratio is defined as (h - h_obstacle) / F where F the radius of the 1st Fresnel zone at this distance.
- len: FLOAT64 the length in meters of the path that intersecrs with the obstacle.
distance: RECORD
- distance: FLOAT64 3D distance (m) between the transmitter and the receiver.
- tilt: FLOAT64 The tilt angle in degrees as defined in ITU-R red 452-16.
- bearing: FLOAT64 is the angle in degrees measured between the line from point Tx facing true North to the line segment from point Tx to point Rx, as defined in ITU-R rec 452-16.

The second, optional, table output_prefix+_details has the following schema:

tx_id: STRING the id of the transmitter as it appears in the tx_table input.
rx_id: STRING the id of the receiver as it appears in the rx_table input.
id: STRING the id of the obstacle as it appears in the respective input source (buildings, vegetation, clutter, water, terrain points).
type: STRING the type of obstacle. This can be either building, vegetation, water, terrain or as specified in the clutter dataset.
height: FLOAT64 the elevation above ground or above sea level, when terrain dataset is provided, in meters of the obstacle.
geom: GEOGRAPHY the geometry of the intersection of the line between Tx and Rx and the obstacle's geometry.
geom_obstacle: GEOGRAPHY the original geometry of the obstacle.
geom_obstacle_center: GEOGRAPHY the central point of the geom column.
distance: FLOAT64 the distance in meters from the Tx to the obstacle.

Examples

Let's start by setting the OPTIONS to NULL.:

CALL `carto-un`.carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un`.carto.TELCO_PATH_PROFILE(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL carto.TELCO_PATH_PROFILE(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un`.carto.TELCO_PATH_PROFILE(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  'SELECT * FROM `<my-project>.<my-dataset>.<my-rx_locations>` WHERE height > 2.0',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  'SELECT * FROM `<my-project>.<my-dataset>.<my-rx_locations>` WHERE height > 2.0',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL carto.TELCO_PATH_PROFILE(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  'SELECT * FROM `<my-project>.<my-dataset>.<my-rx_locations>` WHERE height > 2.0',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un`.carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"buildings_query": "<my-project>.<my-dataset>.<my-buildings_dataset>", "vegetation_query": "SELECT * FROM <my-project>.<my-dataset>.<vegetation_dataset>"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"buildings_query": "<my-project>.<my-dataset>.<my-buildings_dataset>", "vegetation_query": "SELECT * FROM <my-project>.<my-dataset>.<vegetation_dataset>"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"buildings_query": "<my-project>.<my-dataset>.<my-buildings_dataset>", "vegetation_query": "SELECT * FROM <my-project>.<my-dataset>.<vegetation_dataset>"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un`.carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"buildings_query": "<my-project>.<my-dataset>.<my-buildings_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "terrain_point":"TRUE", "include_obstacles_table":"TRUE", "include_street_width":"TRUE"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"buildings_query": "<my-project>.<my-dataset>.<my-buildings_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "terrain_point":"TRUE", "include_obstacles_table":"TRUE", "include_street_width":"TRUE"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

CALL carto.TELCO_PATH_PROFILE(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"buildings_query": "<my-project>.<my-dataset>.<my-buildings_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "terrain_point":"TRUE", "include_obstacles_table":"TRUE", "include_street_width":"TRUE"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

TELCO_PATH_PROFILE_RASTER

TELCO_PATH_PROFILE_RASTER(tx_table, rx_table, output, options)

Description

This procedure finds the Path Profile between the transmitter (Tx) and receiver (Rx), the obstacles that exist along the propagation path, i.e. those that intersect with the propagation line, and then it evaluates the impact of each obstacle's height on the actual 3D propagation line by computing the obstructed fraction of the first Fresnel zone. It is an optimised version of the nominal Path profile function which only accepts as inputs a clutter and a terrain elevation quadbin rasters loaded using CARTO raster-loader.

First, the procedure computes the geodetic propagation line projected to the ground between Tx and Rx and it derives the intersection between this line and the obstacle's geometry. In this implementation, only one type of obstacles can be included: clutter a quadbin-raster table that covers the whole area of interest. Based on the derived profile, the procedure estimates whether the path is free of obstruction - Line of Sight (LOS) or Non-Line of Sight (NLOS).

Then, for each obstacle on the path, the procedure computes the geodetic distance between Tx and the obstacle and between the obstacle and Rx and for a given frequency computes the fraction of the first Fresnel zone obstructed by the obstacle. When the terrain elevation is taken into account, it has to be provided as a quadbin raster table. If the terrain's elevation is considered, the height of an obstacle is then computed as the sum of the object's height and terrain's elevation, interpolated by the values of the terrain's dataset.

As an output, this procedure produces a table containing the following information for each pair Tx-Rx:

Tx id and Rx id, along with longitudes and latitudes and heights above ground or above sea level.
Distance, downtilt angle and bearing
LOS or NLOS
Summary of obstacles (id, type and ratio of 1st Fresnel Zone obstruction)
Optionally, the procedure returns a table with additional information on the obstacles between each pair Tx-Rx (e.g. the obstacle type, height, geometry, the intersected geometry, etc.)

Input parameters

The input parameters are:

tx_query: STRING the query or the fully qualified name of the table containing the transmitters locations. It should include the following columns: id the id of the Tx, geom the geography of the Tx, height the elevation above ground (m) of the Tx and buffer the radius in meters (m) around the Tx that the Rx can be considered to served by the Tx.
rx_query: STRING the query or the fully qualified name of the table containing the receivers locations. It should include the following columns: id the id of the Rx, geom the geography of the Rx, height the elevation above ground (m) of the Rx.
output_prefix: STRING destination prefix for the output tables. It must contain the project, dataset and prefix. For example <my-project>.<my-dataset>.<output-prefix>.
options: STRING containing a valid JSON with the different options. Valid options are described in the table below.

Option

Description

clutter_query

STRING the fully qualified name of the raster table containing a general type of clutter.

clutter_raster_band

STRING contains the bands and aliases to be extracted from the clutter raster if provided as raster table. The aliases must be height and type. Default is band_1 AS height, band_2 AS type. More information can be found here.

terrain_height_query

STRING the fully qualified name of the raster table containing the terrain morphology.

terrain_raster_band

STRING contains the band to be extracted from the terrain elevation raster table if provided as raster table. Default band_1.

include_obstacles_table

STRING the flag to specify whether or not the optional output table with the details on the obstacles should be exported. It can be TRUE or FALSE. Default value is FALSE.

intersect_center

STRING whether to extract the pixel values from raster tables by intersecting the pixel center or instead the pixel boundary from the Fresnel zone. This operation will take effect for lines, polygons, and multi-geographies, not for single points which follow a different algorithm for pixel extraction. This operation is faster but less accurate for polygons, and not recommended for lines and multi-geographies. Default is TRUE.

intersect_fresnel_zone

STRING whether to use the First Fresnel Zone for extracting the obstructing pixels or the only the line connecting the Tx-Rx. The advantage of the latter is the computational time, while the use of the Fresnel zone leads to more accurate results. Default is TRUE.

adjust_rx_height

STRING the flag to specify whether to adjust the receiver's height in case its height is lower than the clutter's height. If TRUE and the clutter's height at the location of receiver is higher then the receiver's height above ground is adjusted by adding the clutter's height. It can be TRUE or FALSE. Default value is FALSE.

freqGHz

FLOAT64 the operating frequency of the links in GHz. Default value is 2.4 GHz.

Output

The procedures outputs the table output_prefix and optionally the table output_prefix+_details: The first table output_prefix has the following schema:

tx_id: STRING the id of the transmitter as it appears in the tx_table input.
rx_id: STRING the id of the receiver as it appears in the rx_table input.
lontx: FLOAT64 the longitude of the transmitter's location.
lattx: FLOAT64 the latitude of the transmitter's location.
heighttx: FLOAT64 the elevation above ground in meters of the transmitter.
lonrx: FLOAT64 the longitude of the receiver's location.
latrx: FLOAT64 the latitude of the receiver's location.
heightrx: FLOAT64 the elevation above ground in meters of the receiver.
geom: GEOGRAPHY the geometry of the line connecting the transmitter and the receiver.
fresnel_geom: GEOGRAPHY the geometry of the ellipse representing the First Fresnel Zone horizontally connecting the transmitter and the receiver.
los: STRING a flag that specifies whether this link is clear of obstacles or not. It can be los (Line-Of-Sight) or nlos (Non-Line-Of-Sight).
obstacles: RECORD a list of structs of the following fields which describe the characteristics of the obstacle between the transmitter and the receiver.
- id: INT64 the id (quadbin) of the obstacle as it appears in the respective input clutter query,
- type: STRING the type of obstacle as specified in the clutter dataset.
- ratio: FLOAT64 the ratio of the obstruction of the 1st Fresnel zone. If h is the height of the link at the obstacle's location, and h_obstacle the obstacle's height, then the ratio is defined as (h - h_obstacle) / F where F the radius of the 1st Fresnel zone at this distance.
- len: FLOAT64 the length in meters of the path that intersecrs with the obstacle.
distance: RECORD
- distance: FLOAT64 3D distance (m) between the transmitter and the receiver.
- tilt: FLOAT64 The tilt angle in degrees as defined in ITU-R red 452-16.
- bearing: FLOAT64 is the angle in degrees measured between the line from point Tx facing true North to the line segment from point Tx to point Rx, as defined in ITU-R rec 452-16.

The second, optional, table output_prefix+_details has the following schema:

tx_id: STRING the id of the transmitter as it appears in the tx_table input.
rx_id: STRING the id of the receiver as it appears in the rx_table input.
id: INT64 the id (quadbin) of the obstacle as it appears in the respective input clutter source or terrain points.
type: STRING the type of obstacle as specified in the clutter dataset.
height: FLOAT64 the elevation above ground or above sea level, when terrain dataset is provided, in meters of the obstacle.
geom: GEOGRAPHY the central point of the geom column.
geom_intersect: GEOGRAPHY the intersection line of the line between Tx-Rx and the pixel's boundary.
distance: FLOAT64 the distance in meters from the Tx to the obstacle.

Examples

Let's start by setting the OPTIONS to NULL.:

CALL `carto-un`.carto.TELCO_PATH_PROFILE_QUADBIN(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE_QUADBIN(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL carto.TELCO_PATH_PROFILE_QUADBIN(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un`.carto.TELCO_PATH_PROFILE_RASTER(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE_RASTER(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL carto.TELCO_PATH_PROFILE_RASTER(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un`.carto.TELCO_PATH_PROFILE_RASTER(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  'SELECT * FROM `<my-project>.<my-dataset>.<my-rx_locations>` WHERE height > 2.0',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE_RASTER(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  'SELECT * FROM `<my-project>.<my-dataset>.<my-rx_locations>` WHERE height > 2.0',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

CALL carto.TELCO_PATH_PROFILE_RASTER(
  'SELECT * FROM `<my-project>.<my-dataset>.<my-tx_locations>` WHERE id IN ("a", "b")',
  'SELECT * FROM `<my-project>.<my-dataset>.<my-rx_locations>` WHERE height > 2.0',
  '<my-project>.<my-dataset>.<output-prefix>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

-- Introducing clutter information stored in <my-project>.<my-dataset>.<clutter_raster> into the function
CALL `carto-un`.carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "SELECT * FROM <my-project>.<my-dataset>.<clutter_raster>"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

-- Introducing clutter information stored in <my-project>.<my-dataset>.<clutter_raster> into the function
CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "SELECT * FROM <my-project>.<my-dataset>.<clutter_raster>"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

-- Introducing clutter information stored in <my-project>.<my-dataset>.<clutter_raster> into the function
CALL carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "SELECT * FROM <my-project>.<my-dataset>.<clutter_raster>"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created

-- Introducing clutter information, terrain height profile into the function
CALL `carto-un`.carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "<my-project>.<my-dataset>.<my-clutter_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "include_obstacles_table":"TRUE", "clutter_band":"band_2 AS height, band_5 AS type", "terrain_band":"band_3"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

-- Introducing clutter information, terrain height profile into the function
CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "<my-project>.<my-dataset>.<my-clutter_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "include_obstacles_table":"TRUE", "clutter_band":"band_2 AS height, band_5 AS type", "terrain_band":"band_3"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

-- Introducing clutter information, terrain height profile into the function
CALL carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "<my-project>.<my-dataset>.<my-clutter_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "include_obstacles_table":"TRUE", "clutter_band":"band_2 AS height, band_5 AS type", "terrain_band":"band_3"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

-- Introducing clutter information, terrain height profile into the function.
-- The functions finds the cells that intersects with the fresnel geometry, not only the centers
CALL `carto-un`.carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "<my-project>.<my-dataset>.<my-clutter_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "include_obstacles_table":"TRUE", "clutter_band":"band_2 AS height, band_5 AS type", "terrain_band":"band_3", "intersect_center":"FALSE", "intersect_fresnel_zone":"TRUE"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

-- Introducing clutter information, terrain height profile into the function.
-- The functions finds the cells that intersects with the fresnel geometry, not only the centers
CALL `carto-un-eu`.carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "<my-project>.<my-dataset>.<my-clutter_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "include_obstacles_table":"TRUE", "clutter_band":"band_2 AS height, band_5 AS type", "terrain_band":"band_3", "intersect_center":"FALSE", "intersect_fresnel_zone":"TRUE"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

-- Introducing clutter information, terrain height profile into the function.
-- The functions finds the cells that intersects with the fresnel geometry, not only the centers
CALL carto.TELCO_PATH_PROFILE_RASTER(
  '<my-project>.<my-dataset>.<my-tx_locations>',
  '<my-project>.<my-dataset>.<my-rx_locations>',
  '<my-project>.<my-dataset>.<output-prefix>',
  '{"clutter_query": "<my-project>.<my-dataset>.<my-clutter_dataset>", "terrain_height_query": <my-project>.<my-dataset>.<my-terrain_elevation_dataset>", "include_obstacles_table":"TRUE", "clutter_band":"band_2 AS height, band_5 AS type", "terrain_band":"band_3", "intersect_center":"FALSE", "intersect_fresnel_zone":"TRUE"}'
);
-- Table `<my-project>.<my-dataset>.<output-prefix>` will be created
-- Table `<my-project>.<my-dataset>.<output-prefix>_details` will be created

CLOSE_IN

CLOSE_IN(path_profiles, output, options)

Description

This procedure implements the statistical (i.e. stochastic) propagation model presented here, that describes large-scale propagation path loss over distance at all relevant frequencies in a certain outdoor scenario.

It takes as inputs the distance in meters between a transmitter and a receiver, the frequency in GHz and the type of scenario as it is described in the reference. These can be either provided directly or as computed first using the TELCO_PATH_PROFILE procedure. This model uses three parameters to get an estimation of the path gain, defined as :

the Pathloss Exponent ( $n$ ),
the standard deviation ( $\sigma$ ) of a zero-mean Gaussian random variable and
the number of samples to be drawn.

Default ones are considered for each scenario as it can be seen in the table below (these values refer to cases for omnidirectional antennas). The scenarios defined are the following:

UMa - Urban Macrocell (Tx Heights >= 25 m)
UMi-S.C. - Urban microcell street canyon (Tx Heights < 25 m)
UMi-O.S. - Urban microcell open square (Tx Heights < 25 m)

The Close-In formula for the estimation of the path loss ( $p_L$ ) in dB is:

$p_L = FSPL(f) + 10 n \log_{10}(d) + X_{\sigma}$

where

$FSPL(f) = 20 \log_{10}(\frac{4 \pi f}{c})$
d is the distance in meters between the transmitter and receiver.
c ≈ $2.997×10^8$ m/s is the speed of light in the air.
$n$ is the pathloss exponent as defined in the reference.
$X_{\sigma}$ is the computed as the average from multiple draws (as defined by the specified number of samples) from a zero-mean Gaussian random variable with standard deviation $\sigma$ .

The parameters per scenario can be seen in the following table:

Scenario

CI Model Parameters

UMa-LOS

$n$ : 2.0, $\sigma$ : 4.1

UMa-NLOS

$n$ : 3.0, $\sigma$ : 6.8

UMi-S.C.-LOS

$n$ : 1.98, $\sigma$ : 3.1

UMi-S.C.-NLOS

$n$ : 3.19, $\sigma$ : 8.2

UMi-O.S.-LOS

$n$ : 1.85, $\sigma$ : 4.2

UMi-O.S.-NLOS

$n$ : 2.89, $\sigma$ : 7.1

Input parameters

The input parameters are:

path_profiles: STRING the query or the fully qualified name of the table containing the Tx-Rx links information. Each row must have :
- tx_id:STRING the id of the transmitter Tx.
- rx_id:STRING the id of the receiver Rx.
- distance: RECORD with at least an entry called also distance - FLOAT64 which is the 3D distance in meters between Tx and Rx.
- los: STRING it indicates whether the link is los or nlos.
output: STRING destination for the output table. It must contain the project, dataset and table name. For example <my-project>.<my-dataset>.<table_name>.
options: containing a valid JSON with the different options. Valid options are described in the table below.

Option

Description

frequency

FLOAT64 the frequency in GHz. Default one is 1.6 GHz

scenario

STRING the scenario. It can be one of the three: UMa, UMi-S.C., UMi-O.S.. Default one is UMa.

pathLossExponent_los

FLOAT64 the PathlossExponent for the LOS case.

pathLossExponent_nlos

FLOAT64 the PathlossExponent for the NLOS case.

sigma_los

FLOAT64 the standard deviation of the zero-mean Gaussian random variable for the LOS case.

sigma_nlos

FLOAT64 the standard deviation of the zero-mean Gaussian random variable for the NLOS case.

numDataPoints

INT64 the number of data points used to compute $X_{\sigma}$ . Default one is 100.

Output

The output table has the following schema:

tx_id:STRING the id of the transmitter Tx
rx_id:STRING the id of the receiver Rx
pathloss: FLOAT64 the path gain in dB.

Examples

CALL `carto-un`.carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un-eu`.carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un`.carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"UMi-S.C."}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un-eu`.carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"UMi-S.C."}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"UMi-S.C."}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un`.carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"UMi-S.C.", "pathLossExponent_nlos":5.0}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un-eu`.carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"UMi-S.C.", "pathLossExponent_nlos":5.0}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL carto.CLOSE_IN(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"UMi-S.C.", "pathLossExponent_nlos":5.0}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

EXTENDED_HATA

EXTENDED_HATA(path_profiles, output, options)

Description

This procedure implements the empirical propagation model Extended HATA presented here, Section 6 p.26, that describes radio propagation path loss for the range of frequencies 30 MHz - 3000 MHz in an outdoor scenario. It takes as inputs the distance in meters between a transmitter and a receiver, the frequency in GHz and the type of scenario as it is described in the reference, and the heights above ground for both ends. These can be either provided directly or as computed first using the TELCO_PATH_PROFILE procedure. For accurate results, the path profile should be constructed assuming a flat terrain. This ensures the transmitter and receiver heights are measured relative to the ground, not any elevation changes.

The scenarios defined are the following:

urban: For urban environments, metropolitan cities.
suburban: For suburban areas, small cities, low densities towns.
open area: For rural areas.

The Extended Hata model calculates the propagation loss between transmitter and receiver as:

$p_L = F( f, h_m, h_b, d, env )$

Where,

$p_L$ = path loss (dB)
$f$ = frequency (MHz)
$h_b$ = transmitter antenna height above ground (m)
$h_m$ = receiver antenna height above ground (m)
$d$ = 3D distance between transmitter and receiver (km)
$a(H_m)$ , $b(H_b)$ = antenna height correction factors, defined as follows:
- $a(H_m) = \left(1.1\log(f) - 0.7\right)\min(10, H_m) - \left(1.56\log(f) - 0.8\right) + \max\left(0, 20\log\left(\frac{H_m}{10}\right)\right)$
- $b(H_b) = \min\left(0, 20\log\left(\frac{H_b}{30}\right)\right)$

The exponent $\alpha$ is a distance correction factor for distances > 20 km, defined as follows:

\alpha = \begin{cases} 1 & \text{for } d \leq 20 \text{ km} \\ 1 + \left(0.14 + 1.87 \times 10^{-4}f + 1.07 \times 10^{-3}H_b\right)\left(\log\frac{d}{20}\right)^{0.8} & \text{for } 20 \text{ km} < d \leq 100 \text{ km} \end{cases}

with the following model restrictions:

$f$ : 30 MHz to 3000 MHz
$h_b$ : 30 m to 200 m
$h_m$ : 1 m to 10 m
$d$ : 0.1 km to 100 km, but in practice, it is recommended to use it up to 40 km.

Input parameters

The input parameters are:

path_profiles: STRING the query or the fully qualified name of the table containing the Tx-Rx links information. Each row must have :
- tx_id:STRING the id of the transmitter Tx.
- rx_id:STRING the id of the receiver Rx.
- heightTx: FLOAT64 height above ground in meters for the transmitter.
- heightRx: FLOAT64 height above ground in meters for the receiver.
- distance: RECORD with at least an entry called also distance - FLOAT64 which is the 3D distance in meters between Tx and Rx.
output: STRING destination for the output table. It must contain the project, dataset and table name. For example <my-project>.<my-dataset>.<table_name>.
options: containing a valid JSON with the different options. Valid options are described in the table below.

Option

Description

frequency

FLOAT64 the frequency in GHz. Default one is 1.6 Ghz

scenario

STRING the scenario. It can be one of the three: urban, suburban, open area. Default one is urban.

Output

The output table has the following schema:

tx_id:STRING the id of the transmitter Tx
rx_id:STRING the id of the receiver Rx
pathloss: FLOAT64 the path gain in dB.

Examples

CALL `carto-un`.carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un-eu`.carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  NULL
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un`.carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":1.6, "scenario":"urban"}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un-eu`.carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":1.6, "scenario":"urban"}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":1.6, "scenario":"urban"}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un`.carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"suburban"}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL `carto-un-eu`.carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"suburban"}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

CALL carto.EXTENDED_HATA(
  '<my-project>.<my-dataset>.<my-tx_rx_path_profiles>',
  '<my-project>.<my-dataset>.<output>',
  '{"frequency":2.4, "scenario":"suburban"}'
);
-- Table `<my-project>.<my-dataset>.<output>` will be created

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.

Previousstatistics Nexttiler

Last updated 1 day ago