--
-- Creating tables
--
-- Modified by Geoffrey Yu-kai SHEA
-- 05-APR-2026
--

DROP TABLE IF EXISTS "aero-poly";
CREATE TABLE "aero-poly" AS ( 
  SELECT way,aeroway 
  FROM planet_osm_polygon
  WHERE "aeroway" IN ('apron','runway','taxiway','helipad')
  ORDER BY z_order ASC );
CREATE INDEX "aero-poly_way_idx" ON "aero-poly" USING gist (way);

DROP TABLE IF EXISTS "agriculture";
CREATE TABLE "agriculture" AS ( 
  SELECT way,landuse FROM planet_osm_polygon
  WHERE "landuse" IN ('allotments','farm','farmland','farmyard',
                      'orchard','vineyard')
  ORDER BY z_order ASC );
CREATE INDEX "agriculture_way_idx" ON "agriculture" USING gist (way);

DROP TABLE IF EXISTS "amenity-areas";
CREATE TABLE "amenity-areas" AS (
  SELECT way,amenity FROM planet_osm_polygon
  WHERE amenity IN ('hospital','college','school','university') 
  AND (building IS NULL OR building NOT IN ('no'))
);
CREATE INDEX "amenity-areas_way_idx" ON "amenity-areas" USING gist (way);

DROP TABLE IF EXISTS "beach";
CREATE TABLE "beach" AS ( 
  SELECT way,"natural"
  FROM planet_osm_polygon
  WHERE "natural" = 'beach'
  ORDER BY z_order ASC
);
CREATE INDEX "beach_way_idx" ON "beach" USING gist (way);

DROP TABLE IF EXISTS "building";
CREATE TABLE "building" AS ( 
  SELECT way,building,aeroway
  FROM planet_osm_polygon
  WHERE ("building" IS NOT NULL
  AND "building" != 'no')
  OR "aeroway" IN ('terminal')
  ORDER BY z_order ASC 
);
CREATE INDEX "building_way_idx" ON "building" USING gist (way);

DROP TABLE IF EXISTS "forest";
CREATE TABLE "forest" AS ( SELECT *, (CASE 
            WHEN way_area >= 10000000 THEN 'huge'
            WHEN way_area >= 1000000 THEN 'large'
            WHEN way_area >= 100000 THEN 'medium'
            ELSE 'small' END) AS size FROM planet_osm_polygon
          WHERE "natural" IN ('wood') OR "landuse" IN ('forest','wood')
          ORDER BY z_order ASC );
CREATE INDEX "forest_way_idx" ON "forest" USING gist (way);

DROP TABLE IF EXISTS "grass";
CREATE TABLE "grass" AS ( SELECT way,landuse, (CASE 
            WHEN way_area >= 10000000 THEN 'huge'
            WHEN way_area >= 1000000 THEN 'large'
            WHEN way_area >= 100000 THEN 'medium'
            ELSE 'small' END) AS size FROM planet_osm_polygon
          WHERE "landuse" IN ('grass', 'greenfield', 'meadow')
            OR "natural" IN ('fell', 'heath', 'scrub')
          ORDER BY z_order ASC 
        );
CREATE INDEX "grass_way_idx" ON "grass" USING gist (way);

DROP TABLE IF EXISTS "highway-label";
CREATE TABLE "highway-label" AS (
  SELECT way,name,highway, 
    CASE WHEN oneway IN ('yes','true','1') THEN 'yes'::text END AS oneway
  FROM planet_osm_line
  WHERE "highway" IS NOT NULL
  AND ("name" IS NOT NULL OR "oneway" IS NOT NULL)
  ORDER BY z_order ASC 
);
CREATE INDEX "highway-label_way_idx" ON "highway-label" USING gist (way);

DROP TABLE IF EXISTS "park";
CREATE TABLE "park" AS ( SELECT *, (CASE 
            WHEN way_area >= 10000000 THEN 'huge'
            WHEN way_area >= 1000000 THEN 'large'
            WHEN way_area >= 100000 THEN 'medium'
            ELSE 'small' END) AS size  FROM planet_osm_polygon
          WHERE "leisure" IN ('dog_park', 'golf_course', 'pitch', 'park',
            'playground', 'garden', 'common')
            OR "landuse" IN ('allotments', 'cemetery','recreation_ground', 'village_green')
          ORDER BY z_order asc );
CREATE INDEX "park_way_idx" ON "park" USING gist (way);

DROP TABLE IF EXISTS "parking-area";
CREATE TABLE "parking-area" AS (
  SELECT way,amenity 
  FROM planet_osm_polygon
  WHERE amenity = 'parking'
);
CREATE INDEX "parking-area_way_idx" ON "parking-area" USING gist (way);

DROP TABLE IF EXISTS "placenames-medium";
CREATE TABLE "placenames-medium" AS ( 
  SELECT way,place,name
  FROM planet_osm_point
  WHERE place IN ('city','metropolis','town','large_town','small_town')
);
CREATE INDEX "placenames-medium_way_idx" ON "placenames-medium" USING gist (way);

DROP TABLE IF EXISTS "route-bridge-0";
CREATE TABLE "route-bridge-0" AS ( 
  SELECT way,highway,railway,aeroway,tunnel
  FROM planet_osm_line
  WHERE ( "highway" IS NOT NULL
  OR "railway" IS NOT NULL
  OR "aeroway" IN ('apron','runway','taxiway') )
  AND bridge IN ('yes','true','1','viaduct')
  AND (layer IS NULL OR layer = '0')
  ORDER BY z_order asc
 );
CREATE INDEX "route-bridge-0_way_idx" ON "route-bridge-0" USING gist (way);

DROP TABLE IF EXISTS "route-bridge-1";
CREATE TABLE "route-bridge-1" AS ( 
  SELECT way,highway,railway,aeroway,tunnel
  FROM planet_osm_line
  WHERE ( "highway" IS NOT NULL
  OR "railway" IS NOT NULL
  OR "aeroway" IN ('apron','runway','taxiway') )
  AND bridge IN ('yes','true','1','viaduct')
  AND layer = '1'
  ORDER BY z_order asc
 );
CREATE INDEX "route-bridge-1_way_idx" ON "route-bridge-1" USING gist (way);

DROP TABLE IF EXISTS "route-bridge-2";
CREATE TABLE "route-bridge-2" AS ( 
  SELECT way,highway,railway,aeroway,tunnel
  FROM planet_osm_line
  WHERE ( "highway" IS NOT NULL
  OR "railway" IS NOT NULL
  OR "aeroway" IN ('apron','runway','taxiway') )
  AND bridge IN ('yes','true','1','viaduct')
  AND layer = '2'
  ORDER BY z_order asc
 );
CREATE INDEX "route-bridge-2_way_idx" ON "route-bridge-2" USING gist (way);

DROP TABLE IF EXISTS "route-bridge-3";
CREATE TABLE "route-bridge-3" AS ( 
  SELECT way,highway,railway,aeroway,tunnel
  FROM planet_osm_line
  WHERE ( "highway" IS NOT NULL
  OR "railway" IS NOT NULL
  OR "aeroway" IN ('apron','runway','taxiway') )
  AND bridge IN ('yes','true','1','viaduct')
  AND layer = '3'
  ORDER BY z_order asc
 );
CREATE INDEX "route-bridge-3_way_idx" ON "route-bridge-3" USING gist (way);

DROP TABLE IF EXISTS "route-bridge-4";
CREATE TABLE "route-bridge-4" AS ( 
  SELECT way,highway,railway,aeroway,tunnel
  FROM planet_osm_line
  WHERE ( "highway" IS NOT NULL
  OR "railway" IS NOT NULL
  OR "aeroway" IN ('apron','runway','taxiway') )
  AND bridge IN ('yes','true','1','viaduct')
  AND layer = '4'
  ORDER BY z_order asc
 );
CREATE INDEX "route-bridge-4_way_idx" ON "route-bridge-4" USING gist (way);

DROP TABLE IF EXISTS "route-bridge-5";
CREATE TABLE "route-bridge-5" AS ( 
  SELECT way,highway,railway,aeroway,tunnel
  FROM planet_osm_line
  WHERE ( "highway" IS NOT NULL
  OR "railway" IS NOT NULL
  OR "aeroway" IN ('apron','runway','taxiway') )
  AND bridge IN ('yes','true','1','viaduct')
  AND layer = '5'
  ORDER BY z_order asc
 );
CREATE INDEX "route-bridge-5_way_idx" ON "route-bridge-5" USING gist (way);

DROP TABLE IF EXISTS "route-fill";
CREATE TABLE "route-fill" AS ( 
  SELECT way, highway, horse, bicycle, foot, 
    aeroway,
    case when tunnel in ('yes', 'true', '1')
      then 'yes'::text
      else tunnel end as tunnel,
    case when bridge in ('yes','true','1','viaduct')
      then 'yes'::text else bridge end as bridge,
    case when railway in ('spur','siding')
      or (railway='rail' and service in ('spur','siding','yard'))
      then 'spur-siding-yard'::text else railway
      end as railway,
    case when service in 
      ('parking_aisle', 'drive-through', 'driveway')
      then 'INT-minor'::text else service 
      end as service
  FROM planet_osm_line
  WHERE highway IS NOT NULL
    OR aeroway IN ( 'runway','taxiway' )
    OR railway IN ( 'light_rail', 'narrow_gauge', 'funicular',
      'rail', 'subway', 'tram', 'spur', 'siding', 'platform',
      'disused', 'abandoned', 'construction', 'miniature' )
  ORDER BY z_order);
CREATE INDEX "route-fill_way_idx" ON "route-fill" USING gist (way);

DROP TABLE IF EXISTS "route-line";
CREATE TABLE "route-line" AS ( 
  SELECT way,highway,aeroway,
    case when tunnel IN ( 'yes', 'true', '1' ) then 'yes'::text
      else 'no'::text end as tunnel,
    case when service IN ( 'parking_aisle',
      'drive-through','driveway' ) then 'INT-minor'::text
      else service end as service
  FROM planet_osm_line
  WHERE highway in ( 'motorway', 'motorway_link',
    'trunk', 'trunk_link', 'primary', 'primary_link',
    'secondary', 'secondary_link', 'tertiary', 'tertiary_link', 
    'residential', 'unclassified', 'road', 'service',
    'pedestrian', 'raceway', 'living_street' )
  OR "aeroway" IN ('apron','runway','taxiway')
  ORDER BY z_order);
CREATE INDEX "route-line_way_idx" ON "route-line" USING gist (way);

DROP TABLE IF EXISTS "route-tunnels";
CREATE TABLE "route-tunnels" AS ( 
  SELECT way,highway FROM planet_osm_line 
  WHERE highway IN
  ( 'motorway', 'motorway_link', 'trunk', 'trunk_link', 
    'primary', 'primary_link', 'secondary', 'secondary_link',
    'tertiary', 'tertiary_link', 'residential', 'unclassified' )
  AND tunnel IN ( 'yes', 'true', '1' )
  ORDER BY z_order );
CREATE INDEX "route-tunnels_way_idx" ON "route-tunnels" USING gist (way);

DROP TABLE IF EXISTS "route-turning-circles";
CREATE TABLE "route-turning-circles" AS ( SELECT highway,way FROM planet_osm_point
  WHERE "highway" = 'turning_circle' );
CREATE INDEX "route-turning-circles_way_idx" ON "route-turning-circles" USING gist (way);

DROP TABLE IF EXISTS "water-outline";
CREATE TABLE "water-outline" AS ( 
  SELECT "natural", "landuse", "waterway", "way"
  FROM planet_osm_polygon
  WHERE "natural" IN ('lake','water')
  OR "waterway" IN ('canal','mill_pond','riverbank')
  OR "landuse" IN ('basin','reservoir','water')
  ORDER BY z_order ASC
);
CREATE INDEX "water-outline_way_idx" ON "water-outline" USING gist (way);

DROP TABLE IF EXISTS "water";
CREATE TABLE "water" AS ( 
  SELECT "natural", "landuse", "waterway", "way"
  FROM planet_osm_polygon
  WHERE "natural" IN ('lake','water')
  OR "waterway" IN ('canal','mill_pond','riverbank')
  OR "landuse" IN ('basin','reservoir','water')
  ORDER BY z_order asc
);
CREATE INDEX "water_way_idx" ON "water" USING gist (way);

DROP TABLE IF EXISTS "wetland";
CREATE TABLE "wetland" AS (
  SELECT way,"natural" FROM planet_osm_polygon
  WHERE "natural" IN ('marsh','wetland')
);
CREATE INDEX "wetland_way_idx" ON "wetland" USING gist (way);
