Project/NomadWorker
자바 스프링 mysql 위치좌표(위도 경도) 정리, POINT DATA
sung.hyun.1204
2022. 7. 22. 15:58
[한국외대 모각코] 도커 스터디 일정을 만든후 개인 공부를 한내용을 올린다.
다음 주 부터는 정기적으로 도커 내용이 TIL 에 들어 갈 듯 싶다.
내용 :자바 스프링 api 설계 주기능 : request 위도경도에 대한 response 로 주변 가게정보를 넘기는 api
Spring Boot
Spring jpa
mysql
자료 찾다보면 한번씩 하단 깃허브 와 관련된 블로그 자료를 볼것이다.
정말 감사함니다.. 대부분의 코드는 하단 깃헙을 참고하여 만들었습니다.
GitHub - wooody92/wooody92.github.io
Contribute to wooody92/wooody92.github.io development by creating an account on GitHub.
github.com
1. 버전확인 어쩌면 제일 중요한,,
mysql 크게 2 가지 버전이 있는데 v 5.6 과 v - 8이다 상황에 맞춰 gradle 이랑 yaml 을 맞춰 준다. 물론 hybernate 도..
1.application yml
jpa:
hibernate:
ddl-auto: update
properties:
hibernate:
#show_sql: true
format_sql: true
default_batch_fetch_size: 100
dialect: org.hibernate.spatial.dialect.mysql.MySQL8SpatialDialect
database-platform: org.hibernate.spatial.dialect.mysql.MySQL8SpatialDialect
# database-platform: org.hibernate.spatial.dialect.mysql.MySQL56InnoDBSpatialDialect
database: mysql2.build.gradle. 자신의 hibernate version에 맞게,,
implementation group: 'org.hibernate', name: 'hibernate-spatial', version: '5.6.9.Final'
2 . data 넣기
Java sping 에서 import org.locationtech.jts.geom.Point;
을 import 후 변수를 Point type 으로 지정 하면 mysql 에서는 POINT 타입이 아닌 GEOMETRY 로 들어간다.
생성한 point data 종류를 확인을 하자. 제발,,
상황 geometry 타입에 (50.8 4.44) 넣고 싶다.
다음 쿼리는 mysql workbench 에서 넣었다.
UPDATE `testdb`.`place` SET `p_gpoint` = ST_GeomFromText('POINT(50.8 4.44)') WHERE (`p_id` = '7');
출력을 해보자 ! LineString 이란 사각형 범위를 만드는 것이라 생각하면 된다.
mysql 에서는 안보여서 터미널 에서 실행했다.
방금 넣은 좌표 (50.8 4.44 ) 가 범위에 들어 가도록 좌표 범위를 적어주고 , 범위에 맞는다면 맞는 p_weekt 를 출력하게 한다.
// 쿼리문,
SELECT p_weekt from testdb.place AS p
WHERE MBRContains(ST_LINESTRINGFROMTEXT('LineString(60.8 4.492860, 50.149710 4.335226)'), p.p_gpoint);
자 이제 ,, javasping,jpa 를 이용하자.
DOMAIN
package hackathon.nomadworker.domain;
import lombok.val;
import org.locationtech.jts.geom.Point;
import javax.persistence.*;
import lombok.Getter;
import lombok.Setter;
import java.util.ArrayList;
import java.util.List;
@Entity
@Getter
@Setter
public class Place {
@Id @GeneratedValue
@Column(name = "p_id")
private long id;
private String p_cate;
private String p_name;
private String p_weekt;
private String p_weekndt;
private String p_addr;
private String p_image;
private String p_storeType;
private float p_latitude;
private float p_longitude;
private Integer rent_price;
private Point p_gpoint;
...
}
Repository
package hackathon.nomadworker.repository;
import hackathon.nomadworker.domain.Place;
import hackathon.nomadworker.util.Direction;
import hackathon.nomadworker.util.GeometryUtil;
import hackathon.nomadworker.util.Location;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Repository;
import javax.persistence.Query;
import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import java.util.List;
@Repository
@RequiredArgsConstructor
public class PlaceRepository {
private final EntityManager em;
..
..
public List<Place> getNearByCoordinate(Double latitude, Double longitude, Double distance)
{
Location northEast = GeometryUtil
.calculate(latitude, longitude, distance, Direction.NORTHEAST.getBearing());
Location southWest = GeometryUtil
.calculate(latitude, longitude, distance, Direction.SOUTHWEST.getBearing());
double x1 = northEast.getLatitude();
double y1 = northEast.getLongitude();
double x2 = southWest.getLatitude();
double y2 = southWest.getLongitude();
System.out.println(x1);
System.out.println(y1);
System.out.println(x2);
System.out.println(y2);
String pointFormat = String.format("'LINESTRING(%f %f, %f %f)')", x1, y1, x2, y2);
System.out.println(pointFormat);
// Query query = em.createNativeQuery("SELECT p.p_id, p.p_cate, p.p_name,p.p_addr, p.p_image \n" +
// "FROM place AS p \n"+
// "WHERE MBRContains(ST_LINESTRINGFROMTEXT(" + pointFormat + ", p.p_point)",Place.class).setMaxResults(15);
Query query = em.createNativeQuery("SELECT * \n" +
"FROM place AS p \n"+
"WHERE MBRContains(ST_LINESTRINGFROMTEXT(" + pointFormat + ",p.p_gpoint)",Place.class).setMaxResults(15);
List<Place> places = query.getResultList();
System.out.println(places);
return places;
}
}
Repo 단에서 원하는것만 가져올려고 하다가 에러 나서 전체를 * 가져 온후 서비스 단에서 원하는 정보만 걸러준다
Utils 총 3 가지의 class
package hackathon.nomadworker.util;
import lombok.Getter;
@Getter
public enum Direction {
NORTH(0.0),
WEST(270.0),
SOUTH(180.0),
EAST(90.0),
NORTHWEST(315.0),
SOUTHWEST(225.0),
SOUTHEAST(135.0),
NORTHEAST(45.0);
private final Double bearing;
Direction(Double bearing) {
this.bearing = bearing;
}
package hackathon.nomadworker.util;
public class GeometryUtil {
public static Location calculate(Double baseLatitude, Double baseLongitude, Double distance, Double bearing) {
Double radianLatitude = toRadian(baseLatitude);
Double radianLongitude = toRadian(baseLongitude);
Double radianAngle = toRadian(bearing);
Double distanceRadius = distance / 6371.01;
Double latitude = Math.asin(sin(radianLatitude) * cos(distanceRadius) +
cos(radianLatitude) * sin(distanceRadius) * cos(radianAngle));
Double longitude = radianLongitude + Math.atan2(sin(radianAngle) * sin(distanceRadius) *
cos(radianLatitude), cos(distanceRadius) - sin(radianLatitude) * sin(latitude));
longitude = normalizeLongitude(longitude);
return new Location(toDegree(latitude), toDegree(longitude));
}
private static Double toRadian(Double coordinate) {
return coordinate * Math.PI / 180.0;
}
private static Double toDegree(Double coordinate) {
return coordinate * 180.0 / Math.PI;
}
private static Double sin(Double coordinate) {
return Math.sin(coordinate);
}
private static Double cos(Double coordinate) {
return Math.cos(coordinate);
}
private static Double normalizeLongitude(Double longitude) {
return (longitude + 540) % 360 - 180;
}
}
package hackathon.nomadworker.util;
import lombok.Getter;
@Getter
public class Location {
private Double latitude;
private Double longitude;
public Location(Double latitude, Double longitude) {
this.latitude = latitude;
this.longitude = longitude;
}
}
DTO. api requset, response 를 지정.
package hackathon.nomadworker.dto;
import hackathon.nomadworker.domain.Place;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.locationtech.jts.geom.Point;
public class PlaceDtos
{
@Data
@AllArgsConstructor
public static class PlaceResultResponse<T>
{
private String message;
private int status;
private T data;
}
...
@Data
public static class PlaceDtoCoordinate
{
private long p_id;
private String p_cate;
private String p_name;
private String p_image;
public PlaceDtoCoordinate(Place place)
{
this.p_id = place.getId();
this.p_cate = place.getP_cate();
this.p_name = place.getP_name();
this.p_image = place.getP_image();
}
}
...
}
Service
package hackathon.nomadworker.service;
import hackathon.nomadworker.domain.Place;
import hackathon.nomadworker.repository.PlaceRepository;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
@AllArgsConstructor
public class PlaceService {
private final PlaceRepository placeRepository;
public List<Place> findPlacesByCategory(String place_tag) {return placeRepository.findPlacesByCategory(place_tag);
}
public List<Place> findPlacesByCoordinate(float latitude,float longitude)
{ // 반견 1km
return placeRepository.getNearByCoordinate((double)latitude, (double)longitude,(double)1);
}
public List<Place> findPlacesall()
{
return placeRepository.findAll();
}
}
API--
package hackathon.nomadworker.api;
import hackathon.nomadworker.domain.Place;
import hackathon.nomadworker.dto.PlaceDtos.*;
import hackathon.nomadworker.service.PlaceService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestHeader;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.stream.Collectors;
import java.util.List;
@RestController
@RequiredArgsConstructor
public class PlaceApiController {
private final PlaceService placeService;
@GetMapping(value = "/api/place/near", produces = "application/json;charset=UTF-8")
public PlaceResultResponse placeByCoordinateGet(@RequestHeader("Authorization") String u_uid,
@RequestParam("latitude") float latitude,
@RequestParam("longitude") float longitude) {
List<Place> places = placeService.findPlacesByCoordinate(latitude,longitude);
if (places.isEmpty()) {
return new PlaceResultResponse("근처 장소 조회 실패", 400, null);
} else {
List<PlaceDtoCoordinate> collect = places.stream().map(place -> new PlaceDtoCoordinate(place)).collect(Collectors.toList());
return new PlaceResultResponse("근처 장소 조회 성공", 200, collect);
}
}
}
결과.
(50.8 4.44) 을 넣었던 것이 근처 범위로 잘 나온다.
