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

 

[Jollybottom]

Thank you so much Chris, this solution has saved me endless work.

Very happy!

 

[lcano]

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

Hi! I'm having the same "Run-time error '1004':Method 'OpeXML' of object 'Workbooks' failed....

Is there a solution for it? Or do i have to import all the coordinates manually on each sheet? I have like 800 diferent polygons i'm trying to work with.... Hope u can help me out!!!

Cheers!!

 

[Rick Rothstein]

Hi! I'm having the same "Run-time error '1004':Method 'OpeXML' of object 'Workbooks' failed....

Is there a solution for it? Or do i have to import all the coordinates manually on each sheet? I have like 800 diferent polygons i'm trying to work with.... Hope u can help me out!!!

You do not have to put the coordinates into a worksheet in order to use my function... you can create your arrays in memory and pass those into the function. My function takes one polygon at a time, so if you have need to process 800 of them, you will have to create an array of your polygon coordinates and iterate that array doing whatever you need to as each individual polygon is processed. You might find this message (a comment to my mini-blog article which featured my PtInPoly function) to be of interest...

Test Whether A Point Is In A Polygon Or Not

 

[lcano]

You do not have to put the coordinates into a worksheet in order to use my function... you can create your arrays in memory and pass those into the function. My function takes one polygon at a time, so if you have need to process 800 of them, you will have to create an array of your polygon coordinates and iterate that array doing whatever you need to as each individual polygon is processed. You might find this message (a comment to my mini-blog article which featured my PtInPoly function) to be of interest...

Test Whether A Point Is In A Polygon Or Not

Hmmm....i don't understand the function that well, what i'm trying to do is load a ".kml" file from google earth that has city polygons on it and then put some company sales points (coordinates) in excel and see on wich polygon are each one of them (following your instructions, the files are in the same folder and with the right names), that's what i tought the first post was all about...but when i run the "Analysis" macro on excel it shows the "1004error" so i don't know what to do...

I really apprecciate your help Sir!

:D

 

[Man Well]

Hello Rick, I would really appreciate your help. I run into the same problem of not being able to open my .xml file ("Run-time error '1004' so picked up PointInPolygon2.xlsm and inserted the coordinates of polygons. I am afraid I get a lot of wrong results. Any chance you could help me troubleshoot this (https://www.dropbox.com/s/79ciyhdn5jc5ojo/Copy_of_PointInPolygon2.xlsm?dl=0)? Man Well

 

[Custom1]

Had trouble getting it to work but succeeded.

• Created a Google Earth folder called PIP (no spaces in name).
• Used a real (in use) polygon and a 'dummy' polygon because it seems to work best with more than one polygon. I only really cared about one of them.
• Named the polygons with with no spaces in the names.
• Right-clicked PIP folder in google earth, chose 'Save Places As' and saved as 'Polygons.xml' in the same folder with the PointInPolygon file.

Clicked analyze and it worked nicely.

 

[chrism216]

Hello everyone,

After a loooong time, I've decided this post needed a revisit, given the massive amount of views it has gotten.

I fixed several serious issues it had, especially with the way I used to... ehemm... "parse" the .kml file. That's now been addressed. I now properly parse it using XPath. My apologies, but I was a complete newbie back then.
The link to the file is still the same. I've just replaced it.

Don't forget to tell me if you have any issues with it.

For those interested, I've also ported this to Python, and it's available here:
https://github.com/chrism216/GoogleEarth_PointInPolygon

I hope this helps.

Cheers,
Chris

 

[Rick Rothstein]

After a loooong time, I've decided this post needed a revisit, given the massive amount of views it has gotten.

I fixed several serious issues it had, especially with the way I used to... ehemm... "parse" the .kml file. That's now been addressed. I now properly parse it using XPath. My apologies, but I was a complete newbie back then.
The link to the file is still the same. I've just replaced it.

Don't forget to tell me if you have any issues with it.

For those interested, I've also ported this to Python, and it's available here:
https://github.com/chrism216/GoogleEarth_PointInPolygon

While you are under no obligation to do so, I would note that since you included my PtInPoly function exactly as I wrote it, a remark indicating my authorship and a copy of the link I posted in Messages #35 and #43 back to my ExcelFox sub-forum where I posted it originally would have been appreciated.

 

[chrism216]

Hi Rick,

Of course! I will add those. I originally thanked you only in the forum, but you're absolutely right, I'll add the links to your original posts in the workbook as well.

Thanks again.

Cheers,
Chris

While you are under no obligation to do so, I would note that since you included my PtInPoly function exactly as I wrote it, a remark indicating my authorship and a copy of the link I posted in Messages #35 and #43 back to my ExcelFox sub-forum where I posted it originally would have been appreciated.

 

[Cara272]

Hey Chris,

Traveler from 2020 here. This was an excellent build and extremely helpful-- I hope good things are coming your way for sharing this excellent macro. Cheers!

Cara