5.8 空间检索语法

HybridDB的计算规格支持在SQL语法中便捷的空间检索能力,借助于引擎底层强大的空间索引,可以做到百亿数据毫秒级的经纬度圈人,举例如下:

1)建表

CREATE TABLE test2 (
    user_id BIGINT,
    city VARCHAR,
    poi GEO2D delimiter_tokenizer ','
)
DISTRIBUTE BY HASH (user_id)
ENGINE = 'CSTORE'
INDEX_ALL='Y';

2)写入数据

insert into test2 values(1, 'HZ', '116.3972, 39.9096');
insert into test2 values(2, 'BJ', '120.1313, 40.2727');
insert into test2 values(3, 'SH', '116.4041, 39.9863');

mysql> select * from test2 order by user_id;
+---------+------+------------------+
| user_id | city | poi              |
+---------+------+------------------+
|       1 | HZ   | 116.3972,39.9096 |
|       2 | BJ   | 120.1313,40.2727 |
|       3 | SH   | 116.4041,39.9863 |
+---------+------+------------------+
3 rows in set (0.02 sec)

3)空间查询

点查询的语法:

geo_in_points(col_name, 'longitude1 latitude1, longitude2 latitude2, ...');

返回包含这些点数据

举例如下:

mysql> select * from test2 where geo_in_points(poi, '120.1313 40.2727');
+---------+------+------------------+
| user_id | city | poi              |
+---------+------+------------------+
|       2 | BJ   | 120.1313,40.2727 |
+---------+------+------------------+
1 row in set (0.04 sec)

圆形查询的语法:

geo_in_circle(col_name, 'longitude latitude', radiusInMeters);

一个经纬度坐标点和半径长度确定一个圆(‘longitude latitude’, radiusMeters); 半径长度单位是米; 使用WGS-84坐标系统将地球建模为椭球,且圆形为球面。see http://earth-info.nga.mil/GandG/publications/tr8350.2/wgs84fin.pdf

举例如下:

mysql> select * from test2 where geo_in_circle(poi, '116.4 39.91', 10000) and city='HZ';
+---------+------+------------------+
| user_id | city | poi              |
+---------+------+------------------+
|       1 | HZ   | 116.3972,39.9096 |
+---------+------+------------------+
1 row in set (0.03 sec)

多边形查询的语法:

geo_in_polygon(col_name, 'longitude1 latitude1, longitude2 latitude2, ...');
  • 一系列经纬度坐标点确定一个多边形(‘longitude latitude’…);
  • 按照顺时针传入坐标点形成Polygon,点数不少于4个;
  • Polygon形成闭环,即第一个坐标点和最后一个坐标点相同;
  • Polygon中的边不能相交,否则不能得到正确结果;
  • Polygon不能跨越180度经线,如果跨越180度经线,需要分成2个Polygon。

计算距离的语法:

geo_distance(str1, str2);
geo_distance(col, str2);
geo_distance(str1, col);

举例如下:

mysql> select *,geo_distance(poi, '116.3972 39.9096') as distance from test2;
+---------+------+------------------+----------+
| user_id | city | poi              | distance |
+---------+------+------------------+----------+
|       2 | BJ   | 120.1313,40.2727 |   320536 |
|       1 | HZ   | 116.3972,39.9096 |        0 |
|       3 | SH   | 116.4041,39.9863 |     8558 |
+---------+------+------------------+----------+
3 rows in set (0.01 sec)

4)注意

  • 空间查询的经纬度列参数,统一采用wkt格式(即用经纬度用空格分库)

results matching ""

    No results matching ""