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​

 

[lrobbo314]

Well... No. Not with the data you posted. Is this coming out of ARCGIS? You asked if you can determine if the lat long falls within an area.

There are no lat long coordinates there. Using an API or something you could get the lat long from the address info.

But, even if you did that. What area are we talking about? Is there a shape file? It gives the Shape_Area and Shape_Length, but without the polygon information, those numbers are meaningless.

And I don't know how you would read a shape file with VBA.

Seems like you're re-inventing the wheel here.

Think you would be better off using ARCGIS. Or there are other free things like leaflet or mapbox. But, you would be getting into coding with Javascript or Python to use those.

 

[EvonS]

Well... No. Not with the data you posted. Is this coming out of ARCGIS? You asked if you can determine if the lat long falls within an area.

There are no lat long coordinates there. Using an API or something you could get the lat long from the address info.

But, even if you did that. What area are we talking about? Is there a shape file? It gives the Shape_Area and Shape_Length, but without the polygon information, those numbers are meaningless.

And I don't know how you would read a shape file with VBA.

Seems like you're re-inventing the wheel here.

Think you would be better off using ARCGIS. Or there are other free things like leaflet or mapbox. But, you would be getting into coding with Javascript or Python to use those.

Thanks for replying. Wanted users of my excel submission form to know who to submit the form to based on intersection data. But it sounds like it's not possible.

 

[KRice]

I agree with the points made by @lrobbo314...this would require developing a solution that is already handled well by existing GIS systems.

For example, you can go to publicly available sources and see the police division boundaries and even download the shapefiles mentioned (the description of polygons for each division boundary). I believe I started by looking at:

TPS Police Divisions

This Feature Service represents TPS Police Divisions and is accurate as of Jan 1st 2022 post 55/54 division merge. This dataset is based on the City of Toronto road centreline.

data.torontopolice.on.ca

...and by poking around, I discovered the shapefiles in various formats are available from the download button on the left side of that page.
But there is already an arcGIS-based viewer available online (found through links from that same website)...I eventually ended up here:

https://www.arcgis.com/home/webmap/viewer.html?useExisting=1&layers=fda21b25213c4c07b08c5162cba5081f

This allows a user to insert an address or even latitude-longitude coordinates in the Search bar to see where the location falls on the map. In this example, I entered lat-long coordinates of 43.7, -79.3 and discovered that it lies within the jurisdiction of D55.
Depending on your needs, you could use this web-based system, consider a similar GIS-based approach, possibly access a GIS system from Excel through an API, or download the shapefiles and create an algorithm to determine which polygon holds a given set of lat-long coordinates.

 

[EvonS]

Thanks. I am already using a submission form that collects the address so I was hoping to integrate this feature into the form itself. But it sounds like the easiest thing to do is to direct people to the website. Thanks for the help.

 

[pjmorris]

As a thought, Powrquery can access websites and scrape data from them so in theory you could get the data into Excel, but I imagine its quite involved. Others may be able to comment more usefully.

 

[EvonS]

As a thought, Powrquery can access websites and scrape data from them so in theory you could get the data into Excel, but I imagine its quite involved. Others may be able to comment more usefully.

Can you be specific about what I would google re: power query to accomplish what you're suggesting?

 

[KRice]

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.

One critical question: what version of Excel are you using? Please update your account details to show this. I mention it because I've used convenient functions that are available in Excel 365 but not earlier versions.

 

[EvonS]

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.

One critical question: what version of Excel are you using? Please update your account details to show this. I mention it because I've used convenient functions that are available in Excel 365 but not earlier versions.

Wow thanks for this support. I am using Office 365. I should mention that the user will be inputting streets for the intersection. I also have to figure out how to convert that to Long and Lat. Do you want a copy of the form I'm using?

 

[KRice]

Thanks for the info. Inputting streets or addresses complicates matters, and you'll definitely need some way to perform the conversion from address to lat-long coordinates, probably through a custom function that relies on an API for a geolocation service. For now, I don't need to see the form, as I'm just trying to reliably identify a division number given two input cells (one for lat, the other for long).