Adressdaten für Stadt Düsseldorf in Übersichtskarte in Excel

[Michael1]

Die Stadt Düsseldorf würde gerne unsere Knoten mit in Ihre Übersichtskarte aufnehmen , dafür benötigt das Vermessungsamt die Adressen (Straße, Hausnummer) der Hotspots als Excel-Datei.

Wer kann sowas erstellen ?

[netminion]

Wie soll die Exceldatei aufgebaut sein?

[Michael1]

eine Info pro zelle , Strasse , Hausnummer , PLZ
Pro Knoten eine Zeile

Warscheinlich ist es nicht schädlich noch eine Zelle mit geo daten zu haben, die kann man ja ausblenden oder wegschneiden wenn nicht benötigt.

[FxFx]

Wenn das eine Karte werden soll, dann wären doch der Name des Hotspots und die Geokoordinaten wesentlich besser.

Vor allem haben wir sicher auch in Düsseldorf nicht die genauen Anschriften (Str. HSNr) parat. Die müssten vermutlich zumindest zum Teil ermittelt werden.

Ich würde mal fragen, ob dem in dem Kontext nicht auch mit Routername und Geo-Koords zufrieden wären. Die kann man dann recht einfach aus den json Files ziehen.

My2cents.

Beste Grüße, Fx

[netminion]

Einen Lösungsansatz gibt es fast schon…

Algorithmus gesucht - Domänsplit

Folgendes Shell Script ergibt bei Aufruf mit den Lat Long Koordinaten die Strassenadresse inkl. Postleitzahl. Beispiel: ./QueryLatLong 50.769681 6.080055 gibt folgendes aus: Stephanstraße 42-44, 52064 Aachen, Germany So kriegst da also die PLZ zu einer Geokoordinate raus… Falls du jetzt ne Massenabfrage starten willst, würde ich zwischen die Einzelnen Aufrufe aber schon ein Sleep reinpacken, ich denke, deren Server macht bei zu vielen Aufrufen per Zeitfenster zu. Hier der Code: tmpfilex…

[adorfer]

Dann dremelt nochmal den Nominatim drüber,
Beispiel:
http://nominatim.openstreetmap.org/reverse?format=xml&lat=51.2116641&lon=6.8110219&zoom=18&addressdetails=0
drüber

Quelldaten sind zu finden alle mit „lastseen“ auf heute bei:
http://map.freifunk-duesseldorf.de/nodes.json
http://map.ffdus.de/data/nodes.json
http://ffmap.freifunk-rheinland.net/nodes.json

[Sundancer]

Ich kenne ja die Vemessungsämter ein wenig, die haben eine Hausnummerndatei zu der dann Koordinaten gehören, so kann jede Straße/Hausnummer georeferenziert werden. Aber das passiert entweder Mitte Grundstück oder Mitte Gebäude (ohne jetzt darauf eingehen zu wollen, wie ‚Mitte‘ Zustande kommt).
Um Orte einem Gebäude zuzuordnen mehr als ausreichend (Klassischer Fall: Fahrzeugnavigation), aber gerade bei größeren Gebäuden oder Gundstücken kann das so zu einer nicht unerheblichen Ungenauigkeit der Knotenposition führen. Das könntet ihr ruhig anmerken und probieren eine ‚bessee‘ Koordinate zu liefen. In welchem Koordinatensystem dann auch.

[Michael1]

@Sundancer

ch kenne ja die Vemessungsämter ein wenig, die haben eine Hausnummerndatei zu der dann Koordinaten gehören, so kann jede Straße/Hausnummer georeferenziert werden. Aber das passiert entweder Mitte Grundstück oder Mitte Gebäude (ohne jetzt darauf eingehen zu wollen, wie ‚Mitte‘ Zustande kommt).Um Orte einem Gebäude zuzuordnen mehr als ausreichend (Klassischer Fall: Fahrzeugnavigation), aber gerade bei größeren Gebäuden oder Gundstücken kann das so zu einer nicht unerheblichen Ungenauigkeit der Knotenposition führen. Das könntet ihr ruhig anmerken und probieren eine ‚bessee‘ Koordinate zu liefen. In welchem Koordinatensystem dann auch.

Da wir eine Vorgabe bekommen haben mit Excel als Format und wie Zellen aussehen sollen kann man ja die Geodaten in weitere Zellen je Zeile packen, dann haben die alles und können das verwerten was ihnen am besten gefällt

Aber vorgabe ist Str mit Hausnummer und dann sollte man das zumindest ablifern, mehr Info ist ja nicht schädlich

[Sirhcnailuj]

Butter bei die Fische, wollen wir denn echt auf diese Karte? Die wird dann ohnehin völlig überfüllt sein. Ich glaube es wäre besser, die Stadt zu bitten zusätzlich zu ihrer Karte unsere Map zu verlinken.

Hier ist das eigentlich schon ganz gut umgesetzt: Kostenfreies WiFi in Düsseldorf

[ReneSch]

klick

Mit dem verlinkten Skript (ff_node_geolocation.py · GitHub) kann man die Adressen auslesen lassen:
(ff_node_geolocation.py · GitHub)
location = reverseGeo(knoten[‚location‘][‚latitude‘],knoten[‚location‘][‚longitude‘])
if location:
hieraRec(elements, ruleset, location)
#pprint(location)

Siehe diesen Code-Teil mit wenig Aufwand könnte so eine CSV erstellt werden und diese in Excel importiert werden

[Frankla]

Generell halte ich nicht viel von der Idee. Erstens hat der Knotenname null Relevanz, was die Leute auf der Strasse interessiert, ist die SSID. Zweitens kenne ich durchaus Freifunkrouter in Mietswohnungen im Vierten Stock, die prima auch in der Wohnung drüber, drunter, links und rechts zu erreichen sind, aber um’s verrecken nicht aufm Bürgersteig vorm Haus. Drittens kommen und gehen Knoten alle Naselang. Viertens ist bei dem ein oder anderen Knoten die hinterlegte Geokoordinate schlicht falsch oder unbrauchbar ungenau. Fünftens gibt es Friseure, die Ihren Freifunkrouter ausserhalb der Geschäftszeiten ausmachen, da gibt’s dann Montags kein Freifunk auf der Strasse.

Der zweite, dritte vierte und fünfte Punkt würde mich davon abhalten, das zu veröffentlichen, es schadet Freifunk eher, wenn mit nicht vorhandenen/unerreichbaren Knoten geworben wird.

Egal, ich hab da mal was gebastelt:

Folgendes Bash Script zieht sich die Rheinland-Nodeliste und gibt dann folgendes aus:

Nodename, NodeLat, NodeLong, NodeStrasse, NodeStadt, NodeLand, NodeEntfernungVomZentrumVonDDorfInMetern

Hier ein Auszug:

Hochfirstweg-1.OG,47.900803,8.157552,Hochfirstweg 15, 79822 Titisee-Neustadt, Germany,383298
ditug-ne-ff-1,51.208443,6.668548,Peter-Loer-Straße 9B, 41462 Neuss, Germany,10310
niemandsland-oekoma,51.211882,6.808758,Heerstraße 27, 40227 Düsseldorf, Germany,2622
freifunk-NE-KBS,51.203453,6.679917,Katharina-Braeckeler-Straße 1, 41462 Neuss, Germany,9744
ff-d-zoo-herder01,51.23649,6.80105,Herderstraße 59, 40237 Düsseldorf, Germany,645
FF-ME-WDR4300-3,51.24951,6.97497,Gottfried-Wetzel-Straße 8, 40822 Mettmann, Germany,11577
FF-ME-WDR4300-5,51.248294,6.978388,Am Island 11, 40822 Mettmann, Germany,11796
FF-ME-WDR4300-4,51.24825,6.978399,Am Island 36-38, 40822 Mettmann, Germany,11796
FF-MO-CaptainMorgan,51.444681,6.648691,Asberger Str. 76, 47441 Moers, Germany,25827
FF-ME-DUO-Unikat-Schmuck,51.25199,6.976706,Oberstraße 10, 40822 Mettmann, Germany,11737

Das läßt sich so als CSV in Excel reinpfeifen, die letzte Spalte ist dann die Entfernung vom Düsseldorfer Zentrum in Metern. Wenn man also nach der letzten Spalte sortiert, kann man eine Entscheidung treffen, welchen Nodes man behält und ggf. zur Veröffentlichung weiterreicht.

Hier das ganze File (Bereits in Excel importiert, Zeichensatz angepasst, GeoKoordinaten in Zahl umgewandelt, und nach der Distanz zum Zentrum aufsteigend sortiert):

Rheinland.zip (76.6 KB)

Und hier das Bash-Script:

#!/bin/bash
DDorfZentrum="51.235454,6.810170"

CalculateDistance()
  {
  local string="${1}"
  local distance=""
  local lat1=""
  local long1=""
  local lat2=""
  local long2=""
  #local erdradius="6378388" # Am Aequator
  local erdradius="6371007" # Gemittelt
  local pidurch180="0.0174533" #3.1415927 geteilt durch 180
  lat1=$(echo ${string}  | awk -F: '{print $1}' | awk -F, '{print $1}')
  long1=$(echo ${string} | awk -F: '{print $1}' | awk -F, '{print $2}')
  lat2=$(echo ${string}  | awk -F: '{print $2}' | awk -F, '{print $1}')
  long2=$(echo ${string}  | awk -F: '{print $2}' | awk -F, '{print $2}')

  #dist = 6378.388 * acos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1))

  distance=$(python -c "import math; print int(${erdradius}*math.acos(   math.sin(${lat1}*${pidurch180})*math.sin(${lat2}*${pidurch180})+math.cos(${lat1}*${pidurch180})*math.cos(${lat2}*${pidurch180})*math.cos(${long2}*${pidurch180}-${long1}*${pidurch180})  )   )   ")

  echo "${distance}"
  }

tmpX="/tmp/GetFreifunkNodesX.$$.tmp"
tmpY="/tmp/GetFreifunkNodesY.$$.tmp"
tmpZ="/tmp/GetFreifunkNodesZ.$$.tmp"

SpecialWGETParams="--retry-connrefused --inet4-only --waitretry=20 --read-timeout=20 --timeout=15 -t 3 --no-dns-cache"

TheLink="http://ffmap.freifunk-rheinland.net/nodes.json"

wget -q -O $tmpX $SpecialWGETParams "$TheLink"

jq '.nodes | to_entries[] | [ .value .nodeinfo .hostname, ( .value .nodeinfo .location .latitude | tostring ), ( .value .nodeinfo .location .longitude | tostring ) ] | join(  ","  ) '  $tmpX > $tmpY

#cat $tmpY

#Alle Nodes ohne Geokoordinaten rausschmeissen
grep -v ',null,null"' $tmpY >$tmpX

#cat $tmpX

#Bloedes Anfuehrungszeichen raushauen
awk -F"\"" '{ print $2 }' $tmpX >$tmpZ

#cat $tmpZ

while read line
 do
 #echo "$line"
 NodeName="$(echo "$line" | awk -F"," '{ print $1 }')"
 NodeLat="$(echo "$line" | awk -F"," '{ print $2 }')"
 NodeLong="$(echo "$line" | awk -F"," '{ print $3 }')"

 wget -o $tmpX -O $tmpY $SpecialWGETParams "http://maps.googleapis.com/maps/api/geocode/json?latlng=$NodeLat,$NodeLong&sensor=true"
 #cat $tmpX
 #cat $tmpY

 NodeStreetAddress=$(cat $tmpY | awk {'if (index($0, "address")>0) {printf($0)}'} | \
   awk -F"street_address" {'print $1'} | awk -F"formatted_address" {'print $2'} | \
   awk -F\" {'print $3'})

 NodeDistance=$(CalculateDistance "$DDorfZentrum:$NodeLat,$NodeLong")

 echo "$NodeName,$NodeLat,$NodeLong,$NodeStreetAddress,$NodeDistance"

 sleep 1

 done < $tmpZ


rm $tmpX
rm $tmpY
rm $tmpZ

#eof

[adorfer]

Als Google-Doc hier

Als CSV-Datei im Attachment:

Freifunk-Router-Düsseldorf-2015-09-26 - Freifunk-Zugänge Düsseldorf Stand 26.09.2015.csv.zip (8.2 KB)

Freifunk-Router-Düsseldorf-2015-09-26

[adorfer]

Warum es mir jetzt diese Thread anbietet, das Forum: Keine Ahnung.

Aber zum Abschluss (?) des Themas sei gesagt, dass dabei allem Anschein nach nie etwas bei herausgekommen ist.
Zumindest habe ich nicht mitbekommen, dass diese Daten mal irgendwann bei der Stadt Düsseldorf genutzt worden wären.

[Michael1]

Doch wurden Sie, hatte sogar am Freitag dazu ein Gespäch, ging um Geld das ich von der Stadt für das Projekt haben möchte