用于获取多边形内数据的雪花查询

Question

>我有包含位置列数据作为经度信息的表格，如何获取多边形内位置的记录

多边形点

[
[42.811521745097906, -105.60058593749999],
[33.94335994657882, -109.2041015625],
[35.60371874069732, -88.02246093749999],
[46.67959446564018, -89.6923828125],
[42.811521745097906, -105.60058593749999]
]

Answer 1

下面是一个示例 UDF。

create or replace function inside(point array, vs array)
returns string
language javascript
as $$
var point = POINT;
var vs = VS;
var x = point[0], y = point[1];
var inside = false;
for (var i = 0, j = vs.length - 1; i < vs.length; j = i++) {
var xi = vs[i][0], yi = vs[i][1];
var xj = vs[j][0], yj = vs[j][1];
var intersect = ((yi > y) != (yj > y))
&& (x < (xj - xi) * (y - yi) / (yj - yi) + xi);
if (intersect) inside = !inside;
}
return inside;
$$;
select inside(parse_json('[ 4.9, 1.2 ]')::array, parse_json('[ [ 1, 1 ], [ 1, 2 ], [ 2, 2 ], [ 2, 1 ] ]')::array);