Geek Answers Handbook

What is the "E" before the Postgres line?

I read the Postgres / PostGIS instruction as follows:

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

The above creates something from a well-known binary (WKB) file. I have not seen a specific citation method here where the string is single, with E preceding the beginning of the quote.

What is called this format? And what are the formatting rules for this? for example, is 336% E@ at the very end of the special, or just a binary value?

This is with Postgres9.3 / 9.4; PostGIS 2.1.

+6
source share
2 answers

According to PostgreSQL documentation http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html (highlighted by me)

PostgreSQL also accepts "escape" string constants, which are extensions to the SQL standard. The escape string constant is specified by writing the letter E (upper or lower case) immediately before the first quote , for example, E'foo. (When continuing the escape string constant line by line, write E only until the first start quote.) On the escape line, the backslash character () begins a C-like backslash escape sequence in which the backslash combination and the next character (s) represent special byte value

Using \\ in your line means that it escapes the escape sequence, probably to be safe in the path and stored in the .sql file. The string string actually passed to the ST_GeomFromWKB function will be:

 \001\001\000\000\000\321\256B\312O\304Q\300\347\030\220\275\336% E@

These sequences of 3 or 4 characters between the slash will then be directly interpreted by ST_GeoFromWKB .

The documentation for ST_GeoFromWKB ( http://postgis.org/docs/ST_GeomFromWKB.html ) states:

The ST_GeomFromWKB function takes a known binary representation of the geometry and the spatial reference system identifier (SRID) and creates an instance of the corresponding geometry type. This function acts as Factory geometry in SQL. This is an alternate name for ST_WKBToSQL .

Unfortunately, he does not indicate in what format, in fact, the “well-known binary representation” is actually.

It turns out that the contents of the line depend on the coordinate system used, which is set by the SRID parameter. In this case, 4326 corresponds to WGS84 : https://en.wikipedia.org/wiki/World_Geodetic_System#WGS84

You will need to continue reading and researching to unravel this.

+7
source

What you see is not like hex because the bytea string literal is in the escape string syntax (which is pretty dated now).

 E'\\001\\001\\000\\000\\000\\321\\256B\\312O\\304Q\\300\\347\\030\\220\\275\\336% E@ '

Same as the "standard match string":

 '\001\001\000\000\000\321\256B\312O\304Q\300\347\030\220\275\336% E@ '

Both are in the "escape format" , which can be represented more efficiently in the "hex format" as:

 '\x0101000000d1ae42ca4fc451c0e71890bdde254540'

You can use encode() and decode() to convert one form to another.

I answered your next gis.SE question with more details.

+2
source

More articles:


All Articles