Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

postgresql - What's the "E" before a Postgres string?

I was reading a Postgres/PostGIS statement like this:

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). I haven't seen the specific way of quoting here where the string is single quoted with a E preceding the beginning quote.

What is this format called? And what are the formatting rules for this? e.g. is the 336%E@ at the very end special or just some binary value?

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

As per the PostgreSQL documentation http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html (emphasis mine)

PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character () begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represent a special byte value

The use of \ in your string means that it's escaping an escape sequence, probably to be safe in transit and storage in a .sql file. The verbatim string actually passed into the ST_GeomFromWKB function will be:

0101000000321256B312O304Q30034730220275336%E@

These sequences of 3 or 4 characters between slashes would then be interpreted by ST_GeoFromWKB directly.

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

The ST_GeomFromWKB function, takes a well-known binary representation of a geometry and a Spatial Reference System ID (SRID) and creates an instance of the appropriate geometry type. This function plays the role of the Geometry Factory in SQL. This is an alternate name for ST_WKBToSQL.

Unfortunately it doesn't state what format, exactly, the "well-known binary representation" actually is.

It turns out that the content of the string depends on the coordinate system you're using, which is specified by the SRID parameter. In this case 4326 corresponds to WGS84: https://en.wikipedia.org/wiki/World_Geodetic_System#WGS84

You'll need to do further reading and research to untangle that.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...