Opened 4 years ago

Closed 4 years ago

#2870 closed defect (fixed)

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

Reported by: andrewdone Owned by: pramsey
Priority: medium Milestone: PostGIS 2.0.7
Component: postgis Version: 2.0.x
Keywords: Cc:

Description

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.

Attachments (2)

BinaryGeogTest.java (2.7 KB) - added by andrewdone 4 years ago.
2870.patch (881 bytes) - added by pramsey 4 years ago.

Download all attachments as: .zip

Change History (16)

Changed 4 years ago by andrewdone

Attachment: BinaryGeogTest.java added

comment:1 Changed 4 years ago by 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]

comment:2 Changed 4 years ago by 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.

comment:3 in reply to:  2 Changed 4 years ago by 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.

comment:4 Changed 4 years ago by robe

Milestone: PostGIS 2.1.4

comment:5 Changed 4 years ago by 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;

comment:6 Changed 4 years ago by 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?

comment:7 Changed 4 years ago by 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?

comment:8 Changed 4 years ago by 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]

comment:9 Changed 4 years ago by 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...

comment:10 Changed 4 years ago by 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;

Changed 4 years ago by pramsey

Attachment: 2870.patch added

comment:11 Changed 4 years ago by pramsey

Milestone: PostGIS 2.1.4PostGIS 2.0.7
Resolution: fixed
Status: newclosed
Version: 2.1.x2.0.x

Committed,

comment:12 Changed 4 years ago by robe

Resolution: fixed
Status: closedreopened

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.

comment:13 Changed 4 years ago by robe

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

comment:14 Changed 4 years ago by robe

Resolution: fixed
Status: reopenedclosed

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

Note: See TracTickets for help on using tickets.