5

PostgreSQL (via C# Npgsql driver) returns geometry column (PostGIS) like a hex string (EWKB as I understand) for this query:

SELECT ST_Transform(geometry, 4326) FROM myGeometries;

Result (for example):

0101000020E6100000BE6BD0975E595240400E942584A04E40

Is it possible to get it as blob/byte array? Or PostGIS store'em always as text? It's not a good for performance, because you need to convert hex string to a byte array before reading.

PS. I know about ST_AsText, but I don't want to parse WKT, currently I read from EWKB without any problems. Guys, I'm sorry for my primitive English.

Improve this question
6
  • So you want to exchange PostGIS data in binary with the server? You can do that if you're using libpq, just use the binary options to PQexecParams. Commented May 11, 2014 at 12:25
  • @CraigRinger, nope. Read only (Windows, C#, Npsql driver), I want to understand why PostgreSQL/PostGIS returns geometry as hex string, not as byte array. It's really strange, is it usual? Or it is a driver problem. May be I can change this behavior. Commented May 11, 2014 at 12:33
  • It's the normal behaviour, but you're really not expected to use that representation directly. Use ST_AsBinary or ST_AsEWKB if you want the binary form. Commented May 11, 2014 at 13:17
  • @CraigRinger, ST_AsEWKB equals to plain geometry. ST_AsBinary is WKB (OGC format). Both of them is not pure binary, because they are hex strings [bytea in pgsql terms]. It's a large overhead to convert hex string to byte array evertime when you works with millions of records. Commented May 11, 2014 at 13:27
  • Looks like nPgSQL may not support the PostgreSQL binary protocol, so even if you use bytea functions like ST_AsEWKB it'll still do I/O via the bytea_output format, which is hex in newer PostgreSQL versions. Commented May 11, 2014 at 13:49

1 Answer 1

Reset to default
7

There are two things going on here. Pg uses the text I/O functions for datatypes by default, and even if you explicitly request bytea, the default protocol still exchanges bytea as text using the bytea_output mode.

PostgreSQL clients use the text-mode protocol by default when talking to PostgreSQL. When you SELECT a geometry column, PostgreSQL uses the text mode output function for the type, which is geometry_out. In PostGIS, this formats the data as hex for output, because the text protocol cannot transmit arbitrary binary so it can't send the raw on-disk format directly.

If you use functions like ST_AsBinary that return bytea, you still get hex output when using the default protocol. It's formatted like a PostgreSQL bytea literal as it's converted via the bytea_output setting into text for sending to the client, but it's still textual. That's because the PostgreSQL text protocol cannot carry arbitrary binary data without it being encoded, so the server converts it after the PostGIS function returns binary. Many client drivers automatically convert it back to binary on the client side, so you may not notice or care about this text translation, but it can be a performance issue for very high volume applications - which is why PostgreSQL supports a binary protocol mode.

If you enable the PostgreSQL binary protocol in your client, two things happen:

  • PostgreSQL uses geometry_send instead of geometry_out when you SELECT a geometry column. This sends the geometry as bytea; and

  • PostgreSQL sends bytea fields directly as bytes + length to the client in the binary protocol instead of converting them via bytea_output.

So what you need to do is enable the PostgreSQL binary protocol in your client. With libpq you'd use PQexecParams with resultFormat set.

I couldn't find any evidence that nPgSQL supports the binary protocol at all, unfortunately. It's a completely independent driver, not based on libpq, so it doesn't support all server and protocol features.

If you put the client in binary protocol mode you don't need to use ST_AsBinary or ST_AsEWKB, as geometry_send will deliver binary anyway.

If you aren't using the binary protocol there's no way to transfer un-encoded binary, because the text mode protocol just doesn't support it.

Under all circumstances the geometry data is still stored in a compact binary form on disk.

Improve this answer
6
  • You'll need to use ST_AsBinary or ST_AsEWKB. As I said at the beginning there is no way to change the default I/O form. A PostGIS-aware extension to nPgSQL could probably make this transparent to the user so you didn't have to care, but I'm not aware of any such extension. (Edited to add more detail). Commented May 11, 2014 at 13:44
  • thank you for great answer! I solved problem with ST_AsEWKB function and some magic, it's returns bytea, really, as you said. I'm fully understand situation now. I deleted my unuseful comments. Commented May 11, 2014 at 13:57
  • Actually, I'd say if you do a protocol dump with WireShark you'll find you're still exchanging the PostGIS data between client and server as text. It's just that nPgSQL is converting to/from hex for you automatically so you don't see it anymore. See simplified and clarified answer above. The short version is that if you truly feel you must exchange raw binary you must use the binary mode protocol, but it doesn't look like nPgSQL actually supports it. That said - I really doubt conversion to/from hex is really a significant performance impact... Commented May 11, 2014 at 14:08
  • Yeah, but was that your hex/binary conversion code, or the code in Pg and nPgSQL? It's possible to do hex/binary conversion extremely fast if it's done right. Commented May 11, 2014 at 14:13
  • You are right. 1. It's a text protocol (i57.tinypic.com/2ynpc2r.png); 2. Lots of measurements shows that my own hex string -> binary conversion is faster than Npgsql (pastebin.com/BxYsuttN). Commented May 11, 2014 at 14:34

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.