Ich habe mir einige OpenStreetMap-Daten in eine PostgreSQL+PostGIS-Datenbank importiert und möchte diese Daten mit einem Java-Programm auslesen. Dafür scheint zunächst einmal ein ordentliches ORM-Framework (Object-Relational-Mapping) á la JPA (Java Persistence API) sinnvoll, aber leider werden Geo-Typen von JPA 2.0 momentan nicht unterstützt. Dann schauen wir uns Javas Platzhirschen im Bereich ORM an und siehe da: Hibernate bietet die Erweiterung Hibernatespatial für den Umgang mit geographischen Daten. Also, los gehts…
- Datenbank mit Geodaten-Tabelle erstellen
- Tutorial von Hibernatespatial anschauen
- Geodaten-Tabelle mit Geodaten befüllen
- Java-Projekt mit Maven, Hibernate und Hibernatespatial umsetzen
- Hibernate Criteria-API durch HQL-Abfragesprache ersetzen
1. Datenbank mit Geodaten-Tabelle erstellen
Unter den freien Datenbanken mit Geo-Unterstützung ist PostgreSQL mit PostGIS führend. Diese Kombination wird auch von OpenStreetMap (OSM) genutzt, sodass ich mich ebenfalls dafür entscheide. In diesem Artikel habe ich meine Installationsschritte für PostgreSQL mit PostGIS auf meinem MacBook beschrieben. Wenn das erledigt ist, können wir uns dem Hibernatespatial-Tutorial widmen.
2. Tutorial von Hibernatespatial anschauen
Das Tutorial beschreibt die Verwendung von Hibernatespatial sehr gut. Es funktionierte bei mir problemlos, wobei ich für dieses Beispiel einige Anpassungen machen werde – sonst wäre es ja auch langweilig 😉
- Verwendung der Hibernatespatial Version 1.1.1 anstatt 1.1.
- Erstellung der Tabelle und Daten mit SQL-Skript und nicht mit Hibernate
- Entfernung der deprecated-Methoden
- Verwendung der JPA-Annotationen anstatt XML-Dateien
- Geometrien erhalten OSM-Koordinatenreferenzsystem (SRID=4326)
3. Geodaten-Tabelle mit Geodaten befüllen
Wir erstellen uns zunächst eine Datenbank mit PostGIS-Unterstützung, indem wir folgendes SQL nutzen. Nachdem die Datenbank erstellt wurde, müssen wir diese Datenbank erst auswählen, bevor wir die anschließenden Befehle ausführen können:
CREATE DATABASE mypois WITH TEMPLATE = template_postgis; SELECT postgis_full_version(); CREATE TABLE myakws(id bigint NOT NULL, description character varying(100), location geometry, CONSTRAINT myakws_pkey PRIMARY KEY (id));
Dann befüllen wir die Datenbank mit einigen Daten, wobei im Gegensatz zum Tutorial die Geo-Positionen mit einem Koordinatenreferenzsystem versehen sind.
INSERT INTO myakws(id, description, location) VALUES (1,'Brunsbüttel', GeometryFromText('POINT(9.201667 53.891667)', 4326)); INSERT INTO myakws(id, description, location) VALUES (2,'Brokdorf', GeometryFromText('POINT(9.344722 53.850833)', 4326)); INSERT INTO myakws(id, description, location) VALUES (3,'Isar/Ohu 1+2', GeometryFromText('POINT(12.29315 48.605606)', 4326)); INSERT INTO myakws(id, description, location) VALUES (4,'Philippsburg 1+2', GeometryFromText('POINT(8.436436 49.252722)', 4326)); INSERT INTO myakws(id, description, location) VALUES (5,'Grohnde', GeometryFromText('POINT(9.413333 52.035278)', 4326)); INSERT INTO myakws(id, description, location) VALUES (6,'Unterweser', GeometryFromText('POINT(8.480197 53.4277)', 4326)); INSERT INTO myakws(id, description, location) VALUES (7,'Krümmel', GeometryFromText('POINT(10.408889 53.41)', 4326)); INSERT INTO myakws(id, description, location) VALUES (8,'Emsland', GeometryFromText('POINT(7.317858 52.474231)', 4326)); INSERT INTO myakws(id, description, location) VALUES (9,'Neckarwestheim 1+2', GeometryFromText('POINT(9.175 49.041111)', 4326)); INSERT INTO myakws(id, description, location) VALUES (10,'Grafenrheinfeld', GeometryFromText('POINT(10.184669 49.984086)', 4326)); INSERT INTO myakws(id, description, location) VALUES (11,'Gundremmingen B+C', GeometryFromText('POINT(10.402222 48.514722)', 4326)); INSERT INTO myakws(id, description, location) VALUES (12,'Biblis A+B', GeometryFromText('POINT(8.415278 49.71)', 4326));
Jetzt können wir einige SQL-Abfragen auf diesen Daten ausführen. Beispielsweise sucht die folgende Abfrage alle AKWs in einem Quadrat, das Deutschland umschließt. Falls man nur die AKWs in Norddeutschland haben möchte, muss man das verwendete POLYGON
verkleinern (z.B. alle Vorkommnisse der Zahl 47 durch 51 ersetzen). Die Methoden ST_AsText()
und ST_AsEWKT()
geben die Koordinate ohne und mit Koordinatenreferenzsystem in lesbarer Form wieder.
SELECT ma.id, ma.description, ST_AsText(ma.location), ST_AsEWKT(ma.location) FROM myakws AS ma WHERE ST_Within(ma.location, GeometryFromText('POLYGON((5.5 47, 15.5 47, 15.5 55, 5.5 55, 5.5 47))', 4326)) order by ma.description
4. Java-Projekt mit Maven, Hibernate und Hibernatespatial umsetzen
Jetzt setzen wir ein Java-Projekt auf, wie es im Hibernatespatial-Tutorial beschrieben ist. Dabei nehmen wir noch einige Änderungen vor:
- Erhöhung der Hibernatespatial-Version in Mavens pom.xml auf Version 1.1.1
- Erstellung einer passenden Entity-Klasse
MyAkwData
- Anpassen der
hibernate.cfg.xml
durch Eintragen der KlasseMyAkwData
(bei fehlendem Eintrag bekommen wir zwar keine Fehler, aber leider auch keine Daten) - Wert des Schlüssels
hbm2ddl.auto
inhibernate.cfg.xml
durch ‚Update‘ ersetzen - in unserer Hilfsklasse
HibernateUtil
die Deprecated-Klassenew AnnotationConfiguration()
durchnew Configuration()
ersetzen - Datenbank-Service erstellen, der die Daten mit der Criteria-API ausliest
<dependency> <groupId>org.hibernatespatial</groupId> <artifactId>hibernate-spatial-postgis</artifactId> <version>1.1.1</version> </dependency>
package de.bruns.example.hibernatespatial; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; import org.hibernate.annotations.Type; import com.vividsolutions.jts.geom.Point; @Entity @Table(name = "MYAKWS") public class MyAkwData { @Id @Column(name = "ID") private Long id; @Column(name = "DESCRIPTION", length=100) private String description; @Column(name = "LOCATION") @Type(type = "org.hibernatespatial.GeometryUserType") private Point location; public MyAkwData() { } public Long getId() { return id; } protected void setId(Long id) { this.id = id; } public Point getLocation() { return location; } public String getDescription() { return description; } public void setDescription(String value) { this.description = value; } public void setLocation(Point location) { this.location = location; } }
<?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- Database connection settings --> <property name="connection.driver_class">org.postgresql.Driver</property> <property name="connection.url">jdbc:postgresql://localhost:5432/mypois</property> <property name="connection.username">postgres</property> <property name="connection.password">postgres</property> <!-- JDBC connection pool (use the built-in) --> <property name="connection.pool_size">1</property> <!-- SPATIAL SQL dialect --> <property name="dialect">org.hibernatespatial.postgis.PostgisDialect</property> <!-- Enable Hibernate's automatic session context management --> <property name="current_session_context_class">thread</property> <!-- Disable the second-level cache --> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <!-- Echo all executed SQL to stdout --> <property name="show_sql">true</property> <!-- update the database schema on startup --> <property name="hbm2ddl.auto">update</property> <mapping class="de.bruns.example.hibernatespatial.MyAkwData"/> </session-factory> </hibernate-configuration>
package de.bruns.example.hibernatespatial; import org.hibernate.SessionFactory; import org.hibernate.cfg.Configuration; public class HibernateUtil { private static SessionFactory sessionFactory; static { try { // Create the SessionFactory from hibernate.cfg.xml sessionFactory = new Configuration().configure().buildSessionFactory(); } catch (Throwable ex) { // Make sure you log the exception, as it might be swallowed System.err.println("Initial SessionFactory creation failed." + ex); throw new ExceptionInInitializerError(ex); } } public static SessionFactory getSessionFactory() { return sessionFactory; } }
package de.bruns.example.hibernatespatial; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Session; import org.hibernate.criterion.Order; import org.hibernatespatial.criterion.SpatialRestrictions; import com.vividsolutions.jts.geom.Geometry; import com.vividsolutions.jts.geom.GeometryFactory; import com.vividsolutions.jts.geom.PrecisionModel; import com.vividsolutions.jts.io.ParseException; import com.vividsolutions.jts.io.WKTReader; public class MyAkwService { public List<MyAkwData> findAkwsWithinCriteria(Geometry filterGeometry) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); Criteria criteria = session.createCriteria(MyAkwData.class); criteria.add(SpatialRestrictions.within("location", filterGeometry)); criteria.addOrder(Order.asc("description")); @SuppressWarnings("unchecked") List<MyAkwData> results = criteria.list(); session.getTransaction().commit(); return results; } private static final String RECTANGLE_GERMANY_NORTH = "POLYGON((5.5 51, 15.5 51, 15.5 55, 5.5 55, 5.5 51))"; public static void main(String[] args) throws ParseException { MyAkwService myAkwService = new MyAkwService(); GeometryFactory geometryFactory = new GeometryFactory(new PrecisionModel(), 4326); WKTReader wktReader = new WKTReader(geometryFactory); List<MyAkwData> akws = myAkwService.findAkwsWithinCriteria(wktReader.read(RECTANGLE_GERMANY_NORTH)); for (MyAkwData akw : akws) { System.out.println(akw.getDescription()); } } }
Auf der Konsole können wir jetzt die AKWs mit Maven abfragen:
mvn exec:java -Dexec.mainClass="de.bruns.example.hibernatespatial.MyAkwService"
Im Service müssen wir darauf achten, dass wir das Objekt der Klasse WKTReader korrekt erzeugen. Anders als im Tutorial müssen wir das korrekte Koordinatenreferenzsystem angeben, ansonsten wird es eine hässliche Exception geben.
GeometryFactory geometryFactory = new GeometryFactory(new PrecisionModel(), 4326); WKTReader wktReader = new WKTReader(geometryFactory);
3978 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: XX000 3978 [main] ERROR org.hibernate.util.JDBCExceptionReporter - ERROR: Operation on two geometries with different SRIDs Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2536) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) at org.hibernate.loader.Loader.list(Loader.java:2271) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347) at de.bruns.example.hibernatespatial.PoiDbService.findPois(PoiDbService.java:28) at de.bruns.example.hibernatespatial.PoisApp.writeJsonFile(PoisApp.java:14) at de.bruns.example.hibernatespatial.PoisApp.main(PoisApp.java:22) Caused by: org.postgresql.util.PSQLException: ERROR: Operation on two geometries with different SRIDs at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1953) at org.hibernate.loader.Loader.doQuery(Loader.java:802) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) at org.hibernate.loader.Loader.doList(Loader.java:2533) ... 8 more
5. Hibernate Criteria-API durch HQL-Abfragesprache ersetzen
Wir können natürlich innerhalb des Services auch die HQL-Abfragesprache nutzen. Dann sieht die entsprechende Methode folgendermaßen aus:
public List<MyAkwData> findAkwsWithinHql(Geometry filterGeometry) { Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.beginTransaction(); Query hqlQuery = session.createQuery("from MyAkwData as ma where within(ma.location, :filterGeometry) = true order by ma.description"); Type geomType = new CustomType(new GeometryUserType()); hqlQuery.setParameter("filterGeometry", filterGeometry, geomType); @SuppressWarnings("unchecked") List<MyAkwData> results = hqlQuery.list(); session.getTransaction().commit(); return results; }
Ich hoffe, ich konnte mit diesem kleinen Tutorial einigen Entwicklern helfen, die mit OpenStreetMap-Daten und Hibernatespatial arbeiten wollen. Der Code ist wieder unter GitHub verfügbar.
Eine Abfrage, die ich benötige, kann man so momentan allerdings noch nicht lösen. Ich würde gerne die Entfernung von einem Ort zu den AKWs abfragen oder alle AKWs in einem bestimmten Umkreis zu einem Ort bestimmen. Das werde ich beim nächsten Mal näher erläutern.