From 0272ac944a277bc7ed683bab2d1fb13fcfb58869 Mon Sep 17 00:00:00 2001
From: xingjinshuang <xingjs@qq.com>
Date: 星期四, 26 十二月 2024 16:29:44 +0800
Subject: [PATCH] @xingjs@20241226@添加查询管线工具类,修改解决查询站点接口token存在无法获取的情况，解决后可正常获取token，并查询出需要的站点；解析shp文件获取站点范围;新增把降水CSV文件内容解析保存到数据库中
---
src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java | 88 ++++++++++++++++++++++
src/main/java/com/se/simu/utils/ShpToolUtils.java | 66 ++++++++++++++++
src/main/java/com/se/simu/domain/vo/CreateFilesSimuVo.java | 3
src/main/java/com/se/simu/controller/SimuController.java | 62 +++++++++++++--
4 files changed, 209 insertions(+), 10 deletions(-)
diff --git a/src/main/java/com/se/simu/controller/SimuController.java b/src/main/java/com/se/simu/controller/SimuController.java
index 63327f0..3e9aee1 100644
--- a/src/main/java/com/se/simu/controller/SimuController.java
+++ b/src/main/java/com/se/simu/controller/SimuController.java
@@ -1,5 +1,6 @@
package com.se.simu.controller;
+import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.se.simu.config.PropertiesConfig;
@@ -12,9 +13,11 @@
import com.se.simu.domain.vo.R;
import com.se.simu.domain.vo.SimuVo;
import com.se.simu.service.*;
+import com.se.simu.utils.CsvToSQLiteUtils;
import com.se.simu.utils.ShpToolUtils;
import io.swagger.annotations.*;
import lombok.extern.slf4j.Slf4j;
+import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
@@ -207,16 +210,28 @@
String floodFile = vo.getFloodFile();
log.info("鑼冨洿鏂囦欢鍦板潃锛歿}", floodFile);
try {
+ // 鍒ゆ柇鍦板潃涓嶄负绌�
+ if (StringUtils.isEmpty(floodFile)) {
+ return fail("鑼冨洿鏂囦欢鍦板潃涓嶈兘涓虹┖", false);
+ } else {
+ // 鑾峰彇鏂囦欢鐨勫悗缂�鍚�
+ String fileName = floodFile.substring(floodFile.lastIndexOf("."));
+ // 鍒ゆ柇鍚庣紑鍚嶆槸鍚︿负.shp
+ if (!fileName.equalsIgnoreCase(".shp")) {
+ return fail("鑼冨洿鏂囦欢鏍煎紡涓嶆纭�", false);
+ } else {
+ // 1 璇诲彇shp鏂囦欢锛岃幏鍙栬寖鍥村��
+ JSONObject jsonObject = ShpToolUtils.readShp(floodFile);
+ // 2 鑾峰彇jsonObject涓殑鑼冨洿鍊�
+ vo.setMinx(jsonObject.getDouble("minX"));
+ vo.setMaxx(jsonObject.getDouble("maxX"));
+ vo.setMiny(jsonObject.getDouble("minY"));
+ vo.setMaxy(jsonObject.getDouble("maxY"));
+ }
+ }
// todo: 瑙ｆ瀽鑼冨洿鏂囦欢 鏍规嵁涓嶅悓鐨勭殑鏍煎紡鏂囦欢杩涜涓嶅悓鐨勮В鏋愶紝鑾峰彇闇�瑕佽绠楃殑鑼冨洿鍊�
// 涓婁紶鏍煎紡锛�.shp/.tiff/.img/.geojson
// 閲嶆柊缁檝o璧嬪��
- // 1 璇诲彇shp鏂囦欢锛岃幏鍙栬寖鍥村��
- JSONObject jsonObject = ShpToolUtils.readShp(floodFile);
- // 2 鑾峰彇jsonObject涓殑鑼冨洿鍊�
- vo.setMinx(jsonObject.getDouble("minX"));
- vo.setMaxx(jsonObject.getDouble("maxX"));
- vo.setMiny(jsonObject.getDouble("minY"));
- vo.setMaxy(jsonObject.getDouble("maxY"));
} catch (Exception e) {
log.error("瑙ｆ瀽鑼冨洿鏂囦欢澶辫触");
if (null == vo.getMinx() || null == vo.getMiny() || null == vo.getMaxx() || null == vo.getMaxy()) {
@@ -225,13 +240,40 @@
}
String stationFile = vo.getStationFile();
- log.info("绔欑偣鏂囦欢shp鍦板潃锛歿}", floodFile);
-
+ log.info("绔欑偣鏂囦欢shp鍦板潃锛歿}", stationFile);
+ try {
+ // 鍒ゆ柇鍦板潃涓嶄负绌�
+ if (StringUtils.isEmpty(stationFile)) {
+ return fail("绔欑偣鏂囦欢shp鍦板潃涓嶈兘涓虹┖", false);
+ }
+ // 1 璇诲彇shp鏂囦欢锛岃幏鍙栫珯鐐瑰潗鏍囧��
+ JSONArray jsonArray = ShpToolUtils.readShpGetLocal(stationFile);
+ System.out.println("jsonArray = " + jsonArray);
+ } catch (Exception e) {
+ log.error("瑙ｆ瀽绔欑偣鏂囦欢shp澶辫触");
+ }
String stationRainFile = vo.getStationRainFile();
log.info("绔欑偣闆ㄩ噺CSV鏂囦欢鍦板潃锛歿}", floodFile);
- // TODO: 2024/12/24 鏍规嵁杩欎簺鏂囦欢鐨勫湴鍧�锛岃幏鍙栨枃浠跺唴瀹癸紝鍒涘缓浠跨湡瑙嗗浘
+ try {
+ // 鍒ゆ柇鍦板潃涓嶄负绌�
+ if (StringUtils.isEmpty(stationRainFile)) {
+ return fail("绔欑偣闆ㄩ噺CSV鏂囦欢鍦板潃涓嶈兘涓虹┖", false);
+ }
+ // 鍒涘缓琛ㄥ悕 鏃堕棿鎴�
+ String tableName = "station_rain_" + System.currentTimeMillis();
+ // 1 璇诲彇CSV 鏂囦欢
+ CsvToSQLiteUtils.readCsvSaveLocal(stationRainFile, tableName);
+ // 鑾峰彇浠跨湡鏃堕棿 duration
+
+ // 鑾峰彇闄嶉洦鎬婚噺 total
+
+
+ } catch (Exception e) {
+ log.error("瑙ｆ瀽绔欑偣闆ㄩ噺CSV鏂囦欢澶辫触");
+ }
+ // TODO: 2024/12/24 鏍规嵁杩欎簺鏂囦欢鐨勫湴鍧�锛岃幏鍙栨枃浠跺唴瀹癸紝鍒涘缓浠跨湡瑙嗗浘
try {
if (null == vo.getTotal() || vo.getTotal() < 1 || vo.getTotal() > 1000) {
return fail("闄嶉洦閲忎笉鑳戒负绌猴紝涓斿彇鍊煎湪1~1000涔嬮棿");
diff --git a/src/main/java/com/se/simu/domain/vo/CreateFilesSimuVo.java b/src/main/java/com/se/simu/domain/vo/CreateFilesSimuVo.java
index 51af3c2..40111c6 100644
--- a/src/main/java/com/se/simu/domain/vo/CreateFilesSimuVo.java
+++ b/src/main/java/com/se/simu/domain/vo/CreateFilesSimuVo.java
@@ -82,4 +82,7 @@
@ApiModelProperty("绔欑偣闆ㄩ噺CSV鏂囦欢鍦板潃")
private String stationRainFile;
+ @ApiModelProperty("璁＄畻妯″瀷绫诲瀷")
+ private String modelType;
+
}
diff --git a/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
index a4c9c6e..1d35646 100644
--- a/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
+++ b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
@@ -10,6 +10,12 @@
import java.nio.file.Paths;
import java.sql.*;
+/**
+ * CSV 鍒� SQLite 瀹炵敤绋嬪簭
+ *
+ * @author xingjinshuang@smartearth.cn
+ * @date 2024/12/26
+ */
public class CsvToSQLiteUtils {
private static final String DATABASE_URL = "jdbc:sqlite:D:\\0a_project\\simulation\\simuserver\\rainfall.db"; // SQLite鏁版嵁搴撹矾寰�
@@ -142,4 +148,86 @@
System.err.println("璇诲彇CSV鎴栨彃鍏ユ暟鎹椂鍑洪敊: " + e.getMessage());
}
}
+
+
+ /**
+ * 璇诲彇 CSV 淇濆瓨鏈湴
+ *
+ * @param stationRainFile Station Rain 鏂囦欢
+ * @param tableName 琛ㄥ悕绉�
+ */
+ public static void readCsvSaveLocal(String stationRainFile, String tableName) {
+ // 1. 鍒涘缓SQLite鏁版嵁搴撹繛鎺�
+ try (Connection conn = DriverManager.getConnection("jdbc:sqlite:rainfall.db")) {
+ if (conn != null) {
+ // 2. 鍒涘缓琛紙濡傛灉涓嶅瓨鍦級
+ createTableIfNotExists(conn, tableName);
+ // 3. 璁剧疆SQLite杩炴帴鐨勫瓧绗︾紪鐮佷负UTF-8
+ try (Statement stmt = conn.createStatement()) {
+ // 璁剧疆SQLite缂栫爜涓篣TF-8
+ stmt.execute("PRAGMA encoding = 'UTF-8';");
+ }
+ // 4. 寮�濮嬩簨鍔�
+ conn.setAutoCommit(false);
+ // 5. 璇诲彇CSV鏂囦欢骞舵彃鍏ユ暟鎹�
+ readCsvAndInsertDatas(stationRainFile, conn, tableName);
+ // 6. 鎻愪氦浜嬪姟
+ conn.commit();
+ System.out.println("鏁版嵁鎴愬姛鎻掑叆鍒癝QLite鏁版嵁搴擄紒");
+ }
+ } catch (SQLException e) {
+ System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+ }
+ }
+
+
+ private static void readCsvAndInsertDatas(String csvFilePath, Connection conn, String tableName) {
+ // 浣跨敤 Apache Commons CSV 璇诲彇CSV鏂囦欢
+ try (Reader reader = new InputStreamReader(Files.newInputStream(Paths.get(csvFilePath)), "GBK")) {
+ Iterable<CSVRecord> records = CSVFormat.DEFAULT
+ .withHeader("闆ㄩ噺绔�", "闄嶉洦閲�", "缁忓害", "绾害", "datatime")
+ .withSkipHeaderRecord() // 璺宠繃琛ㄥご
+ .parse(reader);
+
+ String insertDataSql = "INSERT INTO " + tableName + " (station_name, rainfall, longitude, latitude, datetime) VALUES (?, ?, ?, ?, ?);";
+
+ try (PreparedStatement pstmt = conn.prepareStatement(insertDataSql)) {
+ // 鎵归噺澶у皬
+ int batchSize = 1000;
+ int count = 0;
+
+ for (CSVRecord record : records) {
+ // 鑾峰彇姣忎竴鍒楃殑鏁版嵁
+ String stationName = record.get("闆ㄩ噺绔�");
+ double rainfall = Double.parseDouble(record.get("闄嶉洦閲�"));
+ double longitude = Double.parseDouble(record.get("缁忓害"));
+ double latitude = Double.parseDouble(record.get("绾害"));
+ String datetime = record.get("datatime");
+
+ // 璁剧疆鎻掑叆鏁版嵁鐨勫弬鏁�
+ pstmt.setString(1, stationName);
+ pstmt.setDouble(2, rainfall);
+ pstmt.setDouble(3, longitude);
+ pstmt.setDouble(4, latitude);
+ pstmt.setString(5, datetime);
+
+ // 娣诲姞鍒版壒澶勭悊
+ pstmt.addBatch();
+ count++;
+
+ // 姣廱atchSize鏉℃暟鎹墽琛屼竴娆℃壒閲忔彃鍏�
+ if (count % batchSize == 0) {
+ pstmt.executeBatch();
+ }
+ }
+ // 鎵ц鍓╀綑鐨勬壒閲忔彃鍏�
+ pstmt.executeBatch();
+ } catch (SQLException e) {
+ System.err.println("鎵归噺鎻掑叆鏁版嵁鏃跺嚭閿�: " + e.getMessage());
+ }
+ } catch (IOException e) {
+ System.err.println("璇诲彇CSV鎴栨彃鍏ユ暟鎹椂鍑洪敊: " + e.getMessage());
+ }
+ }
+
}
diff --git a/src/main/java/com/se/simu/utils/ShpToolUtils.java b/src/main/java/com/se/simu/utils/ShpToolUtils.java
index ab44469..3cb5b5f 100644
--- a/src/main/java/com/se/simu/utils/ShpToolUtils.java
+++ b/src/main/java/com/se/simu/utils/ShpToolUtils.java
@@ -96,6 +96,72 @@
return json;
}
+
+ /**
+ * 璇诲彇 shp get local
+ *
+ * @param strVectorFile str 鍚戦噺鏂囦欢
+ * @return {@link JSONObject}
+ */
+ public static JSONArray readShpGetLocal(String strVectorFile) {
+ // 娉ㄥ唽鎵�鏈夌殑椹卞姩
+ ogr.RegisterAll();
+ // 涓轰簡鏀寔涓枃璺緞锛岃娣诲姞涓嬮潰杩欏彞浠ｇ爜
+ gdal.SetConfigOption("GDAL_FILENAME_IS_UTF8", "YES");
+
+ // 璇诲彇鏁版嵁锛岃繖閲屼互ESRI鐨剆hp鏂囦欢涓轰緥
+ String strDriverName = "ESRI Shapefile";
+ org.gdal.ogr.Driver oDriver = ogr.GetDriverByName(strDriverName);
+
+ if (oDriver == null) {
+ System.out.println(strDriverName + " 椹卞姩涓嶅彲鐢紒\n");
+ return null;
+ }
+
+ // 鎵撳紑鏁版嵁婧�
+ DataSource dataSource = oDriver.Open(strVectorFile);
+ if (dataSource == null) {
+ System.out.println("鏃犳硶鎵撳紑 Shapefile 鏂囦欢锛�");
+ return null;
+ }
+
+ Layer layer = dataSource.GetLayer(0);
+
+ // 鑾峰彇鍥惧眰鐨勮寖鍥达紙鍒濇鐨勭煩褰㈣寖鍥达級
+ double[] layerExtent = layer.GetExtent();
+ System.out.println("鍒濆鍥惧眰鑼冨洿锛歮inx:" + layerExtent[0] + ", maxx:" + layerExtent[1] + ", miny:" + layerExtent[2] + ", maxy:" + layerExtent[3]);
+
+ // 鑾峰彇鍥惧眰鐨勭┖闂村弬鑰�
+ SpatialReference layerSpatialRef = layer.GetSpatialRef();
+
+ // 鍒涘缓鐩爣绌洪棿鍙傝�� (EPSG:4326)
+ SpatialReference targetSpatialRef = new SpatialReference();
+ targetSpatialRef.ImportFromEPSG(4326); // EPSG:4326 鏄� WGS84
+
+ // 鍒涘缓鍧愭爣杞崲瀵硅薄
+ CoordinateTransformation coordTransform = CoordinateTransformation.CreateCoordinateTransformation(layerSpatialRef, targetSpatialRef);
+
+ // 閬嶅巻姣忎釜 feature 鑾峰彇鍧愭爣鐐�
+ for (int i = 0; i < layer.GetFeatureCount(); i++) {
+ Feature feature = layer.GetFeature(i);
+ Geometry geometry = feature.GetGeometryRef();
+ // 鍒ゆ柇鍑犱綍绫诲瀷骞跺鐞�
+ if (geometry != null) {
+ if (geometry.GetGeometryType() == ogr.wkbPoint) {
+ // 鍗曚釜鐐圭殑澶勭悊
+ processPointGeometry(geometry, coordTransform);
+ } else if (geometry.GetGeometryType() == ogr.wkbMultiPoint) {
+ // 澶氫釜鐐圭殑澶勭悊
+ System.out.println("geometry = " + geometry);
+ //processMultiPointGeometry(geometry, coordTransform);
+ }
+ }
+ }
+ // 鎵撳嵃杞崲鍚庣殑鐭╁舰鑼冨洿锛堢粡绾害锛�
+ System.out.println("鎵�鏈夌偣鐨勭粡绾害鐭╁舰鑼冨洿锛歮inX = " + minX + ", maxX = " + maxX + ", minY = " + minY + ", maxY = " + maxY);
+ return coordinatesArray;
+ }
+
// 澶勭悊鍗曚釜鐐圭殑鍑犱綍浣�
private static void processPointGeometry(Geometry geometry, CoordinateTransformation coordTransform) {
double x = geometry.GetX();
--
Gitblit v1.9.3