Determine if map coordinates are within area

[EvonS]

I have this information below that define areas on a map. Is there a way to determine if a longitude and latitude are within one of these areas?

OBJECTID	AGENCY	DIV	UNIT_NAME	ADDRESS	CITY	AREA_SQKM	Shape__Area	Shape__Length
1​	Toronto Police Service	D11	11 Division	2054 Davenport Rd	Toronto	18.67845803​	35692935.07​	28190.11106​
2​	Toronto Police Service	D12	12 Division	200 Trethewey Dr	Toronto	24.78034332​	47417573.68​	33029.13457​
3​	Toronto Police Service	D13	13 Division	1435 Eglinton Av W	Toronto	18.60191063​	35591722.93​	24616.57917​
4​	Toronto Police Service	D14	14 Division	350 Dovercourt Rd	Toronto	14.17525415​	27083979.14​	30017.70564​
5​	Toronto Police Service	D22	22 Division	3699 Bloor St W	Toronto	68.12188386​	130097609.1​	72765.03189​
6​	Toronto Police Service	D23	23 Division	5230 Finch Av W	Toronto	57.64228008​	110369052.3​	54270.05361​
7​	Toronto Police Service	D31	31 Division	40 Norfinch Dr	Toronto	42.66749447​	81789149.85​	44477.14656​
8​	Toronto Police Service	D32	32 Division	30 Ellerslie Av	Toronto	61.06048027​	117079222.9​	43658.49725​
9​	Toronto Police Service	D33	33 Division	50 Upjohn Rd	Toronto	52.7888815​	101244281.9​	45148.84874​
10​	Toronto Police Service	D41	41 Division	2222 Eglinton Av E	Toronto	44.57511239​	85395988.8​	58875.05415​
11​	Toronto Police Service	D42	42 Division	242 Milner Av E	Toronto	84.61174885​	162528804.9​	59824.09155​
12​	Toronto Police Service	D43	43 Division	4331 Lawrence Av E	Toronto	59.37000474​	113873893.7​	54424.50711​
13​	Toronto Police Service	D51	51 Division	51 Parliament St	Toronto	8.601682841​	16437802.1​	25324.5062​
14​	Toronto Police Service	D52	52 Division	255 Dundas St W	Toronto	9.228055772​	17625448.21​	35966.54447​
15​	Toronto Police Service	D53	53 Division	75 Eglinton Av W	Toronto	31.29485302​	59895251.81​	37395.46015​
16​	Toronto Police Service	D55	55 Division	101 Coxwell Av	Toronto	41.03398633​	78486159.8​	82307.80296​

 

[KRice]

My initial Google search was for "determine polygon that point lies in" (without quotes). Several of the more noteworthy results describe a ray-casting technique that counts the number of edges crossed for a given polygon. Also described is a winding number method. One of the search results linked to a stackoverflow site:

How can I determine whether a 2D Point is within a Polygon?

I'm trying to create a fast 2D point inside polygon algorithm, for use in hit-testing (e.g. Polygon.contains(p:Point)). Suggestions for effective techniques would be appreciated.

stackoverflow.com

...where there is a lot of discussion about solution strategies, and in some cases code.
On the 2nd page of the stackoverflow site was a post that included a link to the Geometry Algorithm page I mentioned earlier:

Geometry Algorithms TOC

Another link from that search led to a Wikipedia article:

Point in polygon - Wikipedia

en.wikipedia.org

...which provides a good overview of the problem, and it describes some approaches and interesting history. Who knew that "Ray Tracing News" was discussing this stuff in 1990?

Ray Tracing News, Volume 3, Number 4

Anyway, about the time I began to adapt some code for an Excel-based solution, you offered the link to your "Corner" on excelfox (in post #11). There discussion occurs between 2013 up to 2021, and in the last post from 2021 by DocAElstein (Alan), he offers links to several PointInPolygon excel files on Box. I downloaded this one:

PointInPolygon.xlsm | Powered by Box

app.box.com

Metadata for that file--which includes the PtInPoly code in UDF form--indicates that you were the author with a creation date of 2/28/2003!

 

[Rick Rothstein]

My initial Google search was for "determine polygon that point lies in" (without quotes). Several of the more noteworthy results describe a ray-casting technique that counts the number of edges crossed for a given polygon. Also described is a winding number method. One of the search results linked to a stackoverflow site:

How can I determine whether a 2D Point is within a Polygon?

I'm trying to create a fast 2D point inside polygon algorithm, for use in hit-testing (e.g. Polygon.contains(p:Point)). Suggestions for effective techniques would be appreciated.

stackoverflow.com

...where there is a lot of discussion about solution strategies, and in some cases code.
On the 2nd page of the stackoverflow site was a post that included a link to the Geometry Algorithm page I mentioned earlier:

Geometry Algorithms TOC

Another link from that search led to a Wikipedia article:

Point in polygon - Wikipedia

en.wikipedia.org

...which provides a good overview of the problem, and it describes some approaches and interesting history. Who knew that "Ray Tracing News" was discussing this stuff in 1990?

Ray Tracing News, Volume 3, Number 4

Anyway, about the time I began to adapt some code for an Excel-based solution, you offered the link to your "Corner" on excelfox (in post #11). There discussion occurs between 2013 up to 2021, and in the last post from 2021 by DocAElstein (Alan), he offers links to several PointInPolygon excel files on Box. I downloaded this one:

PointInPolygon.xlsm | Powered by Box

app.box.com

Metadata for that file--which includes the PtInPoly code in UDF form--indicates that you were the author with a creation date of 2/28/2003!

Thank you for the complete and extensive follow-up!

 

[EvonS]

Hey everyone, can this be accomplished without macros?

 

[KRice]

Yes...I believe you mentioned you were using Excel 365. So you'll want to use the LAMBDA version of Rick's code that @lrobbo314 described above.

 

[EvonS]

Yes...I believe you mentioned you were using Excel 365. So you'll want to use the LAMBA version of Rick's code that @lrobbo314 described above.

I am using 365 but unfortunately the users of the submission for aren't. They are having problems with it working. Since you made everything for me, I have no idea what to edit . You mind converting it for me please or pointing me in the right direction to make the edits myself?

 

[KRice]

Oh...that's going to be a problem then. There are a lot of steps being handled by the VBA code because each side of a potential polygon needs to be examined (i.e. we need to determine if a ray extending out from the input location in some pre-defined but arbitrary direction crosses each line segment comprising the division's boundary edges...and then all of those crossings are counted). This is readily done in VBA, and it can be done with a compact formula in Excel 365 because the LAMBDA function allows for making this type of examination repeatedly by looping through segment by segment. If both of those options are off the table, then you would need to perform this analysis using helper columns and additional formulas. Are you saying that both of these approaches are a problem? You can't use VBA code (macros) for some reason? And you need this to work for people who are not using Excel 365?

 

[EvonS]

Some of the orgs of the people using the form don't allow macros so the location feature doesn't work.

 

[KRice]

...and they are not using Excel 365? Is that correct? If so, do you know what version(s) are being used?

If they have 365 but don't allow macros, then the easy solution is to use the LAMBDA version of the PtInPoly routine. If they don't allow macros and they are not using Excel 365 (which gives us LAMBDA), then a much messier solution would be needed.

 

[EvonS]

Some use office 365 while others use older versions. I guess the LAMBDA formula would be ideal since it will be compatible most partners I work with.

 

[KRice]

Below is a worksheet using the conversion of Rick's code into a LAMBDA form that @lrobbo314 offered in post #24. Grab that formula by clicking on the clipboard icon in the upper right of the code posted. Then establish a function name using Excel's Name Manager (Formula > Name Manager, then click New, assign a Name, and paste the code into the "Refers to:" field)...it will resemble this:

Click Close to confirm, and then the LAMBDA function you just created will be available in the spreadsheet. You see in this example, I named the LAMBDA function "PtInPolyL"...the L at the end providing a clue (to me) that the function being called is actually a LAMBDA function. Other than that, the arguments are the same as in the original implementation. So a working version of the main processing worksheet looks like this:

TPS_Police2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1					Bounding Boxes
2		Street Address	City		Division	LongMin	LongMax	LatMin	LatMax		Potential Divisions	Point in Polygon?
3	User Inputs	1077 Leslie St	Toronto		D11	-79.51221491	-79.42864744	43.63170784	43.6751081		D33	In Polygon
4	Lat-Long of address	43.718490	-79.349320		D12	-79.53960143	-79.45596928	43.66264224	43.72469095		D53	Not In Polygon
5	Location in:	D33			D13	-79.4697805	-79.40733705	43.66726949	43.71926757		D55	Not In Polygon
6	Send to:	50 Upjohn Rd	Toronto		D14	-79.44622267	-79.39156201	43.62627154	43.67543759
7					D22	-79.60873798	-79.46913085	43.58100456	43.68413286
8					D23	-79.63926019	-79.51023633	43.66463091	43.76302651
9					D31	-79.58521503	-79.47253029	43.71282315	43.78390308
10					D32	-79.4844639	-79.38110686	43.71081297	43.80326709
11					D33	-79.39632203	-79.30264823	43.7162418	43.81565937
12					D41	-79.31979738	-79.21913498	43.67107241	43.7793425
13					D42	-79.34132113	-79.13391205	43.76837191	43.85546297
14					D43	-79.26476975	-79.11545465	43.70828674	43.80463003
15					D51	-79.3867913	-79.33767222	43.63321019	43.67533123
16					D52A	-79.40490262	-79.3493905	43.61196298	43.63534512
17					D52B	-79.40387398	-79.37420511	43.63701617	43.67023498
18					D53	-79.4298194	-79.33544282	43.66666587	43.73036965
19					D55	-79.36637166	-79.2800285	43.61274054	43.72629795
LocationDivisionL

Cell Formulas
Range	Formula
E3:E19	E3	=UNIQUE(Table_TPS_AllDivs[Division])
F3:F19	F3	=MIN(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivisionL!$E3),Table_TPS_AllDivs[#Headers]="Longitude"))
G3:G19	G3	=MAX(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivisionL!$E3),Table_TPS_AllDivs[#Headers]="Longitude"))
H3:H19	H3	=MIN(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivisionL!$E3),Table_TPS_AllDivs[#Headers]="Latitude"))
I3:I19	I3	=MAX(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivisionL!$E3),Table_TPS_AllDivs[#Headers]="Latitude"))
K3:K5	K3	=LET(potdivs,($B$4>=$H$3:$H$19)*($B$4<=$I$3:$I$19)*($C$4>=$F$3:$F$19)*($C$4<=$G$3:$G$19),FILTER(E3#,potdivs=1))
B4:C4	B4	=TEXTSPLIT(TEXTBEFORE(TEXTAFTER(WEBSERVICE("http://geocoder.ca/?locate=" & SUBSTITUTE(B3," ","+")&"%2c+"&C3&"%2c+Ontario&geoit=GeoCode+it!"),"""ICBM"" content="""),""""),", ")+0
B5	B5	=INDEX(K3#,MATCH("In Polygon",$L$3:$L$19,0))
B6:C6	B6	=FILTER(Table_TPS_Police_Divisions_Top[[ADDRESS]:[CITY]],Table_TPS_Police_Divisions_Top[DIV]=IF(LEFT(B5,3)="D52","D52",B5))
L3:L19	L3	=IF(K3<>"",PtInPolyL($C$4,$B$4,FILTER(Table_TPS_AllDivs[[Longitude]:[Latitude]],Table_TPS_AllDivs[Division]=LocationDivisionL!K3)),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
TPS_AllDivs!ExternalData_18	=TPS_AllDivs!$A$1:$D$24438	F3:I19

This version should work for those using Excel 365, regardless of whether they are allowed to run VBA code...so you may want to make this the default 365 version. I've replaced the earlier version of the full Excel file with one that includes both versions of the location-determining worksheet: one relying on the VBA code, the other relying on the LAMBDA function:

TPS_Police2.xlsm

Shared with Dropbox

www.dropbox.com