前言：

不久前開發(fā)了一個地圖相關(guān)的后端項(xiàng)目，需要提供一些點(diǎn)線面相關(guān)的存儲、查詢、分析相關(guān)的操作，于是對MySQL空間函數(shù)進(jìn)行充分調(diào)研并應(yīng)用在項(xiàng)目中;MySQL為空間數(shù)據(jù)存儲及處理提供了專用的類型geometry(支持所有的空間結(jié)構(gòu))，還有有細(xì)分類型Point, LineString, Polygon,MultiPoint,MultiLineString,MultiPolygon等等，我們了解了空間函數(shù)，在涉及到經(jīng)緯度存儲，路線存儲方面的業(yè)務(wù)就能夠使用此類型進(jìn)行存儲，使用相關(guān)空間函數(shù)進(jìn)行分析業(yè)務(wù)實(shí)現(xiàn)，以下所有數(shù)據(jù)庫操作基于MySQL5.7.20。

一、數(shù)據(jù)類型

1.什么是MySQL空間數(shù)據(jù)

• MySQL提供了數(shù)據(jù)類型geometry用來存儲坐標(biāo)信息，geometry類型支持以下三種數(shù)據(jù)存儲

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

2.什么是geojson

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

除了簡單的點(diǎn)、線、面，為了滿足復(fù)雜的地理環(huán)境及地圖業(yè)務(wù)，還會有多點(diǎn)(MultiPoint),多線(MultiLineString),多面(MultiPolygon),幾何集合(GeometryCollection)等，熟悉json就可以快速的熟悉并應(yīng)用geojson

3.格式化空間數(shù)據(jù)類型（geometry相互轉(zhuǎn)換geojson）

數(shù)據(jù)庫存儲的空間數(shù)據(jù)通過可視化工具展示的明文結(jié)構(gòu)為上面示例中所見，結(jié)構(gòu)并不易于客戶端解析，所以MySQL提供了幾個空間函數(shù)用來解析及格式化空間數(shù)據(jù)，geojson是gis空間數(shù)據(jù)展示的標(biāo)準(zhǔn)格式，前端地圖框架及后端空間分析相關(guān)框架都會支持geojson格式

示例：

準(zhǔn)備示例數(shù)據(jù)

函數(shù)應(yīng)用示例

1.查詢綠藤氣象監(jiān)測點(diǎn)信息將geometry處理成geojson格式

執(zhí)行sql:

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

查詢結(jié)果:

2.新增一個點(diǎn)位信息，客戶端提交的點(diǎn)位geometry字符串需要使用ST_GEOMFROMTEXT函數(shù)處理才能插入，否則會報(bào)錯

客戶端提交點(diǎn)位信息

{
    "point_name":"新帥集團(tuán)監(jiān)測點(diǎn)",
    "geotext":"POINT(117.420671499 40.194914201)"}
}

錯誤示例：

insert into meteorological_point(point_name, point_geom) values("新帥集團(tuán)監(jiān)測點(diǎn)", "POINT(117.420671499 40.194914201)")

報(bào)錯 1416 - Cannot get geometry object from data you send to the GEOMETRY field

正確插入sql:

insert into meteorological_point(point_name, point_geom) values("新帥集團(tuán)監(jiān)測點(diǎn)", ST_GEOMFROMTEXT("POINT(117.420671499 40.194914201)"))

3.新增點(diǎn)位，客戶端提交點(diǎn)位格式為geojson格式，需要使用ST_GeomFromGeoJSON函數(shù)處理后進(jìn)行插入

客戶端提交點(diǎn)位信息

{
    "point_name":"民爆公司監(jiān)測點(diǎn)",
    "geojson":"{"type": "Point", "coordinates": [117.410671499, 40.1549142015]}"}
}

插入SQL

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

空間數(shù)據(jù)格式化小結(jié)

mysql geometry數(shù)據(jù)存儲需要對geometry文本或geojson進(jìn)行函數(shù)處理后才能進(jìn)行存儲，否則會報(bào)錯，查詢時候使用格式化函數(shù)轉(zhuǎn)成geojson方便服務(wù)端傳輸和客戶端框架解析

二、空間分析

在上一部分介紹了空間函數(shù)存儲，查詢格式化處理相關(guān)的操作，了解空間數(shù)據(jù)結(jié)構(gòu)及geojson，這一部分介紹空間數(shù)據(jù)處理函數(shù)的應(yīng)用

1、根據(jù)點(diǎn)位及半徑，生成緩沖區(qū)

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

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

SQL解讀

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

示例：

• 有一個點(diǎn)位的geojson字符串為 "{"type": "Point", "coordinates": [117.410671499, 40.1549142015]}",緩沖半徑50米（注意：ST_BUFFER()的參數(shù)地理信息及返回值均使用墨卡托坐標(biāo)系，如非墨卡托坐標(biāo)系的geojson,需使用工具類進(jìn)行轉(zhuǎn)換處理）

public class MercatorUtils {
    /**
     * 點(diǎn)位geojson轉(zhuǎn)墨卡托
     *
     * @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;
    }
    /**
     * 經(jīng)緯度轉(zhuǎn)墨卡托
     */
    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;
    }
    
    /**
     * 墨卡托坐標(biāo)系數(shù)據(jù)轉(zhuǎn)普通坐標(biāo)系
     */
    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;
    }
}

轉(zhuǎn)換后的geojson就可以作為上面緩沖區(qū)的sql生成緩沖區(qū)空間數(shù)據(jù)了，生成的緩沖區(qū)數(shù)據(jù)也是墨卡托坐標(biāo)系，需使用mercatorPolygon2Lnglat進(jìn)行處理后返回給客戶端，調(diào)用流程如下:

• 客戶端提交點(diǎn)位geojson及半徑
• 使用墨卡托工具類將點(diǎn)位geojson轉(zhuǎn)換成墨卡托坐標(biāo)系的geojson
• 調(diào)用sql進(jìn)行緩沖區(qū)生成
• 返回值使用墨卡托工具類轉(zhuǎn)換成mercatorPolygon2Lnglat返回給調(diào)用方
  

小結(jié)：

上面介紹如何使用mysql st_buffer函數(shù)生成緩沖區(qū)，實(shí)際操作起來經(jīng)過我在研發(fā)中的應(yīng)用是可行的，實(shí)際開發(fā)中還可以使用一些工具包來實(shí)現(xiàn)緩沖區(qū)生成，如geotools...

三、判斷點(diǎn)位所在城市

• 判斷用戶點(diǎn)位所在城市-客戶端提交用戶的定位信息，判斷用戶所在城市(使用ST_INTERSECTS()判斷兩個幾何是否相交即可，返回0或1)

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

SQL解讀：

使用格式化函數(shù)將geojson處理成函數(shù)支持的geomtry格式，使用ST_INTERSECTS進(jìn)行判斷即可

四、常用的空間函數(shù)

總結(jié)：

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

到此這篇關(guān)于MySQL空間數(shù)據(jù)存儲及函數(shù)的文章就介紹到這了,更多相關(guān)MySQL空間數(shù)據(jù)存儲及函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論