[postgis-users] GeomFromWkb() incomplete?

[David Blasby]

Martin Schäfer wrote:
> Hi,
> 
> I want to use GeomFromWkb() together with the PostgreSQL 7.4 function PQexecParams() for insert and update clauses. This would allow me to pass the WKB as real binary data and not just as a hex-encoded byte string. But I get the following error when I set the parameter format to binary:
> 
> ERROR: no binary input function available for type wkb
> 
> Is there something wrong with my installation, or is there really no binary input function for well known binary? (Sounds like a no-brainer to me.) Binary output seems to be supported as I can access AsBinary(the_geom) from a binary cursor.
> 
> Hexencoding on the client side, transmitting double the amount of bytes and hexdecoding on the server side is an unnecessary overhead, especially when dealing with geometries that contain tens of thousands of vertices.

In order to truly transfeer binary data, you need to set up a binary 
cursor.  Unfortunately, this is an all-or-nothing proposition - 
*everything* is in binary, not just your geometries.

The nice thing about using the hex-ized WKB is that you can use it with 
the normal (text) cursor.

An alternative (which I use with the postgis-Mapserver connector) is to 
re-write all your queries like this:

SELECT a::text,b::text,c::text, asBinary(the_geom) FROM ...;

If you use a binary cursor, you'll find its now behaving very much like 
a text cursor but its sending your geometry as true binary.

I dont really like this method, but it does work well.

1. You have to pre-know all the columns you'll be asking for.  You 
cannot use "SELECT *"...

	If you really want to make your application complex and your brain 
hurt, you can actually convert a "SELECT *" into a "SELECT a,b,c" by 
either looking at the system tables or executing a pseudo-query.

For example, you can look in the system table (pg_class and pg_attribute 
I believe) to get the columns in a table.  If you want to be even more
abstract (and have it work on arbitrary SQL), you can::

    SELECT * FROM ( <query> as foo) WHERE false;

This doesnt return any rows and is almost always very fast.  When you 
look at the result set that libpq returns, you'll be able to fine all 
the column names and their types.  From there, you just re-form your 
query like this:

	SELECT a::text,b::text,the_geom FROM <query> as foo;

2. not all types will have a convert-to-text function.  You can get 
around this by directly calling the text output function associated with 
  the type.  This information is in the system tables.

As you can see, it much easier to just use a text cursor and live with 
the to-hex-and-back overhead.

I cannot imagine this being too much of an overhead.  If you have some 
test numbers to show its big, you can encode the binary as Base64 
instead of Hex.

dave