[GIS] How to extract data from a POINT column in PostGIS using JAVA (jdbc)

javapostgissql

How do you get a value from a column which has a point data type.

I've just switched over from MySQL to PostGIS. I have a column which is called point and is of type Point.

I want to get that value as a result of a SQL query.

I've done things like rs.getString("text") and rs.getInt("number") for other columns and they work fine, but doing

rs.getObject("point")

Causes me to get the error

org.postgresql.util.PSQLException: This connection has been closed.

Here's more code, I thought it might a real-dummies question so I didn't stick it in before.

Essentially I have a value in my application which can take on a variety of data types. Not having a truly OO database, I've made a table with lots of columns, depending on which is is populated, I will know what datatype the value is for that record.

public static Set<STriple> readStatements(String subUri, String preUri) {
    Set<STriple> statements = new HashSet<STriple>();
    String sql = "SELECT sub_id, sub_uri, sub_value, pre_id, pre_uri, pre_value, obj_id, obj_uri, obj_value, number, text, file, url, point FROM triples WHERE sub_uri='"
            + subUri + "' AND pre_uri='" + preUri + "'";
    ResultSet rs = Dao.executeQuery(sql);
    try {
        while (rs.next()) {
            STriple s = new STriple();
            s.setSubject(new SResource(rs.getString("sub_uri")));
            s.setPredicate(new SResource(rs.getString("pre_uri")));
            if (rs.getObject("obj_id") != null) {
                s.setObject(new SResource(rs.getString("obj_uri")));
            } else if (rs.getObject("point").toString() != null) {
                Object pointObject =  rs.getObject("point"));
            } else if (rs.getObject("number") != null) {
                s.setObject(new SNumber(rs.getDouble("number")));
            } else if (rs.getObject("text") != null) {
                s.setObject(new SText(rs.getString("text")));
            } else if (rs.getObject("url") != null) {
                s.setObject(new SURL(rs.getString("url")));
            }
            statements.add(s);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return statements;
}

Best Answer

Java is strongly typed so you need to retrieve the point into a geometry object with a "to string" method

PGgeometry geom = (PGgeometry)rs.getObject("point"); 
System.out.println(geom.toString());

see: http://postgis.refractions.net/docs/ch05.html#id2633989

Related Question