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