MySQL空間數據存儲及函數

Posted on by

前言：

不久前開發瞭一個地圖相關的後端項目，需要提供一些點線面相關的存儲、查詢、分析相關的操作，於是對MySQL空間函數進行充分調研並應用在項目中;MySQL為空間數據存儲及處理提供瞭專用的類型geometry(支持所有的空間結構)，還有有細分類型Point, LineString, Polygon,MultiPoint,MultiLineString,MultiPolygon等等，我們瞭解瞭空間函數，在涉及到經緯度存儲，路線存儲方面的業務就能夠使用此類型進行存儲，使用相關空間函數進行分析業務實現，以下所有數據庫操作基於MySQL5.7.20。

一、數據類型

1.什麼是MySQL空間數據

  • MySQL提供瞭數據類型geometry用來存儲坐標信息，geometry類型支持以下三種數據存儲

還有多點MULTIPOINT（多點）、MULTILINESTRING（多線）、MULTIPOLYGON（多面）、GEOMETRYCOLLECTION（集合，可放入點線面）等類型

2.什麼是geojson

GeoJSON是一種對各種地理數據結構進行編碼的格式。GeoJSON對象可以表示幾何、特征或者特征集合。GeoJSON支持下面幾何類型：點、線、面、多點、多線、多面和幾何集合。GeoJSON裡的特征包含一個幾何對象和其他屬性，特征集合表示一系列特征。一個完整的GeoJSON數據結構總是一個（JSON術語裡的）對象。在GeoJSON裡，對象由名/值對–也稱作成員的集合組成。對每個成員來說，名字總是字符串。成員的值要麼是字符串、數字、對象、數組，要麼是下面文本常量中的一個：”true“,”false“和”null“。數組是由值是上面所說的元素組成

除瞭簡單的點、線、面，為瞭滿足復雜的地理環境及地圖業務，還會有多點(MultiPoint),多線(MultiLineString),多面(MultiPolygon),幾何集合(GeometryCollection)等，熟悉json就可以快速的熟悉並應用geojson

3.格式化空間數據類型（geometry相互轉換geojson）

數據庫存儲的空間數據通過可視化工具展示的明文結構為上面示例中所見，結構並不易於客戶端解析，所以MySQL提供瞭幾個空間函數用來解析及格式化空間數據，geojson是gis空間數據展示的標準格式，前端地圖框架及後端空間分析相關框架都會支持geojson格式

示例：

準備示例數據

函數應用示例

1.查詢綠藤氣象監測點信息將geometry處理成geojson格式

執行sql:

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

查詢結果:

2.新增一個點位信息，客戶端提交的點位geometry字符串需要使用ST_GEOMFROMTEXT函數處理才能插入，否則會報錯

客戶端提交點位信息

{
    "point_name":"新帥集團監測點",
    "geotext":"POINT(117.420671499 40.194914201)"}
}

錯誤示例：

insert into meteorological_point(point_name, point_geom) values("新帥集團監測點", "POINT(117.420671499 40.194914201)")


報錯 1416 - Cannot get geometry object from data you send to the GEOMETRY field

正確插入sql:

insert into meteorological_point(point_name, point_geom) values("新帥集團監測點", ST_GEOMFROMTEXT("POINT(117.420671499 40.194914201)"))

3.新增點位，客戶端提交點位格式為geojson格式，需要使用ST_GeomFromGeoJSON函數處理後進行插入

客戶端提交點位信息

{
    "point_name":"民爆公司監測點",
    "geojson":"{"type": "Point", "coordinates": [117.410671499, 40.1549142015]}"}
}

插入SQL

insert into meteorological_point(point_name, point_geom) values("民爆公司監測點", ST_GeomFromGeoJSON("{\"type\": \"Point\", \"coordinates\": [117.410671499, 40.1549142015]}"))

空間數據格式化小結

mysql geometry數據存儲需要對geometry文本或geojson進行函數處理後才能進行存儲，否則會報錯，查詢時候使用格式化函數轉成geojson方便服務端傳輸和客戶端框架解析

二、空間分析

在上一部分介紹瞭空間函數存儲，查詢格式化處理相關的操作，瞭解空間數據結構及geojson，這一部分介紹空間數據處理函數的應用

1、根據點位及半徑，生成緩沖區

在地圖功能中，緩沖區是非常常見的功能，一來可以查看點線面一定范圍類的覆蓋區域，二來在一些分析場景中，已知一個位子坐標信息及緩沖半徑，生成緩沖區作為查詢條件進行地理搜索

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

SQL解讀

調用方傳來一個geojson字符串及半徑（米），使用ST_GeomFromGeoJSON將geojson字符串處理成數據庫中的geometry，再使用ST_BUFFER(geometry, 半徑)s生成緩沖區空間數據，函數返回的格式也是geometry,所以在外面包一層ST_ASGEOJSON函數將返回結果處理成geojson,便於客戶端讀取及渲染

示例：

  • 有一個點位的geojson字符串為 “{“type”: “Point”, “coordinates”: [117.410671499, 40.1549142015]}”,緩沖半徑50米（註意：ST_BUFFER()的參數地理信息及返回值均使用墨卡托坐標系，如非墨卡托坐標系的geojson,需使用工具類進行轉換處理）
public class MercatorUtils {
    /**
     * 點位geojson轉墨卡托
     *
     * @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;
    }
    /**
     * 經緯度轉墨卡托
     */
    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;
    }
    
    /**
     * 墨卡托坐標系數據轉普通坐標系
     */
    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;
    }
}

轉換後的geojson就可以作為上面緩沖區的sql生成緩沖區空間數據瞭，生成的緩沖區數據也是墨卡托坐標系，需使用mercatorPolygon2Lnglat進行處理後返回給客戶端，調用流程如下:

  • 客戶端提交點位geojson及半徑
  • 使用墨卡托工具類將點位geojson轉換成墨卡托坐標系的geojson
  • 調用sql進行緩沖區生成
  • 返回值使用墨卡托工具類轉換成mercatorPolygon2Lnglat返回給調用方

小結：

上面介紹如何使用mysql st_buffer函數生成緩沖區，實際操作起來經過我在研發中的應用是可行的，實際開發中還可以使用一些工具包來實現緩沖區生成，如geotools…

三、判斷點位所在城市

  • 判斷用戶點位所在城市-客戶端提交用戶的定位信息，判斷用戶所在城市(使用ST_INTERSECTS()判斷兩個幾何是否相交即可，返回0或1)
SELECT ST_INTERSECTS(ST_GeomFromGeoJSON('${geoJsonStrA}'), ST_GeomFromGeoJSON('${geoJsonStrB}'))

SQL解讀：

使用格式化函數將geojson處理成函數支持的geomtry格式，使用ST_INTERSECTS進行判斷即可

四、常用的空間函數

總結：

MySQL為空間數據的存儲及分析提供瞭豐富的數據類型及函數，我們學習此類函數能夠幫助我們更好的處理地理信息，使用前需要對坐標系、geojson相關知識進行瞭解，避免踩坑，如果有相關問題也可以在評論區交流，如有誤區請指正。

到此這篇關於MySQL空間數據存儲及函數的文章就介紹到這瞭,更多相關MySQL空間數據存儲及函數內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：