Is adress in a specific area

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Is adress in a specific area

Post by Stefan_Sand »

Hello,

i am searching for almost a year to get out the GEO coordinates of a certain adress of my hometown. As i am working a lot of time with Choropleth Maps in Excel, i wanted to do this with the data from my hometown as well ->f.e: http://www.clearlyandsimply.com/clearly ... excel.html" onclick="window.open(this.href);return false;

Now, as i found out the coordinates, the most difficult problem is, in which area this adress is located, beacause there is a more detailed map for all the 23 districts of Vienna. The Problem is, that all the zip codes from the adresses get out only the district number (f.e , 1030 means 3rd district, 1040 4th and so on). So, my idea went to the next Problem. If i have an adress with ist coordinates, i know, exactly where it is located. If i could match the coordinates of the adress to the coordinates of all the 250 Areas of Vienna (i guess in America You could call it Block) it should be possible to match the right Area for each adress...

So, i gathered the kml file with all the coordinates of the Areas, but now im Standing in front of the wall... Can there be any solution in Excel to match the adress with the given shape coordinates? I send You the Excel file to see what my Problem is. I can´t match the adress with all the coordinates of the Areas, even when i tried to use Andy Popes outstanding formulas for points in given Polygons.

You will find the coordinates data in the table KML_Data. The Excel file also contains all the named Areas for Vienna.

Maybe someone can help me out with a clue.... ???

Stef

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is adress in a specific area

Post by HansV »

Did you intend to attach a file?
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

ok, the Problem is the size.... i have to split it up. one Moment please

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

first part: the Output...
You do not have the required permissions to view the files attached to this post.

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

the basedata :
(the original file ogdwien-ZAEHLBEZIRKOGD.kml from vienna has a size of 2,7 mb...
so, i put only the districts 1 to 3 into it
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is adress in a specific area

Post by HansV »

Thanks, I'll take a look later today.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is adress in a specific area

Post by HansV »

Can you explain the meaning of the KML file?
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

in the klm file there are all the data for the Areas in Vienna. e.g. you have all the coordinates in one cell. the adress should be matched against the coordinates of all the shapes/Polygons.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is adress in a specific area

Post by HansV »

That's a bit vague. I see many columns with coordinates. I have no idea what is what...
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

the coordinates of the shapes are in column aq.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is adress in a specific area

Post by HansV »

I'll see if I can come up with something, but I fear it will be terribly slow, especially if you use the complete KML file.
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

Hi Hans,

any "working" solution would be more than my not working ideas and highly appreciated.

The path to the original file is:http://www.google.at/url?sa=t&rct=j&q=& ... 4317,d.ZGU

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is adress in a specific area

Post by HansV »

Here is a function you can use in a macro. You can NOT use it in a worksheet function, because you cannot open another workbook in a worksheet function.

Code: Select all

Function GetGebiet(Xcoord As Double, Ycoord As Double) As String
    Dim wbkKML As Workbook
    Dim wshKML As Worksheet
    Dim r As Long
    Dim m As Long
    Dim strValue As String
    Dim arr1 As Variant
    Dim arr2 As Variant
    Dim arr3 As Variant
    Dim i As Long
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Set wbkKML = Workbooks.Open(Filename:=ThisWorkbook.Path & "\KML Vienna.xlsx")
    Set wshKML = wbkKML.Worksheets("KML Data")
    m = wshKML.Range("A" & wshKML.Rows.Count).End(xlUp).Row
    For r = 2 To m
        strValue = wshKML.Range("AQ" & r).Value
        arr1 = Split(strValue)
        ReDim arr2(1 To UBound(arr1) + 1, 1 To 2)
        For i = 0 To UBound(arr1)
            arr3 = Split(arr1(i), ",")
            arr2(i + 1, 1) = arr3(0)
            arr2(i + 1, 2) = arr3(1)
        Next i
        If PtInPoly(Xcoord, Ycoord, arr2) Then
            GetGebiet = wshKML.Range("A" & r).Value
            Exit For
        End If
    Next r
ErrHandler:
    On Error Resume Next
    wbkKML.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Function
For example,

Debug.Print GetGebiet(16.51, 48.21)

results in S_306. Your example in row 3 doesn't appear to be in S_306 if the KML Vienna file is correct.
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

Hi Hans,

well thanks. I wanted to have the area data as a worksheet in the same workbook with all the adresses, so i could seperate it from the graphical presentation.

And the given adress must be in Area 306 Erdberg (ist my own old adress), the area where Richard II has been taken prisoner 1192...

see the link :

http://www.kartenwerkstatt.at/#!/map/au ... -bright-01" onclick="window.open(this.href);return false;

best regards,
Stefan

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is adress in a specific area

Post by HansV »

If you place everything in the same workbook you could use it as a worksheet function but I would still recommend against it - the function is very calculation-intensive and Excel tends to recalculate a cell multiple times at the drop of a hat...

If you are certain that the coordinates you provided are in S_306, the Vienna KML sheet doesn't provide the matches correctly.
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

Hi Hans, thanks a lot for Your Support and i tried to use it as afunction, but i get the error message #value...
HAve i overseen something?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is adress in a specific area

Post by HansV »

You forgot to remove the references to wbkKML. And you switched the X and Y coordinates.
It won't help though, since the GoogleGeoCode function results in values that don't match those in the KML Data sheet. According to the GoogleGeoCode function, the coordinates for Keinergasse 21, 1030 Wien are 48.1970594,16.3984047. But the coordinates of S_306 in the KML Data sheet are all near 48.21,16.51. So either the KML Data sheet has incorrect data, or Google returns incorrect coordinates.

For what it's worth, here is my version of the workbook.
I had to leave in the decimal points because of my system settings, and I had to change two declarations in the code; they have comments. (You may get a 'user-defined type not defined' error message)
Test GeoCoordsZgeb.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

Hi thanks,

I have to look over the data file; i tired after adopting the code but got nothing for the Areas. very strange...

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

Hi Hans,

finally i found out what happened. When i Import the kml data as a shape, i get another sorting order instead of the procedure when i import the area data as a XML list. In column T of the basedata there are the Information for all the shapes/Areas. as i had Problems to extract the numbers , i wrote them down manually. Many thanks to You,

best regards,
Stefan

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Is adress in a specific area

Post by Stefan_Sand »

At the end, the GetGebiet Funcion works properly, until it is confronted with not all the needed data (pairs of x/y coordinates for all the points) for the Polygons (Areas). In 86 out of 250 cases, there are to much data for one cell. Is there a possibillity to split the data up to 4 or 5 cells - to be under the cell limitation and calculate the polygon?

Stefan

i added a sample of the kml original data, where You can see the reason for my Problem. It is in between two <coordinates> text... For one single Point of the polygon, You have about 22 to 24 characters. :sad:
You do not have the required permissions to view the files attached to this post.
Last edited by Stefan_Sand on 16 Oct 2014, 15:56, edited 2 times in total.