Thread: How to insert binary data

Hi,

Today, I had some problems inserting Data via WKB format (which we
suspect to be more efficient compared to the text representation) into a
PostGIS database through JDBC.

We have the data as a byte array, but either
PreparedStatement.setBytes() and .setObject() failed to work.

After some experimentation, I found that I have to encode the byte array
into hex, and then it works (which is ugly because we need to
pre-process the data and double the data size).

For retreiving wkb data, there is a bytea() function that returns the
binary data, but there seems to be no constructor for wkb postgres type
that uses binary data.

It also looks like the PGObject subclassing mechanism only works for
String representations, and the JDBC TypeMap mechanism is not
implemented.

Is there any way that allows efficient storing of WKB data into PostGIS?

Thanks,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Markus Schaber wrote:
> Hi,
>
> Today, I had some problems inserting Data via WKB format (which we
> suspect to be more efficient compared to the text representation) into a
> PostGIS database through JDBC.

What is the "WKB" representation exactly? (I'm guessing "well known
binary"?)

> We have the data as a byte array, but either
> PreparedStatement.setBytes() and .setObject() failed to work.
>
> After some experimentation, I found that I have to encode the byte array
> into hex, and then it works (which is ugly because we need to
> pre-process the data and double the data size).
>
> For retreiving wkb data, there is a bytea() function that returns the
> binary data, but there seems to be no constructor for wkb postgres type
> that uses binary data.
>
> It also looks like the PGObject subclassing mechanism only works for
> String representations, and the JDBC TypeMap mechanism is not
> implemented.
>
> Is there any way that allows efficient storing of WKB data into PostGIS?

The standard driver can't use binary parameter values as it does not use
the extended query protocol for sending parameters.

I'm working on some driver modifications that would allow sending
parameters in a binary form (among other things). The main unresolved
question in this area is how it would fit with the PGobject system.

My current thoughts are to have a PGBinaryObject subclass of PGobject
that has methods for streaming the parameter in a binary form. The
addDataType registration mechanism would remain unchanged, and you'd
continue to use setObject to bind parameters. At statement execution
time, if the bound parameter value subclasses PGBinaryObject *and* the
protocol in use allows use of binary parameters (version 2 connections
won't) then the appropriate PGBinaryObject methods are invoked to
actually send the parameter. Otherwise the driver uses the current
string representation approach.

Getting results back in a binary form is further away but would use a
similar strategy (if we have a binary result value and a corresponding
registered type that subclasses PGBinaryObject, call an appropriate
method, otherwise treat it as an unknown type).

Does this sound like the right approach for what you need?

-O

Hi, Oliver,

On Sat, 05 Jun 2004 21:30:33 +1200
Oliver Jowett <oliver@opencloud.com> wrote:

> Markus Schaber wrote:
> > Today, I had some problems inserting Data via WKB format (which we
> > suspect to be more efficient compared to the text representation)
> > into a PostGIS database through JDBC.
>
> What is the "WKB" representation exactly? (I'm guessing "well known
> binary"?)

Yes, exactly. I can send you pointers to the exact specifications, if
needed.

> > Is there any way that allows efficient storing of WKB data into
> > PostGIS?
> The standard driver can't use binary parameter values as it does not
> use the extended query protocol for sending parameters.

That's what I was afraid of :-(

> I'm working on some driver modifications that would allow sending
> parameters in a binary form (among other things). The main unresolved
> question in this area is how it would fit with the PGobject system.

I think it might be better to implement the JDBC type map system, as
soon as we have binary data in place. This would allow to use this jdbc
compliant method, which might have some advantages when running in jboss
or other environments which tend to wrap the connection objects in their
own wrappers.

It may also help to reduce the database specific code in the
application, and could partially render my "arbitrary types framework"
proposal from 10th of March obsolete (for Reading, we could subclass the
Class we want to use, and register this subclass with the driver, we
would only need some trickery for writing...)

> My current thoughts are to have a PGBinaryObject subclass of PGobject
> that has methods for streaming the parameter in a binary form. The
> addDataType registration mechanism would remain unchanged, and you'd
> continue to use setObject to bind parameters. At statement execution
> time, if the bound parameter value subclasses PGBinaryObject *and* the
> protocol in use allows use of binary parameters (version 2 connections
> won't) then the appropriate PGBinaryObject methods are invoked to
> actually send the parameter. Otherwise the driver uses the current
> string representation approach.

Maybe we could also look for implementing SQLData, or make
PGBinaryObject implement SQLData so one can use the same wrapper
Implementation for both methods.

> Getting results back in a binary form is further away

How do we get byte areas currently? I ask this because there's a
bytea(wkb) conversion in postgis. Maybe this is temporary way to get WKB
data into the application until the driver fully supports reading binary
data. (Sadly, there's no wkb(bytea) method...)

> but would use a
> similar strategy (if we have a binary result value and a corresponding
> registered type that subclasses PGBinaryObject, call an appropriate
> method, otherwise treat it as an unknown type).

I think that's the correct way to go.

> Does this sound like the right approach for what you need?

Yes, it sounds reasonable and would help to solve my problem. Although
I'm not shure whether the java type map is the better way to deal with
binary representations.

I'd like to help coding, but I think I need some amounts of time to dig
into the code and protocol to be of any bigger help there...


Thanks for your patience,

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com