Consideration of GIS and O / R Mapper

MySQL GIS data and JPA

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 ...

Try it

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)

Generation of GIS data from latitude / longitude

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.

Use of MySQL GIS function (function)

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.

API for getting a list sorted by distance

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 ♪

Summary

--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