如何使用java在Postgis数据库中存储几何点

Question

我试图将一个几何体对象存储到我的postgist数据库中，该数据库有一个带有几何体列的表。我从另一个带有几何列的表格中得到了几何值，我打印了我之前得到的值，结果没问题。要存储几何体值，我使用下一个函数：

static void insertaGeometria( Geometry geom, int idInstalacion) throws ClassNotFoundException, SQLException{

        Connection congeom = conectarPGA();

        String geomsql ="INSERT INTO georrepositorio.geometria(id, point) VALUES (?,?)";
        PreparedStatement psSE= congeom.prepareStatement(geomsql);
        psSE.setInt(1, idInstalacion);
        psSE.setObject(2, geom);    

        psSE.execute();
        psSE.close();   
        congeom.close();    
    }

但我总是得到这个错误：

组织。postgresql。util。PSQLException：无法推断用于org实例的SQL类型。波斯吉斯。指向使用带有显式类型值的setObject（）指定要使用的类型。

有人知道怎么储存吗？='(

提前谢谢！

Answer 1

根据我的经验，我成功地使用这样的表达式添加了点（请注意，请求的迭代点是我自己的类）：

    java.sql.Connection conpg;

    try {
/*
* Load the JDBC driver and establish a connection.
*/

        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost:5432/postgis_22_sample";
        conpg = DriverManager.getConnection(url, "postgres", "nypassw");
/*
* Add the geometry types to the connection. Note that you
* must cast the connection to the pgsql-specific connection
* implementation before calling the addDataType() method.
*/
        ((org.postgresql.PGConnection) conpg).addDataType("geometry", Class.forName("org.postgis.PGgeometry"));
        //((org.postgresql.PGConnection)conpg).addDataType("point",Class.forName("org.postgis.Point"));
/*
* Create a statement and execute a select query.
*/
        conpg.setAutoCommit(false);

        for (Point p : points) {

            org.postgis.Point pointToAdd = new org.postgis.Point();
            pointToAdd.setX(p.getLongitude());
            pointToAdd.setY(p.getLatitude());

            //Statement s = conn.createStatement();
            //String geomsql = ;
            PreparedStatement psSE = conpg.prepareStatement("INSERT INTO public.\"poi-point\" (name,geom,leisure) VALUES (?,?,?)");
            psSE.setString(1, p.getDescription());
            psSE.setObject(2, new org.postgis.PGgeometry(pointToAdd));
            psSE.setString(3, "marina");

            psSE.execute();
            //ResultSet r = s.executeQuery("select geom,id from geomtable");
            //while (r.next()) {
  /*
  * Retrieve the geometry as an object then cast it to the geometry type.
  * Print things out.
  */
            //    PGgeometry geom = (PGgeometry) r.getObject(1);
            //    int id = r.getInt(2);
            //    System.out.println("Row " + id + ":");
            //    System.out.println(geom.toString());
            //}
            //s.close();
        }
        conpg.commit();
        conpg.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

使其工作的maven依赖项是

    <dependency>
        <groupId>net.postgis</groupId>
        <artifactId>postgis-jdbc</artifactId>
        <version>2.2.0</version>
        <exclusions>
            <exclusion>
                <!-- NOTE: Version 4.2 has bundled slf4j -->
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-api</artifactId>
            </exclusion>
            <exclusion>
                <!-- NOTE: Version 4.2 has bundled slf4j -->
                <groupId>ch.qos.logback</groupId>
                <artifactId>logback-classic</artifactId>
            </exclusion>
            <exclusion>
                <!-- NOTE: Version 4.2 has bundled slf4j -->
                <groupId>ch.qos.logback</groupId>
                <artifactId>logback-core</artifactId>
            </exclusion>
        </exclusions>

请注意，您不需要排除依赖项（这是我自己的兼容性所需要的）

Answer 2

请参阅Java客户端手册。从这一点我看到两个想法。尝试使用PG几何而不是Geometry类型为geom。然后，将几何类型添加到连接congeom：

((org.postgresql.PGConnection)congeom).addDataType("geometry",Class.forName("org.postgis.PGgeometry"));