How to interpret a WKB (Well-known-binary)?

ST_GeomFromWKB() takes the a WKB as input data type bytea.

Not sure why your example uses the old escape string syntax for the string literal. Maybe because the examples in the manual still do so, too.

(The manual has finally been updated. Postgres 9.3 or later showa modern notation.)

It's not wrong, just unnecessarily noisy. Unless you are running a very old Postgres instance with the setting standard_conforming_strings = off, you can replace it with the modern, standard form:

SELECT ST_AsBinary(ST_GeomFromWKB('\001\001\000\000\000\321\256B\312O\304Q\300\347\030\220\275\336%E@', 4326));

The above is the escape format for a bytea literal. The manual advises:

... this format should probably be avoided for most new applications.

So, double "old-school" syntax. Alternatively, you can use the shorter hex format:

SELECT ST_AsBinary(ST_GeomFromWKB('\x0101000000d1ae42ca4fc451c0e71890bdde254540', 4326));

Related:

  • Insert varchar with single quotes in PostgreSQL

It's a type of binary encoding, which can be converted using encode and decode functions. There are several binary encodings, such as base64, hex, and escape (default). Most folks that show WKB as hex-encoded text, which uses digits 0-9 and characters A-F (upper or lower case).

Here is a demo, where all of the outputs are text:

SELECT
  encode(b, 'base64') AS encode_base64,
  encode(b, 'escape') AS encode_escape,
  encode(b, 'hex') AS encode_hex,
  ST_AsEWKT(encode(b, 'hex')::geometry)
FROM (
  SELECT '\001\001\000\000\000\321\256B\312O\304Q\300\347\030\220\275\336%E@'::bytea AS b
) d;

-[ RECORD 1 ]-+-------------------------------------------------------------------
encode_base64 | AQEAAADRrkLKT8RRwOcYkL3eJUVA
encode_escape | \x01\x01\000\000\000\321\256B\312O\304Q\300\347\x18\220\275\336%E@
encode_hex    | 0101000000d1ae42ca4fc451c0e71890bdde254540
st_asewkt     | POINT(-71.06737 42.29586)

Tags:

Postgis

Well Known Binary

Recent Posts