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]

May be a Google Earth bug. I have deleted, redrawn and re-saved the polygon and KML file multiple times, but it appears Google Earth keeps putting out the same data.

Hi

I have your solution. PM me with an e-mail Address so i can send it to you.

I think the error was because, instead of leaving some soordinates blank, they had the value "not found". That made the PtInPoly function crash, because i didn't do my error handling properly

Cheers,

Chris

 

[BenHadman]

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

I am receiving the same error as described above, is there a fix everybody with this error can apply or will it require
you to import the coordinates manually? I would be very grateful if you could help in troubleshooting the problem.

 

[chrism216]

I am receiving the same error as described above, is there a fix everybody with this error can apply or will it require
you to import the coordinates manually? I would be very grateful if you could help in troubleshooting the problem.

Send me your Kml file. Ive PMed you my private email. Cheers,

Chris

 

[Custom1]

I am receiving the same error as described above, is there a fix everybody with this error can apply or will it require
you to import the coordinates manually? I would be very grateful if you could help in troubleshooting the problem.

As I recall my problem was that I had not scrubbed the input data and had a couple bad lines. It's been a while but I believe after I put a filter on the data and fixed the #NA's or whatever errors there were, everything worked as advertised.

Hoepfully that helps - it's a great tool

 

[jmathia]

Chris,
I am trying to use your spreadsheet and I get this error:

Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).name = mySheet

when I unhide the sheets in the workbook I can see my polygon sheet with the coordinates but that is all. Any help is greatly appreciated.

Jen

 

[chrism216]

Chris,
I am trying to use your spreadsheet and I get this error:

Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).name = mySheet

when I unhide the sheets in the workbook I can see my polygon sheet with the coordinates but that is all. Any help is greatly appreciated.

Jen

Hi jen,
You probably have polygon names that are invalid sheet names in excel, or duplicated polygon names. Try changing the names of the polygon, maybe that will work. Also, make sure you dont have anything else but the polygons in the .kml file

Cheers,

Chris

 

[jmathia]

That is what I thought and I made sure I only had one polygon and named it 1_mile_buffer. I've also tried just naming it "buffer" or something simple but I still get the same error.

 

[asifkd]

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

Thank you very much

It works for me, I need one help, how can display names of polygon in cell beside the coodinate

 

[chrism216]

Thank you very much

It works for me, I need one help, how can display names of polygon in cell beside the coodinate

Hi

The names of the polygon are on the column headers.

Cheers,

Chris

 

[asifkd]

Hi

The names of the polygon are on the column headers.

Cheers,

Chris

Thanks Chris
My problem is I have set of coordinates around 5000 and they are without area names. I created polygons in google earth manually based on area and I need to display them on area wise. Is it possible to display them beside each coordinate instead of displaying them on the two header?