Google Earth: determine which points are inside a polygon using coordinates

[chrism216]

Hi everyone,

This is not really a question, but rather something I had needed for some time, did some research and for which I eventually came up with an excel-based solution.

In work I came across the problem of having to calculate work loads for several work groups, based on the geographical location of the places they needed to go. For that, I needed to know which points were inside which polygons.

I made a workbook which can test whether a point(s) is inside a polygon(s). This solution does not need any online "KML" generators or file interpreters. All you need is Excel and your KML file with the polygons.

Thanks to Rick Rothstein and user sijpie, who got me on the right track with this thread:
http://www.mrexcel.com/forum/excel-...lygon-visual-basic-applications-function.html

INSTRUCTIONS:
1. Download the workbook here: https://www.dropbox.com/s/707qslttz428v6w/PointInPolygon.xlsm?dl=0
2. In Google Earth, draw all the polygons you want to test. Put them all in the same folder.
3. Right-click the folder and click "Save As". Save it in the same directory as the Excel file, with the name "Polygons". Choose the format .kml (very important!)
4. Put all the coordinates you want to test on the columns B and C on the first and only sheet in the workbook. Coordinates must be in decimal format, like this: 40.689397 and -74.045036. You can add a Name on column A, though this is optional.
5. Click Analyze.

The workbook will output a matrix filled with True/False. Each column represents one of the polygons you drew on Google Earth, and for each row, you will know in which polygon(s) a point is.

Hopefully this helps someone out there. For any questions, I'll be glad to help.

Cheers,

Chris

 

[HPerry1137]

Hi, I am hoping this thread is still getting replies? I have downloaded the PointInPolygon.xls file. This tool will be amazing if I can get to work! I did have to clean up some of the polygon names to ensure that they fit within the rules of a tab name. When I get points & get polygons, all works wonderfully. But when I hit 'run analysis' I get the error message: "Run-time error '91': Object variable or With block variable not set.
The Debug then highlights this line (attached as image as well): coords = Split(polygonNode.SelectSingleNode(".//ns:coordinates").Text)

Any suggestions on what I need to fix? There is about 2000 points and 100 polygons.

 

[Rick Rothstein]

Hi, I am hoping this thread is still getting replies? I have downloaded the PointInPolygon.xls file. This tool will be amazing if I can get to work! I did have to clean up some of the polygon names to ensure that they fit within the rules of a tab name. When I get points & get polygons, all works wonderfully. But when I hit 'run analysis' I get the error message: "Run-time error '91': Object variable or With block variable not set.
The Debug then highlights this line (attached as image as well): coords = Split(polygonNode.SelectSingleNode(".//ns:coordinates").Text)

Any suggestions on what I need to fix? There is about 2000 points and 100 polygons.

Unless the width you chose for the Module window is hiding it (can't tell from your picture), you are missing a closing parenthesis at the end of that code line.

 

[HPerry1137]

Apologies it was cut off.