[OSM-dev] osmosis pgsql schema / way.bbox removal/option
Florian Lohoff
flo at rfc822.org
Mon Sep 8 10:58:18 BST 2008
Hi,
i was trying to use the postgresql simple schema from osmosis to import
a planet and after 5 days i aborted the import. 4 of the 5 days the
postgresql was busy building the bboxes for the ways.
As for the read-only-api i did not need the bbox or their index i
removed the code from osmosis to update the bbox and create the index.
It would be nice if there would be an option to disable that part of the
schema.
After removing the full planet import took 1200 minutes.
For reference the diff ...
diff -Nur osmosis-0.29/src/com/bretth/osmosis/core/pgsql/v0_5/impl/ChangeWriter.java osmosis-0.29.flo/src/com/bretth/osmosis/core/pgsql/v0_5/impl/ChangeWriter.java
--- osmosis-0.29/src/com/bretth/osmosis/core/pgsql/v0_5/impl/ChangeWriter.java 2008-07-05 00:14:18.000000000 +0200
+++ osmosis-0.29.flo/src/com/bretth/osmosis/core/pgsql/v0_5/impl/ChangeWriter.java 2008-09-08 09:39:13.000000000 +0200
@@ -36,15 +36,6 @@
"INSERT INTO node_tags (node_id, k, v) VALUES (?, ?, ?)";
private static final String DELETE_SQL_NODE_TAG =
"DELETE FROM node_tags WHERE node_id = ?";
- private static final String UPDATE_NODE_WAY_BBOX =
- "UPDATE ways w SET bbox = (" +
- " SELECT Envelope(Collect(n.geom))" +
- " FROM nodes n INNER JOIN way_nodes wn ON wn.node_id = n.id" +
- " WHERE wn.way_id = w.id" +
- " )" +
- " WHERE w.id IN (" +
- " SELECT w.id FROM ways w INNER JOIN way_nodes wn ON w.id = wn.way_id WHERE wn.node_id = ? GROUP BY w.id" +
- " )";
private static final String INSERT_SQL_WAY =
"INSERT INTO ways (id, user_name, tstamp) VALUES (?, ?, ?)";
private static final String DELETE_SQL_WAY =
@@ -57,13 +48,6 @@
"INSERT INTO way_nodes (way_id, node_id, sequence_id) VALUES (?, ?, ?)";
private static final String DELETE_SQL_WAY_NODE =
"DELETE FROM way_nodes WHERE way_id = ?";
- private static final String UPDATE_WAY_BBOX =
- "UPDATE ways SET bbox = (" +
- " SELECT Envelope(Collect(geom))" +
- " FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id" +
- " WHERE way_nodes.way_id = ways.id" +
- " )" +
- " WHERE ways.id = ?";
private static final String INSERT_SQL_RELATION =
"INSERT INTO relations (id, user_name, tstamp) VALUES (?, ?, ?)";
private static final String DELETE_SQL_RELATION =
@@ -200,7 +184,6 @@
deleteNodeStatement = dbCtx.prepareStatement(DELETE_SQL_NODE);
insertNodeTagStatement = dbCtx.prepareStatement(INSERT_SQL_NODE_TAG);
deleteNodeTagStatement = dbCtx.prepareStatement(DELETE_SQL_NODE_TAG);
- updateNodeWayPreparedStatement = dbCtx.prepareStatement(UPDATE_NODE_WAY_BBOX);
}
// Delete any existing records for the node.
@@ -232,12 +215,6 @@
writeEntityTags(insertNodeTagStatement, node);
prmIndex = 1;
- try {
- updateNodeWayPreparedStatement.setLong(prmIndex++, node.getId());
- updateNodeWayPreparedStatement.executeUpdate();
- } catch (SQLException e) {
- throw new OsmosisRuntimeException("Unable to update way bboxes related to node " + node.getId() + ".", e);
- }
}
}
@@ -267,7 +244,6 @@
deleteWayTagStatement = dbCtx.prepareStatement(DELETE_SQL_WAY_TAG);
insertWayNodeStatement = dbCtx.prepareStatement(INSERT_SQL_WAY_NODE);
deleteWayNodeStatement = dbCtx.prepareStatement(DELETE_SQL_WAY_NODE);
- updateWayBboxStatement = dbCtx.prepareStatement(UPDATE_WAY_BBOX);
}
// Delete any existing records for the way.
@@ -326,12 +302,6 @@
}
prmIndex = 1;
- try {
- updateWayBboxStatement.setLong(prmIndex++, way.getId());
- updateWayBboxStatement.executeUpdate();
- } catch (SQLException e) {
- throw new OsmosisRuntimeException("Unable to update bbox for way " + way.getId() + ".", e);
- }
}
}
}
diff -Nur osmosis-0.29/src/com/bretth/osmosis/core/pgsql/v0_5/PostgreSqlWriter.java osmosis-0.29.flo/src/com/bretth/osmosis/core/pgsql/v0_5/PostgreSqlWriter.java
--- osmosis-0.29/src/com/bretth/osmosis/core/pgsql/v0_5/PostgreSqlWriter.java 2008-07-05 00:14:16.000000000 +0200
+++ osmosis-0.29.flo/src/com/bretth/osmosis/core/pgsql/v0_5/PostgreSqlWriter.java 2008-09-07 10:24:41.000000000 +0200
@@ -56,7 +56,6 @@
"DROP INDEX idx_nodes_geom",
"DROP INDEX idx_way_tags_way_id",
"DROP INDEX idx_relation_tags_relation_id",
- "DROP INDEX idx_ways_bbox"
};
private static final String POST_LOAD_SQL[] = {
@@ -68,8 +67,6 @@
"CREATE INDEX idx_nodes_geom ON nodes USING gist (geom)",
"CREATE INDEX idx_way_tags_way_id ON way_tags USING btree (way_id)",
"CREATE INDEX idx_relation_tags_relation_id ON relation_tags USING btree (relation_id)",
- "UPDATE ways SET bbox = (SELECT Envelope(Collect(geom)) FROM nodes JOIN way_nodes ON way_nodes.node_id = nodes.id WHERE way_nodes.way_id = ways.id)",
- "CREATE INDEX idx_ways_bbox ON ways USING gist (bbox)"
};
Flo
--
Florian Lohoff flo at rfc822.org +49-171-2280134
Those who would give up a little freedom to get a little
security shall soon have neither - Benjamin Franklin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20080908/8fecdb68/attachment.pgp>
More information about the dev
mailing list