February 20, 2019 at 11:43

PostGIS and JPA

  • Tutorial


PostGIS is open source software that adds support for geographic features to the PostgreSQL relational database.

This short article will discuss its use in Java. In particular, the task of finding geographical objects by their coordinates.

PostGIS was created back in 2001. It is a good free solution for storing map data in the database. But the article is not entirely about him, but only about a special case - convenient work with PostGIS using JPA tools.

Dependencies


The following libraries are important for our task:

  • Hibernate 5.3.7
  • hibernate-spatial - same version. Theoretically, you can use the older ones. Starting in fifth, hibernate-spatial is the same as hibernate. Previously: Hibernate Spatial 1.1.x for Hibernate 3.6.x, Hibernate Spatial 1.0 for Hibernate 3.2.x - 3.5.x.
  • postgresql 42.2.4. This version was taken, because newer ones have tightened SSL requirements. Choose the driver version that matches the database version.

Well, all you need for JPA is Spring or a container.

Dialects


Hibernate Spatial provides geometric abstractions for working with spatial bases. As in JPA, as a first approximation, we are not interested in which database is used on the server.

Officially supported by PostgresSQL, Oracle, MySQL, MS SQLServer, GeoDB (H2), DB2. Features support details . Muscle may seem like an outsider. But in version 8, spatial data support is decently improved .

We use Postgres. But you need to specify the Hibernate dialect "org.hibernate.spatial.dialect.postgis.PostgisDialect"instead of the standard post-Greek one.

It's time to code


A table in PostGIS can have any fields. Just standard, one of them will be of type geometry. And there is geography (not supported now in Hibernate). If you do not teach Java to work with this type, it will be interpreted as a blob or String of the form "01010000207B7F0000188D594CC9B22541BC4E56674F2C5541".

Of course, you can work with PostGis on a pure JBDC. An example . But this requires a separate painstaking work with org.postgis.PGgeometry. These are not the classes that the article will be about. And there will be no tolerance anymore.

We go to JPA and create a simple class:

@Entity
public class AdressBuilding implements Serializable {
    @Id
    private Integer id;
    private Point geom;
    ...

The remaining fields are omitted (a geographical object can store any information). Nothing unusual here - the standard entity class. Only an object of class Point is interesting - a point of three-dimensional space.

Hereinafter, classes from the com.vividsolutions.jts.geom package are used.

JTS has become the de facto standard for representing geospatial data. It implements the Simple Feature Specification / Simple Feature Access specification created by OpenGIS back in the 90s.

Clarification . Point inherits from the abstract Geometry class. It contains such non-static fields:

    protected Envelope envelope;
    protected final GeometryFactory factory;
    protected int SRID;
    private Object userData;

Envelope is the minimum bounding box for this geometry. But it can come back in the form of geometry. And then you will have an endless attempt at serialization.

SRID - coordinate system number. There are a lot of them. The main differences: the coordinate format (meters, degrees ...), the reference point and the shape of the Earth (the Earth is not round). PostGis knows many coordinate systems and can transform them.

As I said, we have a geometry type in the database. I immediately used the concrete Point class for convenience, because in this table I have only point objects. But PostGIS theoretical can store several types of geometry at once. Just in each geometry its type is indicated:

"geometry":{"type":"MultiPolygon","coordinates":...

According to StackOverflow, using multiple geometries in the same table slows down queries. Geometries can also be nested. Types:


Database Queries


With the implementation of the class figured out. now it's time to get them from the base. Our points are houses, or rather their addresses. You can make familiar SQL queries: get houses by id, number, number of grandmothers ...

We are now interested in spatial queries. For example, find a house by coordinates. Let the desired coordinates x, y, and + -delta be the desired search area. The main queries in STS are performed on the ratio of geometries. Therefore, we need to create it:

        Coordinate c1 = new Coordinate(x - delta, y - delta);
...
        Coordinate[] coordinates = new Coordinate[]{c1, c2, c3, c4, c1};
        GeometryFactory GEOMETRY_FACTORY = new GeometryFactory();//static заранее
        Polygon square_window = GEOMETRY_FACTORY.createPolygon(coordinates);
        square_window.setSRID(32635);

If we do not specify a coordinate system, PostGis will refuse to compare them. You either know the code of your system, or get it from anywhere in the code .getGeom().getSRID().

Next we send a request of the form:

    "select a "
    + "from AdressBuilding a "
    + "where within(a.geom, :window) = true"

A within query means checking if the geometry is inside another. Do not be alarmed if your IDE says that there can be no such request in the JPA. Hibernate Spatial converts it to:
 where
        st_within(adressbuil0_.geom, ?)=true

Where st_withinis already a PostGis function.

There are several options for how to get the same result - the point fell into a square. contains(:window, a.geom) / intersects(a.geom, :window)...



A detailed description of the specifications is here .

Afterword


We got the points - now do whatever you want with them.

I tested the case of a small database on a server with a relatively large amount of RAM. If you load to the maximum and forget about indexes, the search task will run into the processor.
Postgres has many different indexes. And some of them help Postgis . The study showed that only GIST (?) Is suitable for points
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );

But most often, when you import data into PostGis, indexes are created automatically ...

Updates and additional information are welcome.

Manual used:

For Hibernate 5
For Hibernate 4
Tags:

Also popular now: