4

I would like to read the data from PostGIS OSM database to Java object. I have 'planet_osm_line' table. I'd like to get 'osm_id' and 'way' column read by ResultSet, as here:

stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
List<OSMLineData> osmLineDataList = new ArrayList<OSMLineData>();
while (rs.next()) {
    LineString ls = (LineString) rs.getObject("way");
    OSMLineData osmLineData = new OSMLineData(rs.getInt("osm_id"), (LineString) rs.getObject("way"));
    osmLineDataList.add(osmLineData);
}
rs.close();
stmt.close();
return osmLineDataList;

However, I get an ereor:

Caused by: java.lang.ClassCastException: org.postgresql.util.PGobject cannot be cast to com.vividsolutions.jts.geom.LineString

When I change it to read String rs.getString(way), and I also change the class variable type from Linestring to String, I am getting some weird representation of 'way' data.

I thought that it would be ok to import data for 'way' column with 'st_astext(way)' to the variable of String type, and then somehow change it to Linestring type from JTS. Is this a correct way? Is there a different, more efficient way to do this?

2

The easiest way (that I know of) is to use the GeoTools library to handle all the complex database interaction and geometry conversions. Your code boils down to this:

Map<String, Object> params = new HashMap<>();
params.put(PostgisNGDataStoreFactory.DBTYPE.key, PostgisNGDataStoreFactory.DBTYPE.sample);
params.put(PostgisNGDataStoreFactory.PORT.key, 5432);
params.put(PostgisNGDataStoreFactory.HOST.key, "localhost");
params.put(PostgisNGDataStoreFactory.HOST.key, "localhost");
params.put(PostgisNGDataStoreFactory.USER.key, "ian");
params.put(PostgisNGDataStoreFactory.PASSWD.key, "ianian");
params.put(PostgisNGDataStoreFactory.DATABASE.key, "osm");
params.put(PostgisNGDataStoreFactory.SCHEMA.key, "ospr");
DataStore postgisDS = DataStoreFinder.getDataStore(params);
SimpleFeatureSource source = postgisDS.getFeatureSource("ways");
List<OSMLineData> osmLineDataList = new ArrayList<>();
try (SimpleFeatureIterator it = source.getFeatures().features()) {
  while (it.hasNext()) {
    SimpleFeature feature = it.next();
    OSMLineData osmLineData = new OSMLineData(feature.getID(), (LineString) feature.getDefaultGeometry());
    osmLineDataList.add(osmLineData);
  }
}

}

0

On the PostGIS side, use ST_AsBinary(way) to convert your geometry to well-known binary (WKB) format.

On the Java side, read the WKB into a byte[] with the getBytes method of ResultSet and use a JTS WKBReader to parse the byte[] array into a JTS geometry.

You can also use ST_AsText, but your geometries will be modified slightly because ST_AsText does not preserve full coordinate precision.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.