Reading points from a Geography Polygon in a PostGIS database

Question

My goal: To read the points from a Geography Polygon stored in my PostGIS database.

The PostGIS manual has a great example of how to extract 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 am dealing with Geography, however, not Geometry, so this code does not quite work for me. If I try to extract a Polygon from my table, I get the following ClassCastException:

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

I've modified the first two lines to look like this, which works:

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

My problem now is that I can't figure out how to modify the third line of the code sample to work for Geography. I probably shouldn't cast it to the Polygon type, since that is for Geometry, but is there an equivalent for Geography? I know that Geography is only partially supported for a lot of stuff, so I'm not sure what I can or can't do here.

Have you tried `st_geometryn` and `st_pointn`? Pseudocode: `while (st_geometryn(i)) { while (st_pointn(k)) { process_pt(); k++ } i++ }` ? — unmounted, Jul 09 '11 at 03:47
you need to cast the polygon to `GEOMETRY` from the server somehow — Mike T, Jul 09 '11 at 23:02
@bvmou: ST_GeometryN and ST_PointN only work for Geometry, whereas I am working with Geography. I hadn't considered trying to retrieve the points on the database side, though, rather than on the Java side. I suppose I could just do a `ST_AsText(polygon)` and parse the points out of there if I can't figure anything better out. Still, it seems like there must be a better way of doing this through JDBC, I just don't know it. — Steph, Jul 11 '11 at 18:44
@Mike Toews: Do I really have to cast it? I would like to avoid that if I can, since the data could potentially not behave as expected when going over dateline, poles, and for large geometries or geometry pairs that cover more than one UTM zone. — Steph, Jul 11 '11 at 18:46
Where GEOMETRY could misbehave (around datelines, poles, etc.) is when you try the area and length functions. I believe that the data structures are the same between the two types (i.e. 1:1), so you can always cast a GEOMETRY back to GEOGRAPHY for either storage or area/distance calculations. However, I'm no pro with the JDBC interface, so you might want to check with folks on the postgis-users mail list. — Mike T, Jul 11 '11 at 23:09

score 5 · Accepted Answer · answered Jul 12 '11 at 00:16

I ended up deciding to get the coordinates from Postgres using the ST_AsText() method, rather than extracting them from some sort of Geography object in Java. Then I just parsed the polygon string in Java.

The PostgreSQL statement I executed looks like this:

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

In Java I extract the String from the ResultSet after doing my JDBC query:

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

And I get something that looks like this:

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

Then I just parsed the points out of that.

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 it, but it's the best way I could figure out on my own.

I would personally use regular expressions to get the points. — grasshopper, Mar 19 '14 at 10:19
also do substring in SQL to avoid sending word "POLYGON(" for every row substring(ST_AsText(ST_Transform(l.way,4326)),'\((.+)\)') as way — Stan Sokolov, Oct 03 '19 at 18:05

score 1 · Answer 2 · answered Dec 23 '13 at 10:27

If you know that the object is a polygon, it's quite 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));
    }

score 0 · Answer 3 · answered Jul 11 '15 at 09:26

0

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);
}

answered Jul 11 '15 at 09:26

Glenn

1

score 0 · Answer 4 · answered Mar 04 '18 at 13:15

Use ST_AsGeoJSON, and then just parse the json:

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

Then you can get the area as json like this:

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

And now that you have it as geoJSON, you can parse the JSON and do what ever you wish with it.

Reading points from a Geography Polygon in a PostGIS database

4 Answers4