User:Brogo/OpenLayers Datenbankanbindung

=== OpenLayers-Datenbankanbindung

Intention

Ich wollte versuchen die Marker von POIs nicht über eine Textdatei, sondern direkt über meine eigene Datenbank anzuzeigen. Vorteil ist, daß auf schwächeren Systemen die Anzeige oft langsam ist, da der Browser bei jedem Verschieben des Ausschnittes die Daten ausfiltern muß. Bei einer Datenbank übernimmt dieses der Server und liefert nur die Ergebnisse im Anzeigebereich.

Außerdem kann man mit einer einzigen Tabelle mehrere verschiedene POI-Karten füttern und braucht nur einmal die Tabelle zu aktualisieren.

Anlegen der Datenbank

Bei meinem Provider habe ich die Möglichkeit eine kleine MySQL-Datenbank anzulegen. Für unser Beispiel brauche ich nur eine einzige Tabelle.

Diese hat folgende Spalten

id	lat	lon	name	amenity	operator	vending

Zuerst habe ich die Tabelle 'pois' erstellt und dort die einzelnen Spalten anlegt. Alternativ geht es auch mit folgendem SQL-Script

CREATE DATABASE `db123` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
USE hans;

CREATE TABLE `pois` (
  `id` bigint(20) unsigned NOT NULL,
  `lat` float(10,7) NOT NULL,
  `lon` float(10,7) NOT NULL,
  `name` varchar(255) collate utf8_bin NOT NULL,
  `amenity` varchar(255) collate utf8_bin NOT NULL,
  `operator` varchar(255) collate utf8_bin NOT NULL,
  `vending` varchar(255) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Hier sind noch der Datenbankname und der Username anzupassen. Für diese Beispielseite setze ich folgende Variablen

• Datenbankhost = db1000.1und1.de
• Datenbankname = db123
• Username = Hans
• Passwort = geheim

Diese Werte sind durch die eigenen Angaben zu ersetzen.

Im Prinzip kann die Tabelle aber beliebig viele Spalten haben. Da wir per Datenbankabfrage die Daten sowieso noch filtern können wir also Hundekottütenspender, Kindergärten und Notrufsäulen in eine einzige Tabelle schmeißen.

Aufbereitung der Daten

In meinem Beispiel nehme ich einen selbsterstellen Schleswig-Holstein-Extrakt. Damit füttere ich zunächst osmconvert und wandele alle Wege und Relationen in Punkte um.

osmconvert "c:\osm\planet\schleswig-holstein.osm" --all-to-nodes >"c:\osm\planet\pois.osm"

Zur weiteren Verarbeitung gibt es zwei Möglichkeiten. Die alte mit Perl habe ich bisher genutzt. Die zweite setzt auf die neuen Funktionen von 'osmconvert' auf.

Wichtig bei beiden Varianten ist, daß die CSV- bzw. TXT-Datei genau so aufgebaut ist, wie unsere SQL-Tabelle. Die einzelnen Spalten müssen also die gleiche Reihenfolge der Datenbanktabelle haben. Auch die Anzahl der Spalten und Datebbankfelder muß übereinstimmen.

Variante mit Perl

Dann schicke ich die Datei an ein Perl-Script, welches die Umwandlung in die Textdatei vornimmt.

perl c:\osm\planet\osm2db.pl

Da ich mich mit Perl nicht auskenne, habe ich die Bibliothek osm.pm von Garry68 benutzt. Darauf aufbauend konnte ich folgendes Perl-Script schreiben.

#!/usr/bin/perl -w

use strict ;
use OSM::osm ;

my $file ;
my $nodeUser ;
my @nodeTags ;
my $nodeTags ;
my $ref1 ;
my $line ;
my $tag;
my $nodeName;

my $id ="1" ;
my $lat ;
my $lon ;
my $name ;
my $amenity ;
my $operator ;
my $vending;  


$file = "c:/osm/planet/pois.osm" ;

openOsmFile ($file) ;
open(AUSGABE, ">c:/osm/planet/mysql.txt");
($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ;

while ($id != -1 ) {
	$name ="" ;
	$amenity ="" ;
	$operator ="" ;
	$vending ="" ;
	
	@nodeTags = @$ref1;
		foreach my $tag	(@nodeTags) {
			if ($tag->[0] eq "name") { $name = scalar ($tag->[1] )};
			if ($tag->[0] eq "amenity") { $amenity = scalar ($tag->[1] )};
			if ($tag->[0] eq "operator") { $operator = scalar ($tag->[1] )};
			if ($tag->[0] eq "vending") { $vending = scalar ($tag->[1] )}
			}			
		if 	($name ne "" | $amenity ne "" | $operator ne"" | $vending ne"")
			{print AUSGABE "$id^$lat^$lon^$name^$amenity^$operator^$vending\n";}
		($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ;

}
close(AUSGABE);
closeOsmFile () ;

Variante mit osmconvert

Mit osmfilter entferne ich zunächst alle unnnötigen Nodes aus der Datei.

osmfilter.exe c:\osm\planet\pois.osm --keep="name= amenity= operator= vending=" -o=c:\osm\planet\pois_filter.osm

Die neuen CSV-Ausgabe von osmconvert kann ich einfach das gewünschte Dateiformat generieren:

osmconvert.exe c:\osm\planet\pois_filter.osm --csv="@id @lat @lon name amenity operator vending" --csv-separator="^" -o=c:\osm\planet\mysql.txt

Import der Daten

Am einfachsten geht der Datenimport mit Bigdump. Allerdings hat es mir nicht geklappt TABs als Spaltentrenner zu verwenden. Ich habe daher ein anderes Zeichen gesucht, welches nicht in den Daten vorkommt. Ich habe jetzt erstmal '^' genommen. In bigdump.php ergänzt man die indivuellen Daten (Zugangsdaten und Dateiname) und speichert die geänderte Version und die entsprechende CSV-Datei auf dem Server und führt dann die PHP-Datei aus.

Alternativ kann man die Daten PhpMyAdmin einlesen. 1&1 bietet über den Kunden-Login eine entsprechende Oberfläche, oder man installiert PhpMyAdmin selbst in seinem Webspace. Wichtig: Zugangsbeschränkungen beachten. Am besten durch .htaccess mit Passwort sichern. Ansonsten kann jeder an die Datenbank ran und Unfug treiben. Hinweis: Auch wenn man bei 1&1 einen Domainnamen für den Datenbank-Server hat, kann man ihn, aus Sicherheitsgründen, nicht 'von außen' erreichen. Man kann also leider keinen MySQL-Client nutzen.

Man importiert also die Datei in die Tabelle 'pois'. Dabei ist darauf zu achten, daß die Spaltentrenner richtig angegeben sind. Aktualisiert man später die Daten muß die Funktion 'Tabelleninhalt ersetzen' ausgewählt werden.

Auf PhpMyAdmin gehe ich jetzt nicht im Speziellen ein, da der Aufbau je nach Version etwas unterschiedlich ist.

Jetzt ist unsere Datenbank gefüllt und wartet auf die erste Abfrage.

PHP-Dateien

Die Datenbankabfrage läuft über PHP und ist in diesem Beispiel zweigeteilt. Das erste Script stellt die Datenbankverbindung her; das zweite führt die Abfrage aus.

dbconnect.php

<?php
mysql_connect("db1000.1und1.de","hans","geheim") or die ("Keine Verbindung moeglich");;
mysql_select_db("db123")  or die ("Die Datenbank existiert nicht.");;
mysql_query("SET NAMES 'utf8'");
?>

Hier sind wieder die eigenen Zugangsdaten einzusetzen.

Die letzte Zeile ist notwendig, damit unsere UTF8-kodierten Daten auch korrekt übernommen werden und die Umlaute richtig dargstellt werden.

dbcsv.php

<?php

$bbx = $_GET["bbox"] ; 

$array = explode(",",$bbx);

$ble = $array[0] ;
$bbo = $array[1] ;
$bri = $array[2] ;
$bto = $array[3] ;

include("dbconnect.php");
$ergebnis = mysql_query("SELECT lat, lon, name, amenity, operator, vending FROM pois  
WHERE vending LIKE 'excrement_bags'
AND lat BETWEEN $bbo AND $bto
AND lon BETWEEN $ble AND $bri")
OR die("Error: $abfrage <br>".mysql_error()
);

$header = "point\ttitle\tdescription\ticon\n" ;

echo $header ;

while($row = mysql_fetch_object($ergebnis))
{
$daten = $row->lat.",".$row->lon."\t".$row->name."\t"."amenity=".$row->amenity."<br>vending=".$row->vending."<br>operator=".$row->operator."\t"."http://www.openlayers.org/dev/img/marker.png\n" ;
echo $daten ;
	}

?>

Kurz zur Erläuterung der Abfrage:

• Von unserer Beispielseite erhält dbcsv.php den Parameter bbox mit den entsprechenden Eckkoordinaten des aktuellen Kartenausschnittes.
• Der Parameter wird in ein Array kopiert und in seine Bestandteile ($ble, $bbo, $bri und $bto) zerlegt.
• Die Verbindung zur Datenbank wird hergestellt und eine SQL-Abfrage gesendet. Im Beispiel möchten wir die Felder lat, lon, name, amenity, operator und vending als Rückgabe erhalten. Und zwar für den Fall, daß vending den Inhalt excrement_bags hat, 'lat' einen Wert zwischen $bbo und $bto hat und 'lon' einen Wert zwischen $ble und $bri hat.
• Nun wird die Ausgabe so formatiert, wie OpenLayers sie erwartet (Koordinate, Titel, Beschreibung, Icon). Als erstes wird der Header geschrieben. \t bedeutet immer, daß ein TAB geschrieben wird.
• In der folgenden Schleife wird für jede Zeile aus der Abfrage eine Zeile geschrieben; auch hier sind die Felder wieder durch TAB getrennt. $row->lat.",".$row->lon sind die Koordinaten, $row->name der Titel des Popups. Mit "amenity=".$row->amenity."<br>vending=".$row->vending."<br>operator=".$row->operator wird nun des Text des Popups geschrieben, den ich dargestellt haben möchte. <br> ist in diesem Fall HTML-Code, der einen Zeilenwechsel erzeugt.

HTML-Datei

Die HTML-Datei ist wie viele andere OpenLayers-Seiten aufgebaut. Die Anzeige der POIs wird in diesem Fall über ein Vector-Layer gelöst. Der Text-Layer, die bisher viele nutzen, bietet nicht die Funktion auf eine Datenbank zurückzugreifen und wird wohl auch langfristig nicht mehr unterstützt. Der Vector-Layer bietet außerdem weitere Funktionen, die über einen Text-Layer nicht, gehen, wie z.B. das Clustering. Beim Umstellen von Text- auf Vector-Layer ist besonders auf das leicht veränderte Format der Textdatei zu achten.

<html>
<head>
    <title>POI-Layer aus Datenbank</title>
    <script src="ol/OpenLayers.js"></script>
	<script type="text/javascript">
        // Start position for the map
        var lat=54.00
        var lon=10.75
        var zoom=14

		var leftBottom = new OpenLayers.LonLat(10.5,53.75)
		.transform(new OpenLayers.Projection("EPSG:4326"), new OpenLayers.Projection("EPSG:900913") )
		var rightTop = new OpenLayers.LonLat(11.05,54.20) 
		.transform(new OpenLayers.Projection("EPSG:4326"), new OpenLayers.Projection("EPSG:900913") )
		var options = {restrictedExtent: extent}
		var extent = new OpenLayers.Bounds(leftBottom.lon, leftBottom.lat, rightTop.lon, rightTop.lat);
	
        var map; 
		
        function init() {

            map = new OpenLayers.Map ("map", {
                controls:[
				new OpenLayers.Control.Navigation({
				dragPanOptions: {
                                enableKinetic: true
				}
				}),
				new OpenLayers.Control.Attribution(),
				new OpenLayers.Control.LayerSwitcher(),
				new OpenLayers.Control.Zoom()		
			],
                numZoomLevels: 17,
                units: 'm',
                projection: new OpenLayers.Projection("EPSG:900913"),
                displayProjection: new OpenLayers.Projection("EPSG:4326")
            } 
			);

 			layerMapnik = new OpenLayers.Layer.OSM;
            map.addLayer(layerMapnik);
 			
            var pois = new OpenLayers.Layer.Vector("POIs", {
                    projection: new OpenLayers.Projection("EPSG:4326"),
					strategies: [new OpenLayers.Strategy.BBOX({resFactor: 1.1})],
                 	protocol: new OpenLayers.Protocol.HTTP({
					url: "dbcsv.php",
                        format: new OpenLayers.Format.Text()
                    })
                });
            map.addLayer(pois);
			pois.setVisibility(true);

            var lonLat = new OpenLayers.LonLat(lon, lat).transform(new OpenLayers.Projection("EPSG:4326"), map.getProjectionObject());

            map.setCenter (lonLat, zoom);
			
                // Interaction; not needed for initial display.
                selectControl = new OpenLayers.Control.SelectFeature(pois);
                map.addControl(selectControl);
                selectControl.activate();
                pois.events.on({
                    'featureselected': onFeatureSelect,
                    'featureunselected': onFeatureUnselect
                });			
        }
 
		// Needed only for interaction, not for the display.
		function onPopupClose(evt) {
                // 'this' is the popup.
                var feature = this.feature;
                if (feature.pois) { // The feature is not destroyed
                    selectControl.unselect(feature);
                } else { // After "moveend" or "refresh" events on POIs layer all 
                         //     features have been destroyed by the Strategy.BBOX
                    this.destroy();
                }
            }
            function onFeatureSelect(evt) {
                feature = evt.feature;
                popup = new OpenLayers.Popup.FramedCloud("featurePopup",
                                         feature.geometry.getBounds().getCenterLonLat(),
                                         new OpenLayers.Size(100,100),
                                         "<h2>"+feature.attributes.title + "</h2>" +
                                         feature.attributes.description,
                                         null, true, onPopupClose);
                feature.popup = popup;
                popup.feature = feature;
                map.addPopup(popup, true);
            }
            function onFeatureUnselect(evt) {
                feature = evt.feature;
                if (feature.popup) {
                    popup.feature = null;
                    map.removePopup(feature.popup);
                    feature.popup.destroy();
                    feature.popup = null;
                }
            }
    </script>

</head>
	<body onload="init()">
			<div id="mapcontainer">
				<div id="map">
					<div id="location"></div>
				</div>
			</div>
	</body>
</html>

Der Block der mit var pois = new OpenLayers.Layer.Vector("POIs", { beginnt, erstellt nun einen Vector-Layer, in dem unsere POIs angezeigt werden. url: "dbcsv.php", gibt an, welche PHP-Datei aufgerufen werden soll. Die Zeilen // Needed only for interaction, not for the display. und folgende sind so direkt aus dem OL-Beispiel genommen und sind für die Interaktion zuständig.

Damit ist das Ganze lauffähig.

Ich kenne mich leider kaum mit Programmierung, HTML, JS und PHP aus. Ich habe mir das Beispiel durch viel Hilfe aus dem Internet zusammengestellt, immer wieder ausprobiert, geändert, ausprobiert und letztendlich ist was Funktionierendes herauskommen. Manchmal wäre ich allerdings an so mancher Syntax schier verzweifelt, hier mal ein Komma, da ein Semikolon, mal eckige, mal runde und mal geschweifte Klammern. Als sehr hilfreich hat sich das Firefox-Plugin Firebug erwiesen. Da konnte man immer sehen, was der Browser schon alles für Daten empfangen bzw. gesendet hat.

Wahrscheinlich kann man viele Sachen eleganter lösen und manche Sachen sind vielleicht gänzlich überflüssig. Für Anregungen und Tipps bin ich daher sehr dankbar.