在数字化时代,地理空间分析(Geospatial Analytics)成为辅助企业市场策略洞察的重要手段。无论是精准广告投放,还是电商物流的效率优化,都离不开对地理空间数据的查询、分析和可视化处理,以便助力企业更好决策。
随着实时分析报表等OLAP市场的扩大,地理空间分析也作为新的增值特性被业界几大OLAP主流产品所推广。OLAP+GIS能力在满足用户地理空间数据分析的基础上,还能在数据体量大、实效性要求高的情况下,满足业务高性能查询的需求。作为火山引擎推出的一款OLAP引擎,ByteHouse也开发了高性能地理空间分析GIS能力,为位置洞察、人群圈选等场景提供高性能地理数据分析服务。
SRID (Spatial Reference Identifier ,空间引用标识符):
GIS功能包含点、线、面等几何类型,几何之间的空间位置关系以及周边生态。为了兼容OGC(Open Geospatial Consortium)标准,满足用户对使用需求,Bytehouse GIS模块包括Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection共7几何类型。
如果想创建一个 GIS 相关的表,需要在建表的时候声明对应的 geometry 列。
常见的声明数据类型包括:
Geometry('Point', 4326)
,其中 4326 为 SRID 值,不填写的话 Geometry('Point')默认值为 0Geometry('LineString', 4326)
如果想利用空间索引,需要在 order by 里面指定 geometry 列为主键索引。
CREATE TABLE IF NOT EXISTS test_gis_point_type ( `p` Geometry('Point', 4326) ) ENGINE = MergeTree ORDER BY p SETTINGS index_granularity = 8192; insert into test_gis_point_type values ((0,0)), ('(1,1)'), (ST_GeomFromText('POINT (2 2)')); select ST_AsText(p) from test_gis_point_type order by p;
CREATE TABLE IF NOT EXISTS test_gis_linestring_type ( `p` Geometry('LineString', 4326) ) ENGINE = MergeTree ORDER BY p SETTINGS index_granularity = 8192; insert into test_gis_linestring_type values ([(0,0), (0, 0)]), ('[(2,2), (2,2)]'), (ST_GeomFromText('LINESTRING (3 3, 3 3)')); select ST_AsText(p) from test_gis_linestring_type order by p;
CREATE TABLE IF NOT EXISTS test_gis_polygon_type ( `p` Geometry('Polygon', 4326) ) ENGINE = MergeTree ORDER BY p SETTINGS index_granularity = 8192; insert into test_gis_polygon_type values ([[(0,0), (1,0), (1,1), (0,1), (0,0)]]), ('[[(0,0), (2,0), (2,2), (0,2), (0,0)]]'), (ST_GeomFromText('POLYGON ((0 0, 3 0, 3 3, 0 3, 0 0))')); select ST_AsText(p) from test_gis_polygon_type order by p;
CREATE TABLE IF NOT EXISTS test_gis_multipoint_type ( `p` Geometry('MultiPoint', 4326) ) ENGINE = MergeTree ORDER BY p SETTINGS index_granularity = 8192; insert into test_gis_multipoint_type values ([(0,0), (0,0)]), ('[(2,2), (2,2)]'), (ST_GeomFromText('MULTIPOINT ((3 3), (3 3))')); select ST_AsText(p) from test_gis_multipoint_type order by p;
CREATE TABLE IF NOT EXISTS test_gis_multilinestring_type ( `p` Geometry('MultiLineString', 4326) ) ENGINE = MergeTree ORDER BY p SETTINGS index_granularity = 8192; insert into test_gis_multilinestring_type values ([[(0,0), (0,0)], [(0,0), (0,0)]]), ('[[(1, 1), (1, 1)], [(1, 1), (1, 1)]]'), (ST_GeomFromText('MULTILINESTRING ((0 0, 1 1), (2 2, 3 3))')); select ST_AsText(p) from test_gis_multilinestring_type order by p;
CREATE TABLE IF NOT EXISTS test_gis_multipolygon_type ( `p` Geometry('MultiPolygon', 4326) ) ENGINE = MergeTree ORDER BY p SETTINGS index_granularity = 8192; insert into test_gis_multipolygon_type values ([[[(0,0), (1,0), (1,1), (0,1), (0,0)]], [[(0,0), (2,0), (2,2), (0,2), (0,0)]]]), ('[[[(0,0), (1,0), (1,1), (0,1), (0,0)]], [[(0,0), (2,0), (2,2), (0,2), (0,0)]]]'), (ST_GeomFromText('MULTIPOLYGON (((1 1, 1 3, 3 3, 3 1, 1 1)), ((4 3, 6 3, 6 1, 4 1, 4 3)))')), (ST_GeomFromText('MULTIPOLYGON (((0 0, 3 0, 3 3, 0 3, 0 0)))')); select ST_AsText(p) from test_gis_multipolygon_type order by p;
CREATE TABLE IF NOT EXISTS test_gis_geometrycollection_type ( `p` Geometry('GeometryCollection', 4326) ) ENGINE = MergeTree ORDER BY p SETTINGS index_granularity = 8192; insert into test_gis_geometrycollection_type values (ST_GeomFromText('GEOMETRYCOLLECTION (MULTIPOINT((0 0), (1 1)), POINT(3 4), LINESTRING(2 3, 3 4))')), (ST_GeomFromText('GEOMETRYCOLLECTION (MULTIPOINT((2 2), (3 3)), POINT(5 6), LINESTRING(5 3, 3 4))')), (ST_GeomFromText('GEOMETRYCOLLECTION (MULTIPOINT((-1 -1), (1 1)), POINT(10 10), LINESTRING(20 30, 30 40))')); select ST_AsText(p) from test_gis_geometrycollection_type order by p;
请参考 函数-地理函数。