osmCoverage/src/create_busstop.sql at 9292b65b9aeefcc35fbf79ac0528fc7e9456747d

Fork: 0
coverage / osmCoverage
Find file
Newer
Older
osmCoverage / src / create_busstop.sql
yuuhayashi on 30 Jan 2017 6 KB 完成
Raw Blame History
-- DROP TABLE t_busstop;
CREATE TABLE t_busstop (gid SERIAL PRIMARY KEY,name text,fixed integer,area integer,geom GEOMETRY(POINT, 4612));
CREATE INDEX ix_busstop_geom ON t_busstop USING GiST (geom);
 
-- DROP VIEW public."V_14kanagawa_busstop";
CREATE OR REPLACE VIEW public."V_14kanagawa_busstop" AS 
 SELECT admarea.gid,
    (( SELECT fixed FROM t_busstop WHERE st_within(t_busstop.geom, admarea.geom)))::integer AS stncnt,
    admarea.geom,
    admarea.n03_004 AS name
 FROM admarea;
 
-- DROP VIEW public."V_busstop_0";
CREATE OR REPLACE VIEW public."V_busstop_0" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE fixed=0;
CREATE OR REPLACE VIEW public."V_busstop_1" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE fixed>0 AND fixed<50;
CREATE OR REPLACE VIEW public."V_busstop_2" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE fixed>49;
 
-- DROP VIEW public."V_busstop_14";
CREATE OR REPLACE VIEW public."V_busstop_01" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=1;
CREATE OR REPLACE VIEW public."V_busstop_02" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=2;
CREATE OR REPLACE VIEW public."V_busstop_03" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=3;
CREATE OR REPLACE VIEW public."V_busstop_04" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=4;
CREATE OR REPLACE VIEW public."V_busstop_05" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=5;
CREATE OR REPLACE VIEW public."V_busstop_06" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=6;
CREATE OR REPLACE VIEW public."V_busstop_07" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=7;
CREATE OR REPLACE VIEW public."V_busstop_08" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=8;
CREATE OR REPLACE VIEW public."V_busstop_09" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=9;
CREATE OR REPLACE VIEW public."V_busstop_10" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=10;
CREATE OR REPLACE VIEW public."V_busstop_11" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=11;
CREATE OR REPLACE VIEW public."V_busstop_12" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=12;
CREATE OR REPLACE VIEW public."V_busstop_13" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=13;
CREATE OR REPLACE VIEW public."V_busstop_14" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=14;
CREATE OR REPLACE VIEW public."V_busstop_15" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=15;
CREATE OR REPLACE VIEW public."V_busstop_16" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=16;
CREATE OR REPLACE VIEW public."V_busstop_17" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=17;
CREATE OR REPLACE VIEW public."V_busstop_18" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=18;
CREATE OR REPLACE VIEW public."V_busstop_19" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=19;
CREATE OR REPLACE VIEW public."V_busstop_20" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=20;
CREATE OR REPLACE VIEW public."V_busstop_21" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=21;
CREATE OR REPLACE VIEW public."V_busstop_22" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=22;
CREATE OR REPLACE VIEW public."V_busstop_23" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=23;
CREATE OR REPLACE VIEW public."V_busstop_24" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=24;
CREATE OR REPLACE VIEW public."V_busstop_25" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=25;
CREATE OR REPLACE VIEW public."V_busstop_26" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=26;
CREATE OR REPLACE VIEW public."V_busstop_27" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=27;
CREATE OR REPLACE VIEW public."V_busstop_28" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=28;
CREATE OR REPLACE VIEW public."V_busstop_29" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=29;
CREATE OR REPLACE VIEW public."V_busstop_30" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=30;
CREATE OR REPLACE VIEW public."V_busstop_31" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=31;
CREATE OR REPLACE VIEW public."V_busstop_32" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=32;
CREATE OR REPLACE VIEW public."V_busstop_33" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=33;
CREATE OR REPLACE VIEW public."V_busstop_34" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=34;
CREATE OR REPLACE VIEW public."V_busstop_35" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=35;
CREATE OR REPLACE VIEW public."V_busstop_36" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=36;
CREATE OR REPLACE VIEW public."V_busstop_37" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=37;
CREATE OR REPLACE VIEW public."V_busstop_38" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=38;
CREATE OR REPLACE VIEW public."V_busstop_39" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=39;
CREATE OR REPLACE VIEW public."V_busstop_40" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=40;
CREATE OR REPLACE VIEW public."V_busstop_41" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=41;
CREATE OR REPLACE VIEW public."V_busstop_42" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=42;
CREATE OR REPLACE VIEW public."V_busstop_43" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=43;
CREATE OR REPLACE VIEW public."V_busstop_44" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=44;
CREATE OR REPLACE VIEW public."V_busstop_45" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=45;
CREATE OR REPLACE VIEW public."V_busstop_46" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=46;
CREATE OR REPLACE VIEW public."V_busstop_47" AS SELECT gid,name,fixed,area,geom FROM t_busstop WHERE area=47;
 
 
 
-- ADMAREA
 SELECT t_busstop.gid, t_busstop.geom FROM t_busstop,admarea WHERE st_within(rail.c_geom, admarea.geom);
  
  
ALTER TABLE public."V_kanagawa"
  OWNER TO yuu;