How to use(insert) spatial data in mysql and JPA.

Introduction

I introduce how to use spatial type columns in MySQL and JPA.

Subject

I introduce how to use spatial type columns in MySQL and JPA.

MySQL has spatial data types that correspond to OpenGIS classes.
Some spatial data types hold single geometry values.

GEOMETRY
POINT
LINESTRING
POLYGON

GEOMETRY can store geometry values of any type.
The other single value types(POINT, LINESTRING, and POLYGON) restrict their values to a particular geometry type.
The other spatial data types hold collections of values.

MULTIPOLYGON
MULTILINESTRING
MULTIPOLYGON
GEOMETRY COLLECTION

GEOMETRY COLLECTION can store a collection of objects of any type.
The other collection types(MULTIPOINT, MULTILINESTRING, and MULTIPOLYGON) restrict collection members to those having a particular geometry type.

I will use point, multypolygon, polygon of spatial types in my example.
The environment is followings.
spring boot 1.5.21.RELEASE
JDK 1.8
MySQL 8.0.15

First, create Table having spatial type columns in database.

CREATE TABLE `spatialtable` (
    `spatialInd` INT(11) NOT NULL AUTO_INCREMENT,
    `sPolygon` POLYGON NULL DEFAULT NULL,
    `sMultiPolygon` MULTIPOLYGON NULL DEFAULT NULL,
    `sPoint` POINT NULL DEFAULT NULL,
    PRIMARY KEY (`spatialInd`)
)
ENGINE=InnoDB;

Second, create a spring boot project from sts. I will add org.hibernate-spatial library on pom.xml and dependency content is followings.

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-spatial</artifactId>
    <version>5.2.12.Final</version>
</dependency>

Full pom.xml is followings.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.21.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.spatial</groupId>
	<artifactId>spatial</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spatial</name>
	<description>spatial</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		
		<dependency>
		    <groupId>org.hibernate</groupId>
		    <artifactId>hibernate-spatial</artifactId>
		    <version>5.2.12.Final</version>
		</dependency>
		
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
	</dependencies>


	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Let's configure classes of domain, util, repository to use JPA in boot project.

package com.spatial.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

import com.vividsolutions.jts.geom.MultiPolygon;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;

import lombok.Data;

@Data
@Entity
public class SpatialTable {

	@Id
	@GeneratedValue
	private int spatialInd;
	
	@Column
	private Polygon sPolygon;
	
	@Column
	private MultiPolygon sMultiPolygon;
	
	@Column
	private Point sPoint;
	
}

package com.spatial.repository;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.spatial.domain.SpatialTable;

public interface SpatialTableRepository extends JpaRepository<SpatialTable, Integer>{

	String insertPointQuery = "INSERT INTO SPATIALTABLE SET SPOINT = ST_GEOMFROMTEXT(:point, 4326)";

	@Modifying
	@Transactional
	@Query(nativeQuery = true, value = insertPointQuery)
	int savePoint(@Param("point") String point);

}

package com.spatial.utils;

import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.WKTReader;
import com.vividsolutions.jts.io.ParseException;

public class GeometryUtil {

	private static WKTReader wktReader = new WKTReader();
	
	public static Geometry wktToGeometry(String wellKnownText) {
		Geometry geometry = null;
		
		try {
			geometry = wktReader.read(wellKnownText);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		return geometry;
	}
	
}

I wrote WKTReader, SRID(4326) in code.

What is WKTReader?

The default value inserted spatial type data in table is internal geometry format.

But value can convert them to that format from either Well-Known Text(WKT) or Well-Known Binary(WKB) format. Therefore I will use wkt format.

What is SRID(4326)?

SRID: The spatial reference system identifier.

SRS(Spatial reference system): All geometric objects(geometries for short), whether they are points, line strings or polygons, exist in one, and only one. spatial reference system.

4326?

The definition of SRID 4326 starts with the string "GEOGCS".

That means that it is a geographic SRS.

It represents the surface of the earth as an ellipsoid(a sphere with a slightly shortened north-south axis)

The axes in geographic systems are latitude and longitude in the same angle measure, which in this case is degrees the axes are not orthogonal.

All meridians meet at the North Pole and the South Pole, so they are clearly not parallel.

And SRID 4326 is GPS coordinates.

Last, Test to insert a data in the spatial table on spring boot project.

Followings my repositoryTest code.

package com.spatial.repository;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.spatial.domain.SpatialTable;
import com.spatial.utils.GeometryUtil;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.Point;

@SpringBootTest
@RunWith(SpringRunner.class)
public class SpatialTableRepositoryTest {

	@Autowired
	private SpatialTableRepository spatialTableRepository;
	
	@Test
	public void test_savePoint() {
		final String PointWKT = "POINT(37.516455 126.721757)";
		final Geometry GeoPoint = GeometryUtil.wktToGeometry(PointWKT);
		
		assertNotNull(GeoPoint);
		
		SpatialTable spatialTable = new SpatialTable();
		spatialTable.setSPoint((Point)GeoPoint);
		
		spatialTableRepository.savePoint(spatialTable.getSPoint().toString());
		
		spatialTable = spatialTableRepository.findAll().get(0);
		assertEquals(spatialTable.getSPoint().toString(), PointWKT);
		
		spatialTableRepository.delete(spatialTable.getSpatialInd());
	}
	
}

Conclusion

I used wkt format and basic spatial data type.

If you want to know more, see the below reference URLs.

Reference URL

https://dev.mysql.com/doc/refman/8.0/en/spatial-type-overview.html
https://mysqlserverteam.com/geographic-indexes-in-innodb/

더 많은 내용을 보시려면 아래를 참고하세요.

블로그의 다른 글

https://developer-syubrofo.tistory.com/88

The unknown errors occur in pom.xml when using STS4

Introduction I start I made the new spring starter project in sts4. But I checked the unknown error in pom.xml. Subject Why do these errors occur? When the spring framework builds a..