CODEWITHSUNDEEP
geolocationpolygonsnowflake-cloud-data-platform
Shafeeque
Shafeeque

Reputation: 2069

Snowflake query to fetch data within a polygon

I have table with location column data as lat long information, how to get records where location within polygon

Polygon Points 

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

Upvotes: 3

Views: 970

Answers (1)

Nat Taylor
Nat Taylor

Reputation: 1108

Here is an example 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);

Upvotes: 1

Related Questions