'데이터베이스 geom'에 해당되는 글 1건

# Java와 PostgreSQL을 통한 파일을 집어 넣기 전 준비 사항

1. postgreSQL JDBC Jar 추가하기 (https://jdbc.postgresql.org/download.html)

2. 데이터베이스에 기록하기

public class GeomTest {

    private String connurl = "jdbc:postgresql://localhost:5432/postgres";
    private String user = "postgres";
    private String password = "postgres";

    public void insert() {
        String sql = "INSERT INTO " +
                "public.geom_test (_gid, geom_4326, geom_3857, geom_5186, ins_dt, mod_dt) " +
                "VALUES(" +
                " ?, " +
                " st_transform(ST_SetSRID(ST_MakePoint(?, ?), ?), 3857)," +
                " st_transform(ST_SetSRID(ST_MakePoint(?, ?), ?), 4326)," +
                " st_transform(ST_SetSRID(ST_MakePoint(?, ?), ?), 5186)," +
                " ?, ?)" +
                ";";
        Connection connection = null;
        PreparedStatement pstmt = null;

        // 경복궁 지점, 4326 좌표
        double x = 126.97727;
        double y = 37.578472;

        try {
            connection = DriverManager.getConnection(connurl, user, password);
            pstmt = connection.prepareStatement(sql);

            pstmt.setLong(1, 2); // _gid
            // 4326 좌표를 3857로 변환
            // st_transform(ST_SetSRID(ST_MakePoint(?, ?), ?), 3857)
            pstmt.setDouble(2, x);
            pstmt.setDouble(3, y);
            pstmt.setInt(4, 4326); // 원본 좌표

            // 4326 좌표를 4326로 변환
            // st_transform(ST_SetSRID(ST_MakePoint(?, ?), ?), 4326)
            pstmt.setDouble(5, x);
            pstmt.setDouble(6, y);
            pstmt.setInt(7, 4326); // 원본 좌표

            // 4326 좌표를 5186로 변환
            // st_transform(ST_SetSRID(ST_MakePoint(?, ?), ?), 5186)
            pstmt.setDouble(8, x);
            pstmt.setDouble(9, y);
            pstmt.setInt(10, 4326); // 원본 좌표

            pstmt.setTimestamp(11, Timestamp.valueOf(LocalDateTime.now()));
            pstmt.setTimestamp(12, Timestamp.valueOf(LocalDateTime.now()));

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}​

3. 결화화면