[postgis-users] GeomFromWkb() incomplete?

strk strk at keybit.net
Mon Dec 8 08:34:47 PST 2003 

Could you send a complete example so that I can compile it
and test it (including a small valid binary wkb) ?

--strk;

Martin.Schaefer wrote:
> 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
> >

More information about the postgis-users mailing list