Reading points from a geography polygon in a PostGIS database

Question

My goal: to read the points from the polygon of geography stored in my PostGIS database.

The PostGIS manual has a great example of extracting a polygon from a database.

PGgeometry geom = (PGgeometry)r.getObject(1); if (geom.getType() == Geometry.POLYGON ) { Polygon pl = (Polygon)geom.getGeometry(); for (int r = 0; r < pl.numRings(); r++) { LinearRing rng = pl.getRing(r); System.out.println("Ring: " + r); for (int p = 0; p < rng.numPoints(); p++ ) { Point pt = rng.getPoint(p); System.out.println("Point: " + p); System.out.println(pt.toString()); } } } 

I deal with geography, but not geometry, so this code does not work for me. If I try to retrieve Polygon from my table, I get the following ClassCastException :

 org.postgresql.util.PGobject cannot be cast to org.postgis.PGgeometry 

I changed the first two lines to look like this:

 PGobject area = (PGobject)rs.getObject("area"); if (area.getType().compareTo("geography") == 0) { ... } 

Now my problem is that I cannot figure out how to change the third line of the sample code to work in geography. I probably shouldn't point it to the Polygon type, since this is for geometry, but is there an equivalent for geography? I know that geography is partially supported for many things, so I'm not sure what I can or cannot do here.

Answer 1

In the end, I decided to get the coordinates from Postgres using the ST_AsText () method, instead of extracting them from some kind of geography object in Java. Then I just parsed the polygon string in Java.

The PostgreSQL statement that I executed looks like this:

 SELECT id, name, ST_AsText(area) FROM area_table; 

In Java, I retrieve a string from a ResultSet after executing my JDBC request:

 String area = rs.getString("ST_AsText"); 

And I get something similar to this:

 "POLYGON((-49 52,123 52,123 -4,-49 -4,-49 52))" 

Then I just analyzed the points from this.

 double[] bounds = new double[8]; int k = 0; for (int i = 0; i < points.length; i++) { String[] lonLat = points[i].split(" "); for (int j = 0; j < lonLat.length; j++) { bounds[k++] = Double.parseDouble(lonLat[j]); } } 

I don't know if this is the best way to do this, but it is the best way that I could figure out on my own.

Answer 2

If you know that the object is a polygon, this is pretty simple.

 PGpolygon polygon = (PGpolygon)rs.getObject("area"); for (int i = 0; i < polygon.points.length; i++) { System.out.println(String.format("(%d,%d)" polygon.points[i].x, polygon.points[i].y)); } 

Answer 3

 PGgeometry geo = (PGgeometry)rs.getObject("coords"); Geometry poly = foo.getGeometry(); for (int i = 0; i < poly.numPoints(); i++) { System.out.println("-->"+poly.getPoint(i).y+", "+ poly.getPoint(i).x); } 

Answer 4

Use ST_AsGeoJSON and then just parse json:

 SELECT id, name, ST_AsGeoJSON(area) FROM area_table; 

Then you can get the scope as json as follows:

 String area = rs.getString("area"); 

And now that you have it as geoJSON, you can parse JSON and do what you want.