Examples with RelationContainer org.openstreetmap.osmosis.core.container.v0_6.RelationContainer used on opensource projects

Example 16 with RelationContainer

use of org.openstreetmap.osmosis.core.container.v0_6.RelationContainer in project osmosis by openstreetmap.

the class PostgreSqlDatasetContext method iterateBoundingBox.

/**
 * {@inheritDoc}
 */
@Override
public ReleasableIterator<EntityContainer> iterateBoundingBox(double left, double right, double top, double bottom, boolean completeWays) {
    List<Bound> bounds;
    Point[] bboxPoints;
    Polygon bboxPolygon;
    int rowCount;
    List<ReleasableIterator<EntityContainer>> resultSets;
    if (!initialized) {
        initialize();
    }
    // Build the bounds list.
    bounds = new ArrayList<Bound>();
    bounds.add(new Bound(right, left, top, bottom, "Osmosis " + OsmosisConstants.VERSION));
    // PostgreSQL sometimes incorrectly chooses to perform full table scans, these options
    // prevent this. Note that this is not recommended practice according to documentation
    // but fixing this would require modifying the table statistics gathering
    // configuration to produce better plans.
    jdbcTemplate.update("SET enable_seqscan = false");
    jdbcTemplate.update("SET enable_mergejoin = false");
    jdbcTemplate.update("SET enable_hashjoin = false");
    // Build a polygon representing the bounding box.
    // Sample box for query testing may be:
    // GeomFromText('POLYGON((144.93912192855174 -37.82981987499741,
    // 144.93912192855174 -37.79310006709244, 144.98188026000003
    // -37.79310006709244, 144.98188026000003 -37.82981987499741,
    // 144.93912192855174 -37.82981987499741))', -1)
    bboxPoints = new Point[5];
    bboxPoints[0] = new Point(left, bottom);
    bboxPoints[1] = new Point(left, top);
    bboxPoints[2] = new Point(right, top);
    bboxPoints[3] = new Point(right, bottom);
    bboxPoints[4] = new Point(left, bottom);
    bboxPolygon = polygonBuilder.createPolygon(bboxPoints);
    // Select all nodes inside the box into the node temp table.
    LOG.finer("Selecting all nodes inside bounding box.");
    rowCount = jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_nodes ON COMMIT DROP AS" + " SELECT * FROM nodes WHERE (geom && ?)", new PGgeometry(bboxPolygon));
    LOG.finer("Adding a primary key to the temporary nodes table.");
    jdbcTemplate.update("ALTER TABLE ONLY bbox_nodes ADD CONSTRAINT pk_bbox_nodes PRIMARY KEY (id)");
    LOG.finer("Updating query analyzer statistics on the temporary nodes table.");
    jdbcTemplate.update("ANALYZE bbox_nodes");
    // Select all ways inside the bounding box into the way temp table.
    if (capabilityChecker.isWayLinestringSupported()) {
        LOG.finer("Selecting all ways inside bounding box using way linestring geometry.");
        // We have full way geometry available so select ways
        // overlapping the requested bounding box.
        rowCount = jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS" + " SELECT * FROM ways WHERE (linestring && ?)", new PGgeometry(bboxPolygon));
    } else if (capabilityChecker.isWayBboxSupported()) {
        LOG.finer("Selecting all ways inside bounding box using dynamically built" + " way linestring with way bbox indexing.");
        // The inner query selects the way id and node coordinates for
        // all ways constrained by the way bounding box which is
        // indexed.
        // The middle query converts the way node coordinates into
        // linestrings.
        // The outer query constrains the query to the linestrings
        // inside the bounding box. These aren't indexed but the inner
        // query way bbox constraint will minimise the unnecessary data.
        rowCount = jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS" + " SELECT w.* FROM (" + "SELECT c.id AS id, First(c.version) AS version, First(c.user_id) AS user_id," + " First(c.tstamp) AS tstamp, First(c.changeset_id) AS changeset_id, First(c.tags) AS tags," + " First(c.nodes) AS nodes, ST_MakeLine(c.geom) AS way_line FROM (" + "SELECT w.*, n.geom AS geom FROM nodes n" + " INNER JOIN way_nodes wn ON n.id = wn.node_id" + " INNER JOIN ways w ON wn.way_id = w.id" + " WHERE (w.bbox && ?) ORDER BY wn.way_id, wn.sequence_id" + ") c " + "GROUP BY c.id" + ") w " + "WHERE (w.way_line && ?)", new PGgeometry(bboxPolygon), new PGgeometry(bboxPolygon));
    } else {
        LOG.finer("Selecting all way ids inside bounding box using already selected nodes.");
        // No way bbox support is available so select ways containing
        // the selected nodes.
        rowCount = jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS" + " SELECT w.* FROM ways w" + " INNER JOIN (" + " SELECT wn.way_id FROM way_nodes wn" + " INNER JOIN bbox_nodes n ON wn.node_id = n.id GROUP BY wn.way_id" + ") wids ON w.id = wids.way_id");
    }
    LOG.finer(rowCount + " rows affected.");
    LOG.finer("Adding a primary key to the temporary ways table.");
    jdbcTemplate.update("ALTER TABLE ONLY bbox_ways ADD CONSTRAINT pk_bbox_ways PRIMARY KEY (id)");
    LOG.finer("Updating query analyzer statistics on the temporary ways table.");
    jdbcTemplate.update("ANALYZE bbox_ways");
    // Select all relations containing the nodes or ways into the relation table.
    LOG.finer("Selecting all relation ids containing selected nodes or ways.");
    rowCount = jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_relations ON COMMIT DROP AS" + " SELECT r.* FROM relations r" + " INNER JOIN (" + "    SELECT relation_id FROM (" + "        SELECT rm.relation_id AS relation_id FROM relation_members rm" + "        INNER JOIN bbox_nodes n ON rm.member_id = n.id WHERE rm.member_type = 'N' " + "        UNION " + "        SELECT rm.relation_id AS relation_id FROM relation_members rm" + "        INNER JOIN bbox_ways w ON rm.member_id = w.id WHERE rm.member_type = 'W'" + "     ) rids GROUP BY relation_id" + ") rids ON r.id = rids.relation_id");
    LOG.finer(rowCount + " rows affected.");
    LOG.finer("Adding a primary key to the temporary relations table.");
    jdbcTemplate.update("ALTER TABLE ONLY bbox_relations ADD CONSTRAINT pk_bbox_relations PRIMARY KEY (id)");
    LOG.finer("Updating query analyzer statistics on the temporary relations table.");
    jdbcTemplate.update("ANALYZE bbox_relations");
    // relation table and repeat until no more inclusions occur.
    do {
        LOG.finer("Selecting parent relations of selected relations.");
        rowCount = jdbcTemplate.update("INSERT INTO bbox_relations " + "SELECT r.* FROM relations r INNER JOIN (" + "    SELECT rm.relation_id FROM relation_members rm" + "    INNER JOIN bbox_relations br ON rm.member_id = br.id" + "    WHERE rm.member_type = 'R' AND NOT EXISTS (" + "        SELECT * FROM bbox_relations br2 WHERE rm.relation_id = br2.id" + "    ) GROUP BY rm.relation_id" + ") rids ON r.id = rids.relation_id");
        LOG.finer(rowCount + " rows affected.");
    } while (rowCount > 0);
    LOG.finer("Updating query analyzer statistics on the temporary relations table.");
    jdbcTemplate.update("ANALYZE bbox_relations");
    // If complete ways is set, select all nodes contained by the ways into the node temp table.
    if (completeWays) {
        LOG.finer("Selecting all nodes for selected ways.");
        jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP");
        jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()");
        jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS " + "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn " + "WHERE NOT EXISTS (" + "    SELECT * FROM bbox_nodes WHERE id = buwn.id" + ");");
        jdbcTemplate.update("ALTER TABLE ONLY bbox_missing_way_nodes" + " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)");
        jdbcTemplate.update("ANALYZE bbox_missing_way_nodes");
        rowCount = jdbcTemplate.update("INSERT INTO bbox_nodes " + "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;");
        LOG.finer(rowCount + " rows affected.");
    }
    LOG.finer("Updating query analyzer statistics on the temporary nodes table.");
    jdbcTemplate.update("ANALYZE bbox_nodes");
    // Create iterators for the selected records for each of the entity types.
    LOG.finer("Iterating over results.");
    resultSets = new ArrayList<ReleasableIterator<EntityContainer>>();
    resultSets.add(new UpcastIterator<EntityContainer, BoundContainer>(new BoundContainerIterator(new ReleasableAdaptorForIterator<Bound>(bounds.iterator()))));
    resultSets.add(new UpcastIterator<EntityContainer, NodeContainer>(new NodeContainerIterator(nodeDao.iterate("bbox_"))));
    resultSets.add(new UpcastIterator<EntityContainer, WayContainer>(new WayContainerIterator(wayDao.iterate("bbox_"))));
    resultSets.add(new UpcastIterator<EntityContainer, RelationContainer>(new RelationContainerIterator(relationDao.iterate("bbox_"))));
    // Merge all readers into a single result iterator and return.
    return new MultipleSourceIterator<EntityContainer>(resultSets);
}

Example 17 with RelationContainer

use of org.openstreetmap.osmosis.core.container.v0_6.RelationContainer in project osmosis by openstreetmap.

the class PostgreSqlDatasetContext method iterateBoundingBox.

/**
 * {@inheritDoc}
 */
@Override
public ReleasableIterator<EntityContainer> iterateBoundingBox(double left, double right, double top, double bottom, boolean completeWays) {
    List<Bound> bounds;
    PreparedStatement preparedStatement = null;
    int prmIndex;
    Point[] bboxPoints;
    Polygon bboxPolygon;
    MemberTypeValueMapper memberTypeValueMapper;
    int rowCount;
    List<ReleasableIterator<EntityContainer>> resultSets;
    if (!initialized) {
        initialize();
    }
    // Build the bounds list.
    bounds = new ArrayList<Bound>();
    bounds.add(new Bound(right, left, top, bottom, "Osmosis " + OsmosisConstants.VERSION));
    try {
        // PostgreSQL sometimes incorrectly chooses to perform full table scans, these options
        // prevent this. Note that this is not recommended practice according to documentation
        // but fixing this would require modifying the table statistics gathering
        // configuration to produce better plans.
        dbCtx.executeStatement("SET enable_seqscan = false");
        dbCtx.executeStatement("SET enable_mergejoin = false");
        dbCtx.executeStatement("SET enable_hashjoin = false");
        // Create a temporary table capable of holding node ids.
        LOG.finer("Creating node id temp table.");
        dbCtx.executeStatement("CREATE TEMPORARY TABLE box_node_list (id bigint PRIMARY KEY) ON COMMIT DROP");
        // Create a temporary table capable of holding way ids.
        LOG.finer("Creating way id temp table.");
        dbCtx.executeStatement("CREATE TEMPORARY TABLE box_way_list (id bigint PRIMARY KEY) ON COMMIT DROP");
        // Create a temporary table capable of holding relation ids.
        LOG.finer("Creating relation id temp table.");
        dbCtx.executeStatement("CREATE TEMPORARY TABLE box_relation_list (id bigint PRIMARY KEY) ON COMMIT DROP");
        // Build a polygon representing the bounding box.
        // Sample box for query testing may be:
        // GeomFromText('POLYGON((144.93912192855174 -37.82981987499741,
        // 144.93912192855174 -37.79310006709244, 144.98188026000003
        // -37.79310006709244, 144.98188026000003 -37.82981987499741,
        // 144.93912192855174 -37.82981987499741))', -1)
        bboxPoints = new Point[5];
        bboxPoints[0] = new Point(left, bottom);
        bboxPoints[1] = new Point(left, top);
        bboxPoints[2] = new Point(right, top);
        bboxPoints[3] = new Point(right, bottom);
        bboxPoints[4] = new Point(left, bottom);
        bboxPolygon = polygonBuilder.createPolygon(bboxPoints);
        // Instantiate the mapper for converting between entity types and
        // member type values.
        memberTypeValueMapper = new MemberTypeValueMapper();
        // Select all nodes inside the box into the node temp table.
        LOG.finer("Selecting all node ids inside bounding box.");
        preparedStatement = dbCtx.prepareStatement("INSERT INTO box_node_list SELECT id FROM nodes WHERE (geom && ?)");
        prmIndex = 1;
        preparedStatement.setObject(prmIndex++, new PGgeometry(bboxPolygon));
        rowCount = preparedStatement.executeUpdate();
        preparedStatement.close();
        preparedStatement = null;
        LOG.finer(rowCount + " rows affected.");
        // Select all ways inside the bounding box into the way temp table.
        if (capabilityChecker.isWayLinestringSupported()) {
            LOG.finer("Selecting all way ids inside bounding box using way linestring geometry.");
            // We have full way geometry available so select ways
            // overlapping the requested bounding box.
            preparedStatement = dbCtx.prepareStatement("INSERT INTO box_way_list " + "SELECT id FROM ways w where w.linestring && ?");
            prmIndex = 1;
            preparedStatement.setObject(prmIndex++, new PGgeometry(bboxPolygon));
        } else if (capabilityChecker.isWayBboxSupported()) {
            LOG.finer("Selecting all way ids inside bounding box using dynamically built" + " way linestring with way bbox indexing.");
            // The inner query selects the way id and node coordinates for
            // all ways constrained by the way bounding box which is
            // indexed.
            // The middle query converts the way node coordinates into
            // linestrings.
            // The outer query constrains the query to the linestrings
            // inside the bounding box. These aren't indexed but the inner
            // query way bbox constraint will minimise the unnecessary data.
            preparedStatement = dbCtx.prepareStatement("INSERT INTO box_way_list " + "SELECT way_id FROM (" + "SELECT c.way_id AS way_id, ST_MakeLine(c.geom) AS way_line FROM (" + "SELECT w.id AS way_id, n.geom AS geom FROM nodes n" + " INNER JOIN way_nodes wn ON n.id = wn.node_id" + " INNER JOIN ways w ON wn.way_id = w.id" + " WHERE (w.bbox && ?) ORDER BY wn.way_id, wn.sequence_id" + ") c " + "GROUP BY c.way_id" + ") w " + "WHERE (w.way_line && ?)");
            prmIndex = 1;
            preparedStatement.setObject(prmIndex++, new PGgeometry(bboxPolygon));
            preparedStatement.setObject(prmIndex++, new PGgeometry(bboxPolygon));
        } else {
            LOG.finer("Selecting all way ids inside bounding box using already selected nodes.");
            // No way bbox support is available so select ways containing
            // the selected nodes.
            preparedStatement = dbCtx.prepareStatement("INSERT INTO box_way_list " + "SELECT wn.way_id FROM way_nodes wn INNER JOIN box_node_list n ON wn.node_id = n.id" + " GROUP BY wn.way_id");
        }
        rowCount = preparedStatement.executeUpdate();
        preparedStatement.close();
        preparedStatement = null;
        LOG.finer(rowCount + " rows affected.");
        // Select all relations containing the nodes or ways into the relation table.
        LOG.finer("Selecting all relation ids containing selected nodes or ways.");
        preparedStatement = dbCtx.prepareStatement("INSERT INTO box_relation_list (" + "SELECT rm.relation_id AS relation_id FROM relation_members rm" + " INNER JOIN box_node_list n ON rm.member_id = n.id WHERE rm.member_type = ? " + "UNION " + "SELECT rm.relation_id AS relation_id FROM relation_members rm" + " INNER JOIN box_way_list w ON rm.member_id = w.id WHERE rm.member_type = ?" + ")");
        prmIndex = 1;
        preparedStatement.setString(prmIndex++, memberTypeValueMapper.getMemberType(EntityType.Node));
        preparedStatement.setString(prmIndex++, memberTypeValueMapper.getMemberType(EntityType.Way));
        rowCount = preparedStatement.executeUpdate();
        preparedStatement.close();
        preparedStatement = null;
        LOG.finer(rowCount + " rows affected.");
        // relation table and repeat until no more inclusions occur.
        do {
            LOG.finer("Selecting parent relations of selected relations.");
            preparedStatement = dbCtx.prepareStatement("INSERT INTO box_relation_list " + "SELECT rm.relation_id AS relation_id FROM relation_members rm" + " INNER JOIN box_relation_list r ON rm.member_id = r.id WHERE rm.member_type = ? " + "EXCEPT " + "SELECT id AS relation_id FROM box_relation_list");
            prmIndex = 1;
            preparedStatement.setString(prmIndex++, memberTypeValueMapper.getMemberType(EntityType.Relation));
            rowCount = preparedStatement.executeUpdate();
            preparedStatement.close();
            preparedStatement = null;
            LOG.finer(rowCount + " rows affected.");
        } while (rowCount > 0);
        // If complete ways is set, select all nodes contained by the ways into the node temp table.
        if (completeWays) {
            LOG.finer("Selecting all node ids for selected ways.");
            preparedStatement = dbCtx.prepareStatement("INSERT INTO box_node_list " + "SELECT wn.node_id AS id FROM way_nodes wn INNER JOIN box_way_list bw ON wn.way_id = bw.id " + "EXCEPT " + "SELECT id AS node_id FROM box_node_list");
            prmIndex = 1;
            rowCount = preparedStatement.executeUpdate();
            preparedStatement.close();
            preparedStatement = null;
            LOG.finer(rowCount + " rows affected.");
        }
        // Analyse the temporary tables to give the query planner the best chance of producing good queries.
        dbCtx.executeStatement("ANALYZE box_node_list");
        dbCtx.executeStatement("ANALYZE box_way_list");
        dbCtx.executeStatement("ANALYZE box_relation_list");
        // Create iterators for the selected records for each of the entity types.
        LOG.finer("Iterating over results.");
        resultSets = new ArrayList<ReleasableIterator<EntityContainer>>();
        resultSets.add(new UpcastIterator<EntityContainer, BoundContainer>(new BoundContainerIterator(new ReleasableAdaptorForIterator<Bound>(bounds.iterator()))));
        resultSets.add(new UpcastIterator<EntityContainer, NodeContainer>(new NodeContainerIterator(new NodeReader(dbCtx, "box_node_list"))));
        resultSets.add(new UpcastIterator<EntityContainer, WayContainer>(new WayContainerIterator(new WayReader(dbCtx, "box_way_list"))));
        resultSets.add(new UpcastIterator<EntityContainer, RelationContainer>(new RelationContainerIterator(new RelationReader(dbCtx, "box_relation_list"))));
        // Merge all readers into a single result iterator and return.
        return new MultipleSourceIterator<EntityContainer>(resultSets);
    } catch (SQLException e) {
        throw new OsmosisRuntimeException("Unable to perform bounding box queries.", e);
    } finally {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                // We are already in an error condition so log and continue.
                LOG.log(Level.WARNING, "Unable to close prepared statement.", e);
            }
        }
    }
}

Example 18 with RelationContainer

use of org.openstreetmap.osmosis.core.container.v0_6.RelationContainer in project osmosis by openstreetmap.

the class PostgreSqlDatasetContext method iterate.

/**
 * {@inheritDoc}
 */
@Override
public ReleasableIterator<EntityContainer> iterate() {
    List<Bound> bounds;
    List<ReleasableIterator<EntityContainer>> sources;
    if (!initialized) {
        initialize();
    }
    // Build the bounds list.
    bounds = new ArrayList<Bound>();
    bounds.add(new Bound("Osmosis " + OsmosisConstants.VERSION));
    sources = new ArrayList<ReleasableIterator<EntityContainer>>();
    sources.add(new UpcastIterator<EntityContainer, BoundContainer>(new BoundContainerIterator(new ReleasableAdaptorForIterator<Bound>(bounds.iterator()))));
    sources.add(new UpcastIterator<EntityContainer, NodeContainer>(new NodeContainerIterator(nodeDao.iterate())));
    sources.add(new UpcastIterator<EntityContainer, WayContainer>(new WayContainerIterator(wayDao.iterate())));
    sources.add(new UpcastIterator<EntityContainer, RelationContainer>(new RelationContainerIterator(relationDao.iterate())));
    return new MultipleSourceIterator<EntityContainer>(sources);
}