This is a bit of a repost, but I'm hoping someone can help.
Using Excel 2016.
We have about 3500 geo points, each defined by its latitude/longitude. Each geo point resides in one and only one of about 60 geo areas. Each geo area is defined by the latitude/longitude of its "corners." A geo area may have up to 10 "corners." Corners are typically shared by adjacent geo areas. So, how to determine in which geo area each geo point resides?
I appreciate your help.
Without sample bit hard to give you help.
But algorithm would be something like... if you draw polygon outline of geo area (i.e. straight line from one corner to next).
Then if you draw straight line to the boundary from point.
If it crosses odd times, it must be inside boundary. If it crosses even times then it must be out side boundary.
This is likely one of faster calculation model.
Alternately you can use angle summation technique, but this is one of the slowest method.
Have a read of article in link.
https://erich.realtimerendering.com/ptinpoly/
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
When I've encountered this problem on this forum in the past, it seems to have reduced down to solving a "point in polygon" problem for each point in each polygon. See this thread, for example: https://www.excelforum.com/excel-for...phic-area.html
I note that the QGIS program that I mention in the other thread is still an active project. If you are not required to program your own solution in Excel, it might be easiest (even if you have to buy a software license) to use a GIS type of program that already knows how to solve this type of problem out of the box.
If you must use Excel, I will suggest that you look over the information we gathered on the point-in-polygon problem and see how it will adapt to your spreadsheet.
How would you like to proceed?
Originally Posted by shg
I just looked close enough to realize you are the same user who posted this same question 4 years ago in the thread I linked to. Not sure if anything has changed in 4 years, but it looks like the same problem. Any changes we should know about from 4 years ago?
Here is a "point in polygon" function I posted quite a while ago. Since you have 60 geoareas, you will need to create 60 sets of coordinates for them and then run your test point against each geoarea in my function until you get a hit. The comment in message #9 might help you as well (it was for triangular areas, but can be adapted to generalized areas)...
https://www.excelfox.com/forum/showt...Polygon-Or-Not
Please update your forum profile.Using Excel 2016.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Administrative Note:
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/
(Note: this requirement is not optional. As you are relatively inexperienced here, I shall do it for you this time: https://www.excelfox.com/forum/showt...o-area?p=24149)
Yes, I'm the say user. I posed the problem on numerous website and haven't received any workable solutions. As I noted in my first post, this is a repost.
Ali, as I noted in the initial "new" post, this a type of a repost.
I have updated it.
Please let me supply some addition info.
I live in a densely populated county near Washington, DC. There are more than 3500 multi-unit buildings (aka "MUBs"; apartments and condominiums) in the county. The data we are using are from the county's tax database. It contains a plethora of data that are of no use my team. But the data do contain the latitude and longitude of each of the MUBs. Now, the county is divided up into 56 voter precincts. The object of the project is to determine in which precinct a MUB is located.
Thanks!
After 4 years, you have made no progress on the problem, am I understanding correctly?
When I look at the QGIS tutorial I linked to years ago, it seems to me like it should be easy enough to replicate those steps with your own set of polygon points and data points. I'm still inclined to think that gis programs already know how to solve this problem, so it will be easiest to turn to a gis app rather than programming your own solution from scratch in Excel. Have you decided you are not allowed to use a gis program for this analysis?
Every Excel based solution I see being proposed in rooted in solving the point in polygon problem, and a few solutions for that problem have been proposed. Have you been able to solve the point in polygon problem for one point in one polygon? Rick's code seems to show up a few times in these conversations, have you had any luck getting his code to work for one point in one polygon?
MrS, I will give Rick's solution a shot and get back to you.
Thanks for your help!
But you did NOT provide links to the other forums where you have asked the question - you are required to do this as per our cross-posting rules.Ali, as I noted in the initial "new" post, this a type of a repost.
I have asked you to update your forum profile - you have now got two versions of Excel there. Please amend this to just Excel 2016. Thanks.
SOLVED!!
I first delineated each Geo area by the latitude and longitude of its "corners." Most areas ended up with ~15 corners. For each area I created a spreadsheet. The top row in each spreadsheet lists the latitudes and longitudes of the respective area. Note that I grouped each area's latitude and then the area's longitudes. See the attached .jpg. The rows below the aforementioned latitudes and longitudes of the area contain the formulae that subtract the the geo point's latitude from the ~15 corners' latitudes. This is repeated to subtract the geo point's longitude from the ~15 corners' longitudes.
Next for each geo point's latitude I enter two "countif" formulae. The first is "countif(B$4:"$O$4:">0")". The second is "countif(B$4:"$O$4:"<0")". These two formulae are repeated for the geo point's longitude. The rationale is if a geo point is within a geo area, the four "countif" formulae will result in a value greater then "0." Finally, the results of the four "countif" formulae are multiplied. Any cell that contains the product and is greater then "0" is filled with green.
Glad you have something.
How robust does this need to be? Since I didn't recognize your algorithm as any of the usual "point in polygon" algorithms, I tested it against some random polygons, and got several false positives. I suspect that your algorithm works just fine for a subset of polygons, but is not applicable to all polygons. As long as you know that your polygons will always be like your test polygon here, then it's probably okay. If you can't be certain that all of the polygons you need to test will have the same features as your test polygons, you might want to consider a more robust "point in polygon" algorithm.
Again, if you are satisfied, I'm satisfied. To avoid coming back to this again in a few months or years, I recommend testing your point in polygon algorithm sufficiently to make sure it will work for all of the polygons you will need to test.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks