Umkreissuche mit OpenGeoDB und Symfony

Es ist bekannt, es ist beliebt. Jeder braucht es und keiner möchte es, sofern er es denn kennt, wieder missen.

Die beliebtesten Anwendungen in Verbindung mit der OpenGeoDB sind die Entfernungssuche zwischen zwei Postleitzahlen, sowie die Umkreissuche zu einer bestimmten Postleitzahl.

Heute stelle ich Ihnen die Umkreissuche in Verbindung mit dem PHP Framework Symfony und dem ORM Propel vor.

Was wird benötigt?

• Zuerst einmal brauchen wir eine aktuelle GeoDB http://opengeodb.giswiki.org/wiki/OpenGeoDB_Downloads
• Des Weiteren wird symfony in der Version 1.4 benötigt http://www.symfony-project.org/installation/1_4
• Wie man Symfony installiert und konfiguriert ist unter http://www.symfony-project.org/jobeet/1_4/Propel/en/ in einem sehr guten Tutorial beschrieben.
• Die deutschsprachige Symfony Framework Referenz von Timo Haberkern finden Sie über diesen Link

  Die OpenGeoDB installieren wir am besten in einer separaten Datenbank, da nicht die komplette DB für unsere Zwecke benötigt wird. Nach der Installation, beginnen wir eine Tabelle zu erstellen, welche nur die Postleitzahlen mit den Ortsnamen und ihren entsprechenden Koordinaten beinhaltet.

   
  CREATE TABLE `zip_coordinates` (
      zc_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      zc_loc_id INT NOT NULL ,
      zc_zip VARCHAR( 10 ) NOT NULL ,
      zc_location_name VARCHAR( 255 ) NOT NULL ,
      zc_lat DOUBLE NOT NULL ,
      zc_lon DOUBLE NOT NULL
  )
   

  In diese Tabelle werden dann die benötigten Daten für die Postleitzahlen importiert.

   
  INSERT INTO zip_coordinates 
        (zc_loc_id, zc_zip, zc_location_name, zc_lat, zc_lon)
  SELECT gl.loc_id, plz.text_val, name.text_val, coord.lat, coord.lon
  FROM geodb_textdata plz
  LEFT JOIN geodb_textdata name ON name.loc_id = plz.loc_id
  LEFT JOIN geodb_locations gl ON gl.loc_id = plz.loc_id
  LEFT JOIN geodb_coordinates coord ON plz.loc_id = coord.loc_id
  WHERE plz.text_type =500300000/* ID für Postleitzahl */
    AND name.text_type =500100000/* ID für name */
    AND (
          gl.loc_type =100600000/* ID für pol. Gliederung */
          OR 
          gl.loc_type =100700000/* ID für Ortschaft */
    );
   

  Nun können wir die Tabelle zip_coordinates in unsere Datenbank, welche das Symfony Projekt vorhält, exportieren. Hierzu eignet sich am besten PHPmyadmin. Um mit den Daten arbeiten zu können, benötigen wir für die Datenbanktabelle das entsprechende Model. Dies erstellen wir über die Konsole:

   
  php symfony propel:build-schema
  php symfony propel:build-model
   

  In der entsprechenden Funktion der actions.class.php können wir nun die Daten für eine Umkreissuche abfragen und aufbereiten. Wir gehen hierbei davon aus, dass für die Anfrage eine entsprechendes Formular genutzt wird, welches "zipcode" für die Postleitzahl und "zipcodedistance" für die gewünschte Entfernung der Umkreissuche beinhaltet. Ob diese Formularelemente nun einfache inputs sind oder als select Elemente gestaltet werden, ist unrelevant. Auf eine entsprechende Validierung der Daten möchte ich an dieser Stelle auch nicht näher eingehen, da dies die Grundaufgabe eines jeden Programmierers ist.

   
  <?php 
  class someActions extends sfActions
  {
    public function executeDistancesearch(sfWebRequest $request){
      $this->resultset = null; 
      $zipcode = $request->getParameter("zipcode");
      $zipcodedistance = $request->getParameter("zipcodedistance");
      //Criteria bilden
      $c = new Criteria();
      $c->add(ZipCoordinatesPeer::ZC_ZIP, $zipcode, Criteria::EQUAL);
      $zippo = ZipCoordinatesPeer::doSelectOne($c);
      //Koordinaten vorhanden? 
      if(is_object($zippo)){
        $sql = 'SELECT DISTINCT zc_zip
            FROM zip_coordinates
            WHERE ACOS(
              SIN(RADIANS(zc_lat)) * SIN(RADIANS('.$zippo->getZcLat().')) 
              + COS(RADIANS(zc_lat)) * COS(RADIANS('.$zippo->getZcLat().')) 
              * COS(RADIANS(zc_lon)
              - RADIANS('.$zippo->getZcLon().'))
              ) * 6380 <= '.$zipcodedistance.' 
            AND zc_id <> '.$zippo->getZcId().';';
        //händische SQL Abfrage ausführen
        $con = Propel::getConnection(BaseUserPeer::DATABASE_NAME);
        $stmt = $con->prepare($sql);
        $stmt->execute();
        $zips = array(); 
        while($row = $stmt->fetch()) { 
          array_push($zips,$row['zc_zip']);
        }
        if(count($zips)>0){
          //erhaltene Postleitzahlen weiterverarbeiten
          // hole alle Benutzer aus Tabelle `users` die ihre PLZ im Umkreis haben
          $regex = UserPeer::ZIPCODE." IN(".implode(", ",$zips).")";
          $criteria = new Criteria();
          $criteria->add(UserPeer::ZIPCODE, $regex, Criteria::CUSTOM);
          $this->resultset = UserPeer::doSelect($criteria);
        }
      }
    }
  }
  ?>