6.2. 基于Morton的地理位置检索
6.2.1. 基本使用
1. 创建实例
能支持geo查询的数据类型必须为y_geopoint_idmp。
create table geo_example(
id y_string_id,
geoname y_string_id,
geodata y_geopoint_idmp
);
2. 导入数据
(1) 数据样例
1) 每条数据一个点:
{"tablename":"geo_example","partition":"test","id":1,
"geoname":"102.72242222037083,28.88523027458959 ",
"geodata":"102.72242222037083,28.88523027458959 "}
2) 每条数据2个点,每个点之间以空格隔开,当然写成json数组也可以:
{"tablename":"geo_example","partition":"test","id":1,
"geoname":"102.72242222037083,28.88523027458959 103.72242222037083,29.88523027458959 ",
"geodata":"102.72242222037083,28.88523027458959 103.72242222037083,29.88523027458959 "}
(2) 导入测试数据
sh load.sh -t geo_example -p test1 -tp json -local -f /wyh/test.json
(3) 全表扫描
3. 筛选方法
(1) 圆形区域匹配
查询语句如下:
select count(*) from geo_example where partition like '%'
and SYS_JSON_QUERY@{
query_type:"geo"
,geo_type:"circle"
,field:"geodata"
,list:["99.71,29.38"]
,radius:1000
}@SYS_JSON_QUERY
limit 20;
查询结果如下图所示(查询半径不同,匹配到的结果数不同):
(2) 方形区域匹配
查询语句如下:
select count(*) from geo_example where partition like '%'
and SYS_JSON_QUERY@{
query_type:"geo"
,geo_type:"box"
,field:"geodata"
,list:["99.71,29.38"]
,radius:10000
}@SYS_JSON_QUERY
limit 20;
查询结果如下图所示(查询半径不同,匹配到的结果数不同):
(3) 多边形区域匹配
查询语句如下(至少4个点,且因多边形要求闭合,第一个与最后一个点必须相同):
select count(*) from geo_example where partition like '%'
and SYS_JSON_QUERY@{
query_type:"geo"
,geo_type:"polygon"
,field:"geodata"
,list:["105.39,45.40","99.50,29.20","110.50,29.20","105.39,45.40"]
}@SYS_JSON_QUERY
limit 20;
查询结果如下图所示:
(4) Geohash网格汇聚
查询语句如下:
select geohash_s,count(*) as cnt from geo_example where partition like '%'
and SYS_GEO_QUERY@{
field:"geodata"
,minLon:"99.50"
,minLat:"29.20"
,maxLon:"110.50"
,maxLat:"45.40"
,precision:4
}@SYS_GEO_QUERY
group by geohash_s order by cnt desc
limit 20;
查询结果如下图所示(Geohash分块标识在第4章节中有所介绍):