Use NoiseModelling with a PostGIS database¶
Introduction¶
NoiseModelling is distributed with GeoServer. This application has been preconfigured to use H2GIS as the default database.
H2GIS does not need to be configured or installed on the system and is therefore perfectly suitable as a default database.
However, you may want to connect NoiseModelling to a PostgreSQL/PostGIS database (this option may be interesting especially if you are using huge datasets (e.g on large area)).
That is why NoiseModelling has been written with the idea of maintaining the H2GIS/PostGIS compatibility.
This tutorial will not cover the steps for installing and configuring a PostGIS database.
Connect with Java¶
First you have to add some libraries. We will use PostgreSQL/PostGIS wrapper available in the H2GIS library:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <properties> <h2gis-version>2.1.0-SNAPSHOT<</h2gis-version> <noisemodelling-version>4.0.0</noisemodelling-version> </properties> <dependencies> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.12</version> </dependency> <dependency> <groupId>org.orbisgis</groupId> <artifactId>noisemodelling-emission</artifactId> <version>${noisemodelling-version}</version> </dependency> <dependency> <groupId>org.orbisgis</groupId> <artifactId>noisemodelling-propagation</artifactId> <version>${noisemodelling-version}</version> </dependency> <dependency> <groupId>org.orbisgis</groupId> <artifactId>h2gis</artifactId> <version>${h2gis-version}</version> </dependency> <dependency> <groupId>org.orbisgis</groupId> <artifactId>h2gis-api</artifactId> <version>${h2gis-version}</version> </dependency> <dependency> <groupId>org.orbisgis</groupId> <artifactId>h2gis-utilities</artifactId> <version>${h2gis-version}</version> </dependency> <dependency> <groupId>org.orbisgis</groupId> <artifactId>postgis-jts-osgi</artifactId> <version>${h2gis-version}</version> </dependency> </dependencies> </project> |
The new dependency here is postgis-jts-osgi
. It contains some code to convert PostGIS geometries objects into/from JTS objects.
In your code you have to import the PostGIS wrapper class and some utility class:
1 2 3 4 5 6 7 8 9 10 | import org.h2gis.functions.io.geojson.GeoJsonRead; import org.h2gis.postgis_jts_osgi.DataSourceFactoryImpl; import java.net.ConnectException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashSet; import java.util.Locale; |
Then use it to connect to you local or remote PostGIS database and obtain a valid JDBC connection object:
1 2 3 4 5 6 7 8 9 10 | public static void main() throws Exception { DataSourceFactoryImpl dataSourceFactory = new DataSourceFactoryImpl(); Properties p = new Properties(); p.setProperty("serverName", "localhost"); p.setProperty("portNumber", "5432"); p.setProperty("databaseName", "postgres"); p.setProperty("user", "postgres"); p.setProperty("password", ""); try(Connection connection = SFSUtilities.wrapConnection(dataSourceFactory.createDataSource(p).getConnection())) { Statement sql = connection.createStatement(); |
Finally you can use the NoiseModelling functions as usual:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 | package org.noise_planet.nmtutorial01; import org.h2gis.api.EmptyProgressVisitor; import org.h2gis.api.ProgressVisitor; import org.h2gis.functions.io.csv.CSVDriverFunction; import org.h2gis.functions.io.geojson.GeoJsonRead; import org.h2gis.postgis_jts_osgi.DataSourceFactoryImpl; import org.h2gis.utilities.SFSUtilities; import org.junit.Test; import org.noise_planet.noisemodelling.emission.jdbc.LDENConfig; import org.noise_planet.noisemodelling.emission.jdbc.LDENPointNoiseMapFactory; import org.noise_planet.noisemodelling.propagation.ComputeRaysOut; import org.noise_planet.noisemodelling.propagation.IComputeRaysOut; import org.noise_planet.noisemodelling.propagation.RootProgressVisitor; import org.noise_planet.noisemodelling.propagation.jdbc.PointNoiseMap; import org.postgresql.util.PSQLException; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.net.ConnectException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashSet; import java.util.Locale; import java.util.Properties; import java.util.Set; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertTrue; public class Main { static Logger LOGGER = LoggerFactory.getLogger(Main.class); public static void main() throws Exception { DataSourceFactoryImpl dataSourceFactory = new DataSourceFactoryImpl(); Properties p = new Properties(); p.setProperty("serverName", "localhost"); p.setProperty("portNumber", "5432"); p.setProperty("databaseName", "postgres"); p.setProperty("user", "postgres"); p.setProperty("password", ""); try(Connection connection = SFSUtilities.wrapConnection(dataSourceFactory.createDataSource(p).getConnection())) { Statement sql = connection.createStatement(); // Clean DB sql.execute("DROP TABLE IF EXISTS BUILDINGS"); sql.execute("DROP TABLE IF EXISTS LW_ROADS"); sql.execute("DROP TABLE IF EXISTS RECEIVERS"); sql.execute("DROP TABLE IF EXISTS DEM"); // Import BUILDINGS LOGGER.info("Import buildings"); GeoJsonRead.readGeoJson(connection, Main.class.getResource("buildings.geojson").getFile(), "BUILDINGS"); // Import noise source LOGGER.info("Import noise source"); GeoJsonRead.readGeoJson(connection, Main.class.getResource("lw_roads.geojson").getFile(), "lw_roads"); // Set primary key sql.execute("ALTER TABLE lw_roads ADD CONSTRAINT lw_roads_pk PRIMARY KEY (\"PK\");"); // Import BUILDINGS LOGGER.info("Import evaluation coordinates"); GeoJsonRead.readGeoJson(connection, Main.class.getResource("receivers.geojson").getFile(), "receivers"); // Set primary key sql.execute("ALTER TABLE receivers ADD CONSTRAINT RECEIVERS_pk PRIMARY KEY (\"PK\");"); // Import MNT LOGGER.info("Import digital elevation model"); GeoJsonRead.readGeoJson(connection, Main.class.getResource("dem_lorient.geojson").getFile(), "dem"); // Init NoiseModelling PointNoiseMap pointNoiseMap = new PointNoiseMap("buildings", "lw_roads", "receivers"); pointNoiseMap.setMaximumPropagationDistance(160.0d); pointNoiseMap.setSoundReflectionOrder(0); pointNoiseMap.setComputeHorizontalDiffraction(true); pointNoiseMap.setComputeVerticalDiffraction(true); // Building height field name pointNoiseMap.setHeightField("HEIGHT"); // Point cloud height above sea level POINT(X Y Z) pointNoiseMap.setDemTable("DEM"); // Do not propagate for low emission or far away sources. // error in dB pointNoiseMap.setMaximumError(0.1d); // Init custom input in order to compute more than just attenuation // LW_ROADS contain Day Evening Night emission spectrum LDENConfig ldenConfig = new LDENConfig(LDENConfig.INPUT_MODE.INPUT_MODE_LW_DEN); ldenConfig.setComputeLDay(true); ldenConfig.setComputeLEvening(true); ldenConfig.setComputeLNight(true); ldenConfig.setComputeLDEN(true); LDENPointNoiseMapFactory tableWriter = new LDENPointNoiseMapFactory(connection, ldenConfig); tableWriter.setKeepRays(true); pointNoiseMap.setPropagationProcessDataFactory(tableWriter); pointNoiseMap.setComputeRaysOutFactory(tableWriter); RootProgressVisitor progressLogger = new RootProgressVisitor(1, true, 1); pointNoiseMap.initialize(connection, new EmptyProgressVisitor()); // force the creation of a 2x2 cells pointNoiseMap.setGridDim(2); // Set of already processed receivers Set<Long> receivers = new HashSet<>(); ProgressVisitor progressVisitor = progressLogger.subProcess(pointNoiseMap.getGridDim()*pointNoiseMap.getGridDim()); LOGGER.info("start"); long start = System.currentTimeMillis(); // Iterate over computation areas try { tableWriter.start(); for (int i = 0; i < pointNoiseMap.getGridDim(); i++) { for (int j = 0; j < pointNoiseMap.getGridDim(); j++) { // Run ray propagation IComputeRaysOut out = pointNoiseMap.evaluateCell(connection, i, j, progressVisitor, receivers); } } } finally { tableWriter.stop(); } long computationTime = System.currentTimeMillis() - start; logger.info(String.format(Locale.ROOT, "Computed in %d ms, %.2f ms per receiver", computationTime,computationTime / (double)receivers.size())); // Export result tables as csv files CSVDriverFunction csv = new CSVDriverFunction(); csv.exportTable(connection, ldenConfig.getlDayTable(), new File(ldenConfig.getlDayTable()+".csv"), new EmptyProgressVisitor()); csv.exportTable(connection, ldenConfig.getlEveningTable(), new File(ldenConfig.getlEveningTable()+".csv"), new EmptyProgressVisitor()); csv.exportTable(connection, ldenConfig.getlNightTable(), new File(ldenConfig.getlNightTable()+".csv"), new EmptyProgressVisitor()); csv.exportTable(connection, ldenConfig.getlDenTable(), new File(ldenConfig.getlDenTable()+".csv"), new EmptyProgressVisitor()); } catch (PSQLException ex) { if (ex.getCause() instanceof ConnectException) { // Connection issue ignore LOGGER.warn("Connection error to local PostGIS, ignored", ex); } else { throw ex; } } catch (SQLException ex) { LOGGER.error(ex.getLocalizedMessage(), ex.getNextException()); throw ex; } } } |