[postgis-devel] FWD: BUG #4638: Bug with Geometry in Array

[Kevin Neufeld]

Yes, this does appear to be a bug with JDBC and the PGgeometry datatype (at least with v8.3-604.jdbc3.jar, this feature 
is not implemented yet in version 8.2).

If I run this code segment:
         ResultSet rset = stmt.executeQuery("SELECT ARRAY(SELECT column1 FROM "
                 + "(VALUES ('POINT(0 0)'::text), ('POINT(0 0)'::text)) AS foo)");
         while (rset.next()) {
             Array a = rset.getArray(1);
             System.out.println(a.getClass() + " # " + a.toString());
             ResultSet rs = a.getResultSet();
             while (rs.next()) {
                 System.out.print(rs.getObject(1).getClass().toString() + " # " + rs.getObject(1)
                         + "\t");
                 System.out.println(rs.getObject(2).getClass().toString() + " # " + rs.getObject(2));
             }
         }

I get can iterate through both array fields.  This yields:
class org.postgresql.jdbc3.Jdbc3Array # {"POINT(0 0)","POINT(0 0)"}
class java.lang.Integer # 1	class java.lang.String # POINT(0 0)
class java.lang.Integer # 2	class java.lang.String # POINT(0 0)


If I cast them to type "geometry" I only get one array field:
class org.postgresql.jdbc3.Jdbc3Array # 
{010100000000000000000000000000000000000000:010100000000000000000000000000000000000000}
class java.lang.Integer # 1	class org.postgis.PGgeometry # POINT(0 0)


BOX2D and BOX3D do seem to work though:
class org.postgresql.jdbc3.Jdbc3Array # {"BOX3D(0 0 0,1 1 0)","BOX3D(0 0 0,1 1 0)"}
class java.lang.Integer # 1	class org.postgis.PGbox3d # BOX3D(0 0 0,1 1 0)
class java.lang.Integer # 2	class org.postgis.PGbox3d # BOX3D(0 0 0,1 1 0)


This seems limited to the PGgeometry datatype.

Cheers,
Kevin


Tom Lane wrote:
> Can anyone here reproduce the behavior complained of below?
> (I haven't got postgis installed ATM, so not convenient to try it
> myself)
> 
> 			regards, tom lane
> 
> ------- Forwarded Message
> 
> Date:    Tue, 3 Feb 2009 12:49:22 GMT
> From:    "Bjoern Weitzig" <weitzig at supportgis.de>
> To:      pgsql-bugs at postgresql.org
> Subject: [BUGS] BUG #4638: Bug with Geometry in Array
> 
> 
> The following bug has been logged online:
> 
> Bug reference:      4638
> Logged by:          Bjoern Weitzig
> Email address:      weitzig at supportgis.de
> PostgreSQL version: 8.3.5
> Operating system:   Windows
> Description:        Bug with Geometry in Array
> Details: 
> 
> System: PostgreSQL 8.3.5, compiled by Visual C++ build 1400
> (Windows-Installer), PostGIS 1.3.5, GEOS 3.0.3-CAPI-1.4.2 on Windows
> 
> I use arrays like "select array(select n from a)". This works well except of
> the case with geometry as datatype. 
> 
> In case of geometry, the JDBC ResultSet.getArray(1).getResultSet() only
> lists the first element.
> 
> Testcase:
> 
> create table a(n VARCHAR); 
> SELECT AddGeometryColumn( 'a', 'feature', -1, 'GEOMETRY', 3 );
> INSERT INTO a(n, feature) VALUES (1,GeomFromText('Point(1 1 0)',-1));
> INSERT INTO a(n, feature) VALUES (2,GeomFromText('Point(2 2 0)',-1));
> 
> select array(select n from a);
>   =>  "{1,2}"  (ok)
> select array(select feature from a);
>  => 
> 
> "{0101000080000000000000F03F000000000000F03F0000000000000000:010100008000000
> 0000000004000000000000000400000000000000000}"
> I wonder why there is a ":" instead of an ",".
> 
> JDBC:
> 		ResultSet rset = stmt.executeQuery("select array(select feature from
> a)");
> 	        if (rset.next()) {
> 	        	Array array = rset.getArray(1);
> 	        	ResultSet rs2 = array.getResultSet();
> 	        	int n=0;
> 	        	while (rs2.next()) {
> 	        		Object o = rs2.getObject(2);
> 	        		n++;
> 	        		System.out.println(n+": "+o);
> 	        	}
> 	        	rs2.close();
> 	        }
> This lists only the first point "1: POINT(1 1 0)", instead of expected "1:
> POINT(1 1 0) \n 2: POINT(2 2 0)"
>