地理空间数据 Geospatial data

最新推荐文章于 2024-01-02 01:21:22 发布

馥凝儒雅

最新推荐文章于 2024-01-02 01:21:22 发布

阅读量800

点赞数

分类专栏： QuestDB 文章标签：时序数据库 database 数据库 sql 大数据

本文链接：https://blog.youkuaiyun.com/You_are_my_zing/article/details/121790303

版权

QuestDB 专栏收录该内容

11 篇文章

订阅专栏

QuestDB增加了通过 geohash 类型处理地理空间数据的支持。本页面描述了如何使用geohash，并概述了语法，包括关于从纬度和经度转换、通过SQL、fluxdb协议插入和通过Java API插入的提示。

为了方便处理这种数据类型，添加了 spatial 和 operators 来帮助过滤和生成数据。

Geohash 描述

geohash是一种使用短字母数字字符串表示位置的方便方法，使用较长的字符串可以获得更高的精度。其基本思想是，地球被划分成定义大小的网格，每个区域被分配一个独特的id，称为Geohash。对于地球上的给定位置，我们可以将纬度和经度转换为geohash字符串表示的网格的大致中心点。这个字符串是Geohash，它将确定点属于哪个预定义区域。

为了简洁，base32 被用作geohash的表示，因此它由:

所有十进制数字(0-9)和
几乎所有的字母(不区分大小写)，除了“a”，“i”，“l”，“o”。

下图说明了增加geohash的长度如何产生更高精度的网格大小:

QuestDB geohash 类型

Geohash列类型在QuestDB中表示为Geohash(<precision>)。精度以 n{units} 的格式指定，其中 n是数字乘数，units 可以是 c 表示char或 b 表示bits ( c 是5 x b 的缩写)。

下面的示例通过创建一个5 char 精度、29位精度的列，并将geohash值插入到这些列中，展示了geohash的基本用法:

CREATE TABLE geo_data (g5c geohash(5c), g29b geohash(29b));
INSERT INTO geo_data VALUES(#u33d8, ##10101111100101111111101101101)
-- Querying by geohash
SELECT * FROM geo_data WHERE g5c = #u33d8;

不可能在列中存储可变大小的geohash，因此必须事先知道geohash的大小和精度。由于所有位都是有意义的，因此不能将较短精度的geohash插入较长精度的列中。关于geohash大小的详细信息将在下面的 geohash precision 部分中描述。此外，NULL 被支持作为所有精度的geohash列的单独值。

Geohash 语法

geohash有一个字面语法，从hash # 符号开始，后面跟着最多12个字符，即:

INSERT INTO my_geo_data VALUES(#u33, #u33d8b12)

具有单个 hash(#) 的Geohash字面值可以在格式 /{bits} 中包含一个后缀，其中 bits 是从1-60位的数量，以允许进一步控制的Geohash粒度的大小。如果需要指定列大小的精度，但要插入的值使用char 符号，这是很有用的:

-- insert a 5-bit geohash into a 4 bit column
INSERT INTO my_geo_data VALUES(#a/4)
-- insert a 20-bit geohash into an 18 bit column
INSERT INTO my_geo_data VALUES(#u33d/18)

geohash的二进制等价可以用两个 hash 符号(##)后跟最多60位来表示:

INSERT INTO my_geo_data VALUES(##0111001001001001000111000110)

从 strings 到 geohash 的隐式强制转换是可能的，但效率较低，因为必须执行到geohash的字符串转换:

INSERT INTO my_geo_data VALUES(#u33, #u33d8b12)
-- equivalent to
INSERT INTO my_geo_data VALUES('u33', 'u33d8b12')

NULL 值保留1位，这意味着8位的geohash在内部存储为9位的 short 。

指定geohash精度

geohash 类型的大小可以是:

1 ~ 12个字符或
1 ~ 60位

下表显示了使用 char 的geohash精度的所有选项以及geohash表示的网格的计算区域:

Type	Example	Area
`geohash(1c)`	`#u`	5,000km × 5,000km
`geohash(2c)`	`#u3`	1,250km × 625km
`geohash(3c)`	`#u33`	156km × 156km
`geohash(4c)`	`#u33d`	39.1km × 19.5km
`geohash(5c)`	`#u33d8`	4.89km × 4.89km
`geohash(6c)`	`#u33d8b`	1.22km × 0.61km
`geohash(7c)`	`#u33d8b1`	153m × 153m
`geohash(8c)`	`#u33d8b12`	38.2m × 19.1m
`geohash(9c)`	`#u33d8b121`	4.77m × 4.77m
`geohash(10c)`	`#u33d8b1212`	1.19m × 0.596m
`geohash(11c)`	`#u33d8b12123`	149mm × 149mm
`geohash(12c)`	`#u33d8b121234`	37.2mm × 18.6mm

对于大小由 b 决定的geohash，下表比较了一些geohash用比特和字符表示的单位的精度:

Type	Example	Area
`geohash(1c)`	`#u`	5,000km × 5,000km
`geohash(2c)`	`#u3`	1,250km × 625km
`geohash(3c)`	`#u33`	156km × 156km
`geohash(4c)`	`#u33d`	39.1km × 19.5km
`geohash(5c)`	`#u33d8`	4.89km × 4.89km
`geohash(6c)`	`#u33d8b`	1.22km × 0.61km
`geohash(7c)`	`#u33d8b1`	153m × 153m
`geohash(8c)`	`#u33d8b12`	38.2m × 19.1m
`geohash(9c)`	`#u33d8b121`	4.77m × 4.77m
`geohash(10c)`	`#u33d8b1212`	1.19m × 0.596m
`geohash(11c)`	`#u33d8b12123`	149mm × 149mm
`geohash(12c)`	`#u33d8b121234`	37.2mm × 18.6mm

转换 geohash

显式强制转换不是必需的，但给定某些约束，可能需要将字符串强制转换为geohash。对于存储在已设置所有位的列中的geohash值，空字符串被转换为 null :

INSERT INTO my_geo_data VALUES(cast({my_string} as geohash(8c))

在需要创建具有所需模式的表(如下面的查询)的情况下，可能需要强制转换为geohash。注意，使用 WHERE 1 != 1 意味着不插入行，只准备表模式:

CREATE TABLE new_table AS
(SELECT cast(null AS geohash(4c)) gh4c)
FROM source_table WHERE 1 != 1

Geohash类型可以从高精度强制转换为低精度，但不能从低精度强制转换为高精度:

-- The following cast is valid:
CAST(#123 as geohash(1c))
-- Invalid (low-to-high precision):
CAST(#123 as geohash(4c))

SQL示例

下面的查询创建一个包含两个精度不同的 geohash 类型列的表，并将geohash作为字符串值插入:

CREATE TABLE my_geo_data (g1c geohash(1c), g8c geohash(8c));
INSERT INTO my_geo_data values(#u, #u33d8b12);

当插入小精度的列时，大精度的geohases会被截断，并且将小精度的geohases插入大精度的列会产生误差，即:

-- SQL will execute successfully with 'u33d8b12' truncated to 'u'
INSERT INTO my_geo_data values(#u33d8b12, #eet531sq);
-- ERROR as '#e' is too short to insert into 8c_geohash column
INSERT INTO my_geo_data values(#u, #e);

执行地理空间查询是通过检查geohash值是否等于或在其他geohash中。考虑下表:

CREATE TABLE geo_data
  (ts timestamp,
  device_id symbol,
  g1c geohash(1c),
  g8c geohash(8c))
index(device_id) timestamp(ts);

只有当查询中的所有符号列都建立了索引时，才可以使用 within 操作符。

这将创建一个以 symbol 类型列作为标识符的表，我们可以按如下方式插入值:

INSERT INTO geo_data values(now(), 'device_1', #u, #u33d8b12);
INSERT INTO geo_data values(now(), 'device_1', #u, #u33d8b18);
INSERT INTO geo_data values(now(), 'device_2', #e, #ezzn5kxb);
INSERT INTO geo_data values(now(), 'device_1', #u, #u33d8b1b);
INSERT INTO geo_data values(now(), 'device_2', #e, #ezzn5kxc);
INSERT INTO geo_data values(now(), 'device_3', #e, #u33dr01d);

该表包含以下值:

ts	device_id	g1c	g8c
2021-09-02T14:20:04.669312Z	device_1	u	u33d8b12
2021-09-02T14:20:06.553721Z	device_1	u	u33d8b12
2021-09-02T14:20:07.095639Z	device_1	u	u33d8b18
2021-09-02T14:20:07.721444Z	device_2	e	ezzn5kxb
2021-09-02T14:20:08.241489Z	device_1	u	u33d8b1b
2021-09-02T14:20:08.807707Z	device_2	e	ezzn5kxc
2021-09-02T14:20:09.280980Z	device_3	e	u33dr01d

我们可以使用以下查询来检查设备的最后已知位置是否为特定的geohash，该查询将返回基于geohash的精确匹配:

SELECT * FROM geo_data LATEST BY device_id WHERE g8c = #u33dr01d

ts	device_id	g1c	g8c
2021-09-02T14:20:09.280980Z	device_3	e	u33dr01d

first 和 last 方法

在地理空间查询中 first() 和 last() 函数的使用得到了显著优化，从而改进了与位置相关的常见查询类型。这意味着查询，如“最后已知的位置”的索引列在给定的时间范围或样本桶是特别优化的查询速度在大数据集:

SELECT ts, last(g8c) FROM geo_data WHERE device_id = 'device_3';
-- first and last locations sample by 1 hour:
SELECT ts, last(g8c), first(g8c) FROM geo_data
WHERE device_id = 'device_3' sample by 1h;

Within 操作

可以将 within 操作符用作前缀匹配，以计算geohash是否等于或在更大的网格中。如果 g8c 列包含u33d 中的geohash，下面的查询将根据设备ID返回最近的条目:

LATEST BY usage

SELECT * FROM geo_data LATEST BY device_id
WHERE g8c within(#u33d);

within 操作符只能应用于LATEST BY查询，并且查询中的所有 symbol列都必须被索引。

ts	device_id	g1c	g8c
2021-09-02T14:20:08.241489Z	device_1	u	u33d8b1b
2021-09-02T14:20:09.280980Z	device_3	e	u33dr01d

有关该操作符使用的更多信息，请参阅 spatial operators 参考。

Java 操作

通过TableWriter 的 putGeoHash 方法，通过Java QuestDB实例将geohash插入到表中，该方法接受具有目标精度的 LONG 值。此外，GeoHashes.fromString 可以用于字符串转换，但与直接使用long 相比，会带来一些性能开销:

try (TableWriter writer = engine.getWriter(ctx.getCairoSecurityContext(), "geohash_table")) {
    for(int i = 0; i < 10; i++) {
        TableWriter.Row row = writer.newRow();
        row.putSym(0, "my_device");
        // putGeoStr(columnIndex, hash)
        row.putGeoStr(1, "u33d8b1b");
        // putGeoHashDeg(columnIndex, latitude, longitude)
        row.putGeoHashDeg(2, 48.669, -4.329)
        row.append();
    }
    writer.commit();
}

通过Java读取geohash可以通过以下方法完成:

Record.getGeoByte (columnIndex)
Record.getGeoShort (columnIndex)
Record.getGeoInt (columnIndex)
Record.getGeoLong (columnIndex)

因此，有必要事先通过列元数据索引了解列的类型:

ColumnType.tagOf(TableWriter.getMetadata().getColumnType(columnIndex));

调用上面的方法将返回以下其中之一:

ColumnType.GEOBYTE
ColumnType.GEOSHORT
ColumnType.GEOINT
ColumnType.GEOLONG

有关使用表读取器和写入器的更多信息和详细示例，请参阅 Java API文档。

InfluxDB line protocol

Geohash也可以通过 InfluxDB line protocol 插入。以这种方式执行插入;

1.先创建带有geohash类型列的表:

CREATE TABLE tracking (ts timestamp, geohash geohash(8c));

2.通过使用geohash字段的 InfluxDB line protocol 插入:

tracking geohash="46swgj10"

Ifluxdb Line Protocol解析器不支持 geohash 语法，只支持 string 。这意味着在使用该协议插入行之前，必须存在具有所需精度的 geohash 类型的表列。

如果一个值不能被转换或被省略，它将被设置为 NULL

如果插入geohash的精度大于所插入的列的精度，则会导致该值被截断，例如，给定一个精度为8c的列:

# Inserting the following line
geo_data geohash="46swgj10r88k"
# Equivalent to truncating to this value:
geo_data geohash="46swgj10"

Postgres

Geohash也可以作为其他数据类型在Postgres网络协议上使用。下面的Python示例演示了如何通过postgres连接到QuestDB，插入和查询geohash:

当通过Postgres网络协议查询geohash值时，QuestDB总是以文本模式(即字符串)而不是二进制模式返回geohash值

import psycopg2 as pg
import datetime as dt

try:
    connection = pg.connect(user="admin",
                            password="quest",
                            host="127.0.0.1",
                            port="8812",
                            database="qdb")
    cursor = connection.cursor()

    cursor.execute("""CREATE TABLE IF NOT EXISTS geo_data
      (ts timestamp, device_id symbol index, g1c geohash(1c), g8c geohash(8c))
      timestamp(ts);""")

    cursor.execute("INSERT INTO geo_data values(now(), 'device_1', 'u', 'u33d8b12');")
    cursor.execute("INSERT INTO geo_data values(now(), 'device_1', 'u', 'u33d8b18');")
    cursor.execute("INSERT INTO geo_data values(now(), 'device_2', 'e', 'ezzn5kxb');")
    cursor.execute("INSERT INTO geo_data values(now(), 'device_3', 'e', 'u33dr01d');")
    # commit records
    connection.commit()

    print("Data in geo_data table:")
    cursor.execute("SELECT * FROM geo_data;")
    records = cursor.fetchall()
    for row in records:
        print(row)

    print("Records within 'u33d' geohash:")
    cursor.execute("SELECT * FROM geo_data LATEST BY device_id WHERE g8c within(#u33d);")
    records = cursor.fetchall()
    for row in records:
        print(row)

finally:
    if (connection):
        cursor.close()
        connection.close()
        print("QuestDB connection closed")