osm2pgsql-to-imposm-schema.sql Modified for use in TileMill open-streets-dc project

osm2pgsql-to-imposm-schema.sql

/*********************************************************************
  Original Source: https://github.com/jmckenna/basemaps/blob/master/contrib/osm2pgsql-to-imposm-schema.sql
  Modified for use in creating shapefiles to replace in TileMill Open Streets, DC project

  Purpose: This script will modify tables generated through the osm2pgsql
            utilility [1] into tables similar to those as generated from the 
            imposm utility [2].  The generated tables can then be used
            by the mapserver-utils utility [3].
            
            This is most likely useful for the Windows platform.
            
            [1] http://wiki.openstreetmap.org/wiki/Osm2pgsql
            [2] http://imposm.org
            [3] https://github.com/mapserver/basemaps
            
   Author:  Jeff McKenna (www.gatewaygeomatics.com)
            Michael Smith
            
   Last 
   Updated: 2012/07/10
   
   Notes:   This assumes that you already ran the osm2pgsql tool with the
            '-E 3857' switch
            
            Also, you may have to edit osm2pgsql's 'default.style' file 
            beforehand, to make sure it contains a node,way for 'population'

   Execute: psql -U postgres -d osm -f osm2pgsql-to-imposm-schema.sql
*********************************************************************/

----------------------------
-- LANDUSAGES
----------------------------

CREATE VIEW osm_landusages_view AS
  SELECT row_number() OVER (ORDER BY osm_id) id, * FROM (
  SELECT  osm_id, name, landuse  as type, st_area(way) area , z_order, way as geometry
  FROM planet_osm_polygon
  WHERE landuse in (
            'park',
            'forest',
            'residential',
            'retail',
            'commercial',
            'industrial',
            'railway',
            'cemetery',
            'grass',
            'farmyard',
            'farm',
            'farmland',
            'wood',
            'meadow',
            'village_green',
            'recreation_ground',
            'allotments',
            'quarry') 
  UNION ALL 
  SELECT osm_id, name, leisure as type, st_area(way) area, z_order, way as geometry
  FROM planet_osm_polygon 
  WHERE leisure in ( 
            'park',
            'garden',
            'playground',
            'golf_course',
            'sports_centre',
            'pitch',
            'stadium',
            'common',
            'nature_reserve')
  UNION ALL 
  SELECT  osm_id, name, "natural" as type, st_area(way) area, z_order, way as geometry
  FROM planet_osm_polygon 
  WHERE "natural" in ( 
            'wood',
            'land',
            'scrub')
  UNION ALL
  SELECT osm_id, name, highway as type, st_area(way) area, z_order, way as geometry
  FROM planet_osm_polygon 
  WHERE "highway" in (  
            'pedestrian',  
            'footway')  
  UNION ALL 
  SELECT osm_id, name, amenity as type, st_area(way) area, z_order, way as geometry
  FROM planet_osm_polygon 
  WHERE amenity in (  
            'university',
            'school',
            'college',
            'library',
            'fuel',
            'parking',
            'cinema',
            'theatre',
            'place_of_worship',
            'hospital')              
  )  AS foo;

CREATE TABLE osm_landusages AS
  SELECT * FROM osm_landusages_view;
  
ALTER TABLE osm_landusages ADD PRIMARY KEY (id);

-- create spatial index
CREATE INDEX osm_landusages_geom ON osm_landusages USING GIST (geometry);
CLUSTER osm_landusages_geom ON osm_landusages;
-- add spatial constraints
ALTER TABLE osm_landusages ADD CONSTRAINT enforce_dims_geometry CHECK (st_ndims(geometry) = 2);
ALTER TABLE osm_landusages ADD CONSTRAINT enforce_srid_geometry CHECK (st_srid(geometry) = 900913); 

--VACUUM ANALYZE osm_landusages;


---------------------------
-- MAINROADS
---------------------------

CREATE VIEW osm_mainroads_view AS
  SELECT row_number() OVER (ORDER BY osm_id) id, * FROM (
  SELECT osm_id, name, highway as type, 
        CASE 
            WHEN tunnel='yes' THEN 1 
            ELSE 0 
        END tunnel,
        CASE 
            WHEN bridge='yes' THEN 1 
            ELSE 0 
        END bridge,
        CASE 
            WHEN oneway='yes' THEN 1 
            ELSE 0
        END oneway,z_order,way as geometry
  FROM planet_osm_line /*WHERE highway IN ('primary',
                                  'primary_link',
                                  'secondary',
                                  'secondary_link',
                                  'tertiary')*/) as foo; 

CREATE TABLE osm_mainroads AS
  SELECT * FROM osm_mainroads_view WHERE type IN ('primary',
                                  'primary_link',
                                  'secondary',
                                  'secondary_link',
                                  'tertiary');
  
ALTER TABLE osm_mainroads ADD PRIMARY KEY (id);

-- create spatial index
CREATE INDEX osm_mainroads_geom ON osm_mainroads USING GIST (geometry);
CLUSTER osm_mainroads_geom ON osm_mainroads;
-- add spatial constraints
ALTER TABLE osm_mainroads ADD CONSTRAINT enforce_dims_geometry CHECK (st_ndims(geometry) = 2);
ALTER TABLE osm_mainroads ADD CONSTRAINT enforce_geotype_geometry CHECK (geometrytype(geometry) = 'LINESTRING'::text OR geometry IS NULL);
ALTER TABLE osm_mainroads ADD CONSTRAINT enforce_srid_geometry CHECK (st_srid(geometry) = 900913);

--VACUUM ANALYZE osm_mainroads;


----------------------------
-- MOTORWAYS
----------------------------

CREATE VIEW osm_motorways_view AS
  SELECT row_number() OVER (ORDER BY osm_id) id, osm_id, name, highway AS type, 
        CASE 
            WHEN tunnel='yes' THEN 1 
            ELSE 0 
        END tunnel,
        CASE 
            WHEN bridge='yes' THEN 1 
            ELSE 0 
        END bridge,
        CASE 
            WHEN oneway='yes' THEN 1 
            ELSE 0 
        END oneway,ref,z_order,way as geometry
 FROM planet_osm_line WHERE highway in ('motorway',
                                 'motorway_link',
                                 'trunk',
                                 'trunk_link');

CREATE TABLE osm_motorways AS
  SELECT * FROM osm_motorways_view;
  
ALTER TABLE osm_motorways ADD PRIMARY KEY (id);

-- create spatial index
CREATE INDEX osm_motorways_geom ON osm_motorways USING GIST (geometry);
CLUSTER osm_motorways_geom ON osm_motorways;
-- add spatial constraints
ALTER TABLE osm_motorways ADD CONSTRAINT enforce_dims_geometry CHECK (st_ndims(geometry) = 2);
ALTER TABLE osm_motorways ADD CONSTRAINT enforce_geotype_geometry CHECK (geometrytype(geometry) = 'LINESTRING'::text OR geometry IS NULL);
ALTER TABLE osm_motorways ADD CONSTRAINT enforce_srid_geometry CHECK (st_srid(geometry) = 900913);

--VACUUM ANALYZE osm_motorways;


----------------------------
-- PLACES
----------------------------

CREATE VIEW osm_places_view AS
  SELECT  row_number() OVER (ORDER BY osm_id) id,  osm_id, name, place as type, z_order, population, way as geometry
  FROM planet_osm_point
  WHERE place in (
            'country',
            'state',
            'region',
            'county',
            'city',
            'town',
            'village',
            'hamlet',
            'suburb',
            'locality') ;

CREATE TABLE osm_places AS
  SELECT * FROM osm_places_view;
  
-- cast population column as an integer
ALTER TABLE osm_places ADD COLUMN population2 integer;
UPDATE osm_places SET population2 = cast(population as integer) WHERE population IS NOT NULL;
ALTER TABLE osm_places DROP COLUMN population;
ALTER TABLE osm_places RENAME COLUMN population2 TO population;
  
ALTER TABLE osm_places ADD PRIMARY KEY (id);

-- create spatial index
CREATE INDEX osm_places_geom ON osm_places USING GIST (geometry);
CLUSTER osm_places_geom ON osm_places;
-- add spatial constraints
ALTER TABLE osm_places ADD CONSTRAINT enforce_dims_geometry CHECK (st_ndims(geometry) = 2);
ALTER TABLE osm_places ADD CONSTRAINT enforce_geotype_geometry CHECK (geometrytype(geometry) = 'POINT'::text OR geometry IS NULL);
ALTER TABLE osm_places ADD CONSTRAINT enforce_srid_geometry CHECK (st_srid(geometry) = 900913); 

--VACUUM ANALYZE osm_places;


---------------------------
-- ROADS
---------------------------

CREATE VIEW osm_roads_view AS
  SELECT row_number() OVER (ORDER BY osm_id) id, * FROM (
  SELECT osm_id, name, bridge, NULL as ref, tunnel, oneway, z_order, type, geometry
  FROM osm_mainroads_view
  UNION ALL  
  SELECT osm_id, name, bridge, ref, tunnel, oneway, z_order, type, geometry
  FROM osm_motorways_view
  ) AS foo;

CREATE TABLE osm_roads AS
  SELECT * FROM osm_roads_view;
  
ALTER TABLE osm_roads ADD PRIMARY KEY (id);

--add CLASS column
ALTER TABLE osm_roads ADD COLUMN class text;
UPDATE osm_roads SET class = 'mainroads' WHERE type IN ('primary',
                                                            'primary_link',
                                                            'secondary',
                                                            'secondary_link',
                                                            'tertiary');
UPDATE osm_roads SET class = 'minorroads' WHERE type IN ('road',
                                                             'path',
                                                             'track',
                                                             'service',
                                                             'footway',
                                                             'bridleway',
                                                             'cycleway',
                                                             'steps',
                                                             'pedestrian',
                                                             'living_street',
                                                             'unclassified',
                                                             'residential');
UPDATE osm_roads SET class = 'motorways' WHERE type IN ('motorway',
                                                            'motorway_link',
                                                            'trunk',
                                                            'trunk_link');  
UPDATE osm_roads SET class = 'railways' WHERE type IN ('rail',
                                                           'tram',
                                                           'light_rail',
                                                           'subway',
                                                           'narrow_gauge',
                                                           'preserved',
                                                           'funicular',
                                                           'monorail');                                                            

-- create spatial index
CREATE INDEX osm_roads_geom ON osm_roads USING GIST (geometry);
CLUSTER osm_roads_geom ON osm_roads;
-- add spatial constraints
ALTER TABLE osm_roads ADD CONSTRAINT enforce_dims_geometry CHECK (st_ndims(geometry) = 2);
ALTER TABLE osm_roads ADD CONSTRAINT enforce_geotype_geometry CHECK (geometrytype(geometry) = 'LINESTRING'::text OR geometry IS NULL);
ALTER TABLE osm_roads ADD CONSTRAINT enforce_srid_geometry CHECK (st_srid(geometry) = 900913);

--VACUUM ANALYZE osm_roads;


----------------------------
-- WATERAREAS
----------------------------

CREATE VIEW osm_waterareas_view AS
  SELECT row_number() OVER (ORDER BY osm_id) id, * FROM (
  SELECT  osm_id, name, waterway AS type, way AS geometry
  FROM planet_osm_polygon
  WHERE waterway IN ('riverbank') 
  UNION ALL 
  SELECT osm_id, name, "natural" AS type, way AS geometry
  FROM planet_osm_polygon
  WHERE "natural" IN ('water')
  UNION ALL 
  SELECT  osm_id, name, landuse AS type, way as geometry
  FROM planet_osm_polygon
  WHERE landuse IN ('basin', 'reservoir')) AS foo;

CREATE TABLE osm_waterareas AS
  SELECT * FROM osm_waterareas_view;
  
ALTER TABLE osm_waterareas ADD PRIMARY KEY (id);

-- create spatial index
CREATE INDEX osm_waterareas_geom ON osm_waterareas USING GIST (geometry);
CLUSTER osm_waterareas_geom ON osm_waterareas;
-- add spatial constraints
ALTER TABLE osm_waterareas ADD CONSTRAINT enforce_dims_geometry CHECK (st_ndims(geometry) = 2);
ALTER TABLE osm_waterareas ADD CONSTRAINT enforce_srid_geometry CHECK (st_srid(geometry) = 900913);

--VACUUM ANALYZE osm_waterareas;