Thread: BUG #4638: Bug with Geometry in Array
The following bug has been logged online: Bug reference: 4638 Logged by: Bjoern Weitzig Email address: weitzig@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)"
"Bjoern Weitzig" <weitzig@supportgis.de> writes: > I use arrays like "select array(select n from a)". This works well except of > the case with geometry as datatype. The geometry type intentionally uses ':' as the array delimiter. It seems like a good bet that the JDBC driver you're using doesn't expect anything but ',' as array delimiter. I checked with the postgis-devel list and they recommend using the modified JDBC driver that's distributed with postgis. http://postgis.refractions.net/documentation/manual-1.3.6SVN/ch04.html#id2726248 regards, tom lane
Thank you for your fast reply. I can't find any "modified JDBC driver", only the "JDBC extension" postgis.jar (your link), which I use und which doesn't help. postgis.jar seems to be an add-on to the default JDBC driver, not an replacement. I use postgis_1.3.5.jar with postgresql-8.3-603.jdbc3.jar It would be great if you can show me where to get the "modified JDBC driver". As PostGIS is a common extension to PostgreSQL, wouldn't it be good to handle this case in the default JDBC driver? Thank you, Bjoern Weitzig On 2009-02-03 17:43, Tom Lane wrote: > "Bjoern Weitzig" writes: > > I use arrays like "select array(select n from a)". This works well except of > > the case with geometry as datatype. > > The geometry type intentionally uses ':' as the array delimiter. It > seems like a good bet that the JDBC driver you're using doesn't expect > anything but ',' as array delimiter. I checked with the postgis-devel > list and they recommend using the modified JDBC driver that's > distributed with postgis. > http://postgis.refractions.net/documentation/manual-1.3.6SVN/ch04.html#id2726248 > > regards, tom lane > >
On Mon, 2 Mar 2009, "Dr. Bj=F6rn Weitzig" wrote: > I can't find any "modified JDBC driver", only the "JDBC extension" > postgis.jar (your link), which I use und which doesn't help. postgis.jar > seems to be an add-on to the default JDBC driver, not an replacement. > I use postgis_1.3.5.jar with postgresql-8.3-603.jdbc3.jar > Right, postgis.jar is just an addon, and it's really a bug in the main=20 JDBC driver. I have put in a fix for this bug into the JDBC driver for=20 the next release. I've put up a copy of it here, and it should fix things= =20 for you. http://ejurka.com/pgsql/jars/arrdim/ Kris Jurka
Thank you very much! I made several tests, and (with postgresql-8.4dev-arraydim.jdbc3.jar) it worked. Greetings, Bjoern Weitzig Kris Jurka wrote: > On Mon, 2 Mar 2009, "Dr. Björn Weitzig" wrote: >> I can't find any "modified JDBC driver", only the "JDBC extension" >> postgis.jar (your link), which I use und which doesn't help. postgis.jar >> seems to be an add-on to the default JDBC driver, not an replacement. >> I use postgis_1.3.5.jar with postgresql-8.3-603.jdbc3.jar > Right, postgis.jar is just an addon, and it's really a bug in the main > JDBC driver. I have put in a fix for this bug into the JDBC driver > for the next release. I've put up a copy of it here, and it should > fix things for you. > > http://ejurka.com/pgsql/jars/arrdim/ > > Kris Jurka