Introduction to the

Recently, I am working on a business function. The requirement involves obtaining the physical store nearest to the terminal through the positioning of the terminal. Because there is no previous knowledge about calculating distance of geographical position coordinates, only redis can calculate the distance between two coordinate points (latitude and longitude). But it doesn’t meet the current requirements, because there is filtering, sorting, and paging in addition to calculating distances. So I’m not going to implement it in Redis. SQL > alter table memory sort Therefore, I searched online and found the plug-in postGIS for PGSQL, which supports the calculation of latitude and longitude distance at the database level.

Postgis website: Postgis.net/

Postgis installation

There are actually two ways to install Postgis.

The first is to install PGSQL through the Stack Builder when it is installed. But this may not come down now, alive slowly will be stuck.

So I used the second method of installation, directly download the postGIS plug-in installation package from the official website to install, the whole process is also very simple. I’m using PGSQL 14 (postGIS needs to match the PGSQL version, see the official website for details). The installation process is roughly the following steps:

Then select the following steps all yes

Create table

Postgis CREATE EXTENSION postgis; create table t_store ( id serial not null constraint t_store_pk primary key, name varchar(64) not null, longitude varchar, latitude varchar not null, geom geometry(Point, 4326) not null, create_time timestamp default now() not null, update_time timestamp default now() not null ); Comment on column t_store.name is' iD '; Comment on column t_store.longitude is' longitude '; Comment on column t_store.latitude is' latitude '; Comment on column t_store.geom is '地 址 '; alter table t_store owner to postgres;Copy the code

Build Spring Boot + MybatisPlus development framework

There are a few main points to note about the scaffolding code.

Add corresponding dependencies

dependencies { implementation 'org.springframework.boot:spring-boot-starter-web' compileOnly 'org.projectlombok:lombok' annotationProcessor 'org.projectlombok:lombok' testImplementation 'org.springframework.boot:spring-boot-starter-test' Compile group: 'com.baomidou', name: 'mybatis-plus-boot-starter', version: '3.5.1' 'net.postgis', name: 'postgis-jdbc', version: '2.5.1' compile group: 'org. postgresQL ', name: 'postgresQL'}Copy the code

Add the configuration

spring.application.name=gradle_test    
server.port=8080

# pgsql
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/postgis_test
spring.datasource.username=postgres
spring.datasource.password=123456

# mybatis config ：自定义的类型转换类
mybatis-plus.type-handlers-package=com.unfbx.gradle_test.config.typehandler

Copy the code

2. Customize TypeHandler to handle PGSQL geolocation fields

package com.unfbx.gradle_test.config.typehandler; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.postgis.Geometry; import org.postgis.PGgeometry; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** *  * * @author * @date 2022-04-03 */ public class AbstractGeometryTypeHandler<T extends Geometry> extends BaseTypeHandler<T> {/** ** set the value to @param ps * @param I * @param parameter * @param jdbcType * @throws SQLException */ public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException { PGgeometry geometry = new PGgeometry(); geometry.setGeometry(parameter); ps.setObject(i, geometry); } /** * obtain a value based on the columnName ** @param rs * @param columnName * @return * @throws SQLException */ public T getNullableResult(ResultSet rs, String columnName) throws SQLException { PGgeometry pGgeometry = (PGgeometry) rs.getObject(columnName); if (pGgeometry == null) { return null; } return (T) pGgeometry.getGeometry(); } /** * obtain a value based on the columnIndex position ** @param rs * @param columnIndex * @return * @throws SQLException */ public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException { PGgeometry pGgeometry = (PGgeometry) rs.getObject(columnIndex); if (pGgeometry == null) { return null; } return (T) pGgeometry.getGeometry(); } /** * obtain the value by columnIndex ** @param cs * @param columnIndex * @return * @throws SQLException */ public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { PGgeometry pGgeometry = (PGgeometry) cs.getObject(columnIndex); if (pGgeometry == null) { return null; } return (T) pGgeometry.getGeometry(); }}Copy the code

Custom processing type conversion coordinates AbstractGeometryTypeHandler inheritance

@MappedTypes(Point.class)
public class PointTypeHandler extends AbstractGeometryTypeHandler<Point> {
}
Copy the code

Coordinate conversion tool

public class CoordinatesUtil { public static Point buildPoint(String longitude, String latitude) throws Exception { if (StringUtils.isEmpty(longitude) || StringUtils.isEmpty(latitude)) { throw new Exception(" coordinate data Exception "); } try { Point point = new Point(Double.parseDouble(longitude),Double.parseDouble(latitude)); return point; } catch (Exception e) { throw e; }}}Copy the code

Interface Function Test

There are only two interfaces here. Coordinates longitude and latitude pick directly using Baidu map or Gaode map can be.

Baidu map coordinate pickup system

Add latitude and longitude information

@PostMapping("/store") public String add(@RequestBody Store store) throws Exception { store.setGeom(CoordinatesUtil.buildPoint(store.getLongitude(), store.getLatitude())); storeService.save(store); Return "added successfully, id:" + store.getid (); }Copy the code

Query merchants within a distance based on location information

The request parameters carry latitude and longitude information. The following query is within 10000m from nanhuizui.

@PostMapping("/stores") public List<StoreResp> queryList(@RequestBody StoreReq req){ return storeService.queryList(req);  }Copy the code
{"longitude": "121.979515", "latitude": "30.888643", "distance":10000}Copy the code

Return information

[{" id ": 1," name ":" Shanghai planetarium ", "short" : 5585.5020385}, {" id ": 2," name ":" nanhui mouth ", "short" : 0.0}]Copy the code

SQL = “SQL”;

Select name,geom, ST_Distance(ST_GeographyFromText(ST_AsText('POINT(121.926753 30.912628)')), ST_GeographyFromText(ST_AsText(geom))) as distance from t_store;Copy the code

The source address

Github.com/Grt1228/pos…

References:

[1] baidu map coordinate system on pick up: api.map.baidu.com/lbsapi/getp…

Reference [2] CSDN article: blog.csdn.net/zxt521yt/ar…