INPOLY

[lrobbo314]

INPOLY will return if a given latitude and longitude falls within a polygon. The polygon is a 2 column range/array in order of latitude then longitude.

This function was a conversion of a VBA algorithm written by @Rick Rothstein in this post. The example came from this thread.

In the example we used Power Query to get the points of the polygon. But, the polygon could just be a range of latitude and longitude coordinates on any sheet. The user will provide latitude and longitude coordinates of a given location and the function will return whether the location falls within the given polygon.

=LAMBDA(LATITUDE,LONGITUDE,POLYGON,
    LET(r,ROWS(POLYGON),
        IF(NOT(AND(INDEX(POLYGON,1,1)=INDEX(POLYGON,r,1),INDEX(POLYGON,1,2)=INDEX(POLYGON,r,2))),
            "Invalid Polygon",
            IF(MOD(SUM(
                SCAN(0,SEQUENCE(r-1),
                    LAMBDA(s,c,
                        IF(XOR(INDEX(POLYGON,c,2)>LONGITUDE,INDEX(POLYGON,c+1,2)>LONGITUDE),
                            LET(
                                m,(INDEX(POLYGON,c+1,1)-INDEX(POLYGON,c,1))/(INDEX(POLYGON,c+1,2)-INDEX(POLYGON,c,2)),
                                b,(INDEX(POLYGON,c,1)*INDEX(POLYGON,c+1,2)-INDEX(POLYGON,c,2)*INDEX(POLYGON,c+1,1))/(INDEX(POLYGON,c+1,2)-INDEX(POLYGON,c,2)),
                                IF(m*LONGITUDE+b>LATITUDE,s+1,s)
                            ),0
                        )
                    )
                )
            ),2),"In Polygon","Not In Polygon"
            )
        )
    )
)

InPoly Final.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Street Address	City		Bounding Boxes
2	User Inputs	45 Esandar Dr	Toronto		Division	LongMin	LongMax	LatMin	LatMax	In Polygon
3	Lat-Long of address	43.706916	-79.356874		D11	-79.51221491	-79.42864744	43.63170784	43.6751081	Not In Polygon
4	Location in:	D53			D12	-79.53960143	-79.45596928	43.66264224	43.72469095	Not In Polygon
5	Send to:	75 Eglinton Av W	Toronto		D13	-79.4697805	-79.40733705	43.66726949	43.71926757	Not In Polygon
6					D14	-79.44622267	-79.39156201	43.62627154	43.67543759	Not In Polygon
7					D22	-79.60873798	-79.46913085	43.58100456	43.68413286	Not In Polygon
8					D23-A	-79.63926019	-79.51023633	43.66463091	43.76302651	Not In Polygon
9					D23-B	-79.56540241	-79.56536835	43.73735984	43.73742627	Not In Polygon
10					D31	-79.58521503	-79.47253029	43.71282315	43.78390308	Not In Polygon
11					D32	-79.4844639	-79.38110686	43.71081297	43.80326709	Not In Polygon
12					D33	-79.39632203	-79.30264823	43.7162418	43.81565937	Not In Polygon
13					D41	-79.31979738	-79.21913498	43.67107241	43.7793425	Not In Polygon
14					D42	-79.34132113	-79.13391205	43.76837191	43.85546297	Not In Polygon
15					D43	-79.26476975	-79.11545465	43.70828674	43.80463003	Not In Polygon
16					D51	-79.3867913	-79.33767222	43.63321019	43.67533123	Not In Polygon
17					D52-A	-79.40490262	-79.3493905	43.61196298	43.63534512	Not In Polygon
18					D52-B	-79.40387398	-79.37420511	43.63701617	43.67023498	Not In Polygon
19					D53	-79.4298194	-79.33544282	43.66666587	43.73036965	In Polygon
20					D55	-79.36637166	-79.2800285	43.61274054	43.72629795	Not In Polygon
User_Input

Cell Formulas
Range	Formula
E3:E20	E3	=UNIQUE(Table_tbl_Main[DIVX])
F3:F20	F3	=MINIFS(Table_tbl_Coords[Longitude],Table_tbl_Coords[DIVX],User_Input!E3#)
G3:G20	G3	=MAXIFS(Table_tbl_Coords[Longitude],Table_tbl_Coords[DIVX],User_Input!E3#)
H3:H20	H3	=MINIFS(Table_tbl_Coords[Latitude],Table_tbl_Coords[DIVX],User_Input!E3#)
I3:I20	I3	=MAXIFS(Table_tbl_Coords[Latitude],Table_tbl_Coords[DIVX],User_Input!E3#)
B3:C3	B3	=TEXTSPLIT(TEXTBEFORE(TEXTAFTER(WEBSERVICE("http://geocoder.ca/?locate=" & SUBSTITUTE(B2," ","+")&"%2c+"&C2&"%2c+Ontario&geoit=GeoCode+it!"),"""ICBM"" content="""),""""),", ")+0
B4	B4	=INDEX(E3#,MATCH("In Polygon",$J$3:$J$20,0))
B5:C5	B5	=FILTER(Table_tbl_Main[[ADDRESS]:[CITY]],Table_tbl_Main[DIVX]=User_Input!B4)
J3:J20	J3	=INPOLY($B$3,$C$3,FILTER(Table_tbl_Coords[[Longitude]:[Latitude]],Table_tbl_Coords[DIVX]=User_Input!E3))
Dynamic array formulas.

 

[lrobbo314]

Not sure why I used the combination of SUM + SCAN instead of just using REDUCE...

=LAMBDA(XC,YC,P,
        IF(MOD(REDUCE(0,SEQUENCE(ROWS(P)-1),
            LAMBDA(s,c,
                IF(XOR(INDEX(P,c,1)>XC,INDEX(P,c+1,1)>XC),
                    LET(
                        m,(INDEX(P,c+1,2)-INDEX(P,c,2))/(INDEX(P,c+1,1)-INDEX(P,c,1)),
                        b,(INDEX(P,c,2)*INDEX(P,c+1,1)-INDEX(P,c,1)*INDEX(P,c+1,2))/(INDEX(P,c+1,1)-INDEX(P,c,1)),
                        IF(m*XC+b>YC,s+1,s+0)
                    ),s
                )
            )),2),"In Polygon","Not in Polygon"
        ))