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

 

[chrism216]

Hi everyone,

2. In Google Earth, draw all the polygons you want to test. Put them all in the same folder.

To name the polygons, be sure to use only characters which are allowed to name Excel Sheets with. Otherwise the program will give you an error. This is because I name a set of hidden sheets with the names of the polygons. For the same reason, don't use the same name for two polygons.

 

[JustinduToit]

Hi Chris,

If I get it to work your file will save my life! Or at least make it easier...

I've followed the instructions carefully, but get this message "Run time error 1004...This XML file could not be opened because of an error... " When I click to debug it, it highlights "Application.Workbooks.OpenXML (path & "\" & xml)" in the VBA editor.

Appreciated if you can help.

Thanks,

Justin.

 

[chrism216]

Hi Chris,

If I get it to work your file will save my life! Or at least make it easier...

I've followed the instructions carefully, but get this message "Run time error 1004...This XML file could not be opened because of an error... " When I click to debug it, it highlights "Application.Workbooks.OpenXML (path & "\" & xml)" in the VBA editor.

Appreciated if you can help.

Thanks,

Justin.

Hi Justin, glad to help

1. Make sure the downloaded spreadsheet is in the same directory as the KML file containing the polygons.
2. Make sure the KML contains nothing other than the polygons. No points or anything. Just polygons.
3. Make sure the file you get from google earth is in .KML format. Google earth sets it as .KMZ by default, so you will have to change that manually on the "save as" window.

If you did all of this correctly and its still raising an error, try the following:
Go to the directory where you saved the .KML file. Make a copy of it and change the extension from .KML to .xml.
Try to open it with excel. Does it raise an error?

If it does open, it should ask you to choose one of three ways to open the file. But I guess your excel can't open the .xml for some reason so thats raising the error. What excel version are you using?

Cheers,

Chris

 

[JustinduToit]

Hi Chris,

Following your numbering:

1. Definitely in the same directory.
2. I've made sure - as far as I can - that it's only the polygon data. Imported it into excel and re-created a kml (using GEPath) to hopefully knock off any parasitic information.
3. Definitely .kml

But can't get it to work.

Might I ask you to have a look at the files? Might be quicker than me muddling my way through it.

https://www.dropbox.com/sh/riofl7g6axo0mnq/AABiYcx7ZJfwdXfx6o-PEQ7Va?dl=0

Polygon self explanatory, excel file the points to be filtered.

Thanks so much,

Justin.

 

[chrism216]

Hi Chris,

Following your numbering:

1. Definitely in the same directory.
2. I've made sure - as far as I can - that it's only the polygon data. Imported it into excel and re-created a kml (using GEPath) to hopefully knock off any parasitic information.
3. Definitely .kml

But can't get it to work.

Might I ask you to have a look at the files? Might be quicker than me muddling my way through it.

https://www.dropbox.com/sh/riofl7g6axo0mnq/AABiYcx7ZJfwdXfx6o-PEQ7Va?dl=0

Polygon self explanatory, excel file the points to be filtered.

Thanks so much,

Justin.

Ok, so the problem does seem to be with the .xml file. I will try to figure out what is happening and will get back to you soon.

 

[chrism216]

Ok so i got it to work. However, this one is a spreadsheet with your specific polygon data preloaded into it. For some reason, Excel gets an error when trying to open your .XML file, and it is truncating data somewhere. I don't know how to fix that, in any case i would have to parse the xml file with some other means to be able to extract the data. its too much work right now and the idea was to make a tool which would work with excel only. So anyways, for you, this should work:

1. Download this: https://www.dropbox.com/s/qva5rfk7pmvmpfi/PointInPolygon2.xlsm?dl=0. This is the same workbook but i did all the importing of coordinates manually.
2. Fill out the table on the first sheet with all the point and their coordinates.
3. Go to Developer -> Macros and run the "PolyQuery" Macro. Don't click the "Analyze" button on the first sheet, since this will try to import data, which we know now is corrupt. Also, the "import" macro also erases all the preloaded data, and we dont want that =)

This should do it. Tell me if it worked!

Cheers,

Chris

 

[chrism216]

Hi Chris,

Following your numbering:

1. Definitely in the same directory.
2. I've made sure - as far as I can - that it's only the polygon data. Imported it into excel and re-created a kml (using GEPath) to hopefully knock off any parasitic information.
3. Definitely .kml

But can't get it to work.

Might I ask you to have a look at the files? Might be quicker than me muddling my way through it.

https://www.dropbox.com/sh/riofl7g6axo0mnq/AABiYcx7ZJfwdXfx6o-PEQ7Va?dl=0

Polygon self explanatory, excel file the points to be filtered.

Thanks so much,

Justin.

Hey, did you get it to work?

Cheers,

Chris

 

[JustinduToit]

Hi,

Worked!!! Thank you so so much. (Have been away so hence late response).

And with this program I'm sure I'll be able to do the same with other data in the future.

Justin.

 

[chrism216]

Hi,

Worked!!! Thank you so so much. (Have been away so hence late response).

And with this program I'm sure I'll be able to do the same with other data in the future.

Justin.

Great! glad to help!