将数据修改为geometry类型

甲方给了一坨数据

 

和一坨大便差不多    

 

空间字段完全不符合要求  需要手动清洗后入库

 

拿个简单的来说

 

甲方给的经纬度数据是一条条线

 

数据格式是这样的

113.089437,23.233682;113.091573,23.249744

 

需要将数据使用java转换为  wkt格式   再到数据库中转换为  wkb格式   最后将空间字段类型修改为geometry类型

 

贴一段数据清洗的代码

 

代码的大概意思是 把在excel 后面加一个geom字段，将经纬度清洗为wkt格式后存入geom字段

package com.piesat.test;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@SpringBootTest
public class ljwTest {

    @Autowired

    @Test
    public void test() throws IOException {

        String filePath = "C:\\Users\\HTHT\\Desktop\\czt_xdcddqtglxx.xlsx";

        FileInputStream inputStream = new FileInputStream(filePath);

        ExcelReader reader = ExcelUtil.getReader(inputStream);

        Workbook workbook = reader.getWorkbook();

        Sheet sheetAt = workbook.getSheetAt(0);

        int lastRowNum = sheetAt.getLastRowNum();

        Row row1 = sheetAt.getRow(0);

        Cell cell1 = row1.getCell(39);

        String ss1 = "geom";

        Cell head = row1.createCell(39);

        head.setCellValue(ss1);

        for (int i = 1;i<lastRowNum;i++){

            Row row = sheetAt.getRow(i);

            Cell cell = row.getCell(12);

            String s1= "LINESTRING("+cell.toString().replace(","," ")+')';

            String s2 = s1.replace(";",",");

            Cell add = row.createCell(39);

            add.setCellValue(s2);
        }

        ExcelWriter writer = reader.getWriter();

        writer.flush(new File("C:\\Users\\HTHT\\Desktop\\test.xlsx"));

        inputStream.close();

        reader.close();
    }
}

 

清洗完后将excel导入 数据库

 

打命令

update table table_name set geom = st_geometryfromtext(geom)

将数据转换为wkb格式

 

alter table table_name alter column field_name type geometry using field_name::geometry

也可以先把数据库字段设置为geometry

再更新数据

 

update table table_name set geom = st_geometryfromtext(geom)