Home

Embedded Postgresql Postgis

Howto

• Copy binaries from and into src/test/resources

  • postgresql-10.6-postgis-2.5.1-Linux-x86_64.txz
  • postgresql-10.6-postgis-2.5.1-Windows-x86_64.txz
  • MacOS binary is missing (sorry)

• Add dependencies

  <dependency>
    <groupId>net.postgis</groupId>
    <artifactId>postgis-jdbc</artifactId>
    <version>2.3.0</version>
    <scope>test</scope>
  </dependency>

  <dependency>
    <groupId>com.opentable.components</groupId>
    <artifactId>otj-pg-embedded</artifactId>
    <version>0.13.0</version>
    <scope>test</scope>
  </dependency>

• Add Spring-boot dependencies for testing purposes

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
  </dependency>
		
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
  </dependency>

• Create an new PgBinaryResolver

public class ClasspathBinaryResolver implements PgBinaryResolver {

    @Override
    public InputStream getPgBinary(String system, String machineHardware) throws IOException {
        ClassPathResource resource = new ClassPathResource(String.format("postgresql-10.6-postgis-2.5.1-%s-%s.txz", system, machineHardware));
        return resource.getInputStream();
    }

}

• Create a new DatabasePreparer in order to install Postgis extensions

public class PostgisPreparer implements DatabasePreparer {

    @Override
    public void prepare(DataSource ds) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = ds.getConnection();
            statement = connection.createStatement();
            statement.execute("CREATE EXTENSION postgis;");
            statement.execute("CREATE EXTENSION postgis_topology;");
            //statement.execute("CREATE EXTENSION postgis_sfcgal;");
            statement.execute("CREATE EXTENSION fuzzystrmatch;");
            statement.execute("CREATE EXTENSION address_standardizer;");
            statement.execute("CREATE EXTENSION address_standardizer_data_us;");
            statement.execute("CREATE EXTENSION postgis_tiger_geocoder;");
        }
        catch (Exception e) {
            e.printStackTrace(System.out);
        }
        finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

}

Due to some Linux compilation problems, sfcgalextension has been disabled

• Testing
  • Add a new test class annotated with @RunWith(SpringRunner.class) and @SpringBootTest
  • DataSourceAutoConfiguration.class must be disabled as the DataSource can only be instanciated after the Database initialization
  • Add a new @ClassRule (junit4) : this will create and start the database

@ClassRule
public static PreparedDbRule db = EmbeddedPostgresRules.preparedDatabase(new PostgisPreparer())
            .customize(c -> c.setPgBinaryResolver(new ClasspathBinaryResolver()));

• Add a new Spring @Configuration class in order to initialiaze the DataSource

@Configuration
public static class TestConfig {

     @Bean
     public DataSource ds() {
         return db.getTestDatabase();
     }

}

Add this class to the @SpringBootTest declaration @SpringBootTest(classes = { DemoApplication.class, DemoApplicationTests.TestConfig.class })

• Next inject DataSource

@Autowired
DataSource ds;

• test

    @Test
    public void test_Postgis() {

        System.out.println("Driver version: " + Driver.getVersion());
        int major;
        try {
            major = new Driver().getMajorVersion();
        }
        catch (Exception e) {
            System.err.println("Cannot create Driver instance: " + e.getMessage());
            System.exit(1);
            return;
        }

        if (major < 8) {
            System.err.println("Your pgdjbc " + major
                    + ".X is too old, it does not support autoregistration!");
            return;
        }

        Connection conn = null;
        Statement stat = null;
        try {
            conn = ds.getConnection();
            stat = conn.createStatement();
        }
        catch (SQLException e) {
            System.err.println("Connection initialization failed, aborting.");
            e.printStackTrace();
            System.exit(1);
            // signal the compiler that code flow ends here:
            throw new AssertionError();
        }

        int postgisServerMajor = 0;
        try {
            postgisServerMajor = getPostgisMajor(stat);
        }
        catch (SQLException e) {
            System.err.println("Error fetching PostGIS version: " + e.getMessage());
            System.err.println("Is PostGIS really installed in the database?");
            System.exit(1);
            // signal the compiler that code flow ends here:
            throw new AssertionError();
        }

        System.out.println("PostGIS Version: " + postgisServerMajor);

        PGobject result = null;

        /* Test geometries */
        try {
            ResultSet rs = stat.executeQuery("SELECT 'POINT(1 2)'::geometry");
            rs.next();
            result = (PGobject) rs.getObject(1);
            if (result instanceof PGgeometry) {
                System.out.println("PGgeometry successful!");
            }
            else {
                System.out.println("PGgeometry failed!");
            }
        }
        catch (SQLException e) {
            System.err.println("Selecting geometry failed: " + e.getMessage());
            System.exit(1);
            // Signal the compiler that code flow ends here.
            return;
        }

        /* Test box3d */
        try {
            ResultSet rs = stat.executeQuery("SELECT 'BOX3D(1 2 3, 4 5 6)'::box3d");
            rs.next();
            result = (PGobject) rs.getObject(1);
            if (result instanceof PGbox3d) {
                System.out.println("Box3d successful!");
            }
            else {
                System.out.println("Box3d failed!");
            }
        }
        catch (SQLException e) {
            System.err.println("Selecting box3d failed: " + e.getMessage());
            System.exit(1);
            // Signal the compiler that code flow ends here.
            return;
        }

        /* Test box2d if appropriate */
        if (postgisServerMajor < 1) {
            System.out.println("PostGIS version is too old, skipping box2ed test");
            System.err.println("PostGIS version is too old, skipping box2ed test");
        }
        else {
            try {
                ResultSet rs = stat.executeQuery("SELECT 'BOX(1 2,3 4)'::box2d");
                rs.next();
                result = (PGobject) rs.getObject(1);
                if (result instanceof PGbox2d) {
                    System.out.println("Box2d successful!");
                }
                else {
                    System.out.println("Box2d failed! " + result.getClass().getName());
                }
            }
            catch (SQLException e) {
                System.err.println("Selecting box2d failed: " + e.getMessage());
                System.exit(1);
                // Signal the compiler that code flow ends here.
                return;
            }
        }

        System.out.println("Finished.");
    }

    public static int getPostgisMajor(Statement stat) throws SQLException {
        ResultSet rs = stat.executeQuery("SELECT postgis_version()");
        rs.next();
        String version = rs.getString(1);
        if (version == null) {
            throw new SQLException("postgis_version returned NULL!");
        }
        version = version.trim();
        int idx = version.indexOf('.');
        return Integer.parseInt(version.substring(0, idx));
    }