Try to access MySQL GIS data via JPA of JavaEE (WildFly). This time, I will use the information of "latitude / longitude" that is considered to be used most often. The fields of GIS data are stored in their own binary, so how to access them ...
Let's create a mechanism for storing position (latitude / longitude) information via RestAPI and calculating distance using the GIS function. In particular 1: POST latitude / longitude information along with ID from Rest API 2: Save GIS data for ID in MySQL 3: GET by specifying ID from Rest API 4: Get records from MySQL 5: Calculate the distance to yourself using the GIS function of MySQL 6: Sort and output in ascending order of distance I will implement the process. (The environment is WildFly14 + MySQL8.0.13 + Connector / J8.0.13)
GIS data is of type byte [] on Java. (Original binary ...)
Conversion from latitude / longitude to binary can be done with the function ST_GeomFromText ('POINT (longitude latitude)')
, but it is troublesome to call createNativeQuery to get the binary and save it as a setter
, so GeneratedColumn I will generate it with.
createtable.sql
CREATE TABLE `position` (
`id` varchar(64) NOT NULL,
`longitude` varchar(256) DEFAULT NULL,
`latitude` varchar(256) DEFAULT NULL,
`location` point GENERATED ALWAYS AS (st_geomfromtext(concat(_utf8mb4'POINT(',`longitude`,_utf8mb4' ',`latitude`,_utf8mb4')'))) STORED,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
The Entity on the Java side for this is
Position.java
@Entity
@Table(name="position")
public class Position implements Serializable {
@Id
private String id;
private String latitude;
private String longitude;
@Column(
name = "location",
insertable = false,
updatable = false
)
private byte[] location;
private static final long serialVersionUID = 1L;
//Getter and Setter below
//However, location is Getter only
}
It will be. It is necessary to process the GeneratedColumn so that it is not written at the time of insert / update.
The code that receives POST with JAX-RS is
API.java
@RequestScoped
@Path("/api")
@Produces("application/json")
@Consumes("application/json")
public class API {
@PersistenceContext(unitName = "geotest")
private EntityManager em;
@POST
@Path("{ID}")
@Transactional
public Response setPosition(@PathParam("ID") String ID, Point point) {
Position position = new Position();
position.setId(ID);
position.setLatitude(point.getLatitude());
position.setLongitude(point.getLongitude());
em.persist(position);
return Response.ok().status(201).build();
}
}
Point.java
public class Point {
private String Latitude;
private String Longitude;
//Getter and Setter below
}
If you POST JSON to http: // ..../ {ID}
, the GIS data for {ID} will be saved.
It would be nice if there was a Java version of Boost.Geometry, but there is nothing that isn't there ... So, I decided to use the GIS function of MySQL from EntityManager via createNativeQuery.
GeoPoint.java
@Stateless
public class GeoPoint {
@PersistenceContext(unitName = "geotest")
private EntityManager em;
public String distance(byte[] pt1, byte[] pt2) {
return String.valueOf(em.createNativeQuery("select ST_Distance_Sphere(unhex('" + tohex(pt1) + "'), unhex('" + tohex(pt2) + "'))").getSingleResult());
}
private String tohex(byte[] bin) {
String p = "";
for(int i=0; i<bin.length; i++) {
p = p + String.format("%02x", bin[i]);
}
return p;
}
}
I'm doing a lot of work to query and type in binary data, but there may be a cleaner way.
Anyway, by injecting with EJB, the result of the ST_Distance_Sphere
function can be received as a character string.
Add the GET method for list acquisition to the registration API (API.java) created earlier.
API.java
@RequestScoped
@Path("/api")
@Produces("application/json")
@Consumes("application/json")
public class API {
@PersistenceContext(unitName = "geotest")
private EntityManager em;
@EJB
private GeoPoint geoPoint;
@GET
@Path("{ID}")
public Response getPosition(@PathParam("ID") String ID) {
Position mypos = em.find(Position.class, ID);
List<Position> pos = em.createQuery("select p from Position p", Position.class).getResultList();
List <Result> results = pos.stream()
.filter(p -> !p.getId().equals(mypos.getId()))
.map(p -> {
Result result = new Result();
result.setID(p.getId());
result.setDistance(Double.parseDouble(geoPoint.distance(mypos.getLocation(), p.getLocation())));
return result;
})
.sorted(comparing(Result::getDistance))
.collect(Collectors.toList());
return Response.ok(results).build();
}
@POST
@Path("{ID}")
@Transactional
public Response setPosition(@PathParam("ID") String ID, Point point) {
Position position = new Position();
position.setId(ID);
position.setLatitude(point.getLatitude());
position.setLongitude(point.getLongitude());
em.persist(position);
return Response.ok().status(201).build();
}
}
Result.java
public class Result {
private String ID;
private Double Distance;
//Getter and Setter below
}
result.setDistance (Double.parseDouble (geoPoint.distance (mypos.getLocation (), p.getLocation ())));
part is the processing related to GIS.
GIS binary data is assigned to the distance calculation EJB geoPoint.distance (byte [] pos1, byte [] pos2)
created earlier with mypos.getLocation ()
and p.getLocation ()
. ..
I tried to register the latitude and longitude of the station via API.
On the other hand, if you throw http: //...../Osaka station and Get method, the following will be returned.
result.json
[
{
"ID": "Sannomiya Station",
"distance": 26586.663958186175
},
{
"ID": "Kyoto Station",
"distance": 39434.1794831947
},
{
"ID": "Nagoya station",
"distance": 134598.65725231185
}
]
It is sorted in the order of closeness and returned ♪
--GIS data is byte [] type on Entity --Encode / Decord with coordinates such as latitude and longitude is convenient to do with Generated Column --GIS functions such as distance calculation can be executed with EntityManager.createNativeQuery
However, if you use the GIS function to perform a large amount of calculations, the overhead of issuing queries may increase, so it may be necessary to create a StoredFunction on the MySQL side and call it. Java version of Boost.Geometry, isn't it coming out ... www