[postgis-users] GeomFromWkb() incomplete?

Martin Schäfer Martin.Schaefer at cadcorp.com
Tue Dec 9 01:37:32 PST 2003 

I attached a small program that exhibits the problem.
You will probably have to make a couple of changes in order to compile it on your machine.

Martin

> -----Original Message-----
> From: strk [mailto:strk at keybit.net] 
> Sent: 08 December 2003 16:35
> To: Martin Schäfer
> Cc: PostGIS Users Discussion; David Blasby; Paul Ramsey
> Subject: Re: [postgis-users] GeomFromWkb() incomplete?
> 
> 
> 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
> > > 
> 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: TestPostGisBinaryWkb.cpp
Type: application/octet-stream
Size: 2517 bytes
Desc: TestPostGisBinaryWkb.cpp
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20031209/2d0f224c/attachment.obj>

More information about the postgis-users mailing list