skip to Main Content

I am trying to insert Geo location into MySql database using spring data JPA.
MySql Version : 5.7.17

For spring boot I have used 2.5.6 boot version and below dependency I have used

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

Below is the Entity

@Entity
@Table(name = "USER_DETAILS")
@Getter
@Setter
@Data
public class UserDetails implements Serializable {

    private static final long serialVersionUID = 3937414011943770889L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Integer id;

    @Column(name = "USER_NAME")
    private String userName;

    @Column(name = "MOBILE_NO")
    private String mobileNo;

    @Column(name = "EMAIL_ID")
    private String emailId;

    @Column(name = "COORDINATE")
    private Point point;

From java side I have used below code

public Geometry wktToGeometry(String wellKnownText) throws ParseException {
    return new WKTReader().read(wellKnownText);
}

public void saveUser(String lat, String longitude) {
    try {
        final User user = getUserDetails();

        UserDetails userDetails = new UserDetails();
        userDetails.setUserName(user.getName());
        userDetails.setEmailId(user.getEmail());
        userDetails.setPoint( (Point) wktToGeometry("POINT (2 5)"));
        userDetailsRepository.save(userDetails);
    } catch (Exception e) {
        System.out.println("Something went wrong!n" + e.getMessage());
    }
}

But after executing above code getting below issue

Hibernate: insert into user_details (email_id, mobile_no, coordinate, user_name) values (?, ?, ?, ?)
[2m2023-02-10 11:15:19.905[0;39m [33m WARN[0;39m [35m11468[0;39m [2m---[0;39m [2m[nio-8080-exec-3][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper  [0;39m [2m:[0;39m SQL Error: 1416, SQLState: 22001
[2m2023-02-10 11:15:19.906[0;39m [31mERROR[0;39m [35m11468[0;39m [2m---[0;39m [2m[nio-8080-exec-3][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper  [0;39m [2m:[0;39m Data truncation: Cannot get geometry object from data you send to the GEOMETRY field

Table Structure

CREATE TABLE USER_DETAILS (
      ID INT(10) NOT NULL AUTO_INCREMENT,
      USER_NAME VARCHAR(100) DEFAULT NULL,
      MOBILE_NO VARCHAR(10) DEFAULT NULL, 
      EMAIL_ID VARCHAR(100) DEFAULT NULL,
      COORDINATE POINT NOT NULL,
      PRIMARY KEY (ID),
      SPATIAL INDEX (COORDINATE)
    );

2

Answers


  1. Chosen as BEST ANSWER

    I forgot mentioned one properties in my code. Actually instead of simple MySql dialet we need to use below spatial dialet

    hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect
    

    After updating dialet it will work fine.


  2. As it’s stated here

    This query will throw the mentioned error: #1416 – Cannot get geometry object from data you send to the GEOMETRY field. This happens because the query is invalid. To prevent this exception from appearing, you need to fix your query to match the current version of MySQL or MariaDB.

    I think something is wrong about how you construct Point object, namely instead of wktToGeometry you should use wktToPoint:

    • ST_GeomFromText(wkt, srid) can return ANY spatial type that is supported by MySQL and can be represented by WKT. This makes it loosely typed if you want to think of it like that.
    • ST_PointFromText(wkt, srid) a strongly-typed POINT-constructor from Well-known text.
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search