StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>PostGIS doesn't use (only) the WKB standard when an SRID is present, it uses the <a href="http://postgis.refractions.net/docs/ch04.html#EWKB_EWKT" rel="nofollow">EWKB</a> format that supports 3d and SRIDs, which the <a href="http://portal.opengeospatial.org/files/?artifact_id=829" rel="nofollow">WKB specification</a> does not. Think of WKB as a kind of light binary 2d array, it is a very simple format. It doesnt include elevation or an SRID in the binary representation, in the OGC standard spatial reference systems are metadata. Here are some of the kinds of interactions that might shed light on how you work with WKB and EWKB:</p> <pre><code>moveable=> insert into my_table (some_other_geom) values (GeomFromText('POINT(4 5)')); moveable=> insert into my_table (some_other_geom) values (GeomFromText('POINT(4 5)', 4326)); </code></pre> <p>This gives you:</p> <pre><code>010100000000000000000010400000000000001440 0101000020E610000000000000000010400000000000001440 </code></pre> <p>as you saw. If you start with a hex value and want to add SRID projection and store as EWKB you do some variant of:</p> <pre><code>==>insert into my_table (some_other_geom) values (ST_GeomFromWKB(decode('010100000000000000000010400000000000001440', 'hex'), 4326)); INSERT 0 1 </code></pre> <p>or:</p> <pre><code>moveable=> insert into my_table (some_other_geom) values (ST_GeomFromWKB(decode('010100000000000000000010400000000000001440', 'hex'))); INSERT 0 1 </code></pre> <p>As you would expect these yield, respectively:</p> <pre><code>0101000020E610000000000000000010400000000000001440 010100000000000000000010400000000000001440 </code></pre> <p>where the null <code>-1</code> default SRID is the same as the non-SRID 2d WKB format. In general I think it is safer to work with WKT, GeoJSON, and KML, since they are also <strike>OGC</strike>(?)<sup>1</sup> open standards and the SQL version has some needed updates pending. As the PostGIS docs suggest: "PostGIS extended formats are currently superset of OGC one (every valid WKB/WKT is a valid EWKB/EWKT) but this might vary in the future, specifically if OGC comes out with a new format conflicting with our extensions. Thus you SHOULD NOT rely on this feature!"</p> <p>(1) Actually have no idea what the status of half these standards are, but json, text and xml seem likely to be around for the life of any given application.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload