坐标检索和排序

ESQL 为您提供了坐标类型（geo_point）数据的检索和排序功能，相关的检索函数有：

bounding_box	区域检索函数，在 FILTER 中使用
distance	距离检索函数，在 FILTER 中使用
distance	距离排序函数，在 ORDER BY 中使用

FILTER 子句 和 WHERE 子句是 AND 的关系

实例：

DROP TABLE hz_point
CREATE TABLE hz_point.base(
    name STRING,
    location geo_point
);
DESC hz_point

DELETE hz_point
-- ES规定以字符串表示的坐标纬度在前
BULK INTO hz_point.base(name, location) values
('九和路', '30.311865,120.258854'),
('七堡', '30.306378,120.247427'),
('彭埠', '30.300267,120.229892'),
('杭州东站', '30.297086,120.219328'),
('闸弄口', '30.290163,120.198991'),
('打铁关', '30.290849,120.183396'),
('西湖文化广场', '30.285797,120.172329'),
('武林广场', '30.278188,120.170892'),
('凤起路', '30.268238,120.170389'),
('龙翔桥', '30.260502,120.170604'),
('定安路', '30.251705,120.174341'),
('城站', '30.250083,120.187708'),
('婺江路', '30.242533,120.197553'),
('近江', '30.236542,120.204452'),
('江陵路', '30.215417,120.223065'),
('滨和路', '30.205741,120.223999'),
('西兴', '30.193256,120.227161'),
('滨康路', '30.189885,120.237294');

FLUSH hz_point
SELECT * FROM hz_point;

-- 检索两点（西湖文化广场、江陵路）的矩形内的坐标
SELECT name, location FROM hz_point FILTER location.bounding_box('30.285797,120.172329', '30.215417,120.223065');

-- 检索距离坐标（凤起路）1公里内的所以坐标
SELECT * FROM hz_point FILTER location.distance('30.268238,120.170389', '1km')

-- 检索距离坐标（凤起路）2公里外、10公里内的所以坐标
SELECT * FROM hz_point FILTER location.distance('30.268238,120.170389', '2km', '10km')

-- 取得所以坐标，并由近到远排序（相对凤起路）
SELECT * FROM hz_point ORDER BY location.distance('30.268238,120.170389')

-- 取得所以坐标，并由远到近排序（相对凤起路）
SELECT * FROM hz_point ORDER BY location.distance('30.268238,120.170389') DESC

-- 取得所以坐标，并由近到远排序（相对九和路和滨康路）
SELECT * FROM hz_point ORDER BY location.distance('["30.311865,120.258854", "30.189885,120.237294"]')

-- （凤起路2km内 或者 距离九和路和滨康路10km内）并且 （名称前面以『武林』或『武林』开头 并且 以『广场』结尾）的地标
SELECT * FROM hz_point
FILTER location.distance('30.268238,120.170389', '2km') OR (
    location.distance('30.311865,120.258854', '10km')  AND location.distance('30.189885,120.237294', '10km'))
WHERE name='*广场' AND (name = '武林*' OR name='西湖文化*')

-- 删除（凤起路2km内 或者 距离九和路和滨康路10km内）并且 （名称前面以『武林』或『武林』开头 并且 以『广场』结尾）的地标
DELETE hz_point
FILTER location.distance('30.268238,120.170389', '2km') OR (
    location.distance('30.311865,120.258854', '10km')  AND location.distance('30.189885,120.237294', '10km'))
WHERE name='*广场' AND (name = '武林*' OR name='西湖文化*')

-- 上面的 FILTER、ORDER BY 和 WHERE 可自由组合；适用于 DELETE 语句。

实例2：

DROP TABLE hz_point_a;
CREATE TABLE hz_point_a.base(
    name STRING,
    a OBJECT (
        location geo_point
    )
);
DESC hz_point_a;

DELETE hz_point_a;
-- ES规定以字符串表示的坐标纬度在前
BULK INTO hz_point_a.base(name, a()) values
('九和路', '{"location":"30.311865,120.258854"}'),
('七堡', '{"location":"30.306378,120.247427"}'),
('彭埠', '{"location":"30.300267,120.229892"}'),
('杭州东站', '{"location":"30.297086,120.219328"}'),
('闸弄口', '{"location":"30.290163,120.198991"}'),
('打铁关', '{"location":"30.290849,120.183396"}'),
('西湖文化广场', '{"location":"30.285797,120.172329"}'),
('武林广场', '{"location":"30.278188,120.170892"}'),
('凤起路', '{"location":"30.268238,120.170389"}'),
('龙翔桥', '{"location":"30.260502,120.170604"}'),
('定安路', '{"location":"30.251705,120.174341"}'),
('城站', '{"location":"30.250083,120.187708"}'),
('婺江路', '{"location":"30.242533,120.197553"}'),
('近江', '{"location":"30.236542,120.204452"}'),
('江陵路', '{"location":"30.215417,120.223065"}'),
('滨和路', '{"location":"30.205741,120.223999"}'),
('西兴', '{"location":"30.193256,120.227161"}'),
('滨康路', '{"location":"30.189885,120.237294"}');

FLUSH hz_point_a;
SELECT * FROM hz_point_a;

-- 检索两点（西湖文化广场、江陵路）的矩形内的坐标
SELECT * FROM hz_point_a FILTER a.location.bounding_box('30.285797,120.172329', '30.215417,120.223065');
-- 检索距离坐标（凤起路）1公里内的所以坐标
SELECT * FROM hz_point_a FILTER a.location.distance('30.268238,120.170389', '1km');
-- 检索距离坐标（凤起路）2公里外、10公里内的所以坐标
SELECT * FROM hz_point_a FILTER a.location.distance('30.268238,120.170389', '2km', '10km');
-- 取得所以坐标，并由近到远排序（相对凤起路）
SELECT * FROM hz_point_a ORDER BY a.location.distance('30.268238,120.170389') limit 20;
-- 取得所以坐标，并由远到近排序（相对凤起路）
SELECT * FROM hz_point_a ORDER BY a.location.distance('30.268238,120.170389') DESC limit 20;
-- 取得所以坐标，并由近到远排序（相对九和路和滨康路）
SELECT * FROM hz_point_a ORDER BY a.location.distance('["30.311865,120.258854", "30.189885,120.237294"]') limit 20;