##MySQLのGISデータとJPA
JavaEE(WildFly)のJPA経由でMySQLのGISデータにアクセスしてみます。
今回は最も良く利用するであろうと考えられる「緯度・経度」の情報を利用してみます。
GISデータのフィールドは独自バイナリで保存されているので、ここにどうアクセスするか…というお話。
##やってみること
RestAPI経由による位置(緯度・経度)情報の保管、ならびにGIS機能による距離計算の仕組みを作ってみます。
具体的には
1: RestAPIからIDと共に緯度・経度情報をPOST
2: IDに対するGISデータをMySQLに保存
3: RestAPIからIDを指定してGET
4: MySQLからレコードを取得
5: MySQLのGIS機能を用いて自分との距離を計算
6: 距離が近い順にソートして出力
という処理を実装してみます。
(なお環境は WildFly14+MySQL8.0.13+Connector/J8.0.13)
##緯度・経度からGISデータの生成
GISデータはJava上でbyte[]型となります。（独自バイナリ…）
緯度・経度からバイナリへの変換はST_GeomFromText('POINT(経度 緯度)')という関数で行えるのですが、いちいちcreateNativeQueryを呼び出してバイナリ取得してsetterで保存というのも面倒なので、GeneratedColumnで生成してしまうことにします。
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
これに対するJava側のEntityは
@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とSetter
// ただしlocationはGetterのみ
}
となります。GeneratedColumnに対してinsert/update時に書き込まないようにする処理が必要ですね。
JAX-RSでPOSTを受信するコードは
@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();
}
}
public class Point {
private String Latitude;
private String Longitude;
// 以下GetterとSetter
}
http://..../{ID}にJSONをPOSTすれば、{ID}に対するGISデータが保存されます。
##MySQLのGIS機能(関数)の利用
Boost.GeometryのJava版があれば良いのですが無いものは無い…ということで、EntityManagerからcreateNativeQuery経由でMySQLのGIS機能を利用することにします。
@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;
}
}
バイナリデータをクエリ化して叩き込むために面倒なことやってますが、もう少し綺麗な方法があるかも知れませんね。
ともかくEJBでインジェクトしてやることで、ST_Distance_Sphere関数の結果を文字列として受け取ることが出来るようになりました。
##距離でソートされた一覧取得のAPI
先程作成した登録用のAPI(API.java)に、一覧取得用のGETメソッドを追加します。
@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();
}
}
public class Result {
private String ID;
private Double Distance;
// 以下GetterとSetter
}
result.setDistance(Double.parseDouble(geoPoint.distance(mypos.getLocation(), p.getLocation()))); の部分がGISに関する処理です。
先程作成した距離計算のEJB geoPoint.distance(byte[] pos1, byte[] pos2) に対して mypos.getLocation() と p.getLocation() でGISバイナリデータを代入しています。
API経由で駅の緯度・経度を登録してみました。
これに対して http://...../大阪駅 とGetメソッドを投げると次のように返ってきます。
[
{
"ID": "三宮駅",
"distance": 26586.663958186175
},
{
"ID": "京都駅",
"distance": 39434.1794831947
},
{
"ID": "名古屋駅",
"distance": 134598.65725231185
}
]
ちゃんと近い順にソートされて返ってきてますね♪
##まとめ
- GISデータはEntity上ではbyte[]型
- 緯度・経度などの座標とのEncode/DecordはGeneratedColumnで行うのが便利
- 距離計算などのGIS機能はEntityManager.createNativeQueryで実行可能
ただしGIS機能を使って大量の計算をさせる場合には、クエリ発行のオーバヘッドが大きくなるかも知れませんので、MySQL側でStoredFunctionを作っておいて呼び出す…などの工夫が必要かも。
Boost.GeometryのJava版、出ないですかね…www