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
Show quoted text
regards, tom lane
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
Thank you very much!
I made several tests, and (with postgresql-8.4dev-arraydim.jdbc3.jar) it
worked.
Greetings,
Bjoern Weitzig
Kris Jurka wrote:
Show quoted text
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.jarRight, 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