自动生成航线的思路和核心代码

来源：互联网发布：qq群一键群发软件编辑：程序博客网时间：2024/06/06 01:21

背景

在船舶业务中，航线是重要的要素。传统的航线是在纸质上标识，目前需要将纸质的电子化，即绘制成航线地图。航线数据在原有系统中，是以二维表的方式存储，格式为，例子：

釜山/青岛/上海/宁波/香港(CHT/HIT)/盐田/南沙/新加坡/苏伊士运河/比雷埃夫斯（希腊）/拉斯佩齐亚（意大利）/热那亚（意大利）/巴塞罗那（西班牙）/巴伦西亚（西班牙）/比雷埃夫斯（希腊）/苏伊士运河/新加坡/头顿(越南)/香港(CHT/HIT)

除了航线数据，同时还提供了每个港口的坐标信息。

思路

由于航线比较多，而且以后还会开发新的航线，如果直接采用人工的方式，效率会比较低。所以想用程序的方式实现，具体思路如下：

分解航线成航段(删除重复的航段)，每条航段格式：开始港口-结束港口；
利用ST_Geometry函数构建航段，即线要素；
对航段进行人工手动处理。由于构建的航段为直线，直接横跨大陆，而船舶是在海上行驶，并不符合要求，所以需要对部分横跨大陆的航段进行人工处理；
将航段组合成航线，也是利用到了ST_Geometry函数。

数据结构

港口表：

原始航线表：

航段表：

航段-航线关系表：

航线表：

核心代码

/**

* @Title: createRouteSegment

* @Description: 根据航线表创建航段要素(航段为直线)

* @param@param ports

* @return void

* @throws

publicvoid createRouteSegment() {

try {

Connection conn = Conn.getConnection();

Statement stmt = conn

.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

// 查询航线表，并过滤不必要的港口名称，并分拆成航段

List<Segment> segments = new ArrayList<Segment>(); // 航段表

String sqlByTRoute = "select routeid,waytoports from t_route";

ResultSet rs = stmt.executeQuery(sqlByTRoute);

while (rs.next()) {

String routeID = rs.getString("routeid");

String waytoports = rs.getString("waytoports");

waytoports = waytoports.replace("(CHT/HIT)", "");

waytoports = waytoports.replace("(Document only)", "");

waytoports = waytoports.replace("(MTL)", "");

waytoports = waytoports.replace("(HIT)", "");

waytoports = waytoports.replace("(CMCS)", "");

waytoports = waytoports.replace("(HIT/CHT)", "");

waytoports = waytoports.replace("(HIT/MTL)", "");

waytoports = waytoports.replace("(ACT)", "");

waytoports = waytoports.replace("(MTL/HIT)", "");

waytoports = waytoports.replace("(CHI/HIT)", "");

waytoports = waytoports.replace("(HIT/DP world)", "");

String[] portArray = waytoports.split("/"); // 分拆成港口数组

String startPort; // 起点港口

String endPort; // 终点港口

for (inti = 0; i < portArray.length - 1; i++) {

startPort = portArray[i];

endPort = portArray[i + 1];

if (startPort.contains("（")) {

startPort = startPort.substring(0,

startPort.indexOf("（"));

}

if (startPort.contains("(")) {

startPort = startPort.substring(0,

startPort.indexOf("("));

}

if (endPort.contains("（")) {

endPort = endPort.substring(0, endPort.indexOf("（"));

}

if (endPort.contains("(")) {

endPort = endPort.substring(0, endPort.indexOf("("));

}

Segment vo = new Segment();

vo.setRouteID(routeID); // 航线编码

vo.setOrderID(i + 1); // 航段序号

vo.setStartPort(startPort); // 航段开始港口

vo.setEndPort(endPort);// 航段结束港口

segments.add(vo);

}

System.out.println("分拆航线后，总的航段数：" + segments.size());

// 删除重复的航段，并给航段编码，以及配置坐标

List<Segment> delRepSegments = new ArrayList<Segment>(); // 已删除过重复的航段集合

List<Segment> copyRepeatSegments = new ArrayList<Segment>(); // 重新构建一个新区域存储航段数据，避免和原有的航段数据重合

copyRepeatSegments.addAll(segments); // 复制一份，避免修改原有的航段数据

Iterator<Segment> it = copyRepeatSegments.iterator();

intstartSegmentIDNumber = 1;

while (it.hasNext()) {

Segment a = it.next();

booleanflag = true;

for (inti = 0; i < delRepSegments.size(); i++) {

if (delRepSegments.get(i).getStartPort()

.equals(a.getStartPort())

&& delRepSegments.get(i).getEndPort()

.equals(a.getEndPort())) {

it.remove();

flag = false;

break;

}

if (flag) {

a.setSegmentID("S00" + startSegmentIDNumber); // 航段编码

startSegmentIDNumber++;

delRepSegments.add(a);

}

System.out.println("删除重复的航段后，剩余航段数：" + delRepSegments.size());

// 循环构建每段航段的所属航线编码、对应航段的序号

for (inti = 0; i < segments.size(); i++) {

for (intj = 0; j < delRepSegments.size(); j++) {

if (segments.get(i).getStartPort()

.equals(delRepSegments.get(j).getStartPort())

&& segments.get(i).getEndPort()

.equals(delRepSegments.get(j).getEndPort())) {

segments.get(i).setSegmentID(

delRepSegments.get(j).getSegmentID()); // 配置关系表中的航段编码

break;

}

PreparedStatement pstmt = null;

for (inti = 0; i < segments.size(); i++) {

String segmentSQL = "INSERT INTO r_routetosegment(objectid,routeid,segmentid,orderid) VALUES(?, ?, ?, ?)";

pstmt = conn.prepareStatement(segmentSQL);

pstmt.setInt(1, (i + 1));

pstmt.setString(2, segments.get(i).getRouteID());

pstmt.setString(3, segments.get(i).getSegmentID());

pstmt.setInt(4, segments.get(i).getOrderID());

pstmt.execute();

}

System.out.println("关系表ID入库成功！");

// 给航段上的港口配置坐标，循环查询所有的航段起止港口坐标

for (Segment vo : delRepSegments) {

// 查询航段的开始港口坐标

String startPortSQL = "select x,y from port where portname='"

+ vo.getStartPort() + "'";

rs = stmt.executeQuery(startPortSQL);

while (rs.next()) {

vo.setStartX(rs.getString("x"));

vo.setStartY(rs.getString("y"));

}

// 查询航段的截止港口坐标

String endPortSQL = "select x,y from port where portname='"

+ vo.getEndPort() + "'";

rs = stmt.executeQuery(endPortSQL);

while (rs.next()) {

vo.setEndX(rs.getString("x"));

vo.setEndY(rs.getString("y"));

}

// 生成航段

for (intj = 0; j < delRepSegments.size(); j++) {

Segment segment = delRepSegments.get(j); // 一段航段

String segmentLineSQL = "INSERT INTO segment(objectid, segmentid, startport, endport, shape) VALUES ("

+ (j + 1)

+ ",'"

+ segment.getSegmentID()

+ "','"

+ segment.getStartPort()

+ "','"

+ segment.getEndPort()

+ "',sde.st_geometry ('linestring("

+ segment.getStartX()

+ " "

+ segment.getStartY()

+ ","

+ segment.getEndX()

+ " "

+ segment.getEndY()

+ ")', 3857));";

System.out.println(segmentLineSQL);

stmt.executeUpdate(segmentLineSQL);

}

System.out.println("生成新航段成功！");

rs.close();

pstmt.close();

stmt.close();

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

/**

* @Title: createRouteBySegment

* @Description: 根据已有的航段创建航线(航段已经经过人工调整，避开了大陆部分)

* @param

* @return void

* @throws

publicvoid createRouteBySegment() {

List<Route> routeList = new ArrayList<Route>(); // 航线集合

String sql = "select routeid, direction, company, agent, waytoports from t_route order by objectid";

try {

Connection conn = Conn.getConnection();

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while (rs.next()) {

Route route = new Route();

route.setRouteID(rs.getString("routeid"));

route.setDirection(rs.getString("direction"));

route.setCompany(rs.getString("company"));

route.setAgent(rs.getString("agent"));

route.setWaytoports(rs.getString("waytoports"));

routeList.add(route);

}

// 查询每条航线上的所有航段编码

for (inti = 0; i < routeList.size(); i++) {

Route route = routeList.get(i); // 一条航线

String sqlSegment = "SELECT segmentid FROM r_routetosegment WHERE routeid='"

+ route.getRouteID() + "';";

rs = stmt.executeQuery(sqlSegment);

List<String> segmentids = new ArrayList<String>(); // 航线上的所有航段编码

while (rs.next()) {

segmentids.add(rs.getString("segmentid"));

}

route.setSegmentids(segmentids); // 航线上的所有航段编码

}

// 根据已生成的航段，构建航线并入库

for (inti = 0; i < routeList.size(); i++) {

Route route = routeList.get(i); // 一条航线

String sqlRoute = "INSERT INTO szroute(objectid, routeid, direction, company, agent, waytoports, shape) VALUES("

+ (i + 1)

+ ",'"

+ route.getRouteID()

+ "','"

+ route.getDirection()

+ "','"

+ route.getCompany()

+ "','"

+ route.getAgent()

+ "','"

+ route.getWaytoports() + "',";

String secondeValue = "";

for (intj = 0; j < route.getSegmentids().size(); j++) {

if (j == 0) {

secondeValue = secondeValue

+ "(SELECT shape FROM segment WHERE segmentid='"

+ route.getSegmentids().get(j) + "')";

} elseif (j == (route.getSegmentids().size() - 1)) {

secondeValue = "st_union("

+ secondeValue

+ ",(SELECT shape FROM segment WHERE segmentid='"

+ route.getSegmentids().get(j) + "'))";

} else {

secondeValue = "(SELECT * FROM st_union("

+ secondeValue

+ ",(SELECT shape FROM segment WHERE segmentid='"

+ route.getSegmentids().get(j) + "')))";

}

sqlRoute = sqlRoute + secondeValue + ");";

stmt.executeUpdate(sqlRoute);

}

System.out.println("航线入库成功！");

rs.close();

stmt.close();

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

0 0