Binary insert into geography column results in value being inserted as geometry.

[andrewdone]

Hi,

Please see example (in Java) attached. 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).

— The result of step #4 is: ID: 1, WKB: 0101000020E610000009C6C1A5E3E662406BB75D68AEED40C0, Summary:Point[GS] ID: 2, WKB: 0101000020E610000009C6C1A5E3E662406BB75D68AEED40C0, Summary:Point[S] —

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 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

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

Cheers, Andrew.

[andrewdone]

Postgres log entry for the binary insert is:

LOG: execute : insert into px(pt) values($1)
DETAIL: parameters: $1 = '\x0101000020e610000009c6c1a5e3e662406bb75d68aeed40c0'

Output as SQL:

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

[pramsey]

You're seeing an error in the metadata, everything about the object should still be OK, so your library is going to be OK. I will fix the bug though.

[andrewdone]

Replying to pramsey:

You're seeing an error in the metadata, everything about the object should still be OK, so your library is going to be OK. I will fix the bug though.

Hi Paul,

It actually doesn't work correctly; if you can run that Java file you'll see that ID:2 (not geography) doesn't work as expected with the majority of spatial filters (st_dwithin, st_covers, st_coversby, etc). The st_summary case I presented was all I could come up with to try and ascertain that something was different about them.

I tried to dig into the postgis code to see what was actually different about them, but wasn't able to make good headway there. Sorry.

I'm pushing the library I'm building to just use WKT and rely on the canonical translation to geography for now — which works fine — but, to confirm, if I used binary transfer in the manner isolated above, the only thing that seemed to work was st_distance. In most cases, it wasn't a case of getting erroneous results back, it simply wouldn't get any results back at all.

So, yeah, my immediate problem is sorted but I'm more than happy to help in any way I can if you want to get to the bottom of it (and it'd be great to be able to use WKB).

Cheers,

• AD.

[robe]

should probably report back to stackexchange post when done for completeness — ​http://gis.stackexchange.com/questions/109186/binary-insert-into-postgis-geography-column-results-in-value-being-inserted-as-g

[pramsey]

Doing it by hand doesn't cause the problem

create table px (id SERIAL PRIMARY KEY, pt GEOGRAPHY(Point, 4326));
insert into px(pt) values(ST_GeographyFromText('SRID=4326;Point(151.215289 -33.856885)'));
insert into px(pt) values ('0101000020E610000009C6C1A5E3E662406BB75D68AEED40C0');
select id, pt, ST_Summary(pt) from px;

[pramsey]

Doing it with prepared statements doesn't seem to cause problems either:

drop table px;
create table px (id SERIAL PRIMARY KEY, pt GEOGRAPHY(Point, 4326));
prepare i1 as insert into px(pt) values(ST_GeographyFromText($1));
execute i1('SRID=4326;Point(151.215289 -33.856885)');
prepare i2 as insert into px(pt) values($1);
execute i2('0101000020E610000009C6C1A5E3E662406BB75D68AEED40C0');
select id, pt, ST_Summary(pt) from px;

Can you turn on PostgreSQL statement logging and capture the exact SQL that Java is sending into the database?

[robe]

This is interesting

I noticed if I add this

insert into px(pt) values ('0101000020E610000009C6C1A5E3E662406BB75D68AEED40C0'::geometry);

select id, pt, ST_Summary(pt) from px;

I get:

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

How come the one I create with geometry cast has a cached bounding box and the others don't?

[robe]

pramsey,

I was able to replicate what he had by doing this:

create table px (id SERIAL PRIMARY KEY, pt GEOGRAPHY(Point, 4326));
insert into px(pt) values(ST_GeographyFromText('SRID=4326;Point(151.215289 -33.856885)'));
insert into px(pt) values (decode('0101000020E610000009C6C1A5E3E662406BB75D68AEED40C0','hex'));
select id, pt, ST_Summary(pt) from px;

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

[pramsey]

Jeebus, why does that even work? So you turn it into a bytea and then somehow it morphs into a geometry and finds a code path that avoids even the typmod check…

[pramsey]

It's actually an error in the function bindings. The bytea⇒geography path is bound into the same function as the bytea⇒geometry path. Try this:

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

[pramsey]

Committed,

• 2.0 @ r12885
• 2.1 @ r12886
• trunk @ r12887

[robe]

I'm just playing the role of a whiny bastard, but I do believe you forgot to include a regression test to exercise this issue.

[robe]

Also noticed no change notice in sql script in place. Fixed and added regress at r12889 for PostGIS 2.2 (trunk).

[robe]

regress tests and change not added at r12890 for 2.1 and r12891 for 2.0