diff --git a/database.properties b/database.properties
new file mode 100644
index 0000000..e66e66c
--- /dev/null
+++ b/database.properties
@@ -0,0 +1,4 @@
+db_driver=org.hsqldb.jdbcDriver
+db_url=jdbc:hsqldb:file:./database/db
+db_user=sa
+db_passwd=
diff --git a/doc/Fuel_All.qgs b/doc/Fuel_All.qgs
new file mode 100644
index 0000000..450d8d4
--- /dev/null
+++ b/doc/Fuel_All.qgs
@@ -0,0 +1,573 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ meters
+
+ 15160526.31886820308864117
+ 4362243.61678864248096943
+ 16330369.8164325188845396
+ 5118470.21764160878956318
+
+ 0
+ 1
+
+
+ +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs
+ 3857
+ 3857
+ EPSG:3857
+ WGS 84 / Pseudo Mercator
+ merc
+ WGS84
+ false
+
+
+ 0
+
+
+
+
+
+
+
+ - t_fuel20170910135816777
+ - OSM_Humanitarian_Data_Model20170910141511409
+
+
+
+
+
+
+ -20037508.33999999985098839
+ -20037508.33999999985098839
+ 20037508.33999999985098839
+ 20037508.33999999985098839
+
+ OSM_Humanitarian_Data_Model20170910141511409
+ <GDAL_WMS>
+ <Service name="TMS">
+ <ServerUrl>http://a.tile.openstreetmap.fr/hot/${z}/${x}/${y}.png</ServerUrl>
+ </Service>
+ <DataWindow>
+ <UpperLeftX>-20037508.34</UpperLeftX>
+ <UpperLeftY>20037508.34</UpperLeftY>
+ <LowerRightX>20037508.34</LowerRightX>
+ <LowerRightY>-20037508.34</LowerRightY>
+ <TileLevel>18</TileLevel>
+ <TileCountX>1</TileCountX>
+ <TileCountY>1</TileCountY>
+ <YOrigin>top</YOrigin>
+ </DataWindow>
+ <Projection>EPSG:3857</Projection>
+ <BlockSizeX>256</BlockSizeX>
+ <BlockSizeY>256</BlockSizeY>
+ <BandsCount>3</BandsCount>
+ <Cache />
+</GDAL_WMS>
+
+
+
+
+ OSM Humanitarian Data Model
+
+
+ +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs
+ 3857
+ 3857
+ EPSG:3857
+ WGS 84 / Pseudo Mercator
+ merc
+ WGS84
+ false
+
+
+
+
+
+
+ gdal
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ 0
+
+
+
+ 122.9914434300000039
+ 24.33596406999999928
+ 145.75993869000001268
+ 45.52172312999999804
+
+ t_fuel20170910135816777
+ dbname='gisdb' host=surveyor.mydns.jp port=5432 user='gisuser' password='gisuser' sslmode=disable key='gid' srid=4612 type=Point table="public"."t_fuel" (geom) sql=
+
+
+
+ t_fuel
+
+
+ +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs
+ 3465
+ 4612
+ EPSG:4612
+ JGD2000
+ longlat
+ GRS80
+ true
+
+
+ postgres
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ 0
+ 0
+ 0
+ gid
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ 0
+
+
+ 0
+ generatedlayout
+
+
+
+
+
+
+
+
+
+
+
+ COALESCE( "gid", '<NULL>' )
+
+
+
+
+
+
+
+
+ +proj=longlat +datum=WGS84 +no_defs
+ EPSG:4326
+ 3452
+ 1
+
+
+ false
+
+
+ 0
+ 255
+ 255
+ 255
+ 255
+ 255
+ 255
+
+
+ 2
+ current_layer
+ off
+ 0
+
+
+ 2
+ true
+
+
+
+
+
diff --git "a/doc/Fuel\343\201\256\344\275\277\343\201\204\346\226\271.odt" "b/doc/Fuel\343\201\256\344\275\277\343\201\204\346\226\271.odt"
new file mode 100644
index 0000000..6e15454
--- /dev/null
+++ "b/doc/Fuel\343\201\256\344\275\277\343\201\204\346\226\271.odt"
Binary files differ
diff --git "a/doc/Fuel\343\201\256\344\275\277\343\201\204\346\226\271.pdf" "b/doc/Fuel\343\201\256\344\275\277\343\201\204\346\226\271.pdf"
new file mode 100644
index 0000000..1953200
--- /dev/null
+++ "b/doc/Fuel\343\201\256\344\275\277\343\201\204\346\226\271.pdf"
Binary files differ
diff --git "a/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.odt" "b/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.odt"
deleted file mode 100644
index c1aeada..0000000
--- "a/doc/NagoyaBusstop\343\201\256\344\275\277\343\201\204\346\226\271.odt"
+++ /dev/null
Binary files differ
diff --git a/doc/db.fuel.class.violet.html b/doc/db.fuel.class.violet.html
deleted file mode 100644
index 31aacdb..0000000
--- a/doc/db.fuel.class.violet.html
+++ /dev/null
@@ -1,330 +0,0 @@
-
-
-
-
-
-
-
-
- This file was generated with Violet UML Editor 2.1.0.
- ( View Source / Download Violet )
-
-
-
-
-
- 
-
-
\ No newline at end of file
diff --git a/doc/fuel.activity.violet.html b/doc/fuel.activity.violet.html
deleted file mode 100644
index 001d89b..0000000
--- a/doc/fuel.activity.violet.html
+++ /dev/null
@@ -1,268 +0,0 @@
-
-
-
-
-
-
-
-
- This file was generated with Violet UML Editor 2.1.0.
- ( View Source / Download Violet )
-
-
-
-
-
- 
-
-
\ No newline at end of file
diff --git a/doc/sql.txt b/doc/sql.txt
deleted file mode 100644
index 4514cb2..0000000
--- a/doc/sql.txt
+++ /dev/null
@@ -1,22 +0,0 @@
-CREATE TABLE t_busstop (
- gid SERIAL PRIMARY KEY,
- osm_id bigint,
- name text,
- 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));
diff --git a/postgis.properties b/postgis.properties
new file mode 100644
index 0000000..04ba2fe
--- /dev/null
+++ b/postgis.properties
@@ -0,0 +1,4 @@
+db_driver=org.postgresql.Driver
+db_url=jdbc:postgresql://surveyor.mydns.jp:5432/gisdb
+db_user=gisuser
+db_passwd=gisuser
diff --git a/src/ImportGML_FUEL.sh b/src/ImportGML_FUEL.sh
new file mode 100755
index 0000000..88ce067
--- /dev/null
+++ b/src/ImportGML_FUEL.sh
@@ -0,0 +1,5 @@
+rm -r database
+java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbFuel
+java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.DbExist
+java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.Fuel
+java -cp .:osmCoverage.jar:hayashi_0225.jar:hsqldb_2.2.9.jar:postgresql-9.4.1212.jar osm.jp.coverage.fuel.ToPostgis
diff --git a/src/db.fuel.class.violet.html b/src/db.fuel.class.violet.html
new file mode 100644
index 0000000..5390200
--- /dev/null
+++ b/src/db.fuel.class.violet.html
@@ -0,0 +1,998 @@
+
+
+
+
+
+
+
+
+ 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/fuel.activity.violet.html b/src/fuel.activity.violet.html
new file mode 100644
index 0000000..5b1501d
--- /dev/null
+++ b/src/fuel.activity.violet.html
@@ -0,0 +1,1308 @@
+
+
+
+
+
+
+
+
+ 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/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java
index 3e71d8c..f5863f4 100644
--- a/src/osm/jp/coverage/fuel/DbExist.java
+++ b/src/osm/jp/coverage/fuel/DbExist.java
@@ -58,7 +58,6 @@
/**
*
* @param con
- * @param oFile
* @throws MalformedURLException
* @throws ProtocolException
* @throws IOException
diff --git a/src/osm/jp/coverage/fuel/DbFuel.java b/src/osm/jp/coverage/fuel/DbFuel.java
index b2dcf12..10b9325 100644
--- a/src/osm/jp/coverage/fuel/DbFuel.java
+++ b/src/osm/jp/coverage/fuel/DbFuel.java
@@ -29,10 +29,10 @@
* @throws org.xml.sax.SAXException */
public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException, ParserConfigurationException, SAXException
{
- // HSQLディレクトリがなければ作る
+ // HSQLディレクトリがなければエラー
File dbdir = new File("database");
if (!dbdir.isDirectory()) {
- dbdir.mkdir();
+ throw new FileNotFoundException("Directory 'database' is not found.");
}
Connection con = null;
try {
@@ -40,6 +40,10 @@
con = DatabaseTool.openDb("database");
create(con);
+ try (PreparedStatement ps = con.prepareStatement("DELETE FROM "+ TABLE_NAME)) {
+ ps.executeUpdate();
+ }
+
//
File gmlDir = new File("GML_FUEL");
for (File gmlFile : gmlDir.listFiles()) {
@@ -124,7 +128,6 @@
showGmPoint(con, nodePoint, areacode);
break;
default:
- System.out.println(nodePoint.getNodeName());
break;
}
}
@@ -169,7 +172,7 @@
}
}
- try (PreparedStatement ps = con.prepareStatement("INSERT INTO "+ TABLE_NAME +"(lat,lon,fixed,idref,area) VALUES(?,?,?,?,?)")) {
+ try (PreparedStatement ps = con.prepareStatement("INSERT INTO "+ TABLE_NAME +"(lat,lon,fixed,idref,area,up) VALUES(?,?,?,?,?,2)")) {
double lat = Double.parseDouble(latStr);
double lon = Double.parseDouble(lonStr);
ps.setDouble(1, lat);
@@ -177,7 +180,7 @@
ps.setInt(3, 0);
ps.setString(4, idStr);
ps.setInt(5, areacode);
- System.out.println("INSERT "+ TABLE_NAME +"("+ idStr +", lat="+ lat +", lon="+ lon +", fixed=0, area="+ areacode +")");
+ System.out.println("INSERT INTO "+ TABLE_NAME +"("+ idStr +", lat="+ lat +", lon="+ lon +", fixed=0, area="+ areacode +", up=2)");
ps.executeUpdate();
}
}
@@ -209,7 +212,7 @@
// 'table.FUEL'を新規に作る
//Db.drop(con, TABLE_NAME);
- createSt = "CREATE TABLE "+ TABLE_NAME +" (idref VARCHAR(12) NOT NULL, lat DOUBLE, lon DOUBLE, fixed INT, area INT)";
+ createSt = "CREATE TABLE "+ TABLE_NAME +" (idref VARCHAR(12) NOT NULL, lat DOUBLE, lon DOUBLE, fixed INT, area INT, up INT)";
Db.updateSQL(con, createSt);
/*
diff --git a/src/osm/jp/coverage/fuel/Fuel.java b/src/osm/jp/coverage/fuel/Fuel.java
index a54c253..2c8bbf4 100644
--- a/src/osm/jp/coverage/fuel/Fuel.java
+++ b/src/osm/jp/coverage/fuel/Fuel.java
@@ -103,47 +103,4 @@
}
}
}
-
- /**
- *
- * @param conHsql
- * @param conPost
- * @param areacode
- * @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 updateFile (Connection conHsql, Connection conPost, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
- int iCounter = 0;
- PreparedStatement ps2;
- try (PreparedStatement ps1 = conPost.prepareStatement("SELECT gid,name,fixed,ST_X(geom) LON, ST_Y(geom) LAT FROM t_busstop WHERE area=?")) {
- ps2 = conHsql.prepareStatement("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES (?,?,?,?,?,?)");
- // idref と nameStr をデータベースに格納する
- ps1.setInt(1, areacode);
- try (ResultSet rset1 = ps1.executeQuery()) {
- while (rset1.next()) {
- iCounter++;
- int idref = rset1.getInt("gid");
- String nameStr = rset1.getString("name");
- int fixed = rset1.getInt("fixed");
- double lat = rset1.getDouble(4);
- double lon = rset1.getDouble(5);
-
- ps2.setInt(1, idref);
- ps2.setString(2, nameStr);
- ps2.setInt(3, fixed);
- ps2.setInt(4, areacode);
- ps2.setDouble(5, lon);
- ps2.setDouble(6, lat);
- System.out.println("INSERT INTO bus_stop (idref,name,fixed,area,lat,lon) VALUES ("+ idref +",'"+ nameStr +"',"+ fixed +","+ areacode +","+ lat +","+ lon +")");
- ps2.executeUpdate();
- }
- }
- }
- ps2.close();
- System.out.println("("+ areacode +") バス停数["+ iCounter +"]");
- }
}
\ No newline at end of file
diff --git a/src/osm/jp/coverage/fuel/ToPostgis.java b/src/osm/jp/coverage/fuel/ToPostgis.java
index 555ea4e..ff35238 100644
--- a/src/osm/jp/coverage/fuel/ToPostgis.java
+++ b/src/osm/jp/coverage/fuel/ToPostgis.java
@@ -1,70 +1,95 @@
package osm.jp.coverage.fuel;
-import java.io.BufferedWriter;
-import java.io.File;
import java.io.FileNotFoundException;
-import java.io.FileOutputStream;
import java.io.IOException;
-import java.io.OutputStreamWriter;
-import java.io.UnsupportedEncodingException;
+import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import javax.xml.parsers.ParserConfigurationException;
+import jp.co.areaweb.tools.database.DatabaseTool;
+import org.xml.sax.SAXException;
/**
* 国土数値情報から読み取ったデータをPostGISへインサートするためのSQLファイルに出力する。
- * 出力ファイル名： "busstop.sql"
- * テーブル名： t_busstop
- * インデックス： ix_busstop_geom
- * gid PostGISの識別ID
- * name バス停名称
- * fixed OSMのバス停が周辺に存在するかどうか、存在しない場合は0,存在する場合はその数。
+ * 出力ファイル名： "t_FUEL.sql"
+ * テーブル名： t_FUEL
+ * インデックス： ix_t_FUEL_geom
+ * gid PostGISの識別ID
+ * idstr 国土数値情報のノードID
+ * fixed OSMのNodeが周辺に存在するかどうか、存在しない場合は0,存在する場合はそのScoreの合計。
+ * area 都道府県コード
* geom PostGIS形式の位置情報(4612:)
* @author yuu
*
*/
public class ToPostgis {
- public static final String SQL_FILE_NAME = "busstop.sql";
- BufferedWriter ow = null;
-
- public ToPostgis(File dir) throws UnsupportedEncodingException, FileNotFoundException {
- File sqlFile = new File(dir, SQL_FILE_NAME);
- this.ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(sqlFile), "UTF-8"));
- }
-
- public int outputDb(Connection con) throws IOException, SQLException {
- int counter = 0;
-
- PreparedStatement ps8 = con.prepareStatement("SELECT idref,name,kana,lat,lon,fixed FROM bus_stop");
- try (ResultSet rset8 = ps8.executeQuery()) {
- while (rset8.next()) {
- String idref = rset8.getString("idref");
- String name = rset8.getString("name");
- String kana = rset8.getString("kana");
- Double lat = rset8.getDouble("lat");
- Double lon = rset8.getDouble("lon");
- int score = rset8.getInt("fixed");
-
- counter++;
- String osm_node;
- osm_node = "insert into t_busstop(name,fixed,area,geom) VALUES('"+ escapeStr(name) +"',"+ score +",0, ST_GeomFromText('POINT("+ Double.toString(lon) +" "+ Double.toString(lat) +")', 4612));";
- System.out.println(osm_node);
- this.ow.write(osm_node);
- this.ow.newLine();
+ public static void main(String[] argv) 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);
}
}
- this.ow.flush();
- return counter;
}
/**
- * postgresql 用に(')をエスケープする
+ * 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 name
- * @return
+ * @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 String escapeStr(String name) {
- return ((name == null) ? "" : name.replaceAll("'", "''"));
+ public static void transport (Connection conHsql, Connection conPost) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
+ try (PreparedStatement ps1 = conHsql.prepareStatement("SELECT idref,area,fixed,lat,lon FROM FUEL where up=2")) {
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ while (rset1.next()) {
+ String idref = rset1.getString("idref");
+ int area = rset1.getInt("area");
+ int fixed = rset1.getInt("fixed");
+ double lat = rset1.getDouble("lat");
+ double lon = rset1.getDouble("lon");
+
+ String sqlStr = "INSERT INTO t_FUEL (idref,fixed,area,geom) VALUES ('"+ idref +"',"+ fixed +","+ area +",ST_GeomFromText('POINT("+ BigDecimal.valueOf(lon).toPlainString() +" "+ BigDecimal.valueOf(lat).toPlainString() +")', 4612))";
+ try (PreparedStatement ps = conPost.prepareStatement(sqlStr)) {
+ System.out.println(sqlStr);
+ ps.executeUpdate();
+ }
+ }
+ }
+ }
+
+ try ( PreparedStatement ps1 = conHsql.prepareStatement("SELECT idref,fixed FROM FUEL where up=1");
+ PreparedStatement ps2 = conPost.prepareStatement("UPDATE t_FUEL set fixed=? WHERE idref=?")) {
+ try (ResultSet rset1 = ps1.executeQuery()) {
+ while (rset1.next()) {
+ String idref = rset1.getString("idref");
+ int fixed = rset1.getInt("fixed");
+ ps2.setInt(1, fixed);
+ ps2.setString(2, idref);
+ System.out.println("UPDATE t_FUEL set fixed="+ fixed +" WHERE idstr="+ idref);
+ ps2.executeUpdate();
+ }
+ }
+ }
}
}
diff --git a/src/sql.txt b/src/sql.txt
new file mode 100644
index 0000000..102f920
--- /dev/null
+++ b/src/sql.txt
@@ -0,0 +1,24 @@
+CREATE TABLE t_busstop (
+ gid SERIAL PRIMARY KEY,
+ osm_id bigint,
+ name text,
+ 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));
+
+----
\ No newline at end of file
diff --git a/src/t_FUEL.sql b/src/t_FUEL.sql
new file mode 100644
index 0000000..a6d80fc
--- /dev/null
+++ b/src/t_FUEL.sql
@@ -0,0 +1,46 @@
+---<< postgis: postgres >>---
+db_driver=org.postgresql.Driver
+db_url=jdbc:postgresql://surveyor.mydns.jp:5432/gisdb
+db_user=gisuser
+db_passwd=gisuser
+-------------------------
+DROP TABLE t_FUEL;
+CREATE TABLE t_FUEL (
+ gid SERIAL PRIMARY KEY,
+ idref text,
+ fixed integer,
+ area integer,
+ geom GEOMETRY(POINT, 4612)
+);
+CREATE INDEX ix_t_FUEL_geom ON t_fuel USING GiST (geom);
+
+insert into t_FUEL (idref,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));
+insert into t_FUEL (idref,fixed,area,geom) VALUES('n000002',0,0, ST_GeomFromText('POINT(136.969047121627 35.102834616747685)', 4612));
+insert into t_FUEL (idref,fixed,area,geom) VALUES('n000003',0,0, ST_GeomFromText('POINT(136.9743151095072 35.10462957171922)', 4612));
+
+
+---<< database: hsqldb >>---
+-------------------------
+
+CREATE TABLE FUEL_EXIST (
+ idref VARCHAR(12) NOT NULL,
+ lat DOUBLE,
+ lon DOUBLE,
+ score INT,
+ PRIMARY KEY(idref)
+);
+CREATE INDEX FUEL_EXIST_index ON FUEL_EXIST (lat, lon);
+
+----
+CREATE TABLE FUEL (
+ idref VARCHAR(12) NOT NULL,
+ lat DOUBLE,
+ lon DOUBLE,
+ fixed INT,
+ area INT,
+ up INT
+)
+
+INSERT INTO FUEL (lat,lon,fixed,idref,area,up) VALUES(?,?,?,?,?,2)
+
+----
\ No newline at end of file
diff --git a/src/t_fuel.sql b/src/t_fuel.sql
deleted file mode 100644
index e7c784f..0000000
--- a/src/t_fuel.sql
+++ /dev/null
@@ -1,13 +0,0 @@
-DROP TABLE t_fuel;
-CREATE TABLE t_fuel (gid SERIAL PRIMARY KEY, idstr text, kana text, fixed integer, area integer, geom GEOMETRY(POINT, 4612));
-CREATE INDEX ix_t_fuel_geom ON t_fuel USING GiST (geom);
-
-insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000001','アイカワイッチョウメ',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));
-insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000002','アイカワイッチョウメ',0,0, ST_GeomFromText('POINT(136.969047121627 35.102834616747685)', 4612));
-insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000003','アイオイヤマジュウタク',0,0, ST_GeomFromText('POINT(136.9743151095072 35.10462957171922)', 4612));
-insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000004','アイオイヤマジュウタク',0,0, ST_GeomFromText('POINT(136.97421198072038 35.10334076583495)', 4612));
-insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000005','アイオイヤマジュウタク',0,0, ST_GeomFromText('POINT(136.9744339059364 35.10354449054206)', 4612));
-insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000006','アイオイヤマジュウタクミナミ',0,0, ST_GeomFromText('POINT(136.97261499203069 35.102706336248744)', 4612));
-insert into t_fuel(idstr,kana,fixed,area,geom) VALUES('n000007','アイオイヤマジュウタクミナミ',0,0, ST_GeomFromText('POINT(136.97317171870043 35.10254162416889)', 4612));
-
-