Обсуждение: 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