createUpdatePolygons.php

<?php
/**
 * This script is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 *
 * This script reads a list of tiles, which has been created by osm2pgsql after a differential update with parameter "-e" and "-o".
 * The tilenumbers get converted to BoundingBoxes, and these are stored in a PostGIS DB.
 * This information can then be used with MapProxy, to seed or cleanup specific regions (using an ogr datasource in the seed.yaml)
 *
 * If you need more Information or Details on how to use this script, you can email me anytime.
 *
 * Parts of the code (converting tiles to boundingboxes) come from http://wiki.openstreetmap.org/wiki/Slippy_map_tilenames
 * You can find examples in lots of different programming languages there
 *
 * The script includes connection details for the PostGIS-database in the pgsql.conf.php which has to look like this:
 *
 * <?php
 * $host = "the_host";
 * $port = "5432";
 * $dbname = "osm";
 * $user = "username";
 * $password = "pass";
 * $connection = "host=$host port=$port dbname=$dbname user=$user password=$password";
 * ?>
 *
 * @author J. Weskamm <weskamm@terrestris.de>
 *
 */
include ("./pgsql.conf.php");

// Define the name of the table where the polygons should be written to
// The table hast to have a "the_geom" column of type Geometry
$updateTable = 'updatepolygons';

// Enter the correct path to the expire.list, which is genereated by osm2pgsql with option "-e" and "-o"
$list = '/opt/diffs/expire.list';

if (!file_exists($list)) {
    echo "ERROR! File expire.list not found, cancelling...";
    exit ;
}

$con = pg_connect($connection);
$listentry = file($list);

// First we clean up the old data
$cleanupsql = "DELETE from ".$updateTable;
$resclean = pg_query($cleanupsql);
if (!$resclean) {
    echo "ERROR: Cleanup failed! Continuing anyway...";
}

foreach ($listentry as $line) {

    $parts = explode("/", $line);
    $z = $parts[0];
    $x = $parts[1];
    $y = $parts[2];

    $bbox = tile2boundingBox($x, $y, $z);

    $insertsql = "INSERT INTO ".$updateTable."(the_geom) SELECT ST_Transform(ST_GeomFromText('POLYGON((";

    $insertsql .= $bbox[west] . " " . $bbox[south] . ", ";
    $insertsql .= $bbox[west] . " " . $bbox[north] . ", ";
    $insertsql .= $bbox[east] . " " . $bbox[north] . ", ";
    $insertsql .= $bbox[east] . " " . $bbox[south] . ", ";
    $insertsql .= $bbox[west] . " " . $bbox[south];

    $insertsql .= "))', 4326),900913)";

    $res = pg_query($insertsql);
    if (!$res) {
        echo "error on INSERT in DB, the SQL was: " . $insertsql . "     cancel operation....";
        exit;
    }
}

echo "finished Postgis INSERTS...";

/**

 *
 *
 * @param object $x
 * @param object $y
 * @param object $zoom
 * @return
 */
function tile2boundingBox($x, $y, $zoom) {

    $x = intval($x);
    $y = intval($y);
    $zoom = intval($zoom);

    $bb = array();
    $bb['north'] = tile2lat($y, $zoom);
    $bb['south'] = tile2lat($y + 1, $zoom);
    $bb['west'] = tile2lon($x, $zoom);
    $bb['east'] = tile2lon($x + 1, $zoom);

    return $bb;
}

/**
 *
 * @param object $x
 * @param object $z
 * @return
 */
function tile2lon($x, $z) {

    $n = pow(2, $z);
    $ret = $x / $n * 360.0 - 180.0;

    return $ret;
}

/**
 *
 * @param object $y
 * @param object $z
 * @return
 */
function tile2lat($y, $z) {

    $n = pow(2, $z);
    $ret = rad2deg(atan(sinh(pi() * (1 - 2 * $y / $n))));
    return $ret;
}
?>