topo clean workflow

topo.txt

* 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();
}