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

 

[asifkd]

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 row header?

spelling correction

 

[asifkd]

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 row header?

I found the solution enter the formula "=INDEX($D$1:$CK$1,MATCH(TRUE,D2:CK2,0))" in A2 and copy to whole column
Note : $CK$1 is the last cell in the first row

 

[aldwinbigguy]

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.

Hi, im Aldwin from the Philippines and im currently using the same xls file and getting the same result. maybe there is a limit to the number of times you can use the analyze tool?

 

[aldwinbigguy]

Hi, im Aldwin from the Philippines and im currently using the same xls file and getting the same result. maybe there is a limit to the number of times you can use the analyze tool?

I got it! you need to have more than one polygon for this to work. just having one will cause the error

 

[Rick Rothstein]

I got it! you need to have more than one polygon for this to work. just having one will cause the error

If you want to test with only one polygon, see my mini-blog article here...

Test Whether A Point Is In A Polygon Or Not

 

[Jaafar Tribak]

If you want to test with only one polygon, see my mini-blog article here...

Test Whether A Point Is In A Polygon Or Not

Nice function Rick ! - Thanks

 

[Rick Rothstein]

Nice function Rick ! - Thanks

Thanks Jaafar, I am glad you liked it. Did you get a chance to try the Excel workbook I attached to my article? I think the demo ended up being kind of cool.

 

[Jaafar Tribak]

Hi Rick,

Yes, I tried the the attached workbook ..I liked the idea and the algorithm

I would normally use the API functions Polygon and PtInRegion to solve this which would save us the need for the math algorithm but then that would take the fun out of it

 

[Rick Rothstein]

I would normally use the API functions Polygon and PtInRegion to solve this which would save us the need for the math algorithm but then that would take the fun out of it

The PtInPoly code actually goes back to the year 2000 when I used to volunteer answering VB (the compiled version) question on the old newsgroup that were popular then. Originally, posted the PtInRegion API solution when I first saw a question asking how to determine if a point was inside a polygon or not, but someone challenged me to come up with a non-API solution (I was "famous" back then for posting alternatives to API solutions whenever someone post one) and my PtInPoly was the result. Here is a link to when my non-API solution was born (I tightened it up somewhat within the thread and later on in time further tightened it up even more)...

https://groups.google.com/forum/?hl...b.general.discussion/qMWtT93O5No/fRd8XDyA9-wJ

In tests performed as a result of discussions about my code in later newsgroup threads, my routine was determined to be as fast as the API solution. I cannot find a link to the thread where that as fully detailed, but in a different thread, Mike D Sutton said back then "The overhead involved in creating a GDI region and performing a point in region test is actually a lot higher than you may expect, where as your mathematical point in polygon test is a lot more efficient." Now this was all for the compiled version of VB, but I believe it applies to the VBA world as well. The nice thing about my code is I think it can be run as is on a Mac whereas an API solution cannot.

 

[Jaafar Tribak]

Hi Rick,

Good work - Well done !

I absolutely agree with everything you said about API vs Non API .. It is often more difficult to get things done without resorting to the API and it can also be more efficient and here we have a good example.

Anyways, I've just written the API code alternative in case anyone is interested

Here is an attachement of your workbook with the new function applied

Option Explicit

Private Type POINTAPI
    x As Long
    y As Long
End Type

#If VBA7 Then
    Declare PtrSafe Function CreatePolygonRgn Lib "gdi32" (lpPoint As POINTAPI, ByVal nCount As Long, ByVal nPolyFillMode As Long) As LongPtr
    Declare PtrSafe Function PtInRegion Lib "gdi32" (ByVal hRgn As LongPtr, ByVal x As Long, ByVal y As Long) As Long
    Declare PtrSafe Function DeleteObject Lib "gdi32" (ByVal hObject As LongPtr) As Long
    Dim hRgn As LongPtr
#Else
    Declare Function CreatePolygonRgn Lib "gdi32" (lpPoint As POINTAPI, ByVal nCount As Long, ByVal nPolyFillMode As Long) As Long
    Declare Function PtInRegion Lib "gdi32" (ByVal hRgn As Long, ByVal x As Long, ByVal y As Long) As Long
    Declare Function DeleteObject Lib "gdi32" (ByVal hObject As Long) As Long
    Dim hRgn As Long
#End If

Public Function PtInPoly(Xcoord As Double, Ycoord As Double, Polygon As Variant) As Boolean
    Dim poly As Variant
    Dim NumCoords As Long
    Dim i As Long
    
    poly = Polygon
    NumCoords = UBound(poly)
    ReDim PolyCoord(UBound(poly)) As POINTAPI
    For i = LBound(poly) To UBound(poly)
        PolyCoord(i - 1).x = poly(i, 1) * 10
        PolyCoord(i - 1).y = poly(i, 2) * 10
    Next
    hRgn = CreatePolygonRgn(PolyCoord(0), NumCoords, 1)
    PtInPoly = CBool(PtInRegion(hRgn, Xcoord * 10, Ycoord * 10))
    DeleteObject hRgn
End Function