Is adress in a specific area
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Is adress in a specific area
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
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
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
ok, the Problem is the size.... i have to split it up. one Moment please
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
first part: the Output...
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
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
(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.
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
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.
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is adress in a specific area
That's a bit vague. I see many columns with coordinates. I have no idea what is what...
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
the coordinates of the shapes are in column aq.
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is adress in a specific area
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
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
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
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
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is adress in a specific area
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.
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.
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
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
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
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
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
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is adress in a specific area
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.
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
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
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?
HAve i overseen something?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78238
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is adress in a specific area
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)
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)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
Hi thanks,
I have to look over the data file; i tired after adopting the code but got nothing for the Areas. very strange...
I have to look over the data file; i tired after adopting the code but got nothing for the Areas. very strange...
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
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
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
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Is adress in a specific area
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.
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.
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.