Hintergrund:
Ich muss die folgenden Daten in einer Datenbank speichern:
-
osm-Knoten mit Tags;
-
osm-Kanten mit Gewichten (d. h. eine Kante zwischen zwei Knoten, die aus "way" aus einer .osm-Datei extrahiert wurde).
Knoten, die Kanten bilden, die sich in denselben "Weg"-Sets befinden, sollten dieselben Tags wie diese Wege haben, d.h. jeder Knoten in einem "Weg"-Set von Knoten, der eine Autobahn ist, sollte ein "Autobahn"-Tag haben.
Ich benötige diese Struktur, um auf einfache Weise einen Graphen zu erstellen, der auf verschiedenen Filtern basiert, z. B. einen Graphen, der nur aus Knoten und Kanten besteht, die Autobahnen sind, oder einen "Fußwege"-Graphen usw.
Problem:
Ich habe noch nie von einem räumlichen Index gehört, also habe ich gerade eine .osm-Datei in eine MySQL-Datenbank geparst:
- alle Knoten in eine "Knoten"-Tabelle (mit entsprechenden Koordinatenspalten) - OK, in meinem Fall etwa 9.000.000 Zeilen:
(INSERT INTO nodes VALUES [pseudocode]node_id,lat,lon[/pseudocode] ;
- alle Wege zu einer "Kanten"-Tabelle (normalerweise erzeugt ein Weg ein paar Kanten) - OK, auch etwa 9.000.000 Zeilen:
(INSERT INTO edges VALUES [pseudocode]edge_id,from_node_id,to_node_id[/pseudocode] ;
- Hinzufügen von Tags zu Knoten, Berechnung von Gewichten für Kanten - Problem :
Hier ist das problematische php-Skript:
$query = mysql_query('SELECT * FROM edges');
$i=0;
while ($res = mysql_fetch_object($query)) {
$i++;
echo "$i\n";
$node1 = mysql_query('SELECT * FROM nodes WHERE id='.$res->from);
$node1 = mysql_fetch_object($node1);
$tag1 = $node1->tags;
$node2 = mysql_query('SELECT * FROM nodes WHERE id='.$res->to);
$node2 = mysql_fetch_object($node2);
$tag2 = $node2->tags;
mysql_query('UPDATE nodes SET tags="'.$tag1.$res->tags.'" WHERE nodes.id='.$res->from);
mysql_query('UPDATE nodes SET tags="'.$tag2.$res->tags.'" WHERE nodes.id='.$res->to);`Nohup zeigt die Ausgabe für 'echo "$i \n " alle 55-60 Sekunden (was mehr als 17 Jahre dauern kann, wenn die Größe der Tabelle "Kanten" mehr als 9.000.000 Zeilen beträgt, wie in meinem Fall).
Htop zeigt einen /usr/bin/mysqld-Prozess, der 40-60% der CPU beansprucht.
Das gleiche Problem besteht bei dem Skript, das versucht, die Gewichtung (den Abstand) einer Kante zu berechnen (alle Kanten auswählen, eine Kante nehmen, dann zwei Knoten dieser Kante aus der Tabelle "Knoten" auswählen, dann den Abstand berechnen, dann die Tabelle "Kanten" aktualisieren).
Frage:
Wie kann ich diese SQL-Aktualisierungen beschleunigen? Sollte ich irgendwelche MySQL-Konfigurationseinstellungen ändern? Oder sollte ich PostgreSQL mit der PostGIS-Erweiterung verwenden? Sollte ich eine andere Struktur für meine Daten verwenden? Oder sollte ich den räumlichen Index irgendwie nutzen?
