Adapt code for Point in Polygon, for multiple polygons, and refine search to optimize time

[DanSanDST]

Greetings, i been following this guide: (and first the DAX aproach)

Implementing Point-in-Polygon as a Custom Function in Power Query | dynAnalytics: Data Analysis and ...
Implementing Point-in-Polygon in DAX | dynAnalytics: Data Analysis and Visualization

And while the function worked in DAX for multiple points, it searchs every Point in every Polygon and i cant even get in what polygon name was the point.

Now looking into the Power Query solution, you can only pass 1 polygon into the function, so im looking a way to pass it one Polygon every time, and only those where the "zone" from the Points table coincides with the "zone" from the Poligons table (else its not optimized and takes ages to calculate thousand points into 100 polygons with 800 points each). Also i need to get the "polygon name" and "zone" the point was.

I tried passing into the function the Polygons table and match the position of "zone" in each tables, but it only passes the first polygon for each Zone, i need it to seach into all the polygons of each zone, but only by zone because searching in all the polygons takes too much.

= (lat as number, lon as number, Poligonos4 as table, zona as text) as logical => 
let
    WktPolygon = Poligonos4[value]{List.PositionOf(Poligonos4[zona], zona)},
    Polylist = Geometry.FromWellKnownText(WktPolygon)[Rings]{0}[Points],
    Edges = List.Zip({List.RemoveLastN(Polylist,1),List.RemoveFirstN(Polylist,1)}),
    Crossed = List.Select(Edges,each 
        (_{0}[Y]>lat <> _{1}[Y]>lat) 
          and 
        (lon < (_{0}[X]-_{1}[X]) * (lat-_{1}[Y]) / (_{0}[Y]-_{1}[Y]) + _{1}[X])
    ),
    Result = Number.IsOdd(List.Count(Crossed))
in
    Result

as an example my data looks like: (im invoking the function as a new column to return true or false, i could get the 3 values i need in that column then split the column)

Polygons

Points

[burakkaragoz · Accepted solution]

Hi @DanSanDST ,

Looking at your code, the issue is that you're only getting the first polygon per zone because List.PositionOf() returns the first occurrence. You need to iterate through all polygons in a zone and return which one contains the point.

Here's a modified approach:

= (lat as number, lon as number, Poligonos4 as table, zona as text) as record => 
let
    // Filter polygons to only the matching zone
    FilteredPolygons = Table.SelectRows(Poligonos4, each [zona] = zona),
    
    // Function to check if point is in a single polygon
    PointInPolygon = (wktPolygon as text) =>
        let
            Polylist = Geometry.FromWellKnownText(wktPolygon)[Rings]{0}[Points],
            Edges = List.Zip({List.RemoveLastN(Polylist,1),List.RemoveFirstN(Polylist,1)}),
            Crossed = List.Select(Edges,each 
                (_{0}[Y]>lat <> _{1}[Y]>lat) 
                  and 
                (lon < (_{0}[X]-_{1}[X]) * (lat-_{1}[Y]) / (_{0}[Y]-_{1}[Y]) + _{1}[X])
            ),
            Result = Number.IsOdd(List.Count(Crossed))
        in
            Result,
    
    // Check each polygon in the zone
    PolygonResults = Table.AddColumn(FilteredPolygons, "Contains", 
        each PointInPolygon([value])),
    
    // Get the first polygon that contains the point
    MatchingPolygon = Table.SelectRows(PolygonResults, each [Contains] = true),
    
    // Return result record
    Result = if Table.RowCount(MatchingPolygon) > 0 
        then [
            IsInPolygon = true,
            PolygonName = MatchingPolygon[PlaceName]{0},
            Zone = zona
        ]
        else [
            IsInPolygon = false,
            PolygonName = null,
            Zone = zona
        ]
in
    Result

Then in your main query:

= Table.AddColumn(Points, "PolygonResult", 
    each CheckPointInPolygons([LATITUD], [LONGITUD], Poligonos4, [zona]))

To expand the record into separate columns:

= Table.ExpandRecordColumn(#"Added Custom", "PolygonResult", 
    {"IsInPolygon", "PolygonName", "Zone"})

This approach:

1. Filters polygons to only the matching zone (optimization)
2. Checks each polygon in that zone
3. Returns the first match with polygon name and zone
4. Returns a record instead of just boolean, so you get all the info you need

This should be much faster since it only searches relevant polygons per zone.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[DanSanDST]

Thanks for your approach, it worked well and made me understand a bit more how to approach something like this in the future (was like a LOOP function).

Do you think it can be optimized more? since its creating a table for every row calculation, currently it takes 3mins and 35 secs (fairly good PC, R7 9700x and 32gb ram, pcie5 ssd, with my Laptop on the office i will suffer), and this is only using the data for 1 zone, wich has 20 polygons (each one with over 800 points) against 11900 rows that cointains Points (lat long).

I removed the Poligons filter to leave all the zones there(84 polygons) and it took close to the same (3:50) meaning the first filter by zone does its job. Now ill test with all the data (75340 rows), i would be updating this daily, currently im evaluating data from 2020-2025 so that number might got up to 100k at the end of the year. Luckly i only have to calculate it once per month at most.

Im tempted to test if it would calculate it faster as a calculated column on DAX, i will attempt to adapt it to DAX.