diff --git a/src/default.style b/src/default.style
new file mode 100644
index 0000000..921404c
--- /dev/null
+++ b/src/default.style
@@ -0,0 +1,232 @@
+# This is the default osm2pgsql .style file that comes with osm2pgsql.
+#
+# A .style file has 4 columns that define how OSM objects end up in tables in
+# the database and what columns are created. It interacts with the command-line
+# hstore options.
+#
+# Columns
+# =======
+#
+# OsmType: This is either "node", "way" or "node,way" and indicates if this tag
+# applies to nodes, ways, or both.
+#
+# Tag: The tag
+#
+# DataType: The type of the column to be created. Normally "text"
+#
+# Flags: Flags that indicate what table the OSM object is moved into.
+#
+# There are 6 possible flags. These flags are used both to indicate if a column
+# should be created, and if ways with the tag are assumed to be areas. The area
+# assumptions can be overridden with an area=yes/no tag
+#
+# polygon - Create a column for this tag, and objects with the tag are areas
+#
+# linear - Create a column for this tag
+#
+# nocolumn - Override the above and don't create a column for the tag, but do
+# include objects with this tag
+#
+# phstore - Same as polygon,nocolumn for backward compatibility
+#
+# delete - Drop this tag completely and don't create a column for it. This also
+# prevents the tag from being added to hstore columns
+#
+# nocache - Deprecated and does nothing
+#
+# If an object has a tag that indicates it is an area or has area=yes/1,
+# osm2pgsql will try to turn it into an area. If it succeeds, it places it in
+# the polygon table. If it fails (e.g. not a closed way) it places it in the
+# line table.
+#
+# Nodes are never placed into the polygon or line table and are always placed in
+# the point table.
+#
+# Hstore
+# ======
+#
+# The options --hstore, --hstore-match-only, and --hstore-all interact with
+# the .style file.
+#
+# With --hstore any tags without a column will be added to the hstore column.
+# This will also cause all objects to be kept.
+#
+# With --hstore-match-only the behavior for tags is the same, but objects are
+# only kept if they have a non-NULL value in one of the columns.
+#
+# With --hstore-all all tags are added to the hstore column unless they appear
+# in the style file with a delete flag, causing duplication between the normal
+# columns and the hstore column.
+#
+# Special database columns
+# ========================
+#
+# There are some special database columns that if present in the .style file
+# will be populated by osm2pgsql.
+#
+# These are
+#
+# z_order - datatype int4
+#
+# way_area - datatype real. The area of the way, in the units of the projection
+# (e.g. square mercator meters). Only applies to areas
+#
+# osm_user - datatype text
+# osm_uid - datatype integer
+# osm_version - datatype integer
+# osm_changeset - datatype integer
+# osm_timestamp - datatype timestamptz(0).
+# Used with the --extra-attributes option to include metadata in the database.
+# If importing with both --hstore and --extra-attributes the meta-data will
+# end up in the tags hstore column regardless of the style file.
+
+# OsmType Tag DataType Flags
+node,way access text linear
+node,way addr:housename text linear
+node,way addr:housenumber text linear
+node,way addr:interpolation text linear
+node,way admin_level text linear
+node,way aerialway text linear
+node,way aeroway text polygon
+node,way amenity text polygon
+node,way area text polygon # hard coded support for area=1/yes => polygon is in osm2pgsql
+node,way barrier text linear
+node,way bicycle text linear
+node,way brand text linear
+node,way bridge text linear
+node,way boundary text linear
+node,way building text polygon
+node capital text linear
+node,way construction text linear
+node,way covered text linear
+node,way culvert text linear
+node,way cutting text linear
+node,way denomination text linear
+node,way disused text linear
+node ele text linear
+node,way embankment text linear
+node,way foot text linear
+node,way generator:source text linear
+node,way harbour text polygon
+node,way highway text linear
+node,way historic text polygon
+node,way horse text linear
+node,way intermittent text linear
+node,way junction text linear
+node,way landuse text polygon
+node,way layer text linear
+node,way leisure text polygon
+node,way lock text linear
+node,way man_made text polygon
+node,way military text polygon
+node,way motorcar text linear
+node,way name text linear
+node,way natural text polygon # natural=coastline tags are discarded by a hard coded rule in osm2pgsql
+node,way office text polygon
+node,way oneway text linear
+node,way operator text linear
+node,way place text polygon
+node,way population text linear
+node,way power text polygon
+node,way power_source text linear
+node,way public_transport text polygon
+node,way railway text linear
+node,way ref text linear
+node,way religion text linear
+node,way route text linear
+node,way service text linear
+node,way shop text polygon
+node,way sport text polygon
+node,way surface text linear
+node,way toll text linear
+node,way tourism text polygon
+node,way tower:type text linear
+way tracktype text linear
+node,way tunnel text linear
+node,way water text polygon
+node,way waterway text polygon
+node,way wetland text polygon
+node,way width text linear
+node,way wood text linear
+node,way z_order int4 linear # This is calculated during import
+way way_area real linear # This is calculated during import
+
+# Area tags
+# We don't make columns for these tags, but objects with them are areas.
+# Mainly for use with hstore
+way abandoned:aeroway text polygon,nocolumn
+way abandoned:amenity text polygon,nocolumn
+way abandoned:building text polygon,nocolumn
+way abandoned:landuse text polygon,nocolumn
+way abandoned:power text polygon,nocolumn
+way area:highway text polygon,nocolumn
+
+# Deleted tags
+# These are tags that are generally regarded as useless for most rendering.
+# Most of them are from imports or intended as internal information for mappers
+# Some of them are automatically deleted by editors.
+# If you want some of them, perhaps for a debugging layer, just delete the lines.
+
+# These tags are used by mappers to keep track of data.
+# They aren't very useful for rendering.
+node,way note text delete
+node,way note:* text delete
+node,way source text delete
+node,way source_ref text delete
+node,way source:* text delete
+node,way attribution text delete
+node,way comment text delete
+node,way fixme text delete
+
+# Tags generally dropped by editors, not otherwise covered
+node,way created_by text delete
+node,way odbl text delete
+node,way odbl:note text delete
+node,way SK53_bulk:load text delete
+
+# Lots of import tags
+# TIGER (US)
+node,way tiger:* text delete
+
+# NHD (US)
+# NHD has been converted every way imaginable
+node,way NHD:* text delete
+node,way nhd:* text delete
+
+# GNIS (US)
+node,way gnis:* text delete
+
+# Geobase (CA)
+node,way geobase:* text delete
+# NHN (CA)
+node,way accuracy:meters text delete
+node,way sub_sea:type text delete
+node,way waterway:type text delete
+
+# KSJ2 (JA)
+# See also note:ja and source_ref above
+node,way KSJ2:* text delete
+# Yahoo/ALPS (JA)
+node,way yh:* text delete
+
+# osak (DK)
+node,way osak:* text delete
+
+# kms (DK)
+node,way kms:* text delete
+
+# ngbe (ES)
+# See also note:es and source:file above
+node,way ngbe:* text delete
+
+# naptan (UK)
+node,way naptan:* text delete
+
+# Corine (CLC) (Europe)
+node,way CLC:* text delete
+
+# misc
+node,way 3dshapes:ggmodelk text delete
+node,way AND_nosr_r text delete
+node,way import text delete
+node,way it:fvg:* text delete
diff --git a/src/osm/jp/coverage/fuel/DbExist.java b/src/osm/jp/coverage/fuel/DbExist.java
index 1b5137e..014d515 100644
--- a/src/osm/jp/coverage/fuel/DbExist.java
+++ b/src/osm/jp/coverage/fuel/DbExist.java
@@ -1,11 +1,14 @@
package osm.jp.coverage.fuel;
+import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.SQLIntegrityConstraintViolationException;
import jp.co.areaweb.tools.csv.CsvRecord;
import jp.co.areaweb.tools.database.*;
+import org.hsqldb.HsqlException;
import osm.jp.api.Osmdb;
public class DbExist extends Osmdb {
@@ -194,38 +197,18 @@
try {
osmdb = DatabaseTool.openDb("osmdb");
- PreparedStatement ps1 = osmdb.prepareStatement("SELECT osm_id,brand,name,disused,tags,ST_Y(ST_Transform(way,4326)) as lat,ST_X(ST_Transform(way,4326)) as lon FROM planet_osm_point,planet_osm_nodes WHERE osm_id=id AND '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)");
+ PreparedStatement ps1 = osmdb.prepareStatement("SELECT id,tags,lat,lon FROM planet_osm_nodes WHERE '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)");
try (ResultSet rset1 = ps1.executeQuery()) {
while (rset1.next()) {
String tags = rset1.getString("tags");
- if (tags != null) {
- tags = tags.substring(1);
- tags = tags.substring(0, tags.length()-1);
- CsvRecord csv = new CsvRecord();
- csv.analizeRecord(tags);
-
- boolean key = false;
- for (String str : csv) {
- if (key) {
- if (str.equals(vStr)) {
- counter += importExistingNode(hsqldb, osmdb, rset1, point);
- break;
- }
- key = false;
- }
- else {
- if (str.equals(kStr)) {
- key = true;
- }
- }
- }
- }
- }
- }
- PreparedStatement ps2 = osmdb.prepareStatement("SELECT osm_id,brand,disused,name,tags,ST_Y(ST_Transform(ST_Centroid(way),4326)) as lat,ST_X(ST_Transform(ST_Centroid(way),4326)) as lon FROM planet_osm_polygon,planet_osm_nodes WHERE osm_id=id AND '"+ kStr +"'=ANY(tags) AND '"+ vStr +"'=ANY(tags)");
- try (ResultSet rset2 = ps2.executeQuery()) {
- while (rset2.next()) {
- counter += importExistingNode(hsqldb, osmdb, rset2, point);
+ String idref = rset1.getString("id");
+ String latStr = rset1.getString("lat");
+ String lonStr = rset1.getString("lon");
+ double lat = Double.valueOf(latStr);
+ double lon = Double.valueOf(lonStr);
+ int score = score(point, tags);
+ //counter += importExistingNode(hsqldb, osmdb, rset1, point);
+ counter += insertExistingNode(hsqldb, idref, lat, lon, score, "");
}
}
System.out.println("Exists Node count = " + counter);
@@ -234,4 +217,102 @@
DatabaseTool.closeDb(osmdb);
}
}
+
+ int score(int point, String tags) {
+ int score = 50;
+ if (tags == null) {
+ return score;
+ }
+
+ boolean brandYes = false;
+ boolean busYes = false;
+ boolean fixmeYes = false;
+ boolean nameYes = false;
+
+ tags = tags.substring(1);
+ tags = tags.substring(0, tags.length()-1);
+ CsvRecord csv = new CsvRecord();
+ csv.analizeRecord(tags);
+
+ boolean key = true;
+ boolean bus = false;
+ for (String str : csv) {
+ if (key) {
+ if (str.startsWith("fixme")) {
+ fixmeYes = true;
+ }
+ else if (str.equals("bus")) {
+ bus = true;
+ }
+ else if (str.equals("brand")) {
+ brandYes = true;
+ }
+ else if (str.startsWith("name")) {
+ nameYes = true;
+ }
+ key = false;
+ }
+ else {
+ if (bus) {
+ if (str.equals("yes")) {
+ busYes = true;
+ }
+ bus = false;
+ }
+ key = true;
+ }
+ }
+
+ if (((point & POINT_NO_BRAND) != 0) && !brandYes) {
+ score = 1;
+ }
+ if (((point & POINT_NO_NAME) != 0) && !nameYes) {
+ score = 1;
+ }
+ if (((point & POINT_FIXME) != 0) && fixmeYes) {
+ score = 1;
+ }
+ if (((point & POINT_BUS_NO) != 0) && !busYes) {
+ score = 0;
+ }
+ return score;
+ }
+
+
+ /**
+ *
+ * @param hsqldb
+ * @param idref
+ * @param lat
+ * @param lon
+ * @param score
+ * @param name
+ * @return
+ * @throws IOException
+ * @throws SQLException
+ */
+ public int insertExistingNode(Connection hsqldb, String idref, double lat, double lon, int score, String name) throws IOException, SQLException {
+ // idref と brandStr をデータベースに格納する
+ String sql = String.format("INSERT INTO %s (idref,lat,lon,score,name) VALUES (%s,%2.7f,%3.7f,%d,%s)",
+ TABLE_NAME,
+ idref, lat, lon, score, name);
+ System.out.println(sql);
+ sql = String.format("INSERT INTO %s (idref,lat,lon,score,name) VALUES (?,?,?,?,?)", TABLE_NAME);
+ try (PreparedStatement ps5 = hsqldb.prepareStatement(sql)) {
+ ps5.setString(1, idref);
+ ps5.setDouble(2, lat);
+ ps5.setDouble(3, lon);
+ ps5.setInt(4, score);
+ ps5.setString(5, name);
+ ps5.executeUpdate();
+ return 1;
+ }
+ catch (HsqlException | SQLIntegrityConstraintViolationException e) {
+ // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
+ // [HsqlException]は、無視する
+ // integrity constraint violation: unique constraint or index violation; SYS_PK_10069 table: FUEL_EXIST
+ // [SQLIntegrityConstraintViolationException]は、無視する
+ }
+ return 0;
+ }
}
\ No newline at end of file
diff --git a/src/osmCoverage.style b/src/osmCoverage.style
new file mode 100644
index 0000000..65bab8d
--- /dev/null
+++ b/src/osmCoverage.style
@@ -0,0 +1,247 @@
+# This is the default osm2pgsql .style file that comes with osm2pgsql.
+#
+# A .style file has 4 columns that define how OSM objects end up in tables in
+# the database and what columns are created. It interacts with the command-line
+# hstore options.
+#
+# Columns
+# =======
+#
+# OsmType: This is either "node", "way" or "node,way" and indicates if this tag
+# applies to nodes, ways, or both.
+#
+# Tag: The tag
+#
+# DataType: The type of the column to be created. Normally "text"
+#
+# Flags: Flags that indicate what table the OSM object is moved into.
+#
+# There are 6 possible flags. These flags are used both to indicate if a column
+# should be created, and if ways with the tag are assumed to be areas. The area
+# assumptions can be overridden with an area=yes/no tag
+#
+# polygon - Create a column for this tag, and objects with the tag are areas
+#
+# linear - Create a column for this tag
+#
+# nocolumn - Override the above and don't create a column for the tag, but do
+# include objects with this tag
+#
+# phstore - Same as polygon,nocolumn for backward compatibility
+#
+# delete - Drop this tag completely and don't create a column for it. This also
+# prevents the tag from being added to hstore columns
+#
+# nocache - Deprecated and does nothing
+#
+# If an object has a tag that indicates it is an area or has area=yes/1,
+# osm2pgsql will try to turn it into an area. If it succeeds, it places it in
+# the polygon table. If it fails (e.g. not a closed way) it places it in the
+# line table.
+#
+# Nodes are never placed into the polygon or line table and are always placed in
+# the point table.
+#
+# Hstore
+# ======
+#
+# The options --hstore, --hstore-match-only, and --hstore-all interact with
+# the .style file.
+#
+# With --hstore any tags without a column will be added to the hstore column.
+# This will also cause all objects to be kept.
+#
+# With --hstore-match-only the behavior for tags is the same, but objects are
+# only kept if they have a non-NULL value in one of the columns.
+#
+# With --hstore-all all tags are added to the hstore column unless they appear
+# in the style file with a delete flag, causing duplication between the normal
+# columns and the hstore column.
+#
+# Special database columns
+# ========================
+#
+# There are some special database columns that if present in the .style file
+# will be populated by osm2pgsql.
+#
+# These are
+#
+# z_order - datatype int4
+#
+# way_area - datatype real. The area of the way, in the units of the projection
+# (e.g. square mercator meters). Only applies to areas
+#
+# osm_user - datatype text
+# osm_uid - datatype integer
+# osm_version - datatype integer
+# osm_changeset - datatype integer
+# osm_timestamp - datatype timestamptz(0).
+# Used with the --extra-attributes option to include metadata in the database.
+# If importing with both --hstore and --extra-attributes the meta-data will
+# end up in the tags hstore column regardless of the style file.
+
+# OsmType Tag DataType Flags
+node,way access text linear
+node,way addr:housename text linear
+node,way addr:housenumber text linear
+node,way addr:interpolation text linear
+node,way admin_level text linear
+node,way aerialway text linear
+node,way aeroway text polygon
+node,way amenity text polygon
+node,way abandoned:amenity text linear
+node,way demolished:amenity text linear
+node,way disused:amenity text linear
+node,way removed:amenity text linear
+node,way historic:amenity text linear
+node,way no:amenity text linear
+node,way was:amenity text linear
+node,way area text polygon # hard coded support for area=1/yes => polygon is in osm2pgsql
+node,way barrier text linear
+node,way bicycle text linear
+node,way brand text linear
+node,way bridge text linear
+node,way boundary text linear
+node,way building text polygon
+node,way bus text linear
+node capital text linear
+node,way construction text linear
+node,way covered text linear
+node,way culvert text linear
+node,way cutting text linear
+node,way denomination text linear
+node,way disused text linear
+node ele text linear
+node,way embankment text linear
+node,way fixme text linear
+node,way foot text linear
+node,way generator:source text linear
+node,way harbour text polygon
+node,way highway text linear
+node,way historic text polygon
+node,way horse text linear
+node,way intermittent text linear
+node,way junction text linear
+node,way landuse text polygon
+node,way layer text linear
+node,way leisure text polygon
+node,way lock text linear
+node,way man_made text polygon
+node,way military text polygon
+node,way motorcar text linear
+node,way name text linear
+node,way natural text polygon # natural=coastline tags are discarded by a hard coded rule in osm2pgsql
+node,way office text polygon
+node,way oneway text linear
+node,way operator text linear
+node,way place text polygon
+node,way population text linear
+node,way power text polygon
+node,way power_source text linear
+node,way public_transport text polygon
+node,way railway text linear
+node,way ref text linear
+node,way religion text linear
+node,way route text linear
+node,way service text linear
+node,way shop text polygon
+node,way sport text polygon
+node,way surface text linear
+node,way toll text linear
+node,way tourism text polygon
+node,way tower:type text linear
+way tracktype text linear
+node,way tunnel text linear
+node,way water text polygon
+node,way waterway text polygon
+node,way wetland text polygon
+node,way width text linear
+node,way wood text linear
+node,way z_order int4 linear # This is calculated during import
+way way_area real linear # This is calculated during import
+
+# Area tags
+# We don't make columns for these tags, but objects with them are areas.
+# Mainly for use with hstore
+way abandoned:aeroway text polygon,nocolumn
+way abandoned:amenity text polygon,nocolumn
+way disused:amenity text polygon,nocolumn
+way demolished:amenity text polygon,nocolumn
+way historic:amenity text polygon,nocolumn
+way was:amenity text polygon,nocolumn
+way removed:amenity text polygon,nocolumn
+way no:amenity text polygon,nocolumn
+way abandoned:building text polygon,nocolumn
+way abandoned:landuse text polygon,nocolumn
+way abandoned:power text polygon,nocolumn
+way area:highway text polygon,nocolumn
+
+# Deleted tags
+# These are tags that are generally regarded as useless for most rendering.
+# Most of them are from imports or intended as internal information for mappers
+# Some of them are automatically deleted by editors.
+# If you want some of them, perhaps for a debugging layer, just delete the lines.
+
+# These tags are used by mappers to keep track of data.
+# They aren't very useful for rendering.
+node,way note text delete
+node,way note:* text delete
+node,way source text delete
+node,way source_ref text delete
+node,way source:* text delete
+node,way attribution text delete
+node,way comment text delete
+#node,way fixme text delete
+
+# Tags generally dropped by editors, not otherwise covered
+node,way created_by text delete
+node,way odbl text delete
+node,way odbl:note text delete
+node,way SK53_bulk:load text delete
+
+# Lots of import tags
+# TIGER (US)
+node,way tiger:* text delete
+
+# NHD (US)
+# NHD has been converted every way imaginable
+node,way NHD:* text delete
+node,way nhd:* text delete
+
+# GNIS (US)
+node,way gnis:* text delete
+
+# Geobase (CA)
+node,way geobase:* text delete
+# NHN (CA)
+node,way accuracy:meters text delete
+node,way sub_sea:type text delete
+node,way waterway:type text delete
+
+# KSJ2 (JA)
+# See also note:ja and source_ref above
+node,way KSJ2:* text delete
+# Yahoo/ALPS (JA)
+node,way yh:* text delete
+
+# osak (DK)
+node,way osak:* text delete
+
+# kms (DK)
+node,way kms:* text delete
+
+# ngbe (ES)
+# See also note:es and source:file above
+node,way ngbe:* text delete
+
+# naptan (UK)
+node,way naptan:* text delete
+
+# Corine (CLC) (Europe)
+node,way CLC:* text delete
+
+# misc
+node,way 3dshapes:ggmodelk text delete
+node,way AND_nosr_r text delete
+node,way import text delete
+node,way it:fvg:* text delete