Spring Boot and PostGIS

If you need to keep geo-data in the PostgreSQL database, you may use the PostGIS extension.

PostGIS provides spatial objects […], allowing storage and query information about location and mapping.

In the Spring Boot application, you should add necessary dependencies in pom.xml.

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.graphhopper.external</groupId>
    <artifactId>jackson-datatype-jts</artifactId>
    <version>${jackson-datatype-jts.version}</version>
</dependency>
<dependency>
    <groupId>org.locationtech.jts</groupId>
    <artifactId>jts-core</artifactId>
    <version>${jts-core.version}</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-spatial</artifactId>
    <version>${hibernate-spatial.version}</version>
</dependency>

In the demo project, I’ll use Liquibase for filling a table with the test data. Liquibase is a data migration tool. It has a maven plugin for manual/auto migration while the build process runs.

Maven plugin configuration:

<plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>${liquibase.version}</version>
    <dependencies>
        <dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
            <version>${liquibase.version}</version>
        </dependency>
    </dependencies>
    <configuration>
        <changeLogFile>src/main/resources/dbchangelog.xml</changeLogFile>
        <driver>org.postgresql.Driver</driver>
        <url>jdbc:postgresql://127.0.0.1:5432/postgres?prepareThreshold=0</url>
        <username>postgres</username>
        <password>123</password>
        <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
    </configuration>
</plugin>

For the Liquibase usage, you need to define changelog and SQL script itself.

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
           http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">

<changeSet id="fillTestTable">
    <sqlFile relativeToChangelogFile="true" path="postgresql/fillTable.sql" />
</changeSet>

</databaseChangeLog>

In a root tag, you need to define the changeset. It must have a unique id and the path to the SQL file (relative or absolute).

The SQL file can contain DDL and DML queries.

insert into test(test_name, geog)
select n as test_name, ST_SetSRID( ST_Point(random() * 10, random() * 10), 4326)::geography as geog
from unnest(ARRAY['test0',
'test1',
...]) n
on conflict do nothing;

The result of this SQL script execution will be inserted as many records as many elements you define in the array.

In this script, ‘geog’ is a column with the geography PostGIS type. The minimal configuration for geography is longitude, latitude, and SRID (Spatial Reference System Identifier). Latitude and longitude are set for the point by calling ST_Point(double, double) function. Then, set SRID via ST_SetSRID and cast to the geography type with ‘::’ or cast operator.

The next step is setting application properties.

#Spring Data general properties
spring.datasource.url=jdbc:postgresql://postgres.default.svc.cluster.local:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=123
spring.jpa.show-sql=true
spring.datasource.driver-class-name=org.postgresql.Driver
#Update used for JPA schema creation, if you use Liquibase, set this property to none. 
spring.jpa.hibernate.ddl-auto=update
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
#PostGis specific prop
spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.postgis.PostgisDialect
#Liquibase specific prop
spring.liquibase.enabled=false
spring.liquibase.change-log=classpath*:dbchangelog.xml

Documentation for this project based on OpenApi v3, so we can use springdoc-openapi for Swagger UI. You need to add the dependency in pom.xml.

<dependency>
    <groupId>org.springdoc</groupId>
    <artifactId>springdoc-openapi-ui</artifactId>
    <version>${swagger.version}</version>
</dependency>

For proper Jackson JSON conversion, you should add JtsModule Bean in the configuration.

@Configuration
public class JacksonConfig {
  @Bean
  public JtsModule jtsModule() {
    return new JtsModule();
  }
}

The next step is model creation.

@Entity
public class Test implements Serializable {
    // identity + int = serial type in postgres. identity + long = bigserial. 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    // column definition define postgres column type
    @Column(columnDefinition = "geography")
    private Geometry geog;
    // Constructor, getters and setters should be here
}

Geography is a binary PostGIS type defined by longitude, latitude, SRID. Other way is conversion from EWKT( Extended Well-Known Text/Binary). EWKT example: SRID=4326;POINT(37.617635 55.755814).

PostGIS provides a lot of useful functions for GIS operations. You can create PostGIS objects in Java.

GeometryFactory geometryFactory = new GeometryFactory();
Coordinate coordinate = new Coordinate(x, y);
Point point = geometryFactory.createPoint(coordinate);
point.setSRID(4326);

Then, use this object in the native query. In this example, query result is three nearest objects to the provided point.

@Query(value = "SELECT * FROM public.test ORDER BY ST_Distance(geog,  :geom ) LIMIT 3", nativeQuery = true)
List<Test> findNearest(final Point geom);

In alternative, you can create a fully native query.

@Transactional
@Modifying
@Query(value = "insert into test(test_name, geog) values (:name, ST_SetSRID(ST_Point( :lat, :lon ), 4326)\\:\\:geography)", nativeQuery = true)
void createOrUpdate(final String name, final Double lat, final Double lon);

Let’s deploy this application to the k8s cluster. All that we need for installation of PostgreSQL + PostGIS extension in the k8s is configuration yaml.

For the PostgreSQL + PostGIS deployment, let’s create k8s config. This config contains the ConfigMap with credentials, PersistentVolume and PersistentVolumeClaim, Deployment and Service. You should save it to the k8s folder as postgres.yml.

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  labels:
    app: postgres
data:
  POSTGRES_DB: "postgres"
  POSTGRES_USER: "postgres"
  POSTGRES_PASSWORD: "123"
---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: postgres-pv-volume
  labels:
    type: local
    app: postgres
spec:
  storageClassName: manual
  capacity:
    storage: 1Gi
  accessModes:
    - ReadWriteMany
  hostPath:
    path: "/mnt/data"
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgres-pv-claim
  labels:
    app: postgres
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 1Gi
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgis/postgis:latest
          imagePullPolicy: "IfNotPresent"
          ports:
            - containerPort: 5432
          envFrom:
            - configMapRef:
                name: postgres-config
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgredb
      volumes:
        - name: postgredb
          persistentVolumeClaim:
            claimName: postgres-pv-claim
---
apiVersion: v1
kind: Service
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  type: NodePort
  ports:
   - port: 5432
  selector:
   app: postgres

Then, create a config for the application to define Deployment and Service.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgis-example-deployment
  labels:
    app: postgis-example
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgis-example
  template:
    metadata:
      labels:
        app: postgis-example
    spec:
      containers:
        - name: postgis-example
          imagePullPolicy: Never
          image: example/postgis:2
          ports:
            - containerPort: 8080
---
apiVersion: v1
kind: Service
metadata:
  name: postgis-example
  labels:
    app: postgis-example
spec:
  type: NodePort
  ports:
    - port: 8080
  selector:
    app: postgis-example

With the local docker-machine, you should pay attention to imagePullPolicy property.

imagePullPolicy: Never

After all these preparations, build the application (by ‘mvn clean install’ command) and follow these steps.

Go to the root of the application via cd.

Set the local docker repository.

minikube -p minikube docker-env | Invoke-Expression

Build a local docker image for the application with tag name example/postgis:2.

docker build -t example/postgis:2 .

Deploy PostgreSQL and PostGIS in the k8s cluster.

kubectl apply -f .\k8s\postgres.yml

Deploy the application.

kubectl apply -f .\k8s\deployment.yaml

Then, forward the port for the local access.

kubectl port-forward service/postgis-example 8081:8080

After this, you can open OpenApi UI by this link

The source code of the application is available on Github.