postgresql 在 Spring Boot 上使用 Hibernate 映射 PostGIS 几何点字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27624940/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Map a PostGIS geometry point field with Hibernate on Spring Boot
提问by bluish
In my PostgreSQL 9.3 + PostGIS 2.1.5 I have a table PLACEwith a column coordinatesof type Geometry(Point,26910).
在我的 PostgreSQL 9.3 + PostGIS 2.1.5 中，我有一个PLACE带有coordinates类型列的表Geometry(Point,26910)。
I want to map it to Placeentity in my Spring Boot 1.1.9 web application, which uses Hibernate 4.0.0 + . Placeis available with a REST repository.
我想将它映射到Place使用 Hibernate 4.0.0 + 的 Spring Boot 1.1.9 Web 应用程序中的实体。Place可与 REST 存储库一起使用。
Unfortunately when I GET http://localhost:8080/mywebapp/placesI receive this strange JSON response:
不幸的是，当我GET http://localhost:8080/mywebapp/places收到这个奇怪的 JSON 响应时：
{
"_embedded" : {
"venues" : [ {
"id" : 1,
"coordinates" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
"envelope" : {
and so on indefinetely...! Spring log doesn't help..
等等无限……！Spring日志没有帮助..
I'm working with this application.properties:
我正在使用这个 application.properties：
spring.jpa.database-platform=org.hibernate.spatial.dialect.postgis.PostgisDialect
spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:postgresql://192.168.1.123/mywebapp
spring.datasource.username=postgres
spring.datasource.password=mypwd
spring.datasource.driverClassName=org.postgresql.Driver
First of all, is it ok to use database-platforminstead of database?
And maybe do I have to use following settings instead of the above?
首先，可以使用database-platform代替database吗？也许我必须使用以下设置而不是以上设置？
spring.datasource.url=jdbc:postgresql_postGIS://192.168.1.123/mywebapp
spring.datasource.driverClassName=org.postgis.DriverWrapper
Anyway my entity is something like this:
无论如何，我的实体是这样的：
@Entity
public class Place {
@Id
public int id;
@Column(columnDefinition="Geometry")
@Type(type="org.hibernate.spatial.GeometryType") //"org.hibernatespatial.GeometryUserType" seems to be for older versions of Hibernate Spatial
public com.vividsolutions.jts.geom.Point coordinates;
}
My pom.xml contains this relevant part:
我的 pom.xml 包含这个相关部分：
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.3-1102-jdbc41</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-spatial</artifactId>
<version>4.3</version><!-- compatible with Hibernate 4.3.x -->
<exclusions>
<exclusion>
<artifactId>postgresql</artifactId>
<groupId>postgresql</groupId>
</exclusion>
</exclusions>
</dependency>
A bit strange configuration, I found it on the internet, it is the one that works best for now.
有点奇怪的配置，我在网上找到的，这是目前最好的配置。
I hope that someone could help me with this mistery. :)
我希望有人能帮助我解决这个谜团。:)
回答by bluish
Finally I discovered that my configuration is ok and might be Hymansonthat cannot manage Pointdata type correctly. So I customized its JSON serialization and deserialization:
最后我发现我的配置没问题，可能是Hyman逊无法Point正确管理数据类型。所以我自定义了它的JSON序列化和反序列化：
add these annotations to our
coordinatesfield:@JsonSerialize(using = PointToJsonSerializer.class) @JsonDeserialize(using = JsonToPointDeserializer.class)create such serializer:
import java.io.IOException; import com.fasterxml.Hymanson.core.JsonGenerator; import com.fasterxml.Hymanson.core.JsonProcessingException; import com.fasterxml.Hymanson.databind.JsonSerializer; import com.fasterxml.Hymanson.databind.SerializerProvider; import com.vividsolutions.jts.geom.Point; public class PointToJsonSerializer extends JsonSerializer<Point> { @Override public void serialize(Point value, JsonGenerator jgen, SerializerProvider provider) throws IOException, JsonProcessingException { String jsonValue = "null"; try { if(value != null) { double lat = value.getY(); double lon = value.getX(); jsonValue = String.format("POINT (%s %s)", lat, lon); } } catch(Exception e) {} jgen.writeString(jsonValue); } }create such deserializer:
import java.io.IOException; import com.fasterxml.Hymanson.core.JsonParser; import com.fasterxml.Hymanson.core.JsonProcessingException; import com.fasterxml.Hymanson.databind.DeserializationContext; import com.fasterxml.Hymanson.databind.JsonDeserializer; import com.vividsolutions.jts.geom.Coordinate; import com.vividsolutions.jts.geom.GeometryFactory; import com.vividsolutions.jts.geom.Point; import com.vividsolutions.jts.geom.PrecisionModel; public class JsonToPointDeserializer extends JsonDeserializer<Point> { private final static GeometryFactory geometryFactory = new GeometryFactory(new PrecisionModel(), 26910); @Override public Point deserialize(JsonParser jp, DeserializationContext ctxt) throws IOException, JsonProcessingException { try { String text = jp.getText(); if(text == null || text.length() <= 0) return null; String[] coordinates = text.replaceFirst("POINT ?\(", "").replaceFirst("\)", "").split(" "); double lat = Double.parseDouble(coordinates[0]); double lon = Double.parseDouble(coordinates[1]); Point point = geometryFactory.createPoint(new Coordinate(lat, lon)); return point; } catch(Exception e){ return null; } } }
将这些注释添加到我们的
coordinates字段中：@JsonSerialize(using = PointToJsonSerializer.class) @JsonDeserialize(using = JsonToPointDeserializer.class)创建这样的序列化程序：
import java.io.IOException; import com.fasterxml.Hymanson.core.JsonGenerator; import com.fasterxml.Hymanson.core.JsonProcessingException; import com.fasterxml.Hymanson.databind.JsonSerializer; import com.fasterxml.Hymanson.databind.SerializerProvider; import com.vividsolutions.jts.geom.Point; public class PointToJsonSerializer extends JsonSerializer<Point> { @Override public void serialize(Point value, JsonGenerator jgen, SerializerProvider provider) throws IOException, JsonProcessingException { String jsonValue = "null"; try { if(value != null) { double lat = value.getY(); double lon = value.getX(); jsonValue = String.format("POINT (%s %s)", lat, lon); } } catch(Exception e) {} jgen.writeString(jsonValue); } }创建这样的解串器：
import java.io.IOException; import com.fasterxml.Hymanson.core.JsonParser; import com.fasterxml.Hymanson.core.JsonProcessingException; import com.fasterxml.Hymanson.databind.DeserializationContext; import com.fasterxml.Hymanson.databind.JsonDeserializer; import com.vividsolutions.jts.geom.Coordinate; import com.vividsolutions.jts.geom.GeometryFactory; import com.vividsolutions.jts.geom.Point; import com.vividsolutions.jts.geom.PrecisionModel; public class JsonToPointDeserializer extends JsonDeserializer<Point> { private final static GeometryFactory geometryFactory = new GeometryFactory(new PrecisionModel(), 26910); @Override public Point deserialize(JsonParser jp, DeserializationContext ctxt) throws IOException, JsonProcessingException { try { String text = jp.getText(); if(text == null || text.length() <= 0) return null; String[] coordinates = text.replaceFirst("POINT ?\(", "").replaceFirst("\)", "").split(" "); double lat = Double.parseDouble(coordinates[0]); double lon = Double.parseDouble(coordinates[1]); Point point = geometryFactory.createPoint(new Coordinate(lat, lon)); return point; } catch(Exception e){ return null; } } }
Maybe you can also use this serializerand this deserializer, available here.
回答by Martin Naughton
The solutions above helped me to fix the problem. I simplify it so other people can understand.
上面的解决方案帮助我解决了这个问题。我把它简化了，以便其他人可以理解。
I included this library in my pom.xml:
我在我的 pom.xml 中包含了这个库：
<dependency>
<groupId>com.bedatadriven</groupId>
<artifactId>Hymanson-datatype-jts</artifactId>
<version>2.2</version>
</dependency>
This is the POJO object I used. Then I was able to get the REST call to work without the envelope error and proper coodinates.
这是我使用的 POJO 对象。然后我能够在没有信封错误和正确坐标的情况下让 REST 调用正常工作。
import com.bedatadriven.Hymanson.datatype.jts.serialization.GeometryDeserializer;
import com.bedatadriven.Hymanson.datatype.jts.serialization.GeometrySerializer;
import com.fasterxml.Hymanson.databind.annotation.JsonDeserialize;
import com.fasterxml.Hymanson.databind.annotation.JsonSerialize;
import com.vividsolutions.jts.geom.Geometry;
@Entity
@Table(name = "boundary")
public class Boundary {
private int id;
private Geometry geomertry;
@Id
public int getId() {
return ogc_fid;
}
public void setId(int id) {
this.id = id;
}
@JsonSerialize(using = GeometrySerializer.class)
@JsonDeserialize(using = GeometryDeserializer.class)
@Column(name = "geometry", columnDefinition = "Geometry")
public Geometry getGeomertry() {
return geomertry;
}
public void setGeomertry(Geometry geomertry) {
this.geomertry = geomertry;
}
}
My table had these 2 columns:
我的表有以下两列：
id | integer
geometry | geometry(Geometry,4326) |
回答by thiagogcm
This serialization/deserialization also worked fine for me.
这种序列化/反序列化对我来说也很好用。
回答by minnow73
The problem doesn't appear to be related to PostgreSQL. It appears that your POJO has a backreference, which means that your mapper doesn't know how to handle it. You need to explicitly define the recursive relationships so that the mapper knows when to stop. (My Goto link --> http://vard-lokkur.blogspot.com/2010/10/json-Hymanson-to-rescue.html)
该问题似乎与 PostgreSQL 无关。看来您的 POJO 有反向引用，这意味着您的映射器不知道如何处理它。您需要明确定义递归关系，以便映射器知道何时停止。（我的转到链接 --> http://vard-lokkur.blogspot.com/2010/10/json-Hymanson-to-rescue.html）
回答by nono
If you don't want to add the annotation on all your fields that are using a Point, you can also use the @JsonComponentto register your JsonSerializerand JsonDeserializer.
如果您不想在所有使用 Point 的字段上添加注释，您还可以使用@JsonComponent来注册您的JsonSerializer和JsonDeserializer。
@JsonComponent
public class PointSerializer extends JsonSerializer<com.vividsolutions.jts.geom.Point>{
@Override
public void serialize(com.vividsolutions.jts.geom.Point value, JsonGenerator gen, SerializerProvider provider) throws IOException {
gen.writeStartObject();
gen.writeNumberField("lat", value.getY());
gen.writeNumberField("lon", value.getX());
gen.writeEndObject();
}
}
