[postgis-users] GeomFromWkb() incomplete?

[Martin Schäfer]

Hi,

I've tested the CVS version on Windows XP: I created a new database, installed postgis in the database using the postgis.sql from the CVS version, but I always get a PGRES_FATAL_ERROR with this message:

server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

This is approximately the code I'm using:

char *strSQL = "insert into country (the_geom,gid) values (GeomFromWkb($1,4326),nextval('public.country_gid_seq'::text))";
int nParams = 1;
Oid paramType = m_nOidWkb; // 123192
const char *paramValue = <some binary wkb data>;
int paramLength = <length of binary wkb data>;
int paramFormat = 1; // binary
int resultFormat = 0; // text
PGresult *res;
res = PQexecParams(m_pConn, strSQL, nParams, &paramType, &paramValue, &paramLength, &paramFormat, resultFormat);

I'm sure that the wkb data is correct, because everything works fine if I hexencode the wkb data and change the paramFormat from 1 to 0.

Also, if I do a 'vacuum analyze' on the database afterwards, the backend uses up all available memory and the vacuum fails with the error message:
ERROR:  out of memory
DETAIL:  Failed on request of size 12.
The database seems to be messed up and I have to recreate it.

Martin


> -----Original Message-----
> From: strk [mailto:strk at keybit.net] 
> Sent: 28 November 2003 11:13
> To: Martin Schäfer
> Cc: PostGIS Users Discussion; David Blasby; Paul Ramsey
> Subject: Re: [postgis-users] GeomFromWkb() incomplete?
> 
> 
> I've added definitions for wkb_from_binary.
> You need latest CVS version for it. Please help testing it :)
> 
> If you want to keep your asset I think you can do the following:
> 
> CREATE FUNCTION wkb_recv(internal)
>         RETURNS wkb
>         AS '$libdir/libpostgis.so.0.8','WKB_recv'
>         LANGUAGE 'C' WITH (isstrict);
> 
> UPDATE pg_type SET typreceive = 'wkb_recv' 
> 	WHERE typname = 'wkb';
> 
> --strk;
> 
> Martin.Schaefer wrote:
> > 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
> > > 
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>