MySQL spatial data storage and functions

There are also types such as MULTIPOINT (multi-point), MULTILINESTRING (multi-line), MULTIPOLYGON (multi-faceted), GEOMETRYCOLLECTION (collection, which can contain points, lines and faces)

2. What is GeoJSON

GeoJSON is a format for encoding various geographic data structures. A GeoJSON object can represent a geometry, a feature, or a collection of features. GeoJSON supports the following geometry types: point, line, polygon, multipoint, multiline, multipolygon, and geometry collection. A feature in GeoJSON contains a geometry object and other properties, and a feature collection represents a set of features. A complete GeoJSON data structure is always an object (in JSON terminology). In GeoJSON, objects consist of collections of name/value pairs, also called members. For each member, name is always a string. The value of a member is either a string, a number, an object, an array, or one of the following literal constants: " true ", " false ", and " null ". An array is made up of the elements whose values ​​are mentioned above.

In addition to simple points, lines, and surfaces, in order to meet the complex geographical environment and map business, there will be multi-points ( MultiPoint ), multi-lines ( MultiLineString ), multi-polygons ( MultiPolygon ), geometry collections ( GeometryCollection ), etc. Familiar with JSON can quickly familiarize yourself with and apply geojson

3. Formatting spatial data types (converting geometry to geojson)

The spatial data stored in the database is displayed in plain text format through visualization tools as shown in the example above. The structure is not easy for the client to parse, so MySQL provides several spatial functions to parse and format spatial data. geojson is the standard format for displaying GIS spatial data. Both the front-end map framework and the back-end spatial analysis framework support geojson format.

Example:

Preparing sample data

Function application examples

1. Query the information of Luteng meteorological monitoring points and process the geometry into geojson format

Execute sql:

select id,point_name,ST_ASGEOJSON(point_geom) as geojson from meteorological_point where id = 1

Query results:

2. Add a new point information. The point geometry string submitted by the client needs to be processed using the ST_GEOMFROMTEXT function before it can be inserted, otherwise an error will be reported

Client submits point information

{
    "point_name":"Xinshuai Group Monitoring Point",
    "geotext":"POINT(117.420671499 40.194914201)"}
}

Error example:

insert into meteorological_point(point_name, point_geom) values("Xinshuai Group Monitoring Point", "POINT(117.420671499 40.194914201)")

Error 1416 - Cannot get geometry object from data you sent to the GEOMETRY field

Correct insert sql:

insert into meteorological_point(point_name, point_geom) values("Xinshuai Group Monitoring Point", ST_GEOMFROMTEXT("POINT(117.420671499 40.194914201)"))

3. Add new points. The point format submitted by the client is in geojson format. It needs to be processed by ST_GeomFromGeoJSON function before insertion.

Client submits point information

{
    "point_name":"Civil Explosives Company Monitoring Point",
    "geojson":"{"type": "Point", "coordinates": [117.410671499, 40.1549142015]}"}
}

Insert SQL

insert into meteorological_point(point_name, point_geom) values("Minbao Company Monitoring Point", ST_GeomFromGeoJSON("{\"type\": \"Point\", \"coordinates\": [117.410671499, 40.1549142015]}"))

Summary of spatial data formatting

mysql geometry data storage requires function processing of geometry text or geojson before storage, otherwise an error will be reported. When querying, use the formatting function to convert it into geojson to facilitate server-side transmission and client-side framework parsing

2. Spatial Analysis

In the previous section, we introduced the storage of spatial functions, query formatting and related operations, and learned about spatial data structures and geojson. This section introduces the application of spatial data processing functions.

1. Generate a buffer zone based on the point and radius

Buffer is a very common function in map functions. First, it can be used to view the coverage area of ​​a certain range of points, lines and surfaces. Second, in some analysis scenarios, a location coordinate information and buffer radius are known, and a buffer is generated as a query condition for geographic search.

SELECT ST_ASGEOJSON(ST_BUFFER(ST_GeomFromGeoJSON('${geojsonStr}'),${radius}))

SQL Interpretation

The caller passes a geojson string and a radius (meters). ST_GeomFromGeoJSON is used to process geojson string into geometry in the database. ST_BUFFER(geometry , radius) is then used to generate the buffer space data. The function returns the same format as geometry, so an ST_ASGEOJSON function is wrapped around it to process the returned result into geojson for easy reading and rendering by the client.

Example:

• There is a point geojson string "{"type": "Point", "coordinates": [117.410671499, 40.1549142015]}", with a buffer radius of 50 meters (Note: the parameter geographic information and return value of ST_BUFFER() are all in the Mercator coordinate system. If the geojson is not in the Mercator coordinate system, it needs to be converted using a tool class)

public class MercatorUtils {
    /**
     * Convert point geojson to Mercator*
     * @param point
     * @return
     */
    public static JSONObject point2Mercator(JSONObject point) {
        JSONArray xy = point.getJSONArray(COORDINATES);
        JSONArray mercator = lngLat2Mercator(xy.getDouble(0), xy.getDouble(1));
        point.put(COORDINATES, mercator);
        return point;
    }
    /**
     * Convert latitude and longitude to Mercator*/
    public static JSONArray lngLat2Mercator(double lng, double lat) {
        double x = lng * 20037508.342789 / 180;
        double y = Math.log(Math.tan((90 + lat) * M_PI / 360)) / (M_PI / 180);
        y = y * 20037508.34789 / 180;
        JSONArray xy = new JSONArray();
        xy.add(x);
        xy.add(y);
        return xy;
    }
    
    /**
     * Convert Mercator coordinate system data to ordinary coordinate system*/
    public static JSONObject mercatorPolygon2Lnglat(JSONObject polygon) {
        JSONArray coordinates = polygon.getJSONArray(COORDINATES);
        JSONArray xy = coordinates.getJSONArray(0);
        JSONArray ms = new JSONArray();
        for (int i = 0; i < xy.size(); i++) {
            JSONArray p = xy.getJSONArray(i);
            JSONArray m = mercator2lngLat(p.getDouble(0), p.getDouble(1));
            ms.add(m);
        }
        JSONArray newCoordinates = new JSONArray();
        newCoordinates.add(ms);
        polygon.put(COORDINATES, newCoordinates);
        return polygon;
    }
}

The converted geojson can be used as the sql of the above buffer to generate buffer spatial data. The generated buffer data is also in the Mercator coordinate system and needs to be processed using mercatorPolygon2Lnglat before being returned to the client. The calling process is as follows:

• The client submits the point geojson and radius
• Use the Mercator tool class to convert point geojson into geojson in the Mercator coordinate system
• Call sql to generate buffer
• The return value is converted into mercatorPolygon2Lnglat using the Mercator tool class and returned to the caller
  

summary:

The above describes how to use the mysql st_buffer function to generate a buffer. In actual operation, it is feasible after my application in research and development. In actual development, you can also use some toolkits to achieve buffer generation, such as geotools...

3. Determine the city where the point is located

• Determine the city where the user's point is located - the client submits the user's location information and determines the city where the user is located (use ST_INTERSECTS() to determine whether two geometries intersect and return 0 or 1)

SELECT ST_INTERSECTS(ST_GeomFromGeoJSON('${geoJsonStrA}'), ST_GeomFromGeoJSON('${geoJsonStrB}'))

SQL interpretation:

Use the formatting function to process geojson into the geomtry format supported by the function, and use ST_INTERSECTS to make a judgment

4. Commonly used spatial functions

Summarize:

MySQL provides a variety of data types and functions for the storage and analysis of spatial data. Learning such functions can help us better process geographic information. Before using them, you need to understand the coordinate system and geojson related knowledge to avoid pitfalls. If you have any related questions, you can also communicate in the comment area. If there are any misunderstandings, please correct them.

This is the end of this article about MySQL spatial data storage and functions. For more relevant MySQL spatial data storage and functions content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

Table of contents 1. Data Type 1. What is MySQL spatial data 2. What is GeoJSON 3. Formatting spatial data types (converting geometry to geojson) 2. Spatial Analysis 1. Generate a buffer zone based on the point and radius 3. Determine the city where the point is located 4. Commonly used spatial functions Preface: Not long ago, we developed a map-related backend project, which required some point-line-surface storage, query, and analysis operations. Therefore, we conducted a thorough research on MySQL spatial functions and applied them in the project. MySQL provides a dedicated type geometry (supporting all spatial structures) for spatial data storage and processing, as well as subdivided types Point , LineString , Polygon , MultiPoint , MultiLineString , MultiPolygon , etc. We learned about spatial functions, and we can use this type for storage of longitude and latitude and route storage, and use related spatial functions for analysis. All the following database operations are based on MySQL5.7.20 . 1. Data Type 1. What is MySQL spatial data MySQL provides the geometry data type to store coordinate information. The geometry type supports the following three types of data storage: