如何利用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();    
    }

但我总是会犯这样的错误：

org.postgresql.util.PSQLException:无法推断要用于org.postgis.Point实例的SQL类型。使用具有显式类型值的setObject()指定要使用的类型。

有人知道怎么储存它吗？

提前感谢！

Answer 1

Java的见手册。从这里我看到了两个想法。尝试为PGgeometry使用Geometry类型而不是Geometry类型。然后，将几何类型添加到连接congeom中。

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

Answer 2

据我的经验，我使用这样的表达式添加了点(请注意，请求被迭代的点是我自己的类)：

    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>

​

注意，您不需要直接排除依赖项(这是我自己的兼容性所需要的)。