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]

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.