Mybatis custom field types take Mysql space data store as an example

class toilet {
    String name;
    double lng;
    double lat
}
Copy the code

I won’t go into detail here, but it’s a very common implementation

Because the data structure is not of spatial type, spatial indexes cannot be created, and the query performance is prone to bottlenecks.

Query mysql official website, the result is as follows

The actual storage format of Geometry is 25 bytes

• 4 bytes for the integer SRID (0)
• 1 byte (integer byte order) (1 = small byte order)
• 4 bytes for integer type information (MySQL uses values from 1 to 7 to representPoint.LineString.Polygon.MultiPoint.MultiLineString.MultiPolygon, andGeometryCollection).
• 8 – byte double – precision X coordinates
• 8 – byte double – precision Y coordinates

For example, POINT(1-1) consists of the following 25-byte sequence, each represented by two hexadecimal digits:

mysql> SET @g = ST_GeomFromText('POINT(1 -1)');
mysql> SELECT LENGTH(@g);
+------------+
| LENGTH(@g) |
+------------+
|         25 |
+------------+
mysql> SELECT HEX(@g);
+----------------------------------------------------+
| HEX(@g)                                            |
+----------------------------------------------------+
| 000000000101000000000000000000F03F000000000000F0BF |
+----------------------------------------------------+
Copy the code

Entity class

class toilet {
    String name;
    
	geopoint location;
}
// Customize the data type
class geopoint {
    double lng;
    double lat 
}
Copy the code

GeoPointTypeHandler

@Slf4j
@MappedTypes({GeoPoint.class})
public class GeoPointTypeHandler extends BaseTypeHandler<GeoPoint> {
    /** * space reference identifier MySQL database default is 0 */
    private static int SRID = 0;
    A byte order indicator of 1 or 0 indicates small-endian or big-endian storage. Small and large byte orders are also called network data representation (NDR) and external data representation (XDR) */, respectively
    private static byte ENDIAN = (byte) 1;


    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, GeoPoint parameter, JdbcType jdbcType) throws SQLException {
        ps.setBytes(i, to(parameter));
    }

    @Override
    public GeoPoint getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parse(rs.getBytes(columnName));
    }

    @Override
    public GeoPoint getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return parse(rs.getBytes(columnIndex));
    }

    @Override
    public GeoPoint getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return parse(cs.getBytes(columnIndex));
    }

    /** * bytes Go to GeoPoint object **@param bytes
     */
    private GeoPoint parse(byte[] bytes) {
        ByteBuffer wrap = ByteBuffer.wrap(bytes)
                // Small endpoint sort (Java default is big endpoint sort, this should be changed)
                .order(ByteOrder.LITTLE_ENDIAN);
        int SRID = wrap.getInt();
        byte endian = wrap.get();
        int wkbType = wrap.getInt();
        double x = wrap.getDouble();
        double y = wrap.getDouble();
        GeoPoint geoPoint = new GeoPoint(x, y);
        log.info("geo-point:{}", JSONUtil.toJsonStr(geoPoint));
        return geoPoint;
    }

    /** * GeoPoint Converts bytes to **@param geoPoint
     */
    private byte[] to(GeoPoint geoPoint) {
        ByteBuffer wrap = ByteBuffer.allocate(25)
                // Small endpoint sort (Java default is big endpoint sort, this should be changed)
                .order(ByteOrder.LITTLE_ENDIAN);
        // SRID: 0
        wrap.putInt(SRID);
        A byte order indicator of 1 or 0 indicates small-endian or big-endian storage. Small and large byte orders are also known as network Data Representation (NDR) and External Data Representation (XDR), respectively
        wrap.put(ENDIAN);
        // the WKB type is the code indicating the geometry type wkbType: 1 MySQL uses values from 1 to 7 to represent Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
        wrap.putInt(1);
        / / X coordinate
        wrap.putDouble(geoPoint.getLon());
        / / Y
        wrap.putDouble(geoPoint.getLat());
        returnwrap.array(); }}Copy the code

The entity class is annotated

@TableName(autoResultMap = true) // Attention!! Mapping annotations must be enabled
class toilet {
    String name;
    @TableField(typeHandler = GeoPointTypeHandler.class)
	geopoint location;
}
Copy the code

The calculation results are consistent with the calculation distance API provided by Autonavi. Autonavi calculates distance webAPI

SELECT ( st_distance_sphere ( point ( 116.481028.39.989643  ), point ( 114.465302.40.004717 ), 6378137.0))AS distance
Copy the code

Note here that the data should not be arbitrarily manipulated or the st_distance_sphere parameter will be returned with an error.

Reference:

• Dev.mysql.com/doc/refman/…