4

I read this example of a WKB (well know binary) from "PostGIS in Action" 2ed.:

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

I understand the E'..' is an escape string, and the escaped backslash is a separator. But I don't understand the rest of the well-known binary.

Can anyone explain what are the numbers and letters?

They do not look like hexidecimal according to wikipedia consider e.g. 304Q. Also the 336%E@ looks weird.

10

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:

2

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.