The following are the built-in table-valued functions available in the MSQL language.
These functions return a set of rows instead of a single scalar (see the docs about table expressions).
Available table-valued functions are divided into two categories:
CONST.SEQUENCE(end: int64) -> RECORD(num: int64)CONST.SEQUENCE(start: int64, end: int64) -> RECORD(num: int64)CONST.SEQUENCE(start: int64, end: int64, step: int64) -> RECORD(num: int64)Generate a sequence of integers from start (inclusive) to end (exclusive), with a given step.
Error handling
When the step argument is 0, the function will return an error: Non-terminating sequence.
Notes
The step argument can be negative, in which case the sequence elements will decrease from start down to end (exclusive).
When the start argument is absent, the default value of 0 is assumed.
When the step argument is absent, the default value of 1 is assumed, unless start > end, in which case the default value for step will be -1.
Examples
SELECT * FROM const.sequence(5);
--> +-----+
--> | num |
--> +-----+
--> | 0 |
--> | 1 |
--> | 2 |
--> | 3 |
--> | 4 |
--> +-----+
SELECT * FROM const.sequence(3, 7);
--> +-----+
--> | num |
--> +-----+
--> | 3 |
--> | 4 |
--> | 5 |
--> | 6 |
--> +-----+
SELECT * FROM const.sequence(10, 0, -3);
--> +-----+
--> | num |
--> +-----+
--> | 10 |
--> | 7 |
--> | 4 |
--> | 1 |
--> +-----+
SELECT * FROM const.sequence(5, 0);
--> +-----+
--> | num |
--> +-----+
--> | 5 |
--> | 4 |
--> | 3 |
--> | 2 |
--> | 1 |
--> +-----+
MAP.GEOCODE_PTS(term: text) -> geocode_resultSearches the geoinformational database for point-like map features matching the given term.
Arguments
The term argument is an arbitrary search-term string, e.g. 'Times Square, New York' or 'gas station, Vienna'.
Return type
The function returns multiple records matching the given search term (currently up to 100), of the following structure: RECORD(country: text, city: text, area: text, PLZ: text, street: text, house_no: text, POI: text, type: text, formatted_address: text, geometry: point)
Notes
The PLZ field (from German: Postleitzahl) in the result record contains the postal code for the given point coordinates.
The POI field in the result record contains the name of a point-of-interest at that location (if any), e.g. a school, hospital, supermarket etc.
Examples
SELECT formatted_address FROM map.geocode_pts('gas station, zagreb, ilica')
ORDER BY 1 LIMIT 10;
--> +------------------------------------+
--> | formatted_address |
--> +------------------------------------+
--> | 'INA LPG Zagreb Vrapče, Ilica 419' |
--> | 'INA Zagreb Vrapče, Ilica 419' |
--> | 'INA Zagreb Črnomerec, Ilica 278' |
--> | 'Lukoil, Ilica 437' |
--> +------------------------------------+
See also
MAP.GEOCODE_LINES(term: text) -> geocode_resultSearches the geoinformational database for linear map features matching the given term.
Arguments
The term argument is an arbitrary search-term string, e.g. 'Times Square, New York' or 'gas station, Vienna'.
Return type
The function returns multiple records matching the given search term (currently up to 100), of the following structure: RECORD(country: text, city: text, area: text, PLZ: text, street: text, house_no: text, POI: text, type: text, formatted_address: text, geometry: multilinestring)
Notes
The PLZ field (from German: Postleitzahl) in the result record contains the postal code for the given point coordinates.
The POI field in the result record contains the name of a point-of-interest at that location (if any), e.g. a school, hospital, supermarket etc.
Examples
SELECT formatted_address FROM map.geocode_lines('zagreb, ilica')
ORDER BY 1 LIMIT 10;
--> +----------------------------------------+
--> | formatted_address |
--> +----------------------------------------+
--> | 'Ilica, 10000 Zagreb' |
--> | 'Luke Ilića Oriovčanina, 10000 Zagreb' |
--> +----------------------------------------+
See also
MAP.GEOCODE_AREAS(term: text) -> geocode_resultSearches the geoinformational database for areal (i.e. polygonal) map features matching the given term.
Arguments
The term argument is an arbitrary search-term string, e.g. 'Times Square, New York' or 'gas station, Vienna'.
Return type
The function returns multiple records matching the given search term (currently up to 100), of the following structure: RECORD(country: text, city: text, area: text, PLZ: text, street: text, house_no: text, POI: text, type: text, formatted_address: text, geometry: multipolygon)
Notes
The PLZ field (from German: Postleitzahl) in the result record contains the postal code for the given point coordinates.
The POI field in the result record contains the name of a point-of-interest at that location (if any), e.g. a school, hospital, supermarket etc.
This function depends on the underlying map
Examples
SELECT formatted_address FROM map.geocode_areas('zagreb')
ORDER BY 1 LIMIT 10;
See also
MAP.REV_GEOCODE(x: float64, y: float64) -> rev_geocode_resultReverse-geocodes a given (x,y) point on the map, returning its address info.
Return type
The function always returns a single result row of the following structure: RECORD(country: text, city: text, area: text, PLZ: text, street: text, house_no: text, POI: text, type: text, formatted_address: text, location: point).
Notes
The PLZ field (from German: Postleitzahl) in the result record contains the postal code for the given point coordinates.
The POI field in the result record contains the name of a point-of-interest at that location (if any), e.g. a school, hospital, supermarket etc.
Examples
SELECT country, city, street, house_no FROM map.rev_geocode(to_wpx(15.9775), to_wpy(45.813));
--> +------------+----------+----------------------------+----------+
--> | country | city | street | house_no |
--> +------------+----------+----------------------------+----------+
--> | 'Hrvatska' | 'Zagreb' | 'Trg bana Josipa Jelačića' | '7' |
--> +------------+----------+----------------------------+----------+
See also
MAP.ROUTE(start_x: float64, start_y: float64, end_x: float64, end_y: float64) -> route_resultCalculates the quickest route between two points on a map and returns its properties and geometry.
Return type
The function always returns a single result row of the following structure: RECORD(length: int64, duration: int64, path: linestring, error_message: text)
Error handling
If the route cannot be calculated for whatever reason, the resulting record will contain an error_message, and the other record fields will be empty (zero or NULL).
Notes
The length in the result record is expressed in meters, and the duration is given in seconds.
Examples
SELECT length / 1000, duration / 60 FROM map.route(to_wpx(15.94748), to_wpy(45.775950), to_wpx(15.548507), to_wpy(45.512264));
--> +--------+----------+
--> | length | duration |
--> +--------+----------+
--> | 44 | 26 |
--> +--------+----------+
See also