diff --git a/src/ImportGML_FUEL.sh b/src/ImportGML_FUEL.sh
index 9aa57bb..d246691 100755
--- a/src/ImportGML_FUEL.sh
+++ b/src/ImportGML_FUEL.sh
@@ -8,6 +8,7 @@
java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToCartoCSV
java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.csv
java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Coverage coverage.fuel.json
+git commit -a -m "coverage fuel update $(date +%Y-%m-%d_%H:%M)"
cp coverage.fuel.json ../www-root/coverage
cd ../www-root
git add .
diff --git a/src/busstop.activity.violet.html b/src/busstop.activity.violet.html
new file mode 100644
index 0000000..c9be366
--- /dev/null
+++ b/src/busstop.activity.violet.html
@@ -0,0 +1,2161 @@
+
+
+
+
+
+
+
+
+ This file was generated with Violet UML Editor 2.1.0.
+ ( View Source / Download Violet )
+
+
+
+
+
+ 
+
+
\ No newline at end of file
diff --git a/src/db.busstop.class.violet.html b/src/db.busstop.class.violet.html
new file mode 100644
index 0000000..1fb908a
--- /dev/null
+++ b/src/db.busstop.class.violet.html
@@ -0,0 +1,1094 @@
+
+
+
+
+
+
+
+
+ This file was generated with Violet UML Editor 2.1.0.
+ ( View Source / Download Violet )
+
+
+
+
+
+ 
+
+
\ No newline at end of file
diff --git a/src/osm/jp/coverage/busstop/DbBusstop.java b/src/osm/jp/coverage/busstop/DbBusstop.java
index 8a761c3..a87a435 100644
--- a/src/osm/jp/coverage/busstop/DbBusstop.java
+++ b/src/osm/jp/coverage/busstop/DbBusstop.java
@@ -1,15 +1,22 @@
package osm.jp.coverage.busstop;
import java.io.*;
+import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
+import javax.xml.parsers.ParserConfigurationException;
import jp.co.areaweb.tools.database.*;
+import org.xml.sax.SAXException;
public class DbBusstop {
+ public static final String TABLE_NAME = "bus_stop";
+ public static boolean UPDATE = false;
+ public static boolean DROP = false;
+
File inputFile;
String filter = "";
int iCounter = 0;
@@ -24,16 +31,61 @@
* @throws SQLException
* @throws ClassNotFoundException
* @throws FileNotFoundException */
- public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException
+ public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException
{
- Connection con = null;
+ for (String arg : args) {
+ if (arg.toUpperCase().equals("-UPDATE")) {
+ UPDATE = true;
+ }
+ if (arg.toUpperCase().equals("-DROP")) {
+ DROP = true;
+ }
+ }
+
+ // HSQLディレクトリがなければエラー
+ File dbdir = new File("database");
+ if (!dbdir.isDirectory()) {
+ throw new FileNotFoundException("Directory 'database' is not found.");
+ }
+
+ Connection conHsql = null;
+ Connection conPost = null;
try {
- con = DatabaseTool.openDb("database");
- DbBusstop.export(con);
+ conHsql = DatabaseTool.openDb("database");
+ if (DROP) {
+ dropTable(conHsql);
+ if (UPDATE) {
+ create(conHsql);
+ }
+ }
+ if (!UPDATE) {
+ create(conHsql);
+ }
+
+ try (PreparedStatement ps = conHsql.prepareStatement("DELETE FROM "+ TABLE_NAME)) {
+ ps.executeUpdate();
+ }
+
+ if (UPDATE) {
+ conPost = DatabaseTool.openDb("postgis");
+ transport(con, conPost);
+ }
+ else {
+ File gmlDir = new File("GML_FUEL");
+ for (File gmlFile : gmlDir.listFiles()) {
+ if (checkGMLfile(gmlFile)) {
+ int index = "P07-15_".length();
+ int areacode = Integer.parseInt(gmlFile.getName().substring(index, index+2));
+ inputFile(con, gmlFile, areacode);
+ }
+ }
+ }
+
+ DbBusstop.export(conHsql);
}
finally {
- if (con != null) {
- DatabaseTool.closeDb(con);
+ if (conHsql != null) {
+ DatabaseTool.closeDb(conHsql);
}
}
}
@@ -41,27 +93,24 @@
/**
* 'table.BUS_STOP'を新規に作る
* 既にテーブルが存在する時には何もしない
- * @param con
+ * @param conHsql
* @throws SQLException
*/
- public static void create(Connection con) throws SQLException {
+ public static void create(Connection conHsql) throws SQLException {
String createSt;
// 'table.BUS_STOP'を新規に作る
- drop(con, "bus_stop");
- createSt = "CREATE TABLE bus_stop (idref VARCHAR(12) NOT NULL, name VARCHAR(128), kana VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, area INT, ifile VARCHAR(128), CONSTRAINT bus_stop_pk PRIMARY KEY(idref));";
- create(con, createSt);
+ createSt = "CREATE TABLE bus_stop (idref VARCHAR(12) NOT NULL, gid LONG, name VARCHAR(128), kana VARCHAR(128), lat DOUBLE, lon DOUBLE, fixed INT, fixed1 INT, area INT, ifile VARCHAR(128), up INT, CONSTRAINT bus_stop_pk PRIMARY KEY(idref));";
+ create(conHsql, createSt);
- drop(con, "existing_data");
- createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));";
- create(con, createSt);
+ //createSt = "CREATE TABLE existing_data (idref VARCHAR(12) NOT NULL, name VARCHAR(128), lat DOUBLE, lon DOUBLE, score INT, CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));";
+ //create(conHsql, createSt);
- drop(con, "coverage");
- createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);";
- create(con, createSt);
+ //createSt = "CREATE TABLE coverage (area INT, name VARCHAR(128), denominator BIGINT, lv1 BIGINT, lv2 BIGINT, lv3 BIGINT);";
+ //create(conHsql, createSt);
}
- public static void create(Connection con, String createsql) throws SQLException {
+ static void create(Connection con, String createsql) throws SQLException {
System.out.println(createsql);
try (PreparedStatement ps = con.prepareStatement(createsql)) {
ps.executeUpdate();
@@ -76,10 +125,19 @@
/**
* 'table.BUS_STOP'を削除する
- * @param con
- * @param tableName
+ * @param conHsql
+ * @throws java.io.FileNotFoundException
+ * @throws java.lang.ClassNotFoundException
* @throws SQLException
+ * @throws javax.xml.parsers.ParserConfigurationException
+ * @throws org.xml.sax.SAXException
*/
+ public static void dropTable (Connection conHsql) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
+ drop(conHsql, "bus_stop");
+ //drop(conHsql, "existing_data");
+ //drop(conHsql, "coverage");
+ }
+
public static void drop(Connection con, String tableName) throws SQLException {
String createSt = "DROP TABLE "+ tableName +";";
System.out.println(createSt);
@@ -93,27 +151,48 @@
}
}
}
-
+
/**
- * 'table.BUS_STOP'の内容を空にする
- * @param con
- * @param tableName
- * @throws SQLException
+ * HSQLDB: "SELECT idref,area,fixed,lat,lon FROM FUEL"
+ * POSTGIS "insert into t_FUEL(idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));"
+ *
+ * @param conHsql
+ * @param conPost
+ * @throws java.io.FileNotFoundException
+ * @throws java.lang.ClassNotFoundException
+ * @throws java.sql.SQLException
+ * @throws java.io.IOException
+ * @throws javax.xml.parsers.ParserConfigurationException
+ * @throws org.xml.sax.SAXException
*/
- public static void clear(Connection con, String tableName) throws SQLException {
- String createSt = "DELETE FROM "+ tableName +";";
- System.out.println(createSt);
- try (PreparedStatement ps = con.prepareStatement(createSt)) {
- ps.executeUpdate();
- }
- catch (SQLSyntaxErrorException e) {
- System.out.println("107:"+ e.toString());
- if (!(e.toString().startsWith("java.sql.SQLSyntaxErrorException: user lacks privilege or object not found:"))) {
- throw e;
+ public static void transport (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
+ try (PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,area,fixed,ST_Y(geom) AS lat,ST_X(geom) AS lon FROM t_busstop")) {
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ while (rset1.next()) {
+ long gid = rset1.getLong("gid");
+ String name = rset1.getString("name");
+ int area = rset1.getInt("area");
+ int fixed = rset1.getInt("fixed");
+ double lat = rset1.getDouble("lat");
+ double lon = rset1.getDouble("lon");
+
+ String sqlStr = "INSERT INTO bus_stop (gid,lat,lon,fixed,idref,area,up,fixed1) VALUES(?,?,?,?,?,?,0,0)";
+ try (PreparedStatement ps = conHsql.prepareStatement(sqlStr)) {
+ ps.setLong(1, gid);
+ ps.setString(2, BigDecimal.valueOf(lat).toPlainString());
+ ps.setString(3, BigDecimal.valueOf(lon).toPlainString());
+ ps.setInt(4, fixed);
+ ps.setString(5, idref);
+ ps.setInt(6, area);
+ System.out.println(sqlStr);
+ ps.executeUpdate();
+ }
+ }
}
}
}
+
/**
* 'table.BUS_STOP'の内容をCSV形式にして標準出力に出力する
* @param con
diff --git a/src/t_BUSSTOP.sql b/src/t_BUSSTOP.sql
new file mode 100644
index 0000000..1b8cf09
--- /dev/null
+++ b/src/t_BUSSTOP.sql
@@ -0,0 +1,63 @@
+---<< postgis: postgres >>---
+db_driver=org.postgresql.Driver
+db_url=jdbc:postgresql://surveyor.mydns.jp:5432/gisdb
+db_user=gisuser
+db_passwd=gisuser
+-------------------------
+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);
+
+insert into t_busstop(osm_id,name,geom) select osm_id, st_transform(way,4612) FROM planet_osm_point WHERE (highway='bus_stop');
+insert into t_busstop(osm_id,name,geom) VALUES(884, 'なまえ', ST_GeomFromText('POINT(139.4110836 35.4341053)', 4612));
+
+----
+CREATE TABLE t_test (
+ gid SERIAL PRIMARY KEY,
+ osm_id bigint,
+ name text,
+ geom GEOMETRY(POINT, 4612)
+);
+
+CREATE INDEX ix_test_geom ON t_test USING GiST (geom);
+
+insert into t_test(osm_id,name,geom) VALUES(884, 'なまえ', ST_GeomFromText('POINT(139.4110836 35.4341053)', 4612));
+
+----
+
+---<< database: hsqldb >>---
+CREATE TABLE bus_stop (
+ idref VARCHAR(12) NOT NULL,
+ name VARCHAR(128),
+ kana VARCHAR(128),
+ lat DOUBLE,
+ lon DOUBLE,
+ fixed INT,
+ fixed1 INT,
+ area INT,
+ ifile VARCHAR(128),
+ up INT,
+ CONSTRAINT bus_stop_pk PRIMARY KEY(idref));
+
+CREATE TABLE existing_data (
+ idref VARCHAR(12) NOT NULL,
+ name VARCHAR(128),
+ lat DOUBLE,
+ lon DOUBLE,
+ score INT,
+ CONSTRAINT existing_pk PRIMARY KEY(idref, lat, lon));
+
+CREATE TABLE coverage (
+ area INT,
+ name VARCHAR(128),
+ denominator BIGINT,
+ lv1 BIGINT,
+ lv2 BIGINT,
+ lv3 BIGINT);
+
+-------------------------
\ No newline at end of file