Fix up OSM Data to work with stylesheets.

fix-osm-data.sql

/* Create missing columns */
alter table planet_osm_line drop column if exists name_en;
alter table planet_osm_line rename column "name:en" to name_en;
update planet_osm_line set name_en = name where name_en is null and name is not null;

alter table planet_osm_point drop column if exists population;
alter table planet_osm_point add column population text;
update planet_osm_point set population = tags->'population' where tags->'population' is not null;

alter table planet_osm_point drop column if exists capital;
alter table planet_osm_point add column capital text;
update planet_osm_point set capital = tags->'capital' where tags->'capital' is not null;

alter table planet_osm_point drop column if exists name_en;
alter table planet_osm_point rename column "name:en" to name_en;
update planet_osm_point set name_en = name where name_en is null and name is not null;

alter table planet_osm_polygon drop column if exists name_en;
alter table planet_osm_polygon rename column "name:en" to name_en;
update planet_osm_polygon set name_en = name where name_en is null and name is not null;

alter table planet_osm_roads drop column if exists name_en;
alter table planet_osm_roads rename column "name:en" to name_en;
update planet_osm_roads set name_en = name where name_en is null and name is not null;

/* Reproject data to EPSG:4326 */
SELECT UpdateGeometrySRID('planet_osm_line', 'way', 4326);
SELECT UpdateGeometrySRID('planet_osm_point', 'way', 4326);
SELECT UpdateGeometrySRID('planet_osm_polygon', 'way', 4326);
SELECT UpdateGeometrySRID('planet_osm_roads', 'way', 4326);

UPDATE planet_osm_line SET way = ST_TRANSFORM(ST_SETSRID(way, 900913), 4326);
UPDATE planet_osm_point SET way = ST_TRANSFORM(ST_SETSRID(way, 900913), 4326);
UPDATE planet_osm_polygon SET way = ST_TRANSFORM(ST_SETSRID(way, 900913), 4326);
UPDATE planet_osm_roads SET way = ST_TRANSFORM(ST_SETSRID(way, 900913), 4326);

/*** Create indices ***/
/* General indices */
CREATE INDEX planet_osm_line_index ON public.planet_osm_line USING gist (way);
CREATE INDEX planet_osm_point_index ON public.planet_osm_point USING gist (way);
CREATE INDEX planet_osm_polygon_index ON public.planet_osm_polygon USING gist (way);
CREATE INDEX planet_osm_roads_index ON public.planet_osm_roads USING gist (way);

/* Line indices */
CREATE INDEX planet_osm_line_runway ON public.planet_osm_line USING gist (way) WHERE ("aeroway" = 'runway' AND "aeroway" IS NOT NULL);
CREATE INDEX planet_osm_line_taxiway ON public.planet_osm_line USING gist (way) WHERE ("aeroway" = 'taxiway' AND "aeroway" IS NOT NULL);

CREATE INDEX planet_osm_line_highway_primary ON public.planet_osm_line USING gist (way) where ("highway" = 'primary' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_secondary ON public.planet_osm_line USING gist (way) where ("highway" = 'secondary' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_tertiary ON public.planet_osm_line USING gist (way) where ("highway" = 'tertiary' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_trunk ON public.planet_osm_line USING gist (way) where ("highway" = 'trunk' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_motorway ON public.planet_osm_line USING gist (way) where ("highway" = 'motorway' AND "highway" IS NOT NULL);

CREATE INDEX planet_osm_line_highway_primary_link ON public.planet_osm_line USING gist (way) WHERE ("highway" = 'primary_link' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_secondary_link ON public.planet_osm_line USING gist (way) WHERE ("highway" = 'secondary_link' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_tertiary_link ON public.planet_osm_line USING gist (way) WHERE ("highway" = 'tertiary_link' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_trunk_link ON public.planet_osm_line USING gist (way) WHERE ("highway" = 'trunk_link' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_motorway_link ON public.planet_osm_line USING gist (way) WHERE ("highway" = 'motorway_link' AND "highway" IS NOT NULL);

CREATE INDEX planet_osm_line_highway_road ON public.planet_osm_line USING gist (way) WHERE ("highway" IN ('road', 'residential', 'unsurfaced', 'living_street', 'unclassified') AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_path ON public.planet_osm_line USING gist (way) WHERE ("highway" IN ('path', 'pedestrian', 'footway', 'steps') AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_line_highway_track ON public.planet_osm_line USING gist (way) WHERE (("highway" IN ('track', 'service') AND "highway" IS NOT NULL) OR ("amenity" = 'parking' AND "amenity" IS NOT NULL));

CREATE INDEX planet_osm_line_railway ON public.planet_osm_line USING gist (way) WHERE ("railway" = 'rail' AND "railway" IS NOT NULL);

CREATE INDEX planet_osm_line_water ON public.planet_osm_line USING gist (way) WHERE ("water" = 'river' AND "water" IS NOT NULL);
CREATE INDEX planet_osm_line_river ON public.planet_osm_line USING gist (way) WHERE ("waterway" IN ('river', 'canal') AND "waterway" IS NOT NULL);
CREATE INDEX planet_osm_line_stream ON public.planet_osm_line USING gist (way) WHERE ("waterway" = 'stream' AND "waterway" IS NOT NULL);
CREATE INDEX planet_osm_line_brook ON public.planet_osm_line USING gist (way) WHERE ("waterway" IN ('brook', 'ditch', 'drain') AND "waterway" IS NOT NULL);

/* Point indices */
CREATE INDEX planet_osm_point_country ON public.planet_osm_point USING gist (way) WHERE ("place" = 'country' AND "place" IS NOT NULL);
CREATE INDEX planet_osm_point_state ON public.planet_osm_point USING gist (way) WHERE ("place" IN ('state', 'region', 'province', 'district', 'county') AND "place" IS NOT NULL);
CREATE INDEX planet_osm_point_capital_city ON public.planet_osm_point USING gist (way) WHERE (("place" = 'city' AND "place" IS NOT NULL) AND ("capital" = 'yes' AND "capital" IS NOT NULL));
CREATE INDEX planet_osm_point_normal_city ON public.planet_osm_point USING gist (way) WHERE (("place" = 'city' AND "place" IS NOT NULL) AND ("capital" != 'yes' AND "capital" is NOT NULL));
CREATE INDEX planet_osm_point_town ON public.planet_osm_point USING gist (way) WHERE ("place" IN ('town', 'village') AND "place" IS NOT NULL);
CREATE INDEX planet_osm_point_suburb ON public.planet_osm_point USING gist (way) WHERE ("place" IN ('suburb', 'borough', 'quarter', 'neighbourhood') AND "place" IS NOT NULL);

/* Polygon indices */
CREATE INDEX planet_osm_polygon_aeroway ON public.planet_osm_polygon USING gist (way) WHERE ("aeroway" IN ('aerodrome', 'apron', 'apron_disused', 'control_tower', 'hangar', 'helipad', 'runway', 'taxiway', 'terminal') AND "aeroway" IS NOT NULL);

CREATE INDEX planet_osm_polygon_amenity ON public.planet_osm_polygon USING gist (way) WHERE ("amenity" IN ('university', 'college', 'school', 'hospital', 'kindergarten') AND "amenity" IS NOT NULL);
CREATE INDEX planet_osm_polygon_cemetery ON public.planet_osm_polygon USING gist (way) WHERE (("landuse" IN ('cemetery', 'grave_yard') AND "landuse" IS NOT NULL) OR ("amenity" = 'grave_yard' AND "amenity" IS NOT NULL));
CREATE INDEX planet_osm_polygon_quarry ON public.planet_osm_polygon USING gist (way) WHERE ("landuse" = 'quarry' AND "landuse" IS NOT NULL);

CREATE INDEX planet_osm_polygon_landuse_military ON public.planet_osm_polygon USING gist (way) WHERE ("landuse" = 'military' AND "landuse" IS NOT NULL);
CREATE INDEX planet_osm_polygon_military_barracks ON public.planet_osm_polygon USING gist (way) WHERE ("military" = 'barracks' AND "military" IS NOT NULL);
CREATE INDEX planet_osm_polygon_military_danger_area ON public.planet_osm_polygon USING gist (way) WHERE ("military" = 'danger_area' AND "military" IS NOT NULL);

CREATE INDEX planet_osm_polygon_power_station ON public.planet_osm_polygon USING gist (way) WHERE ("power" = 'station' AND "power" IS NOT NULL);
CREATE INDEX planet_osm_polygon_power_sub_station ON public.planet_osm_polygon USING gist (way) WHERE ("power" = 'sub_station' AND "power" IS NOT NULL);

CREATE INDEX planet_osm_polygon_national_park ON public.planet_osm_polygon USING gist (way) WHERE ("boundary" = 'national_park' AND "boundary" IS NOT NULL);
CREATE INDEX planet_osm_polygon_leisure ON public.planet_osm_polygon USING gist (way) WHERE ("leisure" IN ('park', 'playground', 'recreation_ground', 'garden', 'golf_course') AND "leisure" IS NOT NULL);
CREATE INDEX planet_osm_polygon_tourism ON public.planet_osm_polygon USING gist (way) WHERE ("tourism" IN ('attraction', 'camp_site', 'caravan_site', 'picnic_site') AND "tourism" IS NOT NULL);

CREATE INDEX planet_osm_polygon_marina ON public.planet_osm_polygon USING gist (way) WHERE (("leisure" = 'marina' AND "leisure" IS NOT NULL) OR ("man_made" = 'pier' AND "man_made" IS NOT NULL));
CREATE INDEX planet_osm_polygon_river ON public.planet_osm_polygon USING gist (way) WHERE ("water" = 'river' AND "water" IS NOT NULL);
CREATE INDEX planet_osm_polygon_riverbank ON public.planet_osm_polygon USING gist (way) WHERE ("waterway" = 'riverbank' AND "waterway" IS NOT NULL);
CREATE INDEX planet_osm_polygon_natural_water ON public.planet_osm_polygon USING gist (way) WHERE ("natural" = 'water' AND "natural" IS NOT NULL);
CREATE INDEX planet_osm_polygon_natural_bay ON public.planet_osm_polygon USING gist (way) WHERE ("natural" = 'bay' AND "natural" IS NOT NULL);
CREATE INDEX planet_osm_polygon_landuse_water ON public.planet_osm_polygon USING gist (way) WHERE ("landuse" IN ('reservoir', 'basin') AND "landuse" IS NOT NULL);

/* Roads indices */
CREATE INDEX planet_osm_roads_highway_primary ON public.planet_osm_roads USING gist (way) WHERE ("highway" = 'primary' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_roads_highway_secondary ON public.planet_osm_roads USING gist (way) WHERE ("highway" = 'secondary' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_roads_highway_trunk ON public.planet_osm_roads USING gist (way) WHERE ("highway" = 'trunk' AND "highway" IS NOT NULL);
CREATE INDEX planet_osm_roads_highway_motorway ON public.planet_osm_roads USING gist (way) WHERE ("highway" = 'motorway' AND "highway" IS NOT NULL);