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<?xml version="1.0" encoding="UTF-8"?>
 2<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 3         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
 4    <properties>
 5        <h2gis-version>2.1.0-SNAPSHOT<</h2gis-version>
 6        <noisemodelling-version>4.0.0</noisemodelling-version>
 7    </properties>
 8    <dependencies>
 9        <dependency>
10            <groupId>org.slf4j</groupId>
11            <artifactId>slf4j-simple</artifactId>
12            <version>1.7.12</version>
13        </dependency>
14        <dependency>
15            <groupId>org.orbisgis</groupId>
16            <artifactId>noisemodelling-emission</artifactId>
17            <version>${noisemodelling-version}</version>
18        </dependency>
19        <dependency>
20            <groupId>org.orbisgis</groupId>
21            <artifactId>noisemodelling-propagation</artifactId>
22            <version>${noisemodelling-version}</version>
23        </dependency>
24        <dependency>
25            <groupId>org.orbisgis</groupId>
26            <artifactId>h2gis</artifactId>
27            <version>${h2gis-version}</version>
28        </dependency>
29        <dependency>
30            <groupId>org.orbisgis</groupId>
31            <artifactId>h2gis-api</artifactId>
32            <version>${h2gis-version}</version>
33        </dependency>
34        <dependency>
35            <groupId>org.orbisgis</groupId>
36            <artifactId>h2gis-utilities</artifactId>
37            <version>${h2gis-version}</version>
38        </dependency>
39        <dependency>
40            <groupId>org.orbisgis</groupId>
41            <artifactId>postgis-jts-osgi</artifactId>
42            <version>${h2gis-version}</version>
43        </dependency>
44    </dependencies>
45</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:

 1import org.h2gis.functions.io.geojson.GeoJsonRead;
 2import org.h2gis.postgis_jts_osgi.DataSourceFactoryImpl;
 3
 4import java.net.ConnectException;
 5import java.sql.Connection;
 6import java.sql.ResultSet;
 7import java.sql.SQLException;
 8import java.sql.Statement;
 9import java.util.HashSet;
10import java.util.Locale;

Then use it to connect to you local or remote PostGIS database and obtain a valid JDBC connection object:

 1    public static void main() throws Exception {
 2        DataSourceFactoryImpl dataSourceFactory = new DataSourceFactoryImpl();
 3        Properties p = new Properties();
 4        p.setProperty("serverName", "localhost");
 5        p.setProperty("portNumber", "5432");
 6        p.setProperty("databaseName", "postgres");
 7        p.setProperty("user", "postgres");
 8        p.setProperty("password", "");
 9        try(Connection connection = SFSUtilities.wrapConnection(dataSourceFactory.createDataSource(p).getConnection())) {
10            Statement sql = connection.createStatement();

Finally you can use the NoiseModelling functions as usual:

  1package org.noise_planet.nmtutorial01;
  2
  3import org.h2gis.api.EmptyProgressVisitor;
  4import org.h2gis.api.ProgressVisitor;
  5import org.h2gis.functions.io.csv.CSVDriverFunction;
  6import org.h2gis.functions.io.geojson.GeoJsonRead;
  7import org.h2gis.postgis_jts_osgi.DataSourceFactoryImpl;
  8import org.h2gis.utilities.SFSUtilities;
  9import org.junit.Test;
 10import org.noise_planet.noisemodelling.emission.jdbc.LDENConfig;
 11import org.noise_planet.noisemodelling.emission.jdbc.LDENPointNoiseMapFactory;
 12import org.noise_planet.noisemodelling.propagation.ComputeRaysOut;
 13import org.noise_planet.noisemodelling.propagation.IComputeRaysOut;
 14import org.noise_planet.noisemodelling.propagation.RootProgressVisitor;
 15import org.noise_planet.noisemodelling.propagation.jdbc.PointNoiseMap;
 16import org.postgresql.util.PSQLException;
 17import org.slf4j.Logger;
 18import org.slf4j.LoggerFactory;
 19
 20import java.net.ConnectException;
 21import java.sql.Connection;
 22import java.sql.ResultSet;
 23import java.sql.SQLException;
 24import java.sql.Statement;
 25import java.util.HashSet;
 26import java.util.Locale;
 27import java.util.Properties;
 28import java.util.Set;
 29
 30import static org.junit.Assert.assertEquals;
 31import static org.junit.Assert.assertTrue;
 32
 33public class Main {
 34    static Logger LOGGER = LoggerFactory.getLogger(Main.class);
 35
 36    public static void main() throws Exception {
 37        DataSourceFactoryImpl dataSourceFactory = new DataSourceFactoryImpl();
 38        Properties p = new Properties();
 39        p.setProperty("serverName", "localhost");
 40        p.setProperty("portNumber", "5432");
 41        p.setProperty("databaseName", "postgres");
 42        p.setProperty("user", "postgres");
 43        p.setProperty("password", "");
 44        try(Connection connection = SFSUtilities.wrapConnection(dataSourceFactory.createDataSource(p).getConnection())) {
 45            Statement sql = connection.createStatement();
 46
 47            // Clean DB
 48
 49            sql.execute("DROP TABLE IF EXISTS BUILDINGS");
 50            sql.execute("DROP TABLE IF EXISTS LW_ROADS");
 51            sql.execute("DROP TABLE IF EXISTS RECEIVERS");
 52            sql.execute("DROP TABLE IF EXISTS DEM");
 53
 54            // Import BUILDINGS
 55
 56            LOGGER.info("Import buildings");
 57
 58            GeoJsonRead.readGeoJson(connection, Main.class.getResource("buildings.geojson").getFile(), "BUILDINGS");
 59
 60            // Import noise source
 61
 62            LOGGER.info("Import noise source");
 63
 64            GeoJsonRead.readGeoJson(connection, Main.class.getResource("lw_roads.geojson").getFile(), "lw_roads");
 65            // Set primary key
 66            sql.execute("ALTER TABLE lw_roads ADD CONSTRAINT lw_roads_pk PRIMARY KEY (\"PK\");");
 67
 68            // Import BUILDINGS
 69
 70            LOGGER.info("Import evaluation coordinates");
 71
 72            GeoJsonRead.readGeoJson(connection, Main.class.getResource("receivers.geojson").getFile(), "receivers");
 73            // Set primary key
 74            sql.execute("ALTER TABLE receivers ADD CONSTRAINT RECEIVERS_pk PRIMARY KEY (\"PK\");");
 75
 76            // Import MNT
 77
 78            LOGGER.info("Import digital elevation model");
 79
 80            GeoJsonRead.readGeoJson(connection, Main.class.getResource("dem_lorient.geojson").getFile(), "dem");
 81
 82            // Init NoiseModelling
 83            PointNoiseMap pointNoiseMap = new PointNoiseMap("buildings", "lw_roads", "receivers");
 84
 85            pointNoiseMap.setMaximumPropagationDistance(160.0d);
 86            pointNoiseMap.setSoundReflectionOrder(0);
 87            pointNoiseMap.setComputeHorizontalDiffraction(true);
 88            pointNoiseMap.setComputeVerticalDiffraction(true);
 89            // Building height field name
 90            pointNoiseMap.setHeightField("HEIGHT");
 91            // Point cloud height above sea level POINT(X Y Z)
 92            pointNoiseMap.setDemTable("DEM");
 93            // Do not propagate for low emission or far away sources.
 94            // error in dB
 95            pointNoiseMap.setMaximumError(0.1d);
 96
 97            // Init custom input in order to compute more than just attenuation
 98            // LW_ROADS contain Day Evening Night emission spectrum
 99            LDENConfig ldenConfig = new LDENConfig(LDENConfig.INPUT_MODE.INPUT_MODE_LW_DEN);
100
101            ldenConfig.setComputeLDay(true);
102            ldenConfig.setComputeLEvening(true);
103            ldenConfig.setComputeLNight(true);
104            ldenConfig.setComputeLDEN(true);
105
106            LDENPointNoiseMapFactory tableWriter = new LDENPointNoiseMapFactory(connection, ldenConfig);
107
108            tableWriter.setKeepRays(true);
109
110            pointNoiseMap.setPropagationProcessDataFactory(tableWriter);
111            pointNoiseMap.setComputeRaysOutFactory(tableWriter);
112
113            RootProgressVisitor progressLogger = new RootProgressVisitor(1, true, 1);
114
115            pointNoiseMap.initialize(connection, new EmptyProgressVisitor());
116
117            // force the creation of a 2x2 cells
118            pointNoiseMap.setGridDim(2);
119
120
121            // Set of already processed receivers
122            Set<Long> receivers = new HashSet<>();
123            ProgressVisitor progressVisitor = progressLogger.subProcess(pointNoiseMap.getGridDim()*pointNoiseMap.getGridDim());
124            LOGGER.info("start");
125            long start = System.currentTimeMillis();
126
127            // Iterate over computation areas
128            try {
129                tableWriter.start();
130                for (int i = 0; i < pointNoiseMap.getGridDim(); i++) {
131                    for (int j = 0; j < pointNoiseMap.getGridDim(); j++) {
132                        // Run ray propagation
133                        IComputeRaysOut out = pointNoiseMap.evaluateCell(connection, i, j, progressVisitor, receivers);
134                    }
135                }
136            } finally {
137                tableWriter.stop();
138            }
139            long computationTime = System.currentTimeMillis() - start;
140            logger.info(String.format(Locale.ROOT, "Computed in %d ms, %.2f ms per receiver",
141                    computationTime,computationTime / (double)receivers.size()));
142            // Export result tables as csv files
143            CSVDriverFunction csv = new CSVDriverFunction();
144            csv.exportTable(connection, ldenConfig.getlDayTable(), new File(ldenConfig.getlDayTable()+".csv"), new EmptyProgressVisitor());
145            csv.exportTable(connection, ldenConfig.getlEveningTable(), new File(ldenConfig.getlEveningTable()+".csv"), new EmptyProgressVisitor());
146            csv.exportTable(connection, ldenConfig.getlNightTable(), new File(ldenConfig.getlNightTable()+".csv"), new EmptyProgressVisitor());
147            csv.exportTable(connection, ldenConfig.getlDenTable(), new File(ldenConfig.getlDenTable()+".csv"), new EmptyProgressVisitor());
148        } catch (PSQLException ex) {
149            if (ex.getCause() instanceof ConnectException) {
150                // Connection issue ignore
151                LOGGER.warn("Connection error to local PostGIS, ignored", ex);
152            } else {
153                throw ex;
154            }
155        } catch (SQLException ex) {
156            LOGGER.error(ex.getLocalizedMessage(), ex.getNextException());
157            throw ex;
158        }
159    }
160}