Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- * Extract linework from input (if polys) with ST_Boundary.
- * Load each linestring from linework in separate transactions (or batches) with TopoGeo_AddLineString.
- * If multiple lineworks, load them each to a topo separately and start with the linework most wanted as a reference.
- * Load reference linework to a new topo.
- * Load first topo edge_data in separate transactions per edge, rollback if area created is smaller than wanted.
- Create faces with original attributes using two steps.
- 1. Step 1, create face geometry and point and indexes
- drop table areas;
- create table areas as
- select
- ST_GetFacegeometry('topo', face_id) geom,
- ST_PointOnSurface(ST_GetFacegeometry('topo', face_id)) point
- from topo.face
- where face_id>0;
- CREATE INDEX ON areas USING gist (geom);
- CREATE INDEX ON areas USING gist (point);
- 2. Step 2, left join in attributes
- drop table areas_attr;
- create table areas_attr as
- select
- kommunkod,
- kommunnamn,
- distrkod,
- distrnamn,
- a.geom::geometry(Polygon, 3006)
- from areas a
- left join test.kommuner k on ST_Intersects(k.geom, a.point)
- left join test.distrikt d on ST_Intersects(d.geom, a.point);
- CREATE INDEX ON areas_attr USING gist (geom);
- ### Batch topo loader
- HikariDataSource ds = new HikariDataSource();
- ds.setJdbcUrl("jdbc:postgresql://localhost/lm");
- ds.setUsername("postgres");
- ds.setPassword("postgres");
- Connection connection = ds.getConnection();
- Statement statement = connection.createStatement();
- statement.setFetchSize(100);
- ResultSet resultSet = statement.executeQuery("SELECT gid, geom FROM input");
- Connection connection2 = null;
- PreparedStatement add = null;
- PreparedStatement error = null;
- int count = 0;
- while (resultSet.next()) {
- logger.info("Processing row " + count);
- if (count % 100 == 0) {
- if (connection2 != null) connection2.close();
- connection2 = ds.getConnection();
- add = connection2.prepareStatement("SELECT TopoGeo_AddLineString('topo1', ?, 0.5)");
- error = connection2.prepareStatement("INSERT INTO test.errors VALUES (?, ?)");
- }
- Object geom = resultSet.getObject("geom");
- add.setObject(1, geom);
- try {
- add.execute();
- } catch (PSQLException e) {
- error.setInt(1, resultSet.getInt("gid"));
- error.setString(2, e.getMessage());
- error.execute();
- }
- if (count % 100 == 0) {
- connection2.createStatement().execute("VACUUM ANALYZE topo1.edge_data;");
- connection2.createStatement().execute("VACUUM ANALYZE topo1.face;");
- connection2.createStatement().execute("VACUUM ANALYZE topo1.node;");
- }
- count++;
- }
- resultSet.close();
- statement.close();
- connection.close();
- ds.close();
- ### Single edge loader with too small area rollback
- static double getArea(Connection connection, int face_id) throws SQLException {
- ResultSet rs = connection.createStatement().executeQuery("select ST_Area(ST_GetFaceGeometry('topo2', " + face_id + "))");
- rs.next();
- double area = rs.getDouble(1);
- rs.close();
- return area;
- }
- public static void main(String[] args) throws SQLException {
- HikariDataSource ds = new HikariDataSource();
- ds.setJdbcUrl("jdbc:postgresql://localhost/lm");
- ds.setUsername("postgres");
- ds.setPassword("postgres");
- Connection connection = ds.getConnection();
- Statement statement = connection.createStatement();
- statement.setFetchSize(100);
- ResultSet resultSet = statement.executeQuery("SELECT edge_id, geom FROM topo1.edge_data");
- Connection connection2 = null;
- PreparedStatement add = null;
- PreparedStatement error = null;
- int count = 0;
- while (resultSet.next()) {
- logger.info("Processing row " + count);
- if (connection2 != null) connection2.close();
- connection2 = ds.getConnection();
- connection2.setAutoCommit(false);
- add = connection2.prepareStatement("SELECT TopoGeo_AddLineString('topo2', ?, 0.5)");
- error = connection.prepareStatement("INSERT INTO test.errors VALUES (?, ?)");
- Object geom = resultSet.getObject("geom");
- add.setObject(1, geom);
- try {
- ResultSet trs = add.executeQuery();
- boolean tooSmall = false;
- while (trs.next()) {
- int edge_id = trs.getInt(1);
- ResultSet trs2 = connection2.createStatement().executeQuery("select left_face, right_face from topo2.edge_data where edge_id=" + edge_id);
- trs2.next();
- int left_face_id = trs2.getInt(1);
- int right_face_id = trs2.getInt(2);
- trs2.close();
- if (left_face_id > 0) {
- if (getArea(connection2, left_face_id) < 60000)
- tooSmall = true;
- }
- if (right_face_id > 0) {
- if (getArea(connection2, right_face_id) < 60000)
- tooSmall = true;
- }
- }
- trs.close();
- if (tooSmall) {
- logger.info("Too small area created (will roll back) ");
- connection2.rollback();
- } else {
- connection2.commit();
- }
- } catch (PSQLException e) {
- error.setInt(1, resultSet.getInt("edge_id"));
- error.setString(2, e.getMessage());
- error.execute();
- connection2.rollback();
- }
- count++;
- }
- resultSet.close();
- statement.close();
- connection.close();
- ds.close();
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement