nicht angemeldet
Tabellenkalk
dedlfix
klawischnigg
klawischnigg
klawischnigg
klawischnigg
klawischnigg
Rolf B
Rolf B
Hallo, ich habe mir ein Skript gekauft und bekomme es nicht zum Laufen. Daher hoffe ich, dass es mit eurer Hilfe gehen könnte.
Es geht hiermit los ...
JürgenB/*
Pleas use the following SQL script to import the CSV data into MySQL.
Please note that you might have to adjust the database name and the path to the CSV file.
*/
USE db
DROP TABLE IF EXISTS plz_de
CREATE TABLE plz_de
(
land varchar(2),
bundesland varchar(48),
kreis varchar(48),
kreisschluessel varchar(5),
plz varchar(5),
postfach varchar(1),
ort varchar(48),
ortsteil varchar(48),
latitude decimal(13, 9),
longitude decimal(13, 9),
gemeindeschluessel varchar(8),
iso varchar(5),
fips varchar(5),
nuts varchar(5),
hasc varchar(8),
zeitzone varchar(24),
utc varchar(6),
dst varchar(6)
)
LOAD DATA INFILE 'C:\\DE-complete.csv'
INTO TABLE plz_de
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Wenn ich das in PhpMyAdmin machen möchte, kommt (trotz Änderung des Pfads) folgende Fehlermeldung:
#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'DROP TABLE IF EXISTS plz_de
CREATE TABLE plz_de
(
land varchar(2),
bun' in Zeile 8
Es geht um eine CSV-Datei. Die kann man natürlich auch über die "Importieren"-Funktion importieren, aber es gibt noch eine weitere CSV-Datei und ich weiß nicht, ob die irgendwie miteinander verknüpft sind, schließlich taucht die (DE-plz) nicht in dem Befehl auf.
Ich hab in diesen ganzen Sachen keine Routine mehr (und null Kenntnis über den aktuellen Stand) und bin eigentlich raus aus der Verlosung, daher würde es mich freuen, wenn Ihr mir helfen könntet - herzlichen Dank!
Hallo,
USE db DROP TABLE IF EXISTS plz_de ...
Sollten die einzelnen SQL-Befehle nicht durch ein ";" beendet werden? Die Fehlermeldung weist auch daraufhin, dass das alles als ein Befehl interpretiert wird...
Gruß
Kalk
Tach!
ich habe mir ein Skript gekauft und bekomme es nicht zum Laufen. Daher hoffe ich, dass es mit eurer Hilfe gehen könnte.
Das Forum ist kein Ersatz für Gewährleistungsansprüche gegen den Hersteller.
#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'DROP TABLE IF EXISTS plz_de
SQL-Statements werden mit Semikolon abgeschlossen, besonders dann, wenn es als Trennzeichen zwischen mehreren Statements benötigt wird.
dedlfix.
Hi there,
zusätzlich zum schon Gesagten:
Es geht um eine CSV-Datei. Die kann man natürlich auch über die "Importieren"-Funktion importieren, aber es gibt noch eine weitere CSV-Datei und ich weiß nicht, ob die irgendwie miteinander verknüpft sind, schließlich taucht die (DE-plz) nicht in dem Befehl auf.
CSV-Dateien können nicht verknüpft sein. Wenn eine logische Beziehung existieren sollte (etwa über die PLZ selbst oder irgendeinen Schlüssel), dann kannst Du die ohnehin nur in der Datenbank wieder herstellen. Da kannst Du also nicht viel ruinieren, ausserdem macht die "importieren-Funktion" von Phpmyadmin auch nichts anderes als den in Deinem Skript beschriebenen Befehl...
Vielen Dank, den Wald vor lauter Bäumen nicht gesehen. Bei mir schwingen gerade die Bedenken mit, dass ich etwas gekauft habe, was totaler Schrott ist.
Das hier steht im Code ...
$conn = mysql_connect('127.0.0.1', 'root', '') or die('db connect error: ' . mysql_error());
Das gibt es doch gar nicht mehr, oder?
Hi there,
Vielen Dank, den Wald vor lauter Bäumen nicht gesehen. Bei mir schwingen gerade die Bedenken mit, dass ich etwas gekauft habe, was totaler Schrott ist.
Naja, ich schätze einmal, Du hast in erster Linie für die Daten bezahlt, und nicht für die "genialen" Importroutinen.
Das hier steht im Code ...
$conn = mysql_connect('127.0.0.1', 'root', '') or die('db connect error: ' . mysql_error());Das gibt es doch gar nicht mehr, oder?
In einer Produktivitätsumgebung würde man soetwas nicht mehr machen, aber wenn's auf Deinem Server (der in dem Fall wahrscheinlich Dein Rechner ist) funktioniert, warum nicht? Du hast ja in erster Linie offenbar ein syntaktisches Problem, die fehlenden Semikola wurden ja schon erwähnt.
Alternativ dazu kannst Du diese Befehle ja auch einzeln in Phpmyadmin im SQL-Abfragefenster einzeln eingeben, also zuerst erzeugst Du eine Datenbank (das entspricht vom Effekt her dem "USE db' (brauchst Du natürlich nur einmal machen), dann löscht Du mit 'DROP TABLE IF EXISTS plz_de' die Tabelle, sollte die von vorhergegangen Fehlversuchen schon existieren, dann erzeugst Du sie wieder mit dem 'CREATE TABLE...'-Befehl und so weiter, immer schön der Reihe nach...;)
Vielen Dank!
Die "100% verifizierten Daten" entsprechen auch nicht dem aktuellen Stand, allein in meinem Wohnort finde ich bereits einen Fehler.
Ich habe ewig nichts mehr ge-HTML-t und ge-PHP-t und allein aufgrund der Sicherheit wollte ich auf etwas zurückgreifen, das gekauft und daher vermeintlich sicher ist. Jetzt kommt da so eine Scheiße bei raus.
Hi there,
Die "100% verifizierten Daten" entsprechen auch nicht dem aktuellen Stand, allein in meinem Wohnort finde ich bereits einen Fehler.
Naja, das ist eine andere Geschichte…
Ich habe ewig nichts mehr ge-HTML-t und ge-PHP-t und allein aufgrund der Sicherheit wollte ich auf etwas zurückgreifen, das gekauft und daher vermeintlich sicher ist. Jetzt kommt da so eine Scheiße bei raus.
Ich hoffe, Du hast nicht viel dafür bezahlt. In welchem Format und wofür brauchst Du die Daten eigentlich?
http://www.plz-umkreis.com/
Stolze 99 Euro, ich denke, dass das viel Geld ist, ich habe aber keine Vergleichsmöglichkeiten.
Ich wollte eine Umkreissuche integrieren. Ich bin mittlerweile mit WordPress verheiratet, wie gesagt, ich bin einfach nicht mehr ansatzweise auf der Höhe des Geschehens und kann das zeitlich auch nicht wieder aufholen, weil eben jene Zeit fehlt. Bei "British Virgin Islands" hätte ich wohl hellhörig werden müssen, jetzt ärgert es mich natürlich. Ich weiß auch nicht, wie hoch die Chance ist, das über PayPal zurück zu bekommen.
Mir ist die Genauigkeiten gar nicht so wichtig, Erdkrümmung, etc., es geht mir da nicht um 5 Kilometer, aber eine fertige Lösung schien mir angebracht.
Hi there,
http://www.plz-umkreis.com/ Stolze 99 Euro, ich denke, dass das viel Geld ist, ich habe aber keine Vergleichsmöglichkeiten.
Kann ich Dir nicht sagen, ich hab so etwas noch nie benötigt, aber für 99 Euro bekomm' ich schon eine Flasche von meinem Lieblingsbarolo 😉
Bei "British Virgin Islands" hätte ich wohl hellhörig werden müssen, jetzt ärgert es mich natürlich. Ich weiß auch nicht, wie hoch die Chance ist, das über PayPal zurück zu bekommen.
Mir ist die Genauigkeiten gar nicht so wichtig, Erdkrümmung, etc., es geht mir da nicht um 5 Kilometer, aber eine fertige Lösung schien mir angebracht.
Die 5 Kilometer sind eh nicht drin, ich habs ausprobiert, bei einem Radius von 10km hat mir diese Seite bei einer Testabfrage eine Strecke von 30 km als in der Lösung befindlich vorgeschlagen. Anyway, wie gesagt, da hast Du dann ja quasi zwei Probleme, den Datenimport, damit Du die Daten nutzen kannst und eine sinnvolle Abfrage, damit auch etwas Brauchbares 'rauskommt. Und letzteres bedeutet, daß Du Dich auch noch mit irgendeinem Skript herumschlagen kannst. Das schaut langsam wirklich nach Mühsal und Gewährleistung aus...;(
Den Beitrag habe ich löschen lassen, vielen Dank für den Hinweis.
Wie der leider ebenfalls gelöschte Beitrag von Rolf B vermuten lässt, ist das ganze Unterfangen für die Tonne. Bei mir läuft Php 7 und wie Rolf B bereits ausführte, sind einige Funktionen abgeschaltet.
Bleibt vermutlich nur der Versuch, das Geld zurückzubekommen.
Rolf Bs Hinweis (frag mal, wo er die Daten her hat) habe ich nicht verstanden.
Hi there,
Wie der leider ebenfalls gelöschte Beitrag von Rolf B vermuten lässt, ist das ganze Unterfangen für die Tonne. Bei mir läuft Php 7 und wie Rolf B bereits ausführte, sind einige Funktionen abgeschaltet.
ja, die alte MySQL-API wurde afaik mit PHP7 entfernt. Das ließe sich zur Not aber leicht reparieren.
Bleibt vermutlich nur der Versuch, das Geld zurückzubekommen.
Richtig - die Frage ist, will man überhaupt herumreparieren, wenn man gerade frisch gekauft hat?
Rolf Bs Hinweis (frag mal, wo er die Daten her hat) habe ich nicht verstanden.
Keine Ahnung, vielleicht wollte er damit andeuten, daß Du die Daten (die bestehen ja im wesentlichen nur aus einer Postleitzahl und den entsprechenden Koordinaten) unter Umständen auch wo anders her beziehen könntest...
Hallo klawischnigg,
genau - ich meine nämlich, dass eine PLZ - Koordinaten Tabelle auch frei verfügbar ist. Ich weiß nur nicht wo.
Rolf
Hi there,
genau - ich meine nämlich, dass eine PLZ - Koordinaten Tabelle auch frei verfügbar ist. Ich weiß nur nicht wo.
Ja, das ist das Problem, oder wie es ein bedeutender österreichischer Kabarettist (ja, sowas gibts auch) einmal formuliert hat: "das Leben ist wie ein Supermarkt, theoretisch wäre alles da, aber man weiß halt nicht wo..."😉
Hallo Verzweifelt,
ist das ganze Unterfangen für die Tonne. Bei mir läuft Php 7 und wie Rolf B bereits ausführte, sind einige Funktionen abgeschaltet.
Nein, nur weil die mysql-Funktionen nicht mehr existieren, sind die Script nicht für den Eimer. Das ist sehr schnell auf mysqli umgestellt.
Etwas programmieren musst Du ohnehin, um eine Umkreissuche auf deiner Seite einzubauen.
Was mir nur auffiel - und was ich wegen der Löschung nicht mehr speichern konnte: Die Formel im SQL und die Formel auf deren Supportseite stimmen nicht überein. Auf der Supportseite steht die Orthodrome-Formel, die man auch in der Wikipedia findet. Das ist der Arcuscosinus des Öffnungswinkels zwischen den beiden Punkten. Im SQL steht aber irgendwas mit Wurzel und überhaupt kein Arcuscosinus, das scheint eine Näherungsformel zu sein. Dabei hat mysql eine ACOS-Funktion, aber vielleicht ist sie ihnen zu langsam.
Abgesehen davon kommt mir das SQL sehr ineffizient vor. Wenn man effizienter sein will, dann sollte man nicht eine Näherungsformel für den Arcuscosinus verwenden, sondern
SIN(RADIANS(geodb.longitude)), sondern geodb.sin_long. Erweitere die plz_de Tabelle um vier float-Spalten: sin_long, cos_long, sin_lat, cos_lat, und mach einen Update-Befehl, der diese 4 Inhalte aus latitude und longitude berechnet.Es hilft auch, zwei SELECTs zu schachteln. Der innere berechet die Abstände, der äußere filtert, dann muss man die Abstandsformel nur einmal notieren.
Im SQL wird's dann deutlich übersichtlicher. Ich verwende jetzt die Orthodrome-Formel aus der Wikipedia statt der Näherungsformel.
In $sinLong, $sinLat, $cosLong und $cosLat stehen die vorberechneten Sin-/Cos-Werte der Bezugs-PLZ.
SELECT p.plz,
p.ort,
p.abstand
FROM (SELECT plz, ort,
6367.41 * ACOS( sin_lat * $sinLat
+ cos_lat * $cosLat
* COS(RADIANS($long - longitude))
) AS abstand
FROM plz_de) p
WHERE p.abstand < $radius
Eine echte Cosinusberechnung ist noch nötig. Es sei denn, du machst es wie im Beispiescript und verwendest das Additionstheorem für cos(a-b)=cosa cosb + sina sinb - dann kannst Du weiter die vorberechneten Werte verwenden. Könnte schneller sein, muss man einfach mal messen.
Wenn Dir das zu wüst wird, ok, dann solltest Du mit dem Support von plz_umkreis zusammenarbeiten. Die lassen sich ja auf ihrer Homepage für ihren Support feiern.
Rolf
Vielen Dank schon einmal, nur kurz zum Support ...
https://www.trustpilot.com/review/www.zipcodesoft.com
Aus diesem Grund denke ich auch, dass ich beschubst wurde. Neben alten Daten und einem völlig verhurten Skript reagiert bisher niemand.
Es gibt eine Datenbank, mit der man das erreichen kann, was Du wohl vorhast:
http://www.fa-technik.adfc.de/code/opengeodb/
Die erhält sogar Updates.
Schritt für Schritt:
1. aktuelle Adresse des letzten Dumps auf
http://www.fa-technik.adfc.de/code/opengeodb/dump/
heraussuchen. Bei war das http://www.fa-technik.adfc.de/code/opengeodb/dump/opengeodb-02630_2020-04-13.sql.gz
2.
cd /tmp
wget 'http://www.fa-technik.adfc.de/code/opengeodb/dump/opengeodb-02630_2020-04-13.sql.gz'
3. Nur für MariaDB!:
sed -e 's/TYPE=InnoDB CHARACTER SET utf8//' < 'opengeodb-02630_2020-04-13.sql' > 'opengeodb-02630_2020-04-13.sql.1'
4. Mysql/Mariadb als root
mysql [-u root -p]
5. in der Mysql-Shell
create database `opengeodb_2020`;
quit;
Bitte beachten, dass das Einlesen des Dumps eine ganze Weile dauern kann!
mysql opengeodb_2020 < opengeodb-02630_2020-04-13.sql.1
oder (andere, die Passwort erfordern, dann ohne root-Rechte:)
mysql -u root -p opengeodb_2020 < opengeodb-02630_2020-04-13.sql.1
Dieses Fahrrad wurde schon oft erfunden...
Hallo Raketenwissenschaftler,
Nur für MariaDB!: sed -e...
Nicht nur für MariaDB. Die Table Option TYPE=... heißt schon seit Ewigkeiten ENGINE=, und seit MySQL 5.5 wird nur noch ENGINE unterstützt.
Bitte beachten, dass das Einlesen des Dumps eine ganze Weile dauern kann!
Die Untertreibung des Jahres! Das läuft bei mir jetzt schon 12 Minuten und ist, wie es aussieht, bei 3 Prozent oder so. Ok, das ist MySQL 5.6, und eine Magnetplatte, und ein einfacher Desktop. Aber CPU ist kaum gefragt, dafür ist meine Platte am Rauchen. 99% Auslastung, keine sonderlich hohe Schreibrate, offenbar ist dieses Script extrem ineffizient. Massendaten importiert man nicht per INSERT, sondern anders. Gibt's da keine bessere Quelle?
Rolf
Vorab vielen Dank natürlich! Ich bin entweder komplett eingestaubt oder früher war das einfacher, vielleicht auch beides.
Liegt die Ursache in der TYPE - ENGINE-Geschichte, dass ich beim Import permanent Fehlermeldungen angezeigt bekomme? Bzw: Einen Schritt zurück ...
Ich habe vom Provider zwei Datenbanken, der lasse ich
create database `opengeodb_2020`;
quit;
weg und verwende einfach eine der beiden bestehenden Datenbanken, um Daten dieser Seite: http://www.fa-technik.adfc.de/code/opengeodb/
zu importieren.
Mir erschien die opengeodb-begin.sql als sinnvoll, weil die ja die Tabellen erstellen soll, die danach gefüllt werden sollen. Da erscheint bei mir die Fehlermeldung
#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'TYPE=InnoDB CHARACTER SET utf8' in Zeile 10
Es tut mir wirklich leid, dass ich gerade die einfachsten Schritte nicht mehr hinbekomme. Aber in Zeile 10 ist doch einfach nur ein Kommentarsternchen.
Wie Rolf schon schrieb:
Das TYPE=InnoDB CHARACTER SET utf8 in den Dateien von der Quelle ist nicht nur hyperliquid sondern ein Syntaxfehler - der entsteht, weil die Dumps offensichtlich mit einer schwer veralteten MySQL-Version gemacht wurden. Sowas kommt vor…
Nicht nur für MariaDB. Die Table Option TYPE=... heißt schon seit Ewigkeiten ENGINE=, und seit MySQL 5.5 wird nur noch ENGINE unterstützt.
In solchen Fällen vergleicht man die Fehlermeldung einfach mal mit dem Quelltext. Zeilenummern sind hierbei "Schall und Rauch", weil für MySQL offenbar eine Zeile etwas ganz anderes ist als für Dich…
Die Lösung hattest Du eigentlich schon vor Augen:
Man schaut sich den Quelltext an und überlegt, was den Fehler verursacht. Danach, ob man es weglässt oder ersetzt. Im ersten Beispiel hatte ich mich für "Weglassen" entschieden, da Du aber eine Datenbank eines Providers benutzt kann die „wunderbunt“ konfiguriert sein (und merkwürdige Voreinstellungen haben) also schlage ich jetzt "Ersetzen" vor.
Das Handbuch sagt:
ENGINE=InnoDB CHARACTER SET utf8
wird erwartet.
Du kannst das mit einem Editor beheben (Suchen nach 'TYPE=InnoDB', Ersetzen durch 'ENGINE=InnoDB') oder kurzerhand sed nehmen und genau das (in einer Linux-Shell/SSH-Sitzung) machen:
sed -e 's/TYPE=InnoDB/ENGINE=InnoDB/' < 'datei.sql' > 'datei.neu.sql'
Das sollte "eng genug" sein um sonst nichts zu verändern.
Danach natürlich die neue Datei einlesen.
Übrigens, falls Du eine Umkreissuche mit Postleitzahlen bauen willst, da gibt es eine fertige Datei, welche die Postleitzahlbereiche mit Koordinaten enthält. Die könnte Dir genügen.
http://www.fa-technik.adfc.de/code/opengeodb/PLZ.tab
Du kannst Dir also selbst eine Tabelle bauen (die opengeodb-begin.sql enthält bis auf die veraltete Syntax alles was Du dazu brauchst), die Daten dann einlesen (sieh Dir dazu https://www.mysqltutorial.org/import-csv-file-mysql-table/ an) und nicht vergessen die Tabelle zu indizieren, damit die Abfragen schnell gehen. Wie das geht, kannst Du Dir in der Dir ebenfalls schon bekannten opengeodb-end.sql anschauen.
Das geht mit Lesen, Vergleichen und dem Mittel einfacher Überlegung…
Die PLZ.tab gibt es ja nicht als CSV-Datei. Eine entsprechende Tabelle habe ich (hoffentlich korrekt) angelegt, aber der Import der PLZ.tab funktioniert noch nicht bzw. ich finde dazu auch nichts im Netz. Ich habe auch versucht, die .tab in einer CSV-Datei konvertieren, auch das gelingt mir nicht.
Hallo,
Ich habe auch versucht, die .tab in einer CSV-Datei konvertieren,
Und zwar wie?
Das sollte mit jedem beliebigen Tabellenprogramm gelingen. In meinem Versuch war es aber wichtig, vorher dem Firefox zu sagen, dass er die .tab als utf unicode anzeigen soll, sonst gabs eßhüb-Chaos…
Machen andere Browser das besser?
Gruß
Kalk
Machen andere Browser das besser?
Definiere „besser“ - Der Browser trifft eine Annahme weil ihm nichts zu den Eigenschaften mitgeteilt wird.
HTTP/1.1 200 OK
Date: Sun, 31 May 2020 08:19:54 GMT
Server: Apache
Last-Modified: Tue, 07 Feb 2017 15:39:39 GMT
ETag: "2f214ce-71c1a-547f28e57ff66"
Accept-Ranges: bytes
Content-Length: 465946
X-Powered-By: PleskLin
Keep-Alive: timeout=5, max=100
Connection: Keep-Alive
Content-Language: de
Das wäre also Sache des Betreibers der Webseite.
Die PLZ.tab gibt es ja nicht als CSV-Datei.
Oh doch!
CSV heisst Comma Character Separated Values. Das Trennzeichen ist hier der Tabulator. (Deswegen „.tab“)
Für den Immport der Datei von http://www.fa-technik.adfc.de/code/opengeodb/PLZ.tab gilt also:
Auszulassende Zeilen: 1
Zeilentrenner : \n (Unix)
Spaltentrenner : \t
Textbegrenzer : keine
Textkodierung: : utf-8
Hallo,
CSV heisst
CommaCharacter Separated Values.
Immer diese fiesen Uminterpretationen, die einem das Leben einfacher machen…
Gruß
Kalk
CSV heisst
CommaCharacter Separated Values.Immer diese fiesen Uminterpretationen, die einem das Leben einfacher machen…
In der reinen Lehre des Mahrad Tschumbeldingda bedeutet CSV allerdings Character Separated Valottes, also "rauhe Typen mit unterschiedlichem Karma"
Ich habe die passende Tabelle aus der Tab-Datei mal eben erstellt:
https://home.fastix.org/Files/free_for_all/opengeodb_PLZ_Koordinates.sqlite3
Beachte bitte, dass sqlite(3) nicht mysql und mariadb ist. Aber PHP unterstützt das auch. Ich würde aber die PDO-Variante nehmen.
sqlite3 bietet sich hier an, weil es mit dem wenigen Datensätzen gut zu recht kommt und in der Tabelle im produktiven Einsatz nicht geschrieben, sondern nur gelesen wird...
Hallo Jörg,
du solltest dir noch mal den Unterschied zwischen „Autor“ und „Betreff“ klarmachen.
Gruß
Jürgen
du solltest dir noch mal den Unterschied zwischen „Autor“ und „Betreff“ klarmachen.
Du meinst sicherlich „aufmerksamer beachten“.
Ich habe die passende Tabelle aus der Tab-Datei mal eben erstellt:
https://home.fastix.org/Files/free_for_all/opengeodb_PLZ_Koordinates.sqlite3
Aktualisierte Variante mit Zusatzspalte für Telefeonbuchsortierung (z.B. „Ö“→„oe“) hoch geladen. (Gleiche Stelle)
GUI für sqlite(3):
https://sqlitebrowser.org/
Pakete für Linux
php-sqlite3 *) sqlite3 sqlite3-doc sqlitebrowser sqlite3-pcre +)
*) Das Paket „php-sqlite3“ wird Dein Hoster wohl installiert haben. +) Wenn mit sqlite3 Perl-kompatible reguläre Ausdrücke genutzt werden sollen…
Aktualisierte Variante mit Zusatzspalte für Lexikon-Sortierung (z.B. „Ö“→„o“) hoch geladen. (Gleiche Stelle)
Das scheint schon mal geklappt zu haben, vielen Dank
Zur Performance:
Alle reinen SQL-Abfragen zur Umkreissuche bedienen sich „fürchterlich komplizierter“ mathematischer Formeln.
Mein Trick für die Umkreissuche ist, je nach Radius ein Koordinatenviereck zu berechnen, und sich dann also in einem ersten Schritt die 10 bis 200 Orte aus dem Viereck geben zu lassen und dann nur diese mit der „fürchterlich komplizierten“ mathematischen Formel darauf zu untersuchen, ob diese im wohl auch im Umkreis liegen. Das geht dann in „hastdunichtgesehen".
Außerdem kannst Du bei einer solchen Umkreissuche und bei Radien bis 30 Kilometer durchaus auch die Erdkrümmung vernachlässigen. Die anderen, bei der Beechnung über Koordinaten nicht behebbaren Mängel (Wie wird denn die Koordinate der PLZ festgelegt?; Kein direkter Weg bei Gebieten links und rechts großer Flüsse?; Die Erde ist eher eine schrumpelige Kartoffel als eine Kugel!; …) wiegen bei kleinen Radien schwerer als die hierdurch auftretenden Ungenauigkeiten. Nur Genauheimer aus der nordeutschen Flachebene bestehen auf der Kugel und der reinen Geometrie.
Wir hatten hier im Forum dazu schon jede Menge Diskussionen, Du kannst dennoch fragen.
Wie also weiter?
Zunächst solltest Du erfahren, ob Dein Hoster sqlite3 unterstützt. Den kann man fragen und warten, oder das einfach mal testen. Lade die Datei opengeodb_PLZ_Koordinates.sqlite3 in ein Verzeichnis, dessen Inhalt der Webserver nicht ausliefert (ich hab das in einem Terminal gemacht und einfach mal /tmp/ genommen), passe den Pfad an und teste einfach mal…
<?php
error_reporting( E_ALL );
ini_set( 'display_errors', 1 );
try {
$dbh = new PDO(
'sqlite:/tmp/opengeodb_PLZ_Koordinates.sqlite3',
null,
null,
array(PDO::ATTR_PERSISTENT => true)
);
} catch (PDOException $e) {
trigger_error ('Verbindung fehlgeschlagen: ' . $e->getMessage(), E_USER_ERROR );
}
$sql = 'SELECT ort FROM PLZ ORDER BY ort_sort_at DESC LIMIT 3';
try {
$res = $dbh->query( $sql );
} catch (PDOException $e) {
trigger_error ('Abfrage fehlgeschlagen: ' . $e->getMessage(), E_USER_ERROR );
}
header( 'Content-type: text/plain; charset=utf-8' );
foreach ( $res as $row ) {
print $row['ort'] . "\n";
}
Wenn das klappt kannst Du ja mit der Abfrage ein wenig herumspielen...
Hallo Raketenwissenschaftler,
einerseits hast Du Recht - die Voreinschränkung mit einem „Quadrat“ hilft schonmal gewaltig. D.h. man findet zuerst mal die Intervalle, in denen Längen- und Breitengrad liegen müssen, um überhaupt im Radius enthalten sein zu können.
Für die Breite ist es relativ einfach, der Abstand zwischen zwei Breitengraden beträgt näherungsweise 40000km / 360 = 111.111 km. Bei den Längengraden ist es komplizierter, der Abstand zwischen zwei Längengraden hängt von der Breite ab. Der Radius der "Erdscheibe" in einem bestimmten Breitengrad $$\phi$$ beträgt $$r_\phi = r_E \cos \phi$$, und da der Umfang zum Radius proportional ist, gilt das auch für den Umfang der Erde an diesem Breitengrad. Demnach ist der Abstand zweier Längengrade auf Breite $$\phi$$: $$l_\phi = 111,111 \cos \phi$$. Bleibt man in Deutschland, liegt der südlichste Punkt an der Oberstdorfer Trifthütte, $$\phi=47{,}27^\circ$$, und einem Längengradabstand von 75,4km. Der nördlichste Punkt liegt im Rickelsbüller Koog auf 54,9 Grad mit einem Längengradabstand von 63,9km.
Andererseits kann man sich durch passende Vorausberechnungen auch viel Rechnerei sparen. Ich schrub das ja schon anderswo.
Vorausberechnung Stufe 1: sin und cos von Länge und Breite jedes gespeicherten Ortes bestimmen und in der Ortstabelle abspeichern. Das kann man ab MySQL 5.7 mit generated columns lösen (aber STORED, nicht VIRTUAL, sonst ist's witzlos). Ebenso sin und cos von Länge und Breite des Referenzortes bestimmen.
Die Abstandsformel für Orthodrome steht in der Wikipedia. Sie basiert auf dem Zentriwinkel, d.h. dem Winkel zwischen den beiden Geraden vom Erdmittelpunkt zu den beiden Punkten, deren Abstand zu finden ist. Der Cosinus des Zentriwinkels berechnet sich so:
$$\cos \zeta = \sin(\phi_A) \cdot \sin(\phi_B) + \cos(\phi_A) \cdot \cos(\phi_B) \cdot \cos(\lambda_B - \lambda_A)$$
Das unschöne an dieser Formel ist, dass man den Cosinus der Differenz der Längengrade ausrechnen muss, was sich nicht vorberechnen lässt. Aber Mathematik ist ja die Kunst, das Rechnen zu vermeiden 😉, darum wenden wir zuerst einmal das Additionstheorem für $$\cos(\beta - \alpha)$$ an:
$$\cos \zeta = \sin(\phi_A) \cdot \sin(\phi_B) + \cos(\phi_A) \cdot \cos(\phi_B) \cdot (\cos\lambda_B \cos\lambda_A + \sin\lambda_B \sin\lambda_A) $$
Die Distanz d der beiden Punkte A und B ist $$d = r\cdot\zeta$$, mit r=Erdradius und $$\zeta = \arccos(...)$$ (im Bogenmaß) entsprechend der obigen Formel.
Nun muss man alle Orte finden, für die d einen bestimmten Abstand A unterschreitet. Das führt wieder zu einer Lästigkeit, denn das Berechnen des arccos in der SQL Abfrage ist ebenfalls zeitraubend. Also nutzen wir den bekannten Umstand, dass die arccos-Funktion zwischen x=-1 und x=1 streng monoton fallend ist, dementsprechend gilt
$$ \displaystyle \quad\quad r \cdot \zeta \lt A$$
$$ \displaystyle \Longleftrightarrow \zeta \lt \frac{A}{r}$$
$$ \displaystyle \Longleftrightarrow \cos \zeta \gt \cos\frac{A}{r}$$
Diesen Cosinus kann man wieder vorausberechnen. In der Query berechnet man dann nur noch den Wert innerhalb des arccos-Aufrufs, was 5 Multiplikationen und 2 Additionen sind, und vergleicht ihn mit dem vorausberechneten Referenzwert. Achtung, es ist ein GRÖSSER-Vergleich, weil arccos eine fallende Funktion ist.
Die Hardcore-Lösung wäre übrigens, eine Tabelle mit allen möglichen PLZ Paaren zu bilden und darin den Abstand dieser PLZ zu speichern. Das dauert eine Weile, aber das kann man nach jedem Update der PLZ DB einmal machen und die Abfragen gehen dann Ratz-Superfatz direkt auf den Index, wenn man nach "Von-PLZ" und "Abstand" Indexiert. Das sind 67 Millionen bis 841 Millionen Sätze, je nachdem, ob man nur die 8200 Hauszustellungs-PLZ will oder auch die 16500 Postfach- oder 3100-Großempfänger-PLZ berücksichtigen will. Ob sich das lohnt, hängt vom Mengengerüst der erwarteten Abfragen ab.
Rolf