[postgis-users] GeomFromWkb() incomplete?

[Martin Schäfer]

Hi,

I'm already rewriting the query and using a binary cursor, reading binary data from the server is not a problem.

What I'd like to do is _send_ true binary data from the client to the server via PQexecParams(). This function can take parameters for a query (in my case the query is an update or insert). These parameters can be transferred in textual or binary format. I'd like to use the WKB of an update/insert query as a binary parameter to PQexecParams(). Unfortunately I will get the error message mentioned below.

I looked through postgis.sql and found the following definition of wkb:

CREATE TYPE wkb (
	internallength = variable,
	input = wkb_in,
	output = wkb_out,
	storage = extended,
	send = bytea
);

What I would need in order to use PQexecParams() in the way I described it would be another parameter to the wkb type definition:

	receive = wkb_from_binary,

There's already a send function, so a receive function would make a lot of sense from my point of view.

Similarly there's a cast function defined from wkb to bytea, but no cast from bytea to wkb. Adding this cast would make sense too, I think.

Martin

> -----Original Message-----
> From: David Blasby [mailto:dblasby at refractions.net] 
> Sent: 26 November 2003 17:37
> To: PostGIS Users Discussion; strk
> Subject: Re: [postgis-users] GeomFromWkb() incomplete?
> 
> 
> 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
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>