前言：

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

一、數(shù)據(jù)類(lèi)型

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

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

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

2.什么是geojson

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

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

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

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

示例：

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

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

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

執(zhí)行sql:

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

查詢(xún)結(jié)果:

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

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

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

錯(cuò)誤示例：

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

報(bào)錯(cuò) 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)測(cè)點(diǎn)", ST_GEOMFROMTEXT("POINT(117.420671499 40.194914201)"))

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

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

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

插入SQL

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

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

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

二、空間分析

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

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

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

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

SQL解讀

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

示例：

• 有一個(gè)點(diǎn)位的geojson字符串為 "{"type": "Point", "coordinates": [117.410671499, 40.1549142015]}",緩沖半徑50米（注意：ST_BUFFER()的參數(shù)地理信息及返回值均使用墨卡托坐標(biāo)系，如非墨卡托坐標(biāo)系的geojson,需使用工具類(lèi)進(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)行處理后返回給客戶(hù)端，調(diào)用流程如下:

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

小結(jié)：

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

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

• 判斷用戶(hù)點(diǎn)位所在城市-客戶(hù)端提交用戶(hù)的定位信息，判斷用戶(hù)所在城市(使用ST_INTERSECTS()判斷兩個(gè)幾何是否相交即可，返回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ù)的存儲(chǔ)及分析提供了豐富的數(shù)據(jù)類(lèi)型及函數(shù)，我們學(xué)習(xí)此類(lèi)函數(shù)能夠幫助我們更好的處理地理信息，使用前需要對(duì)坐標(biāo)系、geojson相關(guān)知識(shí)進(jìn)行了解，避免踩坑，如果有相關(guān)問(wèn)題也可以在評(píng)論區(qū)交流，如有誤區(qū)請(qǐng)指正。

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

最新評(píng)論