目前lxdb支持地理位置,实现为float8数组类型在options store 里写成isg,注意是在列明的options里设置,不要在 表的options里设置 .

23.1. 建表方法

CREATE FOREIGN TABLE test_geo(
 jwd float8[] options(store 'isg'),
 cityname text 
 ) SERVER lxdb  options (store 'ids') ;

23.2. 数据导入

INSERT INTO test_geo(jwd,cityname) VALUES
(ARRAY[113.97,33.62], '漯河'),(ARRAY[114.38,36.14], '安阳'),
(ARRAY[115.03,35.81], '濮阳'),(ARRAY[114.27,35.77], '鹤壁'),
(ARRAY[113.93,35.36], '新乡'),(ARRAY[113.24,35.22], '焦作'),
(ARRAY[112.47,34.66], '洛阳'),(ARRAY[111.18,34.78], '三门峡'),
(ARRAY[112.54,33.02], '南阳'),(ARRAY[114.07,32.18], '信阳'),
(ARRAY[114.01,33.05], '驻马店'),(ARRAY[114.69,33.63], '周口'),
(ARRAY[115.63,34.43], '商丘'),(ARRAY[114.29,34.84], '开封'),
(ARRAY[113.81,34.08], '许昌'),(ARRAY[113.22,33.79], '平顶山'),
(ARRAY[113.64,34.78], '郑州');

23.3. 地理位置检索

1)圆形where jwd @> ARRAY[0，经度，纬度，半径/米]
select * from test_geo where jwd @> ARRAY[0,113.85,34.11,100000]::float8[]; 
select * from test_geo where jwd @> ARRAY[0,114.04,33.02,100000]::float8[];

2)矩形where jwd @> ARRAY[1，经度最大值，经度最小值，纬度最大值，纬度最小值]
select * from test_geo where jwd @> ARRAY[1,115.35,113.76,36.34,35.57]::float8[];
select * from test_geo where jwd @> ARRAY[1,114.35,112.67,34.31,33.43]::float8[];

3)多边形where jwd @> ARRAY[2,经度,纬度…]
select * from test_geo where jwd @> ARRAY[2,114.61,34.01,114.35,33.44,115.19,33.53,115.19,34.22,114.37,34.31]::float8[];
select * from test_geo where jwd @> ARRAY[2,113.96,35.61,114.90,35.55,115.80,36.03,115.23,36.49,114.64,36.52,114.02,36.54,113.58,36.23]::float8[];

23.4. 简洁写法

上述写法不容易理解,可以使用lxdb包装的函数简化上述SQL

1.函数说明

圆：circle(ARRAY[经度,纬度],半径/米(小数)) 

矩形：orthogon(ARRAY[经度最大值,经度最小值],ARRAY[纬度最大值,纬度最小值]) 

多边形：polygon(ARRAY[ARRAY[经度,纬度],ARRAY[经度,纬度],ARRAY[经度,纬度],…]) 

至少三个点因为至少三个点才能围成一个图形

2.对比使用例子

1)圆形
select * from test_geo where jwd @> ARRAY[0,114.04,33.02,100000]::float8[];
select * from test_geo where jwd @> circle(array[114.04,33.02]::float8[],100000.0);

2)矩形
select * from test_geo where jwd @> ARRAY[1,114.35,112.67,34.31,33.43]::float8[];
select * from test_geo where jwd @> orthogon (array[114.35,112.67]::float8[],array[34.31,33.43]::float8[]); 

3)多边形
select * from test_geo where jwd @> ARRAY[2,114.61,34.01,114.35,33.44,115.19,33.53,115.19,34.22,114.37,34.31]::float8[];
select * from test_geo where jwd @> polygon(array[array[114.61,34.01]::float8[],array[114.35,33.44]::float8[],array[115.19,33.53]::float8[],array[115.19,34.22]::float8[],array[114.37,34.31]::float8[]]);