Re: How to insert binary data

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