萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> JSP 程式設計

JSP 連接 MySQL 操作 GIS 空間數據:Point幾何類型存儲與查詢實戰

背景:MySQL 空間擴展

MySQL 5.7+ 完整支持 OpenGIS 標準空間數據類型:`POINT`、`LINESTRING`、`POLYGON` 等,配合空間索引 (R-Tree) 實現高效地理查詢 (距離、包含、相交)。

表結構設計

CREATE DATABASE map_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE map_db;

CREATE TABLE biandianzhan_point_tb (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    voltage_level VARCHAR(20),
    -- POINT 類型:存儲經緯度 (X=經度, Y=緯度)
    location POINT NOT NULL SRID 4326, -- SRID 4326 = WGS84 (GPS 坐標系)
    SPATIAL INDEX idx_location (location) -- 空間索引,加速範圍/距離查詢
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

JSP 表單頁 index_map.jsp

<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html><head><meta charset="UTF-8"><title>變電站點位錄入</title></head>
<body>
    <h2>新增變電站</h2>
    <form action="insert_map.jsp" method="post">
        <div>ID: <input name="id" value="0" required/></div>
        <div>名稱: <input name="name" value="示例站" required/></div>
        <div>電壓等級: <input name="voltage_level" value="110kV"/></div>
        <div>經度: <input name="lon" type="number" step="0.000001" value="121.4737" required/></div>
        <div>緯度: <input name="lat" type="number" step="0.000001" value="31.2304" required/></div>
        <button type="submit">保存</button>
    </form>
</body></html>

處理頁 insert_map.jsp (核心:GeometryFromText 構造 POINT)

<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*, java.util.*" %>
<%
    request.setCharacterEncoding("UTF-8");
    
    String id = request.getParameter("id");
    String name = request.getParameter("name");
    String voltage = request.getParameter("voltage_level");
    String lonStr = request.getParameter("lon");
    String latStr = request.getParameter("lat");
    
    // 校驗經緯度格式
    double lon, lat;
    try { lon = Double.parseDouble(lonStr); lat = Double.parseDouble(latStr); }
    catch (NumberFormatException e) { out.println("經緯度格式錯誤"); return; }
    
    // 範圍校驗 (WGS84)
    if (lon < -180 || lon > 180 || lat < -90 || lat > 90) {
        out.println("經緯度超出有效範圍"); return;
    }
    
    String url = "jdbc:mysql://localhost:3306/map_db?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai";
    
    // 關鍵 SQL:GeometryFromText('POINT(lon lat)') 構造幾何對象
    // 注意:MySQL POINT 參數順序為 X(經度) Y(緯度)
    String insertSql = "INSERT INTO biandianzhan_point_tb (id, name, voltage_level, location) " +
                       "VALUES (?, ?, ?, ST_PointFromText(?))"; // MySQL 8.0+ 推薦 ST_PointFromText
    // 兼容 5.7: "VALUES (?, ?, ?, GeomFromText(CONCAT('POINT(', ?, ' ', ?, ')')))"
    
    String querySql = "SELECT id, name, voltage_level, ST_AsText(location) AS location_wkt FROM biandianzhan_point_tb";
    
    try (Connection conn = DriverManager.getConnection(url, "root", "123456");
         PreparedStatement psIns = conn.prepareStatement(insertSql);
         PreparedStatement psQry = conn.prepareStatement(querySql)) {
        
        psIns.setString(1, id);
        psIns.setString(2, name);
        psIns.setString(3, voltage);
        // 構造 WKT 字符串: "POINT(121.4737 31.2304)"
        String wkt = String.format("POINT(%f %f)", lon, lat);
        psIns.setString(4, wkt); // 設置 WKT 文本
        psIns.executeUpdate();
        
        // 查詢回顯
%>
    <h2>所有站點</h2>
    <table border="1" cellpadding="5">
        <tr><th>ID</th><th>名稱</th><th>電壓</th><th>WKT 坐標</th></tr>
<%
        try (ResultSet rs = psQry.executeQuery()) {
            while (rs.next()) {
%>
        <tr>
            <td><%= rs.getString("id") %></td>
            <td><%= rs.getString("name") %></td>
            <td><%= rs.getString("voltage_level") %></td>
            <td><%= rs.getString("location_wkt") %></td>
        </tr>
<%
            }
        }
%>
    </table>
<%
    } catch (SQLException e) {
        e.printStackTrace();
        out.println("錯誤: " + e.getMessage());
    }
%>
    <p><a href="index_map.jsp">繼續新增</a></p>
</body></html>

空間查詢進階 (SQL 示例)

1. 距離查詢:查找某點 10km 內的站點

-- MySQL 5.7+ ST_Distance_Sphere (返回米,球面計算)
SELECT *, ST_Distance_Sphere(location, ST_PointFromText('POINT(121.5 31.2)')) AS dist_m
FROM biandianzhan_point_tb
WHERE ST_Distance_Sphere(location, ST_PointFromText('POINT(121.5 31.2)')) < 10000
ORDER BY dist_m;

2. 矩形範圍查詢 (MBR)

-- 包含在矩形內 (西南角, 東北角)
SELECT * FROM biandianzhan_point_tb
WHERE MBRContains(ST_GeomFromText('POLYGON((121 31, 122 31, 122 32, 121 32, 121 31))'), location);

3. 經緯度解析 (應用層處理)

-- MySQL 8.0+ 提取坐標
SELECT id, name, 
       ST_X(location) AS longitude, 
       ST_Y(location) AS latitude
FROM biandianzhan_point_tb;

-- 5.7 兼容
SELECT id, name,
       X(location) AS longitude,
       Y(location) AS latitude
FROM biandianzhan_point_tb;

Java 端解析 POINT (JDBC 獲取)

// 使用 MySQL Connector/J 8.0+ 內置幾何類型支持
import com.mysql.cj.jdbc.exceptions.MysqlDataTruncation;
import com.mysql.cj.protocol.a.result.GeometryResultSet; // 內部類,不建議直接用

// 通用做法:查詢 WKT 文本 (ST_AsText) 或 坐標 (ST_X/ST_Y)
String sql = "SELECT id, name, ST_X(location) AS lon, ST_Y(location) AS lat FROM biandianzhan_point_tb";
try (PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    while (rs.next()) {
        String id = rs.getString("id");
        String name = rs.getString("name");
        double lon = rs.getDouble("lon");
        double lat = rs.getDouble("lat");
        // 構建領域對象...
    }
}

前端地圖展示整合 (Leaflet 示例)

<div id="map" style="height: 400px;"></div>
<script src="https://unpkg.com/leaflet@1.9.4/dist/leaflet.js"></script>
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.9.4/dist/leaflet.css"/>
<script>
    var map = L.map('map').setView([31.23, 121.47], 12);
    L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png').addTo(map);
    
    // 從後端 API 獲取 GeoJSON
    fetch('/api/stations').then(r => r.json()).then(geojson => {
        L.geoJSON(geojson, {
            pointToLayer: (feat, latlng) => L.circleMarker(latlng, {radius: 6, color: '#e74c3c'}),
            onEachFeature: (feat, layer) => layer.bindPopup(feat.properties.name)
        }).addTo(map);
    });
</script>

避坑指南

  • 經緯度順序:MySQL `POINT(x y)` = `POINT(經度 緯度)`,與 Leaflet/GeoJSON `[lat, lng]` 順序**相反**
  • SRID 一致性:建表指定 `SRID 4326`,插入數據、查詢函數均需匹配,否則距離計算錯誤
  • 空間索引生效條件:查詢必須使用 `MBRContains`、`ST_Distance_Sphere` 等空間函數,普通 WHERE 不走空間索引
  • 精度選擇:`DOUBLE` (默認) 約 1mm 精度,足夠大多數應用;極高精度可用 `DECIMAL` 存儲原始坐標
  • 驅動版本:MySQL 8.0 必須用 `mysql-connector-j 8.x`,支持 `ST_*` 新函數
copyright © 萬盛學電腦網 all rights reserved