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​

 

[EvonS]

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:
View attachment 94781
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:

Dropbox

www.dropbox.com

Thank you so much. You're awesome.

 

[KRice]

Glad to help. I had a look at creating a version that would work for earlier versions of Excel. I wasn't sure about the preferred approach for accomplishing this. One idea follows the path used for the Excel 365 solution, which identifies divisions that might contain the point of interest based on the extremes of bounding boxes around each division. Ultimately, this would require some conditional statements either to perform the edge-crossing checks if the division were identified, or not to consider the division at all. Instead, I opted to ignore the bounding boxes (I left them in the worksheet, but they are not used) and perform the edge-crossing check on all line segments of all divisions because it is simple to apply and avoids the additional logic checks.

I've utilized the PQ M code from @lrobbo314 , as it more elegantly extracts data from the GeoJSON file. The initial query (ExtractGeoJSON) is created as a connection only type, and it contains all of the data needed...a table describing police division/address and subtables holding the polygon coordinates for each division. The ShapefileCoord query utilizes ExtractGeoJSON to create the long coordinates table. And the BoundingBox query determines the min and max latitude and longitude from the ShapefileCoord query and merges those results with the base table initially created by ExtractGeoJSON to create a combination police division/address/bounding box table.

Helper columns to the right of the ShapefileCoord data compute the slope and intercept of each line segment, and the "Crossings" column determines whether the ray cast out from the target location crosses the line segment.

Helper cells on the BoundingBox worksheet take the user-inputted address and apply the WEBSERVICE function to obtain geocoding data. Those data are further processed to extract the latitude and longitude in a few cells using text search/extraction functions, and then the final coordinates are made available to the main sheet (LocationDiv) that the user interacts with. Returning to the BoundingBox worksheet, which holds one of the tables generated by PQ, to the right of that table is a column holding a formula that determines whether, for each division, the number of ray edge crossings on the ShapefileCoord worksheet is odd or even (with odd indicating the target point is located in that division). Ultimately, it is this column ([BoundingBox]J2:J19) that is examined to determine the "Location in:" on the main worksheet.

I haven't tested this exhaustively, but it appears to generate the same results as the Excel365 version. The functions used in this worksheet should work for Excel 2013 and later, I believe.

Dropbox

www.dropbox.com

 

[EvonS]

Glad to help. I had a look at creating a version that would work for earlier versions of Excel. I wasn't sure about the preferred approach for accomplishing this. One idea follows the path used for the Excel 365 solution, which identifies divisions that might contain the point of interest based on the extremes of bounding boxes around each division. Ultimately, this would require some conditional statements either to perform the edge-crossing checks if the division were identified, or not to consider the division at all. Instead, I opted to ignore the bounding boxes (I left them in the worksheet, but they are not used) and perform the edge-crossing check on all line segments of all divisions because it is simple to apply and avoids the additional logic checks.

I've utilized the PQ M code from @lrobbo314 , as it more elegantly extracts data from the GeoJSON file. The initial query (ExtractGeoJSON) is created as a connection only type, and it contains all of the data needed...a table describing police division/address and subtables holding the polygon coordinates for each division. The ShapefileCoord query utilizes ExtractGeoJSON to create the long coordinates table. And the BoundingBox query determines the min and max latitude and longitude from the ShapefileCoord query and merges those results with the base table initially created by ExtractGeoJSON to create a combination police division/address/bounding box table.

Helper columns to the right of the ShapefileCoord data compute the slope and intercept of each line segment, and the "Crossings" column determines whether the ray cast out from the target location crosses the line segment.

Helper cells on the BoundingBox worksheet take the user-inputted address and apply the WEBSERVICE function to obtain geocoding data. Those data are further processed to extract the latitude and longitude in a few cells using text search/extraction functions, and then the final coordinates are made available to the main sheet (LocationDiv) that the user interacts with. Returning to the BoundingBox worksheet, which holds one of the tables generated by PQ, to the right of that table is a column holding a formula that determines whether, for each division, the number of ray edge crossings on the ShapefileCoord worksheet is odd or even (with odd indicating the target point is located in that division). Ultimately, it is this column ([BoundingBox]J2:J19) that is examined to determine the "Location in:" on the main worksheet.

I haven't tested this exhaustively, but it appears to generate the same results as the Excel365 version. The functions used in this worksheet should work for Excel 2013 and later, I believe.

Dropbox

www.dropbox.com

This works perfectly. Thanks. I really appreciate this.