diff --git a/src/create_busstop.sql b/src/create_busstop.sql index 786ce85..044efcf 100644 --- a/src/create_busstop.sql +++ b/src/create_busstop.sql @@ -11,15 +11,63 @@ 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; +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; - SELECT t_busstop.gid, - t_busstop.geom - FROM t_busstop,admarea - WHERE st_within(rail.c_geom, admarea.geom); +-- 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" diff --git a/src/osm/jp/coverage/busstop/ToPostgis.java b/src/osm/jp/coverage/busstop/ToPostgis.java index cb234a4..26a73da 100644 --- a/src/osm/jp/coverage/busstop/ToPostgis.java +++ b/src/osm/jp/coverage/busstop/ToPostgis.java @@ -44,11 +44,11 @@ String name = rset8.getString("name"); Double lat = rset8.getDouble("lat"); Double lon = rset8.getDouble("lon"); - int fixed = rset8.getInt("fixed"); + int score = rset8.getInt("fixed"); int areacode = rset8.getInt("area"); counter++; - String osm_node = "insert into t_busstop(name,fixed,area,geom) VALUES('"+ escapeStr(name) +"',"+ fixed +","+ areacode +", ST_GeomFromText('POINT("+ Double.toString(lon) +" "+ Double.toString(lat) +")', 4612));"; + String osm_node = "insert into t_busstop(name,fixed,area,geom) VALUES('"+ escapeStr(name) +"',"+ score +","+ areacode +", ST_GeomFromText('POINT("+ Double.toString(lon) +" "+ Double.toString(lat) +")', 4612));"; System.out.println(osm_node); this.ow.write(osm_node); this.ow.newLine(); diff --git a/src/osm/jp/postgis/Do_sqlfiles.java b/src/osm/jp/postgis/Do_sqlfiles.java index 8173667..a1d8d02 100644 --- a/src/osm/jp/postgis/Do_sqlfiles.java +++ b/src/osm/jp/postgis/Do_sqlfiles.java @@ -46,8 +46,10 @@ // 既存のreadLine()では\rまたは\n、および\r\nにて改行とみなしている。 String strLine = null; while ((strLine = br.readLine()) != null) { // null=ファイルの最後 + System.out.println(strLine); sqlExecute(con, strLine); } + System.out.println("commit;"); sqlExecute(con, "commit;"); br.close(); fr.close(); diff --git a/src/postgis.properties b/src/postgis.properties index f2dfbfd..4b98375 100644 --- a/src/postgis.properties +++ b/src/postgis.properties @@ -1,4 +1,4 @@ db_driver=org.postgresql.Driver -db_url=jdbc:postgresql://localhost:5432/pgis -db_user=postgres -db_passwd= +db_url=jdbc:postgresql://192.168.0.200:5432/gisdb +db_user=gisuser +db_passwd=gisuser