Issue
Can not resolve 'ST_GeomFromText' in serviceImpl class.
I am trying to save my geometry data to mysql table through JPA.
georepository.save("id", ST_GeomFromText('POLYGON((0 1, 2 5, 2 7, 0 7, 0 1))') );
but I am getting Can not resolve 'ST_GeomFromText' in serviceImpl class.
here is my model
public class Mygeo {
@Id
@Column(name = "id")
private String id;
@Column(name = "geodata", nullable = false)
private Geometry geodata;
}
This is how I am saving manually in DB. what will be the JPA alternate way to save this.
INSERT INTO mygeo (id, geodata)VALUES ("ID",ST_GeomFromText('POLYGON((0 1, 2 5, 2 7, 0 7, 0 1))'));
Note : I do have access of geometry string like this
System.out.println(geometryObject.toString());
{"coordinates":[[[0,1],[2,5],[2,7],[0,7],[0,1]]],"type":"Polygon"}
Solution
The way to save the Geometry data into database while using the Spring Data JPA is given below in details (step - by - step):
I will explain with the help of a project.
The project structure :
MyGeo entity :
package com.solve.problemssolve;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import org.locationtech.jts.geom.Geometry;
import lombok.Data;
@Data
@Entity
public class MyGeo {
@Id
@Column(name = "id")
private String id;
@Column(name = "geodata", nullable = false)
private Geometry geodata;
}
MyGeoRepository class
package com.solve.problemssolve;
import org.springframework.data.jpa.repository.JpaRepository;
public interface MyGeoRepository extends JpaRepository<MyGeo, String> {
}
application.properties :
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=Anish@123
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL5SpatialDialect
Note: You have to enable org.hibernate.spatial.dialect.mysql.MySQL5SpatialDialect in your spring boot application to support spatial features.
According to MySQL5SpatialDialect Hibernate docs :
It is a dialect for MySQL 5 using InnoDB engine, with support for its spatial features.
pom.xml :
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.4</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<groupId>com.solve</groupId>
<artifactId>problems-solve</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>problems-solve</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-spatial</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
In MySQL DB (Table structure) :
Note: This table is automatically generated by Hibernate as I have kept ddl-auto=update but you can create table manually also.
Resource class :
@RestController
public class Resource {
@Autowired
private MyGeoRepository myGeoRepository;
@GetMapping("/save")
public void save() throws ParseException {
WKTReader wktReader = new WKTReader();
Geometry geometry = wktReader.read("POLYGON((0 1, 2 5, 2 7, 0 7, 0 1))");
System.out.println(geometry);
MyGeo geo = new MyGeo();
geo.setGeodata(geometry);
geo.setId("ID");
myGeoRepository.save(geo);
System.out.println(myGeoRepository.findById("ID"));
}
}
You have to use WKTReader to save the data via JPA.
According to WKTReader docs :
Converts a geometry in Well-Known Text format to a Geometry.
WKTReader supports extracting Geometry objects from either Readers or Strings. This allows it to function as a parser to read Geometry objects from text blocks embedded in other data formats (e.g. XML).
A WKTReader is parameterized by a GeometryFactory, to allow it to create Geometry objects of the appropriate implementation. In particular, the GeometryFactory determines the PrecisionModel and SRID that is used.
The WKTReader converts all input numbers to the precise internal representation.
According to WKTReader.read(String wellKnownText) docs :
It reads a Well-Known Text representation of a Geometry from a String.
This SQL statement ST_GeomFromText('POLYGON((0 1, 2 5, 2 7, 0 7, 0 1))') is equivalent to this line Geometry geometry = wktReader.read("POLYGON((0 1, 2 5, 2 7, 0 7, 0 1))");
As soon as, I have run the example, it saved successfully without any issues.
Server log screenshot :
DB row screenshot :
Answered By - Anish B.
Answer Checked By - David Goodson (JavaFixing Volunteer)
