Last active
October 14, 2016 11:34
-
-
Save bjornharrtell/2f34f1b6b7aaf9f7fa39faf36ca95845 to your computer and use it in GitHub Desktop.
topo clean workflow
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
* 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(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment