User:Rubke/ChangeRendering

target: render daily changes in mapnik overlay

url: website

Create a daily table copy

CREATE TABLE planet_osm_line_old AS SELECT * FROM planet_osm_line

sql query

to generate the "planet_osm_line_changes" table

DROP TABLE planet_osm_line_changes;

CREATE TABLE planet_osm_line_changes AS

SELECT MIN(TableName) as TableName, osm_id, name, z_order, way

FROM

(

  SELECT 'planet_osm_line' as TableName, planet_osm_line.osm_id, planet_osm_line.name, planet_osm_line.z_order, planet_osm_line.way

  FROM planet_osm_line

  UNION ALL

  SELECT 'planet_osm_line_old' as TableName, planet_osm_line_old.osm_id, planet_osm_line_old.name, planet_osm_line_old.z_order, planet_osm_line_old.way

  FROM planet_osm_line_old

) tmp

GROUP BY osm_id, name, z_order, way

HAVING COUNT(*) = 1

ORDER BY osm_id;

CREATE INDEX planet_osm_line_changes_index ON planet_osm_line_changes USING gist (way);

CREATE INDEX planet_osm_line_changes_pkey ON planet_osm_line_changes USING btree (osm_id);

don't forget to add this row (once) to the geometry_colums tables

insert into geometry_columns values ('','public','planet_osm_line_changes','way',2,900913,'LINESTRING');

tilecache setup

[changes]
type=MapnikLayer
bbox=311549.5,6555477.5,822458.8125,7118943.5
resolutions=156543.0,78271.5,39135.75,19567.875,9783.9375,4891.96875,2445.984375,1222.9921875,611.49609375,305.748046875,152.874023438,76.4370117188,38.2185058594,19.1092529297,9.55462646484,4.77731323242,2.38865661621,1.19432830811,0.597164154053,0.298582077026
srs=OSGEO:41001
metaTile=yes
metaBuffer=40
mapfile=/home/rubke/osm-changes.xml
debug=1
tms_type=google
projection=+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs +over

Mapnik stylesheet

osm-changes.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE Map>
<Map bgcolor="transparent" srs="+proj=merc +datum=WGS84  +k=1.0 +units=m +over +no_defs">

<Style name="changes">
    <Rule>
	  <Filter>[tablename]='planet_osm_line'</Filter>
      <LineSymbolizer>
        <CssParameter name="stroke">blue</CssParameter>
        <CssParameter name="stroke-width">4</CssParameter>
      </LineSymbolizer>
    </Rule>
    <Rule>
	  <Filter>[tablename]='planet_osm_line_old'</Filter>
      <LineSymbolizer>
        <CssParameter name="stroke">red</CssParameter>
        <CssParameter name="stroke-width">4</CssParameter>
      </LineSymbolizer>
    </Rule>
</Style>

  <Layer name="changes" status="on" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs +over">
	<StyleName>changes</StyleName>	
    <Datasource>
      <Parameter name="type">postgis</Parameter>
      <Parameter name="host">/tmp</Parameter>
      <Parameter name="user">mapnik</Parameter>
      <Parameter name="dbname">osm</Parameter>
      <Parameter name="table">planet_osm_line_changes</Parameter>
      <Parameter name="estimate_extent">true</Parameter>
    </Datasource>
  </Layer> 

</Map>