-1

hi i'm trying to insert and read geo data(POINT) to mySQL DB.

I am using JPA.

I got latitude and longitude from the frontend and I made a String like POINT(10 20) as wkt.

then by using WKTReader, I made a Point Object, made an entity , and call the jpa save method.

but I got Data truncation: Cannot get geometry object from data you send to the GEOMETRY field error.

what's wrong with my code?

by the way i used

org.locationtech.jts.geom.Point;

+) when I tried to insert point as WKT(Stirng), it works. (and it is stored with BLOB in DB) should I have to do like this????

Service

  @Transactional
    public void addClub (AddClubRequest clubRequest) throws ParseException {
        Member master = memberRepository.getReferenceById(clubRequest.getUserId());
  
        Point point = makePoint(clubRequest.getLatitude(), clubRequest.getLongitude());
    
        Club club = clubRequest.toClub(master, point);

        clubRepository.save(club);

    }

public Point makePoint (Float latitude, Float longitude) throws ParseException {
         String wkt = String.format("POINT(%s %s)", latitude, longitude);
         return (Point)new WKTReader().read(wkt);
    }

Entity

 @Column(name = "club_point", columnDefinition = "POINT")
    private Point clubPoint;

yml

spring  
 jpa :
    properties:
      hibernate:
        database-platform: org.hibernate.spatial.dialect.mysql.MySQL8SpatialDialect

build.gradle

implementation 'org.hibernate:hibernate-core:5.6.3.Final'
    implementation group: 'org.locationtech.jts', name: 'jts-core', version: '1.16.1'
    implementation group: 'org.hibernate', name: 'hibernate-spatial', version: '5.6.15.Final'
GyeongEun Kim
  • 21
  • 4

1 Answers1

0

Yhe Objective here is to be able to save before encountering "Data Truncation".

Your error seems to indicate that the data being sent isn't right. I think we can try a different method to save the data using the MySQL function:

ST_PointFromText

In order to save the data directly.

That's how i would modify your code, starting by makePoint:

public String makePoint(Float latitude, Float longitude) {
    return String.format("POINT(%s %s)", latitude, longitude);
}

Now in your addClub: clubRepository.save(club);
I'll modify it to:

clubRepository.saveWithPoint(club, makePoint(clubRequest.getLatitude(), clubRequest.getLongitude()));

And we create a new Method:

@Transactional
@Modifying
@Query(value = "INSERT INTO club (club_name, club_point) VALUES (:clubName, ST_PointFromText(:point))", nativeQuery = true)
void saveWithPoint(@Param("clubName") String clubName, @Param("point") String point);

Don't forget to import all the stuff that you need.

James
  • 25
  • 8