mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-13 00:32:56 +00:00
0b4f00feef
* fix: align numeric type aliases with those used in PostgreSQL and MySQL Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: update create_type_alias test Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: fix colon Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com> Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: clone Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com> Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: style Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com> Signed-off-by: Dennis Zhuang <killme2008@gmail.com> --------- Signed-off-by: Dennis Zhuang <killme2008@gmail.com> Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
440 lines
43 KiB
Plaintext
440 lines
43 KiB
Plaintext
SELECT h3_latlng_to_cell(37.76938, -122.3889, 0), h3_latlng_to_cell_string(37.76938, -122.3889, 0);;
|
|
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),Int64(0)) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),Int64(0)) |
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
| 577199624117288959 | 8029fffffffffff |
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 1), h3_latlng_to_cell_string(37.76938, -122.3889, 1);
|
|
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),Int64(1)) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),Int64(1)) |
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
| 581672437419081727 | 81283ffffffffff |
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8), h3_latlng_to_cell_string(37.76938, -122.3889, 8);
|
|
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),Int64(8)) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),Int64(8)) |
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+------------------------------------------------------------------+-------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 100), h3_latlng_to_cell_string(37.76938, -122.3889, 100);
|
|
|
|
Error: 3001(EngineExecuteQuery), Execution error: H3 error: invalid resolution (got Some(100)): out of range
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, -1), h3_latlng_to_cell_string(37.76938, -122.3889, -1);
|
|
|
|
Error: 3001(EngineExecuteQuery), Cast error: Can't cast value -1 to type UInt8
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::Int8), h3_latlng_to_cell_string(37.76938, -122.3889, 8::Int8);
|
|
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("Int64"))) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("Int64"))) |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::Int16), h3_latlng_to_cell_string(37.76938, -122.3889, 8::Int16);
|
|
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("Int16"))) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("Int16"))) |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::Int32), h3_latlng_to_cell_string(37.76938, -122.3889, 8::Int32);
|
|
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("Int32"))) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("Int32"))) |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::Int64), h3_latlng_to_cell_string(37.76938, -122.3889, 8::Int64);
|
|
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("Int64"))) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("Int64"))) |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::UInt8), h3_latlng_to_cell_string(37.76938, -122.3889, 8::UInt8);
|
|
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("UInt8"))) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("UInt8"))) |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+--------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::UInt16), h3_latlng_to_cell_string(37.76938, -122.3889, 8::UInt8);
|
|
|
|
+---------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("UInt16"))) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("UInt8"))) |
|
|
+---------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+---------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::UInt32), h3_latlng_to_cell_string(37.76938, -122.3889, 8::UInt32);
|
|
|
|
+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("UInt32"))) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("UInt32"))) |
|
|
+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::UInt64), h3_latlng_to_cell_string(37.76938, -122.3889, 8::UInt64);
|
|
|
|
+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
|
|
| h3_latlng_to_cell(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("UInt64"))) | h3_latlng_to_cell_string(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(8),Utf8("UInt64"))) |
|
|
+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
|
|
| 613196570438926335 | 88283082e7fffff |
|
|
+---------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT h3_cell_to_string(h3_latlng_to_cell(37.76938, -122.3889, 8::UInt64)) AS cell_str, h3_string_to_cell(h3_latlng_to_cell_string(37.76938, -122.3889, 8::UInt64)) AS cell_index;
|
|
|
|
+-----------------+--------------------+
|
|
| cell_str | cell_index |
|
|
+-----------------+--------------------+
|
|
| 88283082e7fffff | 613196570438926335 |
|
|
+-----------------+--------------------+
|
|
|
|
SELECT h3_cell_center_latlng(h3_latlng_to_cell(37.76938, -122.3889, 8::UInt64)) AS cell_center;
|
|
|
|
+------------------------------------------+
|
|
| cell_center |
|
|
+------------------------------------------+
|
|
| [37.77246152245501, -122.39010997087324] |
|
|
+------------------------------------------+
|
|
|
|
SELECT
|
|
h3_cell_resolution(cell) AS resolution,
|
|
h3_cell_base(cell) AS base,
|
|
h3_cell_is_pentagon(cell) AS pentagon,
|
|
h3_cell_parent(cell, 6::UInt64) AS parent,
|
|
h3_cell_to_children(cell, 10::UInt64) AS children,
|
|
h3_cell_to_children_size(cell, 10) AS children_count,
|
|
h3_cell_to_child_pos(cell, 6) AS child_pos,
|
|
h3_child_pos_to_cell(25, cell, 11) AS child
|
|
FROM (SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::UInt64) AS cell);
|
|
|
|
+------------+------+----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-----------+--------------------+
|
|
| resolution | base | pentagon | parent | children | children_count | child_pos | child |
|
|
+------------+------+----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-----------+--------------------+
|
|
| 8 | 20 | false | 604189371209351167 | [622203769691602943, 622203769691635711, 622203769691668479, 622203769691701247, 622203769691734015, 622203769691766783, 622203769691799551, 622203769691865087, 622203769691897855, 622203769691930623, 622203769691963391, 622203769691996159, 622203769692028927, 622203769692061695, 622203769692127231, 622203769692159999, 622203769692192767, 622203769692225535, 622203769692258303, 622203769692291071, 622203769692323839, 622203769692389375, 622203769692422143, 622203769692454911, 622203769692487679, 622203769692520447, 622203769692553215, 622203769692585983, 622203769692651519, 622203769692684287, 622203769692717055, 622203769692749823, 622203769692782591, 622203769692815359, 622203769692848127, 622203769692913663, 622203769692946431, 622203769692979199, 622203769693011967, 622203769693044735, 622203769693077503, 622203769693110271, 622203769693175807, 622203769693208575, 622203769693241343, 622203769693274111, 622203769693306879, 622203769693339647, 622203769693372415] | 49 | 45 | 626707369319059455 |
|
|
+------------+------+----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-----------+--------------------+
|
|
|
|
SELECT
|
|
h3_grid_disk(cell, 0) AS current_cell,
|
|
h3_grid_disk(cell, 3) AS grids,
|
|
h3_grid_disk_distances(cell, 3) AS all_grids,
|
|
FROM (SELECT h3_latlng_to_cell(37.76938, -122.3889, 8::UInt64) AS cell);
|
|
|
|
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| current_cell | grids | all_grids |
|
|
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| [613196570438926335] | [613196570438926335, 613196570436829183, 613196569755254783, 613196570378108927, 613196570373914623, 613196570434732031, 613196570432634879, 613196570445217791, 613196570250182655, 613196569753157631, 613196569744769023, 613196569746866175, 613196570369720319, 613196570365526015, 613196570376011775, 613196570336165887, 613196570344554495, 613196570443120639, 613196570441023487, 613196570220822527, 613196570258571263, 613196570248085503, 613196570254376959, 613196569757351935, 613196569748963327, 613196569751060479, 613196569686048767, 613196569688145919, 613196570371817471, 613196570367623167, 613196570394886143, 613196570338263039, 613196570331971583, 613196570340360191, 613196570405371903, 613196570403274751, 613196570216628223] | [613196570438926335, 613196570436829183, 613196569755254783, 613196570378108927, 613196570373914623, 613196570434732031, 613196570432634879, 613196570445217791, 613196570250182655, 613196569753157631, 613196569744769023, 613196569746866175, 613196570369720319, 613196570365526015, 613196570376011775, 613196570336165887, 613196570344554495, 613196570443120639, 613196570441023487, 613196570220822527, 613196570258571263, 613196570248085503, 613196570254376959, 613196569757351935, 613196569748963327, 613196569751060479, 613196569686048767, 613196569688145919, 613196570371817471, 613196570367623167, 613196570394886143, 613196570338263039, 613196570331971583, 613196570340360191, 613196570405371903, 613196570403274751, 613196570216628223] |
|
|
+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT
|
|
h3_grid_distance(cell1, cell2) AS distance,
|
|
h3_grid_path_cells(cell1, cell2) AS path_cells,
|
|
round(h3_distance_sphere_km(cell1, cell2), 5) AS sphere_distance,
|
|
round(h3_distance_degree(cell1, cell2), 14) AS euclidean_distance,
|
|
FROM
|
|
(
|
|
SELECT
|
|
h3_string_to_cell('86283082fffffff') AS cell1,
|
|
h3_string_to_cell('86283470fffffff') AS cell2
|
|
);
|
|
|
|
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------------+
|
|
| distance | path_cells | sphere_distance | euclidean_distance |
|
|
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------------+
|
|
| 9 | [604189371209351167, 604189371075133439, 604189375235883007, 604189375101665279, 604189638034194431, 604189638571065343, 604189638436847615, 604189642597597183, 604189642463379455, 604189641255419903] | 55.05017 | 0.54939243697098 |
|
|
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------------+
|
|
|
|
SELECT
|
|
unnest(h3_grid_path_cells(cell1, cell2)) AS path_cells,
|
|
FROM
|
|
(
|
|
SELECT
|
|
h3_string_to_cell('86283082fffffff') AS cell1,
|
|
h3_string_to_cell('86283470fffffff') AS cell2
|
|
);
|
|
|
|
+--------------------+
|
|
| path_cells |
|
|
+--------------------+
|
|
| 604189371209351167 |
|
|
| 604189371075133439 |
|
|
| 604189375235883007 |
|
|
| 604189375101665279 |
|
|
| 604189638034194431 |
|
|
| 604189638571065343 |
|
|
| 604189638436847615 |
|
|
| 604189642597597183 |
|
|
| 604189642463379455 |
|
|
| 604189641255419903 |
|
|
+--------------------+
|
|
|
|
SELECT
|
|
h3_cells_contains('86283470fffffff,862834777ffffff, 862834757ffffff, 86283471fffffff, 862834707ffffff', '8b283470d112fff') AS R00,
|
|
h3_cells_contains('86283470fffffff,862834777ffffff, 862834757ffffff, 86283471fffffff, 862834707ffffff', 604189641792290815) AS R01,
|
|
h3_cells_contains('86283470fffffff,862834777ffffff, 862834757ffffff, 86283471fffffff, 862834707ffffff', 626707639343067135) AS R02;
|
|
|
|
+------+-------+------+
|
|
| r00 | r01 | r02 |
|
|
+------+-------+------+
|
|
| true | false | true |
|
|
+------+-------+------+
|
|
|
|
SELECT
|
|
h3_cells_contains(['86283470fffffff', '862834777ffffff', '862834757ffffff', '86283471fffffff', '862834707ffffff'], '86283472fffffff') AS R10,
|
|
h3_cells_contains(['86283470fffffff', '862834777ffffff', '862834757ffffff', '86283471fffffff', '862834707ffffff'], '8b283470d112fff') AS R11,
|
|
h3_cells_contains(['86283470fffffff', '862834777ffffff', '862834757ffffff', '86283471fffffff', '862834707ffffff'], 626707639343067135) AS R12;
|
|
|
|
+-------+------+------+
|
|
| r10 | r11 | r12 |
|
|
+-------+------+------+
|
|
| false | true | true |
|
|
+-------+------+------+
|
|
|
|
SELECT
|
|
h3_cells_contains([604189641255419903, 604189643000250367, 604189642463379455, 604189641523855359, 604189641121202175], '8b283470d112fff') AS R20,
|
|
h3_cells_contains([604189641255419903, 604189643000250367, 604189642463379455, 604189641523855359, 604189641121202175], 604189641792290815) AS R21,
|
|
h3_cells_contains([604189641255419903, 604189643000250367, 604189642463379455, 604189641523855359, 604189641121202175], 626707639343067135) AS R22;
|
|
|
|
+------+-------+------+
|
|
| r20 | r21 | r22 |
|
|
+------+-------+------+
|
|
| true | false | true |
|
|
+------+-------+------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 9);
|
|
|
|
+--------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),Int64(9)) |
|
|
+--------------------------------------------------------+
|
|
| 9q8yygxne |
|
|
+--------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 10);
|
|
|
|
+---------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),Int64(10)) |
|
|
+---------------------------------------------------------+
|
|
| 9q8yygxnef |
|
|
+---------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11);
|
|
|
|
+---------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),Int64(11)) |
|
|
+---------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+---------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 100);
|
|
|
|
Error: 3001(EngineExecuteQuery), Execution error: Invalid geohash resolution 100, valid value range: [1, 12]
|
|
|
|
SELECT geohash(37.76938, -122.3889, -1);
|
|
|
|
Error: 3001(EngineExecuteQuery), Cast error: Can't cast value -1 to type UInt8
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11::Int8);
|
|
|
|
+-----------------------------------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(11),Utf8("Int64"))) |
|
|
+-----------------------------------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+-----------------------------------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11::Int16);
|
|
|
|
+-----------------------------------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(11),Utf8("Int16"))) |
|
|
+-----------------------------------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+-----------------------------------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11::Int32);
|
|
|
|
+-----------------------------------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(11),Utf8("Int32"))) |
|
|
+-----------------------------------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+-----------------------------------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11::Int64);
|
|
|
|
+-----------------------------------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(11),Utf8("Int64"))) |
|
|
+-----------------------------------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+-----------------------------------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11::UInt8);
|
|
|
|
+-----------------------------------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(11),Utf8("UInt8"))) |
|
|
+-----------------------------------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+-----------------------------------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11::UInt16);
|
|
|
|
+------------------------------------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(11),Utf8("UInt16"))) |
|
|
+------------------------------------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+------------------------------------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11::UInt32);
|
|
|
|
+------------------------------------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(11),Utf8("UInt32"))) |
|
|
+------------------------------------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+------------------------------------------------------------------------------------+
|
|
|
|
SELECT geohash(37.76938, -122.3889, 11::UInt64);
|
|
|
|
+------------------------------------------------------------------------------------+
|
|
| geohash(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(11),Utf8("UInt64"))) |
|
|
+------------------------------------------------------------------------------------+
|
|
| 9q8yygxneft |
|
|
+------------------------------------------------------------------------------------+
|
|
|
|
SELECT geohash_neighbours(37.76938, -122.3889, 11);
|
|
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
| geohash_neighbours(Float64(37.76938),Float64(-122.3889),Int64(11)) |
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
| [9q8yygxnefv, 9q8yygxnefu, 9q8yygxnefs, 9q8yygxnefk, 9q8yygxnefm, 9q8yygxnefq, 9q8yygxnefw, 9q8yygxnefy] |
|
|
+----------------------------------------------------------------------------------------------------------+
|
|
|
|
WITH cell_cte AS (
|
|
SELECT s2_latlng_to_cell(37.76938, -122.3889) AS cell
|
|
)
|
|
SELECT cell,
|
|
s2_cell_to_token(cell),
|
|
s2_cell_level(cell),
|
|
s2_cell_parent(cell, 3)
|
|
FROM cell_cte;
|
|
|
|
+---------------------+---------------------------------+------------------------------+----------------------------------------+
|
|
| cell | s2_cell_to_token(cell_cte.cell) | s2_cell_level(cell_cte.cell) | s2_cell_parent(cell_cte.cell,Int64(3)) |
|
|
+---------------------+---------------------------------+------------------------------+----------------------------------------+
|
|
| 9263763445276221387 | 808f7fc59ef01fcb | 30 | 9277415232383221760 |
|
|
+---------------------+---------------------------------+------------------------------+----------------------------------------+
|
|
|
|
SELECT json_encode_path(37.76938, -122.3889, 1728083375::TimestampSecond);
|
|
|
|
+----------------------------------------------------------------------------------------------------------------------+
|
|
| json_encode_path(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(1728083375),Utf8("Timestamp(Second, None)"))) |
|
|
+----------------------------------------------------------------------------------------------------------------------+
|
|
| [[-122.3889,37.76938]] |
|
|
+----------------------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT json_encode_path(lat, lon, ts)
|
|
FROM(
|
|
SELECT 37.76938 AS lat, -122.3889 AS lon, 1728083375::TimestampSecond AS ts
|
|
UNION ALL
|
|
SELECT 37.76928 AS lat, -122.3839 AS lon, 1728083373::TimestampSecond AS ts
|
|
UNION ALL
|
|
SELECT 37.76930 AS lat, -122.3820 AS lon, 1728083379::TimestampSecond AS ts
|
|
UNION ALL
|
|
SELECT 37.77001 AS lat, -122.3888 AS lon, 1728083372::TimestampSecond AS ts
|
|
);
|
|
|
|
+-------------------------------------------------------------------------------------+
|
|
| json_encode_path(lat,lon,ts) |
|
|
+-------------------------------------------------------------------------------------+
|
|
| [[-122.3888,37.77001],[-122.3839,37.76928],[-122.3889,37.76938],[-122.382,37.7693]] |
|
|
+-------------------------------------------------------------------------------------+
|
|
|
|
SELECT UNNEST(geo_path(37.76938, -122.3889, 1728083375::TimestampSecond));
|
|
|
|
+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+
|
|
| __unnest_placeholder(geo_path(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(1728083375),Utf8("Timestamp(Second, None)")))).lat | __unnest_placeholder(geo_path(Float64(37.76938),Float64(-122.3889),arrow_cast(Int64(1728083375),Utf8("Timestamp(Second, None)")))).lng |
|
|
+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+
|
|
| [37.76938] | [-122.3889] |
|
|
+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT UNNEST(geo_path(lat, lon, ts))
|
|
FROM(
|
|
SELECT 37.76938 AS lat, -122.3889 AS lon, 1728083375::TimestampSecond AS ts
|
|
UNION ALL
|
|
SELECT 37.76928 AS lat, -122.3839 AS lon, 1728083373::TimestampSecond AS ts
|
|
UNION ALL
|
|
SELECT 37.76930 AS lat, -122.3820 AS lon, 1728083379::TimestampSecond AS ts
|
|
UNION ALL
|
|
SELECT 37.77001 AS lat, -122.3888 AS lon, 1728083372::TimestampSecond AS ts
|
|
);
|
|
|
|
+------------------------------------------------+------------------------------------------------+
|
|
| __unnest_placeholder(geo_path(lat,lon,ts)).lat | __unnest_placeholder(geo_path(lat,lon,ts)).lng |
|
|
+------------------------------------------------+------------------------------------------------+
|
|
| [37.77001, 37.76928, 37.76938, 37.7693] | [-122.3888, -122.3839, -122.3889, -122.382] |
|
|
+------------------------------------------------+------------------------------------------------+
|
|
|
|
SELECT wkt_point_from_latlng(37.76938, -122.3889) AS point;
|
|
|
|
+---------------------------+
|
|
| point |
|
|
+---------------------------+
|
|
| POINT(-122.3889 37.76938) |
|
|
+---------------------------+
|
|
|
|
SELECT
|
|
st_distance(p1, p2) AS euclidean_dist,
|
|
st_distance_sphere_m(p1, p2) AS sphere_dist_m,
|
|
st_distance(p1, polygon1) AS euclidean_dist_pp,
|
|
st_area(p1) as area_point,
|
|
st_area(polygon1) as area_polygon,
|
|
FROM
|
|
(
|
|
SELECT
|
|
wkt_point_from_latlng(37.76938, -122.3889) AS p1,
|
|
wkt_point_from_latlng(38.5216, -121.4247) AS p2,
|
|
'POLYGON ((-121.491698 38.653343, -121.582353 38.556757, -121.469721 38.449287, -121.315883 38.541721, -121.491698 38.653343))' AS polygon1,
|
|
);
|
|
|
|
+--------------------+--------------------+--------------------+------------+----------------------+
|
|
| euclidean_dist | sphere_dist_m | euclidean_dist_pp | area_point | area_polygon |
|
|
+--------------------+--------------------+--------------------+------------+----------------------+
|
|
| 1.2229131483470166 | 118766.03647159638 | 1.1271559800391486 | 0.0 | 0.027022178074000106 |
|
|
+--------------------+--------------------+--------------------+------------+----------------------+
|
|
|
|
SELECT st_distance_sphere_m(wkt_point_from_latlng(37.76938, -122.3889), 'POLYGON ((-121.491698 38.653343, -121.582353 38.556757, -121.469721 38.449287, -121.315883 38.541721, -121.491698 38.653343))');
|
|
|
|
Error: 3001(EngineExecuteQuery), Great circle distance between non-point objects are not supported for now.
|
|
|
|
SELECT
|
|
st_contains(polygon1, p1),
|
|
st_contains(polygon2, p1),
|
|
st_within(p1, polygon1),
|
|
st_within(p1, polygon2),
|
|
st_intersects(polygon1, polygon2),
|
|
st_intersects(polygon1, polygon3),
|
|
FROM
|
|
(
|
|
SELECT
|
|
wkt_point_from_latlng(37.383287, -122.01325) AS p1,
|
|
'POLYGON ((-122.031661 37.428252, -122.139829 37.387072, -122.135365 37.361971, -122.057759 37.332222, -121.987707 37.328946, -121.943754 37.333041, -121.919373 37.349145, -121.945814 37.376705, -121.975689 37.417345, -121.998696 37.409164, -122.031661 37.428252))' AS polygon1,
|
|
'POLYGON ((-121.491698 38.653343, -121.582353 38.556757, -121.469721 38.449287, -121.315883 38.541721, -121.491698 38.653343))' AS polygon2,
|
|
'POLYGON ((-122.089628 37.450332, -122.20535 37.378342, -122.093062 37.36088, -122.044301 37.372886, -122.089628 37.450332))' AS polygon3,
|
|
);
|
|
|
|
+--------------------------+--------------------------+------------------------+------------------------+----------------------------------+----------------------------------+
|
|
| st_contains(polygon1,p1) | st_contains(polygon2,p1) | st_within(p1,polygon1) | st_within(p1,polygon2) | st_intersects(polygon1,polygon2) | st_intersects(polygon1,polygon3) |
|
|
+--------------------------+--------------------------+------------------------+------------------------+----------------------------------+----------------------------------+
|
|
| true | false | true | false | false | true |
|
|
+--------------------------+--------------------------+------------------------+------------------------+----------------------------------+----------------------------------+
|
|
|