{
+ String gmlid = "";
+ String idref = "";
+ int area = 0;
+ int fixed = 0;
+ int code = 0;
+ double lat = 0.0D;
+ double lon = 0.0D;
+
+ public static void main() {
+ PostgisItems items = new PostgisItems();
+ items.add(new PostgisItem("gmlid", "gmlid"));
+ items.add(new PostgisItem("idref", "idref"));
+ items.add(new PostgisItem("area", "area"));
+ items.add(new PostgisItem("fixed", "fixed1"));
+ items.add(new PostgisItem("code", "code"));
+ }
+
+ public PostgisItems() {
+ super();
+ }
+
+ public void setResuit(ResultSet rset) throws SQLException {
+ for (PostgisItem item : this) {
+ switch (item.postName) {
+ case "gmlid":
+ gmlid = rset.getString(item.localName);
+ break;
+ case "idref":
+ idref = rset.getString(item.localName);
+ break;
+ case "area":
+ area = rset.getInt(item.localName);
+ break;
+ case "fixed":
+ fixed = rset.getInt(item.localName);
+ break;
+ case "code":
+ code = rset.getInt(item.localName);
+ break;
+ default:
+ break;
+ }
+ }
+ lat = rset.getDouble("lat");
+ lon = rset.getDouble("lon");
+ }
+
+ public String getSqlStr(String tableName) {
+ StringBuilder sb = new StringBuilder();
+ sb.append("SELECT ");
+ for (PostgisItem item : this) {
+ sb.append(item.localName);
+ sb.append(",");
+ }
+ sb.append("lat,lon FROM "+ tableName);
+ return sb.toString();
+ }
+
+ public String getGeomStr() {
+ return String.format(
+ "ST_GeomFromText('POINT(%s %s)', 4612)",
+ BigDecimal.valueOf(lon).toPlainString(),
+ BigDecimal.valueOf(lat).toPlainString()
+ );
+ }
+}
diff --git a/src/osm/jp/postgis/ToGeoJSON.java b/src/osm/jp/postgis/ToGeoJSON.java
index 90f50d6..eb1989f 100644
--- a/src/osm/jp/postgis/ToGeoJSON.java
+++ b/src/osm/jp/postgis/ToGeoJSON.java
@@ -47,14 +47,14 @@
public String tableName;
public String sqlForm1 = "SELECT row_to_json(feature) FROM ("
- + "select 'Feature' As type, "
- + "ST_AsGeoJSON(ST_Transform(t_%s.geom,4326))::json As geometry, "
- + "row_to_json(("
- + "SELECT p FROM (SELECT t_%s.idref, t_%s.fixed) AS p"
- + ")) AS properties From t_%s %s order by area,idref) As feature";
+ + "select 'Feature' As type, "
+ + "ST_AsGeoJSON(ST_Transform(t_%s.geom,4326))::json As geometry, "
+ + "row_to_json(("
+ + "SELECT p FROM (SELECT t_%s.idref, t_%s.fixed) AS p"
+ + ")) AS properties From t_%s %s order by area,idref) As feature";
public String sqlForm2 = "SELECT row_to_json(feature) FROM ("
- + "select 'Feature' As type, "
- + "ST_AsGeoJSON(ST_Transform(t_%s.geom,4326))::json As geometry From t_%s %s order by area,idref) As feature";
+ + "select 'Feature' As type, "
+ + "ST_AsGeoJSON(ST_Transform(t_%s.geom,4326))::json As geometry From t_%s %s order by area,idref) As feature";
public ToGeoJSON(String tableName) {
this.tableName = tableName;
@@ -88,7 +88,7 @@
ow.write(line);
ow.newLine();
}
- line = rset8.getString(1);
+ line = String.format("\"%s\"", rset8.getString(1));
System.out.print(line);
ow.write(line);
}
diff --git a/src/osm/jp/postgis/ToPostgis.java b/src/osm/jp/postgis/ToPostgis.java
index a1d45ae..42af81f 100644
--- a/src/osm/jp/postgis/ToPostgis.java
+++ b/src/osm/jp/postgis/ToPostgis.java
@@ -26,30 +26,30 @@
*
*/
public class ToPostgis {
- protected boolean UPDATE = false;
String tableName = null;
+ public PostgisItems items = null;
- public ToPostgis(String tableName, boolean update) {
+ public ToPostgis(String tableName) {
this.tableName = tableName;
- this.UPDATE = update;
+ this.items = new PostgisItems();
+ items.add(new PostgisItem("gmlid", "gmlid"));
+ items.add(new PostgisItem("idref", "idref"));
+ items.add(new PostgisItem("area", "area"));
+ items.add(new PostgisItem("fixed", "fixed1"));
+ items.add(new PostgisItem("code", "code"));
+ }
+
+ public ToPostgis(String tableName, PostgisItems items) {
+ this(tableName);
+ this.items = items;
}
public void transport() throws Exception {
- Connection conHsql = null;
- Connection conPost = null;
- try {
- // DB.tableを作成
- conHsql = DatabaseTool.openDb("database");
- conPost = DatabaseTool.openDb("postgis");
- transport(conHsql, conPost);
- }
- finally {
- if (conHsql != null) {
- DatabaseTool.closeDb(conHsql);
- }
- if (conPost != null) {
- DatabaseTool.closeDb(conPost);
- }
+ try (Connection conHsql = DatabaseTool.openDb("database");
+ Connection conPost = DatabaseTool.openDb("postgis"))
+ {
+ transportNew(conHsql, conPost);
+ toInsert(conHsql, conPost);
}
}
@@ -66,62 +66,79 @@
* @throws javax.xml.parsers.ParserConfigurationException
* @throws org.xml.sax.SAXException
*/
- void transport (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
- if (this.UPDATE) {
- transportUpdate(conHsql, conPost);
- }
- else {
- transportNew(conHsql, conPost);
- }
- }
- public void transportNew (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
+ /*
+ {@code
+ CREATE TABLE public.t_TABLENAME
+ (
+ gid integer NOT NULL DEFAULT nextval('t_TABLENAME_gid_seq'::regclass),
+ gmlid text,
+ idref text,
+ name text,
+ fixed integer,
+ area integer,
+ code integer,
+ geom geometry(Point,4612),
+ CONSTRAINT t_TABLENAME_pkey PRIMARY KEY (gid)
+ )
+ WITH (OIDS=FALSE);
+ ALTER TABLE public.t_TABLENAME
+ OWNER TO postgres;
+
+ CREATE INDEX ix_TABLENAME_geom ON public.t_TABLENAME USING gist (geom);
+ }
+ */
+ public void transportNew(Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
HttpPOST posgre = new HttpPOST(conPost, null);
posgre.sql("DROP TABLE IF EXISTS t_"+ tableName +" CASCADE;");
- posgre.sql("CREATE TABLE t_"+ tableName +" (gid SERIAL PRIMARY KEY, idref varchar(24), fixed integer,area integer,geom GEOMETRY(POINT, 4612));");
+ posgre.sql("CREATE TABLE t_"+ tableName
+ +" ("
+ + "gid SERIAL PRIMARY KEY, "
+ + "gmlid varchar(24), "
+ + "idref varchar(24), "
+ + "fixed integer, "
+ + "area integer, "
+ + "code integer, "
+ + "geom GEOMETRY(POINT, 4612)"
+ + ");");
posgre.sql("CREATE INDEX ix_"+ tableName +"_geom ON t_"+ tableName +" USING GiST (geom);");
- toInsert(conHsql, conPost);
}
- void transportUpdate (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
- toInsert(conHsql, conPost);
-
- try ( PreparedStatement ps1 = conHsql.prepareStatement("SELECT gid,fixed1 FROM "+ tableName +" where up=1");
- PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_"+ tableName +" set fixed=? WHERE gid=?")) {
- try (ResultSet rset1 = ps1.executeQuery()) {
- while (rset1.next()) {
- long gid = rset1.getLong("gid");
- int fixed1 = rset1.getInt("fixed1");
- ps2.setInt(1, fixed1);
- ps2.setLong(2, gid);
- System.out.println("UPDATE t_"+ tableName +" set fixed="+ fixed1 +" WHERE gid="+ gid);
- ps2.executeUpdate();
- }
- }
- }
- }
-
- public void toInsert (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
- String sql = "SELECT idref,area,fixed1,lat,lon FROM "+ tableName;
- if (this.UPDATE) {
- sql += " where up=2";
- }
+ public void toInsert (Connection conHsql, Connection conPost)
+ throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException
+ {
+ String sql = items.getSqlStr(tableName);
try (PreparedStatement ps1 = conHsql.prepareStatement(sql)) {
try (ResultSet rset1 = ps1.executeQuery()) {
while (rset1.next()) {
- String idref = rset1.getString("idref");
- int area = rset1.getInt("area");
- int fixed1 = rset1.getInt("fixed1");
- double lat = rset1.getDouble("lat");
- double lon = rset1.getDouble("lon");
+ items.setResuit(rset1);
- String sqlStr = "INSERT INTO t_"+ tableName +" (idref,fixed,area,geom) VALUES ('"+ idref +"',"+ fixed1 +","+ area +",ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))";
+ String sqlStr = "INSERT INTO t_"+ tableName
+ +" (gmlid,idref,fixed,area,code,geom) "
+ + "VALUES (?,?,?,?,?, ST_GeomFromText('POINT(? ?)',4612))";
try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
- System.out.println(sqlStr);
+ printMark();
+ ps.setString(1, items.gmlid);
+ ps.setString(2, items.idref);
+ ps.setInt(3, items.fixed);
+ ps.setInt(4, items.area);
+ ps.setInt(5, items.code);
+ ps.setDouble(6, items.lon);
+ ps.setDouble(7, items.lat);
ps.executeUpdate();
}
}
}
}
}
+
+ public static int outCnt = 0;
+ public static void printMark() {
+ System.out.print(".");
+ outCnt++;
+ if (outCnt >= 100) {
+ outCnt = 0;
+ System.out.println();
+ }
+ }
}