lrobbo314
Well-known Member
- Joined
- Jul 14, 2008
- Messages
- 3,929
- Office Version
- 365
- Platform
- Windows
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.
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.
Excel Formula:
=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. | ||
Upvote
0
