Is it possible to get a binary geometry from PostGIS?

Question

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.

Answer 1

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.