<?php
/*
 * This PHP script reads points from a database and outputs a text formatted
 * as expected by the OpenLayers text parser (actually tab seperated variables)
 * In the absence of bbox param it reads all points from the database.
 *
 * You can point OpenLayers directly at this php script. Optionally you can then
 * use a BBOX strategy. This tells OpenLayers to add on a 'bbox' parameter,
 * which this script can use to limit the number of points returned. 
 */
 
$bbox = isset($_GET['bbox']) ? $_GET['bbox'] : "";

if ($db = new SQLiteDatabase('example.db')) {
        
    $query = 'SELECT * FROM points ';
    if ($bbox!="") {
        //splir out the comma sperated min/max lat/lon values
        $bbox_vars = explode(",", $bbox); 
        $minlon = $bbox_vars[0];
        $minlat = $bbox_vars[1];
        $maxlon = $bbox_vars[2];
        $maxlat = $bbox_vars[3];
        
        //DB deals with integers
        $minx = round($minlon * 10000000);
        $miny = round($minlat * 10000000);
        $maxx = round($maxlon * 10000000);
        $maxy = round($maxlat * 10000000);
        
        $query .= "WHERE x>".$minx." AND x<".$maxx." AND y>".$miny." AND y<".$maxy;
        
        error_log("query=" .$query);
        
        sleep(3); //Artificial 3 second pause, to help see how the bboxes work
    }
    
    //Icon offset. For some reason this doesn't default to a sensible value for
    //the default marker graphic, so we output it in the text file. 
    $icon_offset = "-10,-25";
    
    $result = @$db->query($query);
    if ($result === false) {
            die("Error during basic query:". $err);
    } else {
        
        print "lat	lon	title	description	iconOffset\n";
        
        $count = 0;
        while ($result->valid()) { 
           $count++;
           
            // fetch current row 
            $row = $result->current();                                                     
                                                                                 
            $id   = $row[0];
            $name = $row[1];
            $x    = $row[2];
            $y    = $row[3];
            
            $lon = $x / 10000000;
            $lat = $y / 10000000;
            
            //output text file row which OpenLayers can understand
            print $lat."\t".$lon."\t".$name."\t".$name."\t".$icon_offset."\n";
            
            // proceed to next row 
            $result->next(); 
         }
         
        error_log("Returning " .$count. " points in a text file");
        
    }
    
} else {
    die($err);
}
?>