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;
<%@ 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>
<%@ 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>
-- 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;
-- 包含在矩形內 (西南角, 東北角)
SELECT * FROM biandianzhan_point_tb
WHERE MBRContains(ST_GeomFromText('POLYGON((121 31, 122 31, 122 32, 121 32, 121 31))'), location);
-- 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;
// 使用 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");
// 構建領域對象...
}
}
<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>