[GIS] Binary insert into Postgis geography column results in value being inserted as geometry

postgispostgis-2.0

When writing WKB as a value (and as a binary parameter) to a geography column, the persisted value is not geography.

Example Java is also available here: https://github.com/ayuudee/issue-pad/blob/master/src/com/jesusthecat/im/pggeog/BinaryGeogTest.java

Here's what it does:

  1. Creates a table with an ID and Geography(Point, 4326)
  2. inserts a row using WKT.
  3. inserts a row using WKB (as bytes).
  4. Prints out id, point, and ST_SUMMARY(point).

SQL result of step #4 is:

 id |                         pt                         | st_summary 
----+----------------------------------------------------+------------
  1 | 0101000020E610000009C6C1A5E3E662406BB75D68AEED40C0 | Point[GS]
  2 | 0101000020E610000009C6C1A5E3E662406BB75D68AEED40C0 | Point[S]

Example Log of the binary insert (where pt is Geography(Point, 4326))

LOG: execute : insert into px(pt) values($1)

DETAIL: parameters: $1 = '\x0101000020e610000009c6c1a5e3e662406bb75d68aeed40c0'

You'll notice that:

  1. The WKB for both #1 and #2 are the same; and
  2. The flags in the ST_Summary result for #1 are [GS], where for #2 (the binary) they're [S].

The documentation for ST_Summary would indicate that #2 has spatial information, but is not geodetic (i.e. not geography).

I'm writing a Java library that seeks to persist geography as binary, but this would seem to indicate that it isn't possible. Also, is it normal that this should be allowed to happen anyway (i.e. writing a value to a geography column that's not geography)?

Versions:

POSTGIS="2.1.3 r12547" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23
September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
LIBJSON="UNKNOWN" TOPOLOGY RASTER

POSTGRES PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro? 4.6.3-1ubuntu5) 4.6.3, 64-bit

Best Answer

Looks like there is an error in the geography(bytea) function that is bound to the bytea to geography cast. You can patch it temporarily like this:

CREATE OR REPLACE FUNCTION geography(bytea)
    RETURNS geography
    AS '$libdir/postgis-2.1','geography_from_binary'
    LANGUAGE 'c' IMMUTABLE STRICT;

Should be fixed in upcoming releases.

Related Question