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​

 

[Rick Rothstein]

I believe that comment pertained to pulling shapefile information from the website into Power Query for further processing. If that is the case, you would still need an algorithm to evaluate whether user-inputted latitude-coordinates fell within the geographical boundaries of any of the TPS Divisions, and if so, which one. Quick update: I've already done the former--I pulled the shapefiles into PQ (although I didn't use the scraping feature...I manually downloaded the shapefile archive and processed it in PQ). Processing involved extracting the polygon descriptions for each division (i.e., lat-long coordinates for vertices of each cyclic polygon). After appending all of them together, along with a separate column to identify which division the coordinates belong to, the worksheet is over 24,000 rows. I've used those shapefile coordinates to construct bounding boxes for each division so that, given input lat-long coordinates, we can quickly determine which few divisions might contain the point of interest, while ruling out all other divisions. I'm currently looking into some approaches for examining each of the remaining possibilities to assess whether the point of interest lies within its boundaries. I'll post back with a link to the file after I make some more progress on this last step.

Some time ago, I created a VBA function (can be used as a user defined function on a worksheet) that tells whether a point is inside or outside of a given polygon. Within the thread of replies, I responded to someone's question regarding multiple polygons. Here is a link to my old mini-blog article (in case you wanted to appropriate the algorithm)...
Test Whether A Point Is In A Polygon Or Not

 

[KRice]

Thanks, Rick. That's very helpful. I was looking into the approach you've described...the ray casting/crossing number method; and also considering a "winding number" approach (which I don't quite understand yet). But I really like the idea of incorporating the UDF you've described.

 

[EvonS]

Thanks, Rick. That's very helpful. I was looking into the approach you've described...the ray casting/crossing number method; and also considering a "winding number" approach (which I don't quite understand yet). But I really like the idea of incorporating the UDF you've described.

Now you all have completely lost me. I hope I'm able to implement whatever solution you all come up with.

 

[lrobbo314]

@KRice, I was thinking about ray casting as a way to determine whether a point is in a polygon. I think Rick's algorithm will be fine for the OP, but I started thinking about odd shaped polygons and how that would work. Think that would be a good way to do it.

Also, also....

I can't mess with it now, I'm at work and can't install.

But I found this Excel Add-In that looks very promising.

MapWinGIS

They have a GitHub repo here.

Should be able to use that without coming up with a bespoke ray casting algorithm.

 

[Rick Rothstein]

@KRice, I was thinking about ray casting as a way to determine whether a point is in a polygon. I think Rick's algorithm will be fine for the OP, but I started thinking about odd shaped polygons and how that would work. Think that would be a good way to do it.

Also, also....

I can't mess with it now, I'm at work and can't install.

But I found this Excel Add-In that looks very promising.

MapWinGIS

They have a GitHub repo here.

Should be able to use that without coming up with a bespoke ray casting algorithm.

I guarantee that my function will work no matter what the shape of the polygon is. It was extensively test back when I created it.

 

[KRice]

I think Rick's function is working great. Here's a snippet of the functional worksheet where the user inputs lat-long coordinates, or perhaps this would be where an address input by the user is converted to lat-long coordinates. For the example, I chose coordinates near the northwest corner of Div 13. A quick comparison of the input coordinates to each division's bounding box identifies potential divisions that might include the point of interest. Then Rick's function is called to interrogate each of those potential divisions. All of the polygon coordinates are located on the TPS_AllDivs worksheet and the appropriate subsets are extracted with the FILTER function.

TPS_Police.xlsm
	A	B	C	D	E	F	G	H
1		User Inputs
2	Longitude	-79.469
3	Latitude	43.7108
4	Location in	D13	1435 Eglinton Av W
5
6
7		Bounding Boxes
8	Division	LongMin	LongMax	LatMin	LatMax		Potential Divisions	Point in Polygon?
9	D11	-79.51221	-79.4286	43.63171	43.67511		D12	Not in Polygon
10	D12	-79.5396	-79.456	43.66264	43.72469		D13	In Polygon
11	D13	-79.46978	-79.4073	43.66727	43.71927
12	D14	-79.44622	-79.3916	43.62627	43.67544
13	D22	-79.60874	-79.4691	43.581	43.68413
14	D23	-79.63926	-79.5102	43.66463	43.76303
15	D31	-79.58522	-79.4725	43.71282	43.7839
16	D32	-79.48446	-79.3811	43.71081	43.80327
17	D33	-79.39632	-79.3026	43.71624	43.81566
18	D41	-79.3198	-79.2191	43.67107	43.77934
19	D42	-79.34132	-79.1339	43.76837	43.85546
20	D43	-79.26477	-79.1155	43.70829	43.80463
21	D51	-79.38679	-79.3377	43.63321	43.67533
22	D52A	-79.4049	-79.3494	43.61196	43.63535
23	D52B	-79.40387	-79.3742	43.63702	43.67023
24	D53	-79.42982	-79.3354	43.66667	43.73037
25	D55	-79.36637	-79.28	43.61274	43.7263
LocationDivision

Cell Formulas
Range	Formula
B4	B4	=INDEX(G9#,MATCH("In Polygon",$H$9:$H$25,0))
C4	C4	=INDEX(Table_TPS_Police_Divisions_Top[Column1.properties.ADDRESS],MATCH(IF(LEFT(B4,3)="D52","D52",B4),Table_TPS_Police_Divisions_Top[Column1.properties.DIV],0))
A9:A25	A9	=UNIQUE(Table_TPS_AllDivs[Division])
B9:B25	B9	=MIN(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivision!$A9),Table_TPS_AllDivs[#Headers]="Longitude"))
C9:C25	C9	=MAX(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivision!$A9),Table_TPS_AllDivs[#Headers]="Longitude"))
D9:D25	D9	=MIN(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivision!$A9),Table_TPS_AllDivs[#Headers]="Latitude"))
E9:E25	E9	=MAX(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivision!$A9),Table_TPS_AllDivs[#Headers]="Latitude"))
G9:G10	G9	=LET(potdivs,($B$3>=$D$9:$D$25)*($B$3<=$E$9:$E$25)*($B$2>=$B$9:$B$25)*($B$2<=$C$9:$C$25),FILTER(A9#,potdivs=1))
H9:H25	H9	=IF(G9<>"",IF(@PtInPoly(B$2,B$3,FILTER(Table_TPS_AllDivs[[Longitude]:[Latitude]],Table_TPS_AllDivs[Division]=LocationDivision!G9)),"In Polygon","Not in Polygon"),"")
Dynamic array formulas.

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

The shapefile coordinates on the TPS_AllDivs worksheet look like this...only the first few rows shown:

TPS_Police.xlsm
	A	B	C	D
1	Division	Longitude	Latitude	Index
2	D11	-79.44912907	43.66753873	1
3	D11	-79.44901864	43.66726949	2
4	D11	-79.44890567	43.66728209	3
5	D11	-79.44879559	43.66729105	4
6	D11	-79.44868591	43.6673045	5
7	D11	-79.44857583	43.66731345	6
TPS_AllDivs

Code for the UDF, compliments of @Rick Rothstein from the link he posted above:

Option Explicit

Public Function PtInPoly(Xcoord As Double, Ycoord As Double, Polygon As Variant) As Variant
  Dim x As Long, m As Double, b As Double, Poly As Variant
  Dim LB1 As Long, LB2 As Long, UB1 As Long, UB2 As Long, NumSidesCrossed As Long
  Poly = Polygon
  If Not (Poly(LBound(Poly), 1) = Poly(UBound(Poly), 1) And _
        Poly(LBound(Poly), 2) = Poly(UBound(Poly), 2)) Then
    If TypeOf Application.Caller Is Range Then
      PtInPoly = "#UnclosedPolygon!"
    Else
      Err.Raise 998, , "Polygon Does Not Close!"
    End If
    Exit Function
  ElseIf UBound(Poly, 2) - LBound(Poly, 2) <> 1 Then
    If TypeOf Application.Caller Is Range Then
      PtInPoly = "#WrongNumberOfCoordinates!"
    Else
      Err.Raise 999, , "Array Has Wrong Number Of Coordinates!"
    End If
    Exit Function
  End If
  For x = LBound(Poly) To UBound(Poly) - 1
    If Poly(x, 1) > Xcoord Xor Poly(x + 1, 1) > Xcoord Then
      m = (Poly(x + 1, 2) - Poly(x, 2)) / (Poly(x + 1, 1) - Poly(x, 1))
      b = (Poly(x, 2) * Poly(x + 1, 1) - Poly(x, 1) * Poly(x + 1, 2)) / (Poly(x + 1, 1) - Poly(x, 1))
      If m * Xcoord + b > Ycoord Then NumSidesCrossed = NumSidesCrossed + 1
    End If
  Next
  PtInPoly = CBool(NumSidesCrossed Mod 2)
End Function

The full file I used is here:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Eventually, more complete addresses or other details will probably be necessary in the final step where a division's address is pulled over from the top-level division lookup table (similar to the first table posted by the OP).

P.S. One more note: I noticed that Div 52 seems to have two separate polygon descriptions. I think that division may consist of two different land masses...one connected to the mainland, the other an island?...I don't know. In any case, I arbitrarily denoted these as D52A and D52B, so when the final lookups are done, I needed to map both of these back to simply "D52".

 

[lrobbo314]

Here is a way to get Latitude and Longitude from the address info.

I inserted blank columns for Latitude and Longitude before running the code.

Function RX(s() As String)
Dim Res() As Double:    ReDim Res(1 To UBound(s), 1 To 2)
Dim MX As Object

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "<strong>(\d+\.\d+),\s(\-?\d+\.\d+)<\/strong>"
    For i = 1 To UBound(s)
        Set MX = .Execute(s(i))(0).subMatches
        Res(i, 1) = MX(0)
        Res(i, 2) = MX(1)
    Next i
End With

RX = Res
End Function

Sub Address2LatLong()
Dim r As Range:         Set r = Range("E2:F" & Range("E" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim URLS() As String:   ReDim URLS(1 To UBound(AR))
Dim http As Object:     Set http = CreateObject("MSXML2.XMLHTTP")
Dim Result() As Double

For i = 1 To UBound(AR)
    URLS(i) = "http://geocoder.ca/?locate=" & Replace(AR(i, 1), " ", "+") & "%2c+" & AR(i, 2) & "%2c+Ontario&geoit=GeoCode+it!"
Next i

With http
    For j = 1 To UBound(URLS)
        .Open "GET", URLS(j), False
        .Send
        URLS(j) = .ResponseText
    Next j
End With

Result = RX(URLS)
r.Offset(, 2).Value = Result

End Sub

GetCoords.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	OBJECTID	AGENCY	DIV	UNIT_NAME	ADDRESS	CITY	Latitude	Longitude	AREA_SQKM	Shape__Area	Shape__Length
2	1	Toronto Police Service	D11	11 Division	2054 Davenport Rd	Toronto	43.671071	-79.460825	18.67845803	35692935.07	28190.11106
3	2	Toronto Police Service	D12	12 Division	200 Trethewey Dr	Toronto	43.69457914	-79.48687581	24.78034332	47417573.68	33029.13457
4	3	Toronto Police Service	D13	13 Division	1435 Eglinton Av W	Toronto	43.69832725	-79.43668302	18.60191063	35591722.93	24616.57917
5	4	Toronto Police Service	D14	14 Division	350 Dovercourt Rd	Toronto	43.651297	-79.425978	14.17525415	27083979.14	30017.70564
6	5	Toronto Police Service	D22	22 Division	3699 Bloor St W	Toronto	43.64271515	-79.5301802	68.12188386	130097609.1	72765.03189
7	6	Toronto Police Service	D23	23 Division	5230 Finch Av W	Toronto	43.743866	-79.583522	57.64228008	110369052.3	54270.05361
8	7	Toronto Police Service	D31	31 Division	40 Norfinch Dr	Toronto	43.75675	-79.527474	42.66749447	81789149.85	44477.14656
9	8	Toronto Police Service	D32	32 Division	30 Ellerslie Av	Toronto	43.7717196	-79.41508393	61.06048027	117079222.9	43658.49725
10	9	Toronto Police Service	D33	33 Division	50 Upjohn Rd	Toronto	43.751082	-79.350069	52.7888815	101244281.9	45148.84874
11	10	Toronto Police Service	D41	41 Division	2222 Eglinton Av E	Toronto	43.730805	-79.27711	44.57511239	85395988.8	58875.05415
12	11	Toronto Police Service	D42	42 Division	242 Milner Av E	Toronto	43.7893	-79.23993	84.61174885	162528804.9	59824.09155
13	12	Toronto Police Service	D43	43 Division	4331 Lawrence Av E	Toronto	43.77082674	-79.17405342	59.37000474	113873893.7	54424.50711
14	13	Toronto Police Service	D51	51 Division	51 Parliament St	Toronto	43.651951	-79.362142	8.601682841	16437802.1	25324.5062
15	14	Toronto Police Service	D52	52 Division	255 Dundas St W	Toronto	43.654209	-79.389718	9.228055772	17625448.21	35966.54447
16	15	Toronto Police Service	D53	53 Division	75 Eglinton Av W	Toronto	43.70606	-79.400662	31.29485302	59895251.81	37395.46015
17	16	Toronto Police Service	D55	55 Division	101 Coxwell Av	Toronto	43.66918	-79.317299	41.03398633	78486159.8	82307.80296
Sheet1

 

[KRice]

@lrobbo314, that's great. I was just looking into an alternative approach using Google's Geocode API and managed to get it working with the WEBSERVICE function and then extracting the lat and long from the result. Obviously, the output from WEBSERVICE should be hidden (see the long output on row 5). To use this approach, the user needs to subscribe to Googles API service and obtain an API key from them (I've overwritten mine in the formula below, but indicated where it goes). Lookups are free up to so many thousands per month, I believe...but the terms and conditions should be reviewed carefully because there may be financial implications of using, or heavily using, the service.

For a quick tutorial on how to get started with this...

Convert address to GPS coordinates with Excel

You can convert your addresses to GPS coordinates or the other way round to convert GPS coordinates to addresses.

www.excel-exercise.com

And then several other videos from Frédéric Le Guen provide help with additional details.

TPS_Police.xlsm
	N	O	P
1		from Google Geocode API
2		known coords of address…	"lat": 43.6983134
3		Try to find them with API	"lng": -79.4366764
4	input address -->	1435 Eglinton Av W, Toronto
5		<?xml version="1.0" encoding="UTF-8"?> <GeocodeResponse> <status>OK</status> <result> <type>premise</type> <formatted_address>1435 Eglinton Ave W, York, ON M6C 3Z4, Canada</formatted_address> <address_component> <long_name>1435</long_name> <short_name>1435</short_name> <type>street_number</type> </address_component> <address_component> <long_name>Eglinton Avenue West</long_name> <short_name>Eglinton Ave W</short_name> <type>route</type> </address_component> <address_component> <long_name>York</long_name> <short_name>York</short_name> <type>political</type> <type>sublocality</type> <type>sublocality_level_1</type> </address_component> <address_component> <long_name>Toronto</long_name> <short_name>Toronto</short_name> <type>locality</type> <type>political</type> </address_component> <address_component> <long_name>Toronto</long_name> <short_name>Toronto</short_name> <type>administrative_area_level_3</type> <type>political</type> </address_component> <address_component> <long_name>Toronto</long_name> <short_name>Toronto</short_name> <type>administrative_area_level_2</type> <type>political</type> </address_component> <address_component> <long_name>Ontario</long_name> <short_name>ON</short_name> <type>administrative_area_level_1</type> <type>political</type> </address_component> <address_component> <long_name>Canada</long_name> <short_name>CA</short_name> <type>country</type> <type>political</type> </address_component> <address_component> <long_name>M6C 3Z4</long_name> <short_name>M6C 3Z4</short_name> <type>postal_code</type> </address_component> <geometry> <location> <lat>43.6983134</lat> <lng>-79.4366764</lng> </location> <location_type>ROOFTOP</location_type> <viewport> <southwest> <lat>43.6969645</lat> <lng>-79.4380371</lng> </southwest> <northeast> <lat>43.6996624</lat> <lng>-79.4353391</lng> </northeast> </viewport> <bounds> <southwest> <lat>43.6981387</lat> <lng>-79.4369419</lng> </southwest> <northeast> <lat>43.6984559</lat> <lng>-79.4364343</lng> </northeast> </bounds> </geometry> <partial_match>true</partial_match> <place_id>ChIJywHLYZczK4gR1fe4wMYoidM</place_id> </result> </GeocodeResponse>
6	output -->	lat	43.6983134
7		long	-79.4366764
LocationDivision

Cell Formulas
Range	Formula
O5	O5	=WEBSERVICE("https://maps.googleapis.com/maps/api/geocode/xml?address="&O4&"&key=YOUR API KEY HERE")
P6	P6	=FILTERXML(O5,"//location/lat")
P7	P7	=FILTERXML(O5,"//location/lng")

Still, I prefer the code approach offered by @lrobbo314 for doing this.

 

[lrobbo314]

That's cool, @KRice. I remember when you could use the google maps API without a product key. Those were the days.

Totally forget about the WEBSERVICE function too.

Here it is using that method.

This is using Excel 365 functions... Not sure what version the OP has.

GetCoords.xlsm
	B	C	D	E	F	G	H	I	J	K
1	AGENCY	DIV	UNIT_NAME	ADDRESS	CITY	Latitude	Longitude	AREA_SQKM	Shape__Area	Shape__Length
2	Toronto Police Service	D11	11 Division	2054 Davenport Rd	Toronto	43.671071	-79.460825	18.67845803	35692935.07	28190.11106
3	Toronto Police Service	D12	12 Division	200 Trethewey Dr	Toronto	43.69457914	-79.48687581	24.78034332	47417573.68	33029.13457
4	Toronto Police Service	D13	13 Division	1435 Eglinton Av W	Toronto	43.69832725	-79.43668302	18.60191063	35591722.93	24616.57917
5	Toronto Police Service	D14	14 Division	350 Dovercourt Rd	Toronto	43.651297	-79.425978	14.17525415	27083979.14	30017.70564
6	Toronto Police Service	D22	22 Division	3699 Bloor St W	Toronto	43.64271515	-79.5301802	68.12188386	130097609.1	72765.03189
7	Toronto Police Service	D23	23 Division	5230 Finch Av W	Toronto	43.743866	-79.583522	57.64228008	110369052.3	54270.05361
8	Toronto Police Service	D31	31 Division	40 Norfinch Dr	Toronto	43.75675	-79.527474	42.66749447	81789149.85	44477.14656
9	Toronto Police Service	D32	32 Division	30 Ellerslie Av	Toronto	43.7717196	-79.41508393	61.06048027	117079222.9	43658.49725
10	Toronto Police Service	D33	33 Division	50 Upjohn Rd	Toronto	43.751082	-79.350069	52.7888815	101244281.9	45148.84874
11	Toronto Police Service	D41	41 Division	2222 Eglinton Av E	Toronto	43.730805	-79.27711	44.57511239	85395988.8	58875.05415
12	Toronto Police Service	D42	42 Division	242 Milner Av E	Toronto	43.7893	-79.23993	84.61174885	162528804.9	59824.09155
13	Toronto Police Service	D43	43 Division	4331 Lawrence Av E	Toronto	43.77082674	-79.17405342	59.37000474	113873893.7	54424.50711
14	Toronto Police Service	D51	51 Division	51 Parliament St	Toronto	43.651951	-79.362142	8.601682841	16437802.1	25324.5062
15	Toronto Police Service	D52	52 Division	255 Dundas St W	Toronto	43.654209	-79.389718	9.228055772	17625448.21	35966.54447
16	Toronto Police Service	D53	53 Division	75 Eglinton Av W	Toronto	43.70606	-79.400662	31.29485302	59895251.81	37395.46015
17	Toronto Police Service	D55	55 Division	101 Coxwell Av	Toronto	43.66918	-79.317299	41.03398633	78486159.8	82307.80296
Sheet1

Cell Formulas
Range	Formula
G2:H17	G2	=TEXTSPLIT(TEXTBEFORE(TEXTAFTER(WEBSERVICE("http://geocoder.ca/?locate=" & SUBSTITUTE(E2," ","+")&"%2c+"&F2&"%2c+Ontario&geoit=GeoCode+it!"),"""ICBM"" content="""),""""),", ")+0
Dynamic array formulas.

 

[KRice]

Those were the days, indeed! This last offering is really nice, @lrobbo314. I think that's even more convenient. The OP mentioned Excel 365 is being used, so there shouldn't be any impediments unless there are institutional restrictions on using VBA. Imagining that a form might involve entering just one address, a revised version of the file is available here:

TPS_Police2.xlsm

Shared with Dropbox

www.dropbox.com

In it, the user enters an address. Then your implementation of the WEBSERVICE function contacts a geocoding site to obtain the latitude-longitude coordinates. Those coordinates are compared to bounding boxes (upper and lower limits of latitude and longitude for a simple rectangle that surrounds each division's complex shape) to identify a reduced set of divisions that need to be be investigated further because the point of interest might lie within their geographical boundaries. Rick's Point-in-Polygon VBA offering then utilizes an edge-crossing algorithm to identify which division actually contains the point. Finally, the target division's address details are returned by formula. For the example, I identified an address near the southern tip of (but just inside) division 33's boundaries. That location lies near where 3 divisions intersect, and their shapes are such that all three divisions would need to be examined. In my testing so far, I haven't encountered any issues and the final results appear to be correct.

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
LocationDivision

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]=LocationDivision!$E3),Table_TPS_AllDivs[#Headers]="Longitude"))
G3:G19	G3	=MAX(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivision!$E3),Table_TPS_AllDivs[#Headers]="Longitude"))
H3:H19	H3	=MIN(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivision!$E3),Table_TPS_AllDivs[#Headers]="Latitude"))
I3:I19	I3	=MAX(FILTER(FILTER(Table_TPS_AllDivs,Table_TPS_AllDivs[Division]=LocationDivision!$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<>"",IF(@PtInPoly(C$4,B$4,FILTER(Table_TPS_AllDivs[[Longitude]:[Latitude]],Table_TPS_AllDivs[Division]=LocationDivision!K3)),"In Polygon","Not in Polygon"),"")
Dynamic array formulas.

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

These steps are performed on (or coordinated from) this worksheet. The other worksheets in the workbook hold the shapefile coordinates and other information about the divisions. For reference, the shapefile coordinates were extracted using Power Query from the TPS_Police_Divisions.geojson file that was downloaded from the site I mentioned in an earlier post. One caveat about the large table of shapefile coordinates: it was created with Power Query (in fact vertices for all 17 constituent division shapes were extracted using PQ) and the M code is still present. That code references a source file on my computer (the geojson file) and my file path. If you attempt to run the code (or Refresh All), errors will be obtained. Perhaps it would be better to eliminate the code, but keep the data tables static.