RayCasting function in Power Query

[andrehoyer]

Dear all,

I hope you can help with the below. I have asked ChatGTP to generate a ray casting algorithm function in PowerQuery. Ray Casting is a method to define if a point is within or outside of a polygon (for reference see https://en.wikipedia.org/wiki/Point_in_polygon#Ray_casting_algorithm).

With the help of ChatGPT and some code tweaking I have gotten the following code:

let
rayCasting = (pointX as number, pointY as number) as logical =>
    let
		// Define the polygon coordinates (clockwise or counterclockwise order)
			polygonPoints = {
                {9.179198, 48.779528},
                {9.182713, 48.778624},
                {9.181706, 48.777150},
                {9.177811, 48.778139},
                {9.179184, 48.779532}
                // Add the rest of the polygon coordinates here
            },
        intersectCount = List.Count(
            List.Select(
                List.Transform(
                    {1..List.Count(polygonPoints)}, 
                    each 
                        let 
                            currentPoint = polygonPoints{_}, 
                            nextPoint = if _ = List.Count(polygonPoints) then polygonPoints{0} else polygonPoints{_ + 1},
                            x1 = currentPoint{0}, y1 = currentPoint{1},
                            x2 = nextPoint{0}, y2 = nextPoint{1},
                            intersect = if (y1 > pointY) <> (y2 > pointY) and (pointX < (x2 - x1) * (pointY - y1) / (y2 - y1) + x1) then true else false
                        in 
                            intersect
                ), 
                each _
            )
        )
    in
        if Number.Mod(intersectCount/2) <> 0 then true else false,
    apply_point_in_polygon_to_table = (input_table as table) as table =>
        let
			result_table = Table.AddColumn(input_table, "Result", each rayCasting([Latitude], [Longitude]))
        in
            result_table,
    input_table = Table.FromColumns({
        Coordinates_test[MainID],
        Coordinates_test[Latitude],
        Coordinates_test[Longitude]
    }, {"Main ID", "Latitude", "Longitude"}),
    result_table = apply_point_in_polygon_to_table(input_table),
    Result = result_table{0}[Result]
in
    Result

My input_table looks as follows (examples):

MainID	Latitude	Longitude
1	48,781868	9,181387
2	48,778966	9,182011
3	48,778355	9,180600

Currently I am receiving the following error:

Expression Error : There weren't enough elements in the enumeration to complete the operation

Details:
[List]

Can someone help with this error and/or probably review the code in general?

Many thanks for your help.

[Anonymous]

Hi @andrehoyer ,

Please try this:

let
...
...
    intersectCount = List.Count(
        List.Select(
            List.Transform(
                {0..List.Count(polygonPoints)-1}, 
                each 
                    let 
                        currentPoint = polygonPoints{_}, 
                        nextPoint = if _ = List.Count(polygonPoints)-1 then polygonPoints{0} else polygonPoints{_ + 1},
                        x1 = currentPoint{0}, y1 = currentPoint{1},
                        x2 = nextPoint{0}, y2 = nextPoint{1},
                        intersect = if (y1 > pointY) <> (y2 > pointY) and (pointX < (x2 - x1) * (pointY - y1) / (y2 - y1) + x1) then true else false
                    in 
                        intersect
            ), 
            each _
        )
    )
	in
...
...
in
    Result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[andrehoyer]

Thanks Gao,

I am now receiving the error:

Expression.Error: 1 arguments were passed to a function which expects between 2 and 3

Details:
Pattern=
Arguments=[List]

Any ideas? Thank you.

[andrehoyer]

Thanks for your help. Unfortunately I am still receiving the same error message. If I go to the error it leads me here:

let
rayCasting = (pointX as number, pointY as number, polygonPoints as list) as logical =>
let
intersectCount = List.Count(
List.Select(
List.Transform(
{1..List.Count(polygonPoints)},
each
let
currentPoint = polygonPoints{_},
nextPoint = if _ = List.Count(polygonPoints) then polygonPoints{0} else polygonPoints{_ + 1},
x1 = currentPoint{0}, y1 = currentPoint{1},
x2 = nextPoint{0}, y2 = nextPoint{1},
intersect = if (y1 > pointY) <> (y2 > pointY) and (pointX < (x2 - x1) * (pointY - y1) / (y2 - y1) + x1) then true else false
in
intersect
),
each _
)
)
in
if Number.Mod(intersectCount/2) <> 0 then true else false,

Could it be an issue with the polygonPoints list?

[johnbasha33]

@andrehoyer 

It seems like the error you're encountering occurs when trying to access an element from the result_table using indexing, specifically with result_table{0}[Result]. However, this approach is unnecessary because the result_table is already a table with the "Result" column appended.

Here's the corrected code:

let
rayCasting = (pointX as number, pointY as number, polygonPoints as list) as logical =>
let
intersectCount = List.Count(
List.Select(
List.Transform(
{1..List.Count(polygonPoints)},
each
let
currentPoint = polygonPoints{_},
nextPoint = if _ = List.Count(polygonPoints) then polygonPoints{0} else polygonPoints{_ + 1},
x1 = currentPoint{0}, y1 = currentPoint{1},
x2 = nextPoint{0}, y2 = nextPoint{1},
intersect = if (y1 > pointY) <> (y2 > pointY) and (pointX < (x2 - x1) * (pointY - y1) / (y2 - y1) + x1) then true else false
in
intersect
),
each _
)
)
in
if Number.Mod(intersectCount/2) <> 0 then true else false,

apply_point_in_polygon_to_table = (input_table as table, polygonPoints as list) as table =>
let
result_table = Table.AddColumn(input_table, "Result", each rayCasting([Longitude], [Latitude], polygonPoints))
in
result_table,

input_table = Table.FromColumns({
{1, 2, 3},
{48.781868, 48.778966, 48.778355},
{9.181387, 9.182011, 9.180600}
}, {"Main ID", "Latitude", "Longitude"}),

polygonPoints = {
{9.179198, 48.779528},
{9.182713, 48.778624},
{9.181706, 48.777150},
{9.177811, 48.778139},
{9.179184, 48.779532}
},

result_table = apply_point_in_polygon_to_table(input_table, polygonPoints)
in
result_table

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!