Cklickhouse中的PointInpolygon功能，用于与地理坐标合作
我在clickhouse中有一个表 - telemetry_data ，带有两个字段： lat（float64） lon（float65） 其中存储坐标。 我需要从该表中选择记录，其中使用这些坐标点将属于平面上的多边形。 描述该多边形的点是已知的。 Clickhouse具有一个功能 -
select deviceId, lat, lon from `click-ss`.telemetry_data
WHERE 1=1
and (lat is not null and lon is not null)
and (lat != 0 and lon != 0)
and fix='1' --at that moment there were a sufficient number of satellites
and toTimeZone(eventDt, 'Europe/Moscow') BETWEEN '2022-07-01 00:00:00' AND '2022-07-01 23:59:59'
and pointInPolygon(tuple(lat, lon), array(tuple(55.736916, 37.365922), tuple(55.791985, 37.367902), tuple(55.872216, 37.399581), tuple(55.918200, 37.564574), tuple(55.896698, 37.713728), tuple(55.825806, 37.847042), tuple(55.646882, 37.848362), tuple(55.566931, 37.682709), tuple(55.584878, 37.501877), tuple(55.714776, 37.382372))) = 1
我遇到的错误：
sql错误[1002]：ClickHouse异常，代码：1002，主机：127.0.0.1，端口：17961;代码：43。DB::异常：函数PointInpolygon的参数1必须在位置1：processing（fix ='1'）和（（1 = 1）和（lat不是null）和（（lon是不为lan是）， null）和（lat！= 0）和（lon！= 0）和（totimezone（eventdt，'欧洲/莫斯科'）＆gt; ='2022-07-01 00:00:00'）和（TotimeZone（Eventdt，'欧洲/莫斯科'）＆lt; ='2022-07-01 23:59:59'）and（pointInpolygon（（（（lat，lon）），（55.736916，37.3659222 ），（55.791985，37.367902），， （55.872216，37.399581），（55.9182，37.564574），（55.896698，37.713728），（55.825806，37.847042） 37.682709），（55.584878，37.501877），（55.714776，37.382372）]]）= 1）和（1 = 1））。 （ILLEGAL_TYPE_OF_ARGUMENT）（版本22.3.6.5（官方构建））
该错误说函数pointInpolygon的参数1必须在位置1处包含数字元组，
但如果我查看数据类型：
select deviceId, tuple(lat,lon) as geo, toTypeName(geo) from `click-ss`.telemetry_data
WHERE (lat != 0 and lon != 0)
我会得到：
> >设备1（55.6637,37.6335）元组（无效（float64），无效（float64））
请帮助我了解如何正确使用此功能以及在哪里遇到错误。
I have a table in clickhouse - telemetry_data with two fields:
lat(Float64)
lon(Float65)
in which coordinates are stored.
I need to select records from this table in which the point with these coordinates will belong to the polygon on the plane.
The points describing this polygon are known.
Clickhouse has a function for that - pointInPolygon
But I can't use it.
Here is my request:
select deviceId, lat, lon from `click-ss`.telemetry_data
WHERE 1=1
and (lat is not null and lon is not null)
and (lat != 0 and lon != 0)
and fix='1' --at that moment there were a sufficient number of satellites
and toTimeZone(eventDt, 'Europe/Moscow') BETWEEN '2022-07-01 00:00:00' AND '2022-07-01 23:59:59'
and pointInPolygon(tuple(lat, lon), array(tuple(55.736916, 37.365922), tuple(55.791985, 37.367902), tuple(55.872216, 37.399581), tuple(55.918200, 37.564574), tuple(55.896698, 37.713728), tuple(55.825806, 37.847042), tuple(55.646882, 37.848362), tuple(55.566931, 37.682709), tuple(55.584878, 37.501877), tuple(55.714776, 37.382372))) = 1
And the error I am getting:
SQL Error [1002]: ClickHouse exception, code: 1002, host: 127.0.0.1, port: 17961; Code: 43. DB::Exception: Argument 1 for function pointInPolygon must contain numeric tuple at position 1: While processing (fix = '1') AND ((1 = 1) AND (lat IS NOT NULL) AND (lon IS NOT NULL) AND (lat != 0) AND (lon != 0) AND (toTimeZone(eventDt, 'Europe/Moscow') >= '2022-07-01 00:00:00') AND (toTimeZone(eventDt, 'Europe/Moscow') <= '2022-07-01 23:59:59') AND (pointInPolygon((lat, lon), [(55.736916, 37.365922), (55.791985, 37.367902), (55.872216, 37.399581), (55.9182, 37.564574), (55.896698, 37.713728), (55.825806, 37.847042), (55.646882, 37.848362), (55.566931, 37.682709), (55.584878, 37.501877), (55.714776, 37.382372)]) = 1) AND (1 = 1)). (ILLEGAL_TYPE_OF_ARGUMENT) (version 22.3.6.5 (official build))
The error says that the Argument 1 for function pointInPolygon must contain numeric tuple at position 1
But if i look at the data type:
select deviceId, tuple(lat,lon) as geo, toTypeName(geo) from `click-ss`.telemetry_data
WHERE (lat != 0 and lon != 0)
I will get:
device 1 (55.6637,37.6335) Tuple(Nullable(Float64), Nullable(Float64))
Please help me understand how to use this function correctly and where am i getting an error.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
显然，您不需要指定呼叫 tuple（）在您使用PointInpolygon（）（array（）的同一件事）中函数。有关更多示例，请参见下面的PointInpolygon（）测试（来自ClickHouse源代码）：
https://github.com/clickhouse/clickhouse/blob/9f5cd35a6963cc555556a5121218b46b46b0754dcac754dcac7306a/testss/testss/queries/queries/0_stateless/0_stateless/00500_pointeless/00500_point_point_point_in_in_polygon.sql
Apparently, you don't need to specify the call to the tuple() function in your usage of pointInPolygon() (same thing for array()). For more examples, see the pointInPolygon() tests below (from ClickHouse Source Code):
https://github.com/ClickHouse/ClickHouse/blob/9f5cd35a6963cc556a51218b46b0754dcac7306a/tests/queries/0_stateless/00500_point_in_polygon.sql
在将其传递给PointInpolygon函数之前，您需要明确地施放不可用的类型。例如：
即使您已经有条件
lat不是null，而LON也不是nullYou need to explicitly cast non-nullable type before passing it to pointInPolygon function. Ex:
Even if you already have a condition
lat is not null and lon is not null