cratedb geo 查询

cratedb支持的geo 查询还相对比较全，开发基本的功能已经够用了

安装cratedb

docker run -d -p 4200:4200 crate

创建数据库

创建表

CREATE TABLE country (

  name string,

  country_code string primary key,

  shape geo_shape INDEX USING "geohash" WITH (precision='100m'),

  capital string,

  capital_location geo_point

 ) WITH (number_of_replicas=0);

添加数据

INSERT INTO country (mame, country_code, shape, capital, capital_location)

 VALUES (

  'Austria',

  'at',

  {type='Polygon', coordinates=[

        [[16.979667, 48.123497], [16.903754, 47.714866],

        [16.340584, 47.712902], [16.534268, 47.496171],

        [16.202298, 46.852386], [16.011664, 46.683611],

        [15.137092, 46.658703], [14.632472, 46.431817],

        [13.806475, 46.509306], [12.376485, 46.767559],

        [12.153088, 47.115393], [11.164828, 46.941579],

        [11.048556, 46.751359], [10.442701, 46.893546],

        [9.932448, 46.920728], [9.47997, 47.10281],

        [9.632932, 47.347601], [9.594226, 47.525058],

        [9.896068, 47.580197], [10.402084, 47.302488],

        [10.544504, 47.566399], [11.426414, 47.523766],

        [12.141357, 47.703083], [12.62076, 47.672388],

        [12.932627, 47.467646], [13.025851, 47.637584],

        [12.884103, 48.289146], [13.243357, 48.416115],

        [13.595946, 48.877172], [14.338898, 48.555305],

        [14.901447, 48.964402], [15.253416, 49.039074],

        [16.029647, 48.733899], [16.499283, 48.785808],

        [16.960288, 48.596982], [16.879983, 48.470013],

        [16.979667, 48.123497]]

  ]},

  'Vienna',

  [16.372778, 48.209206]

 );

数据查询

支持match

SELECT name from country

 WHERE match("shape",

   'LINESTRING (13.3813 52.5229, 11.1840 51.5497, 8.6132 50.0782, 8.3715 47.9457, 8.5034 47.3685)'

 );

intersects distance within

SELECT within(capital_location, shape) AS capital_in_country

 FROM country;

SELECT distance(capital_location, 'POINT(0.0 90.0)') as from_northpole

 FROM country ORDER BY country_code;

SELECT intersects(

   {type='LineString', coordinates=[[13.3813, 52.5229],

                                    [11.1840, 51.5497],

                                    [8.6132, 50.0782],

                                    [8.3715, 47.9457],

                                    [8.5034, 47.3685]]},

   shape) as berlin_zurich_intersects

 FROM country ORDER BY country_code;

参考资料

https://crate.io/docs/crate/reference/en/latest/general/dql/geo.html