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;
        }
    }
}