[postgis-users] Input versus output geometry

[strk]

On Wed, Sep 01, 2010 at 01:42:39PM -0700, Alex Zepeda wrote:
> Recently I used pg_dump to export the output of a table with a geometry 
> column representing a bounding box.  There's an exclusion constraint on 
> that table such that rows with overlapping bounding boxes that contained 
> two other overlapping columns would be rejected.  As it turned out I 
> could insert the data, export it, but *not* play it back into another 
> instance of postgres as the exclusion constraint barfed.

Are you sure your constraint checked your input in the original table ?

> A bit of back and forth with some of the postgres guys revealed that the 
> output does not recreate the exact input.
> 
> Is this the intended behaviour?  If so, is there any way to extract a 
> more precise representation of the geometry column?

No, it's not intended for canonical output-input trip to loose anything.
Should not happen and would be a bad bad bug if it really happened.

The only reason I can think for bounding-box related functions
to behave differently for the same HEXWKB is that the internal
bounding box cache is corrupted (as that's not part of HEXWKB).
Try to add a dropbbox() call in yor ST_Expand call: 

SELECT
dropbbox(ST_expand(setsrid(makepoint(-122.50367,37.74189),4326), 0.00004)) &&
dropbbox(ST_expand(setsrid(makepoint(-122.50376,37.74185),4326), 0.00004));

--strk;


> This was with Postgres 9.0rc1 and Postgis 1.5.2 SVN r5851.
> gisttest2=# select 
> ST_expand(setsrid(makepoint(-122.50367,37.74189),4326), 0.00004);
> 
>                       st_expand 
> 
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 
> 0103000020E61000000100000005000000C32ADEC83CA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0ECF5EE8FF7DE424035D252793BA05EC0ECF5EE8FF7DE424035D252793BA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0D044D8F0F4DE4240
> (1 row)
> 
> gisttest2=# select 
> ST_expand(setsrid(makepoint(-122.50376,37.74185),4326), 0.00004);
> 
>                       st_expand 
> 
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 
> 0103000020E6100000010000000500000063EE5A423EA05EC042EC4CA1F3DE424063EE5A423EA05EC05E9D6340F6DE4240D595CFF23CA05EC05E9D6340F6DE4240D595CFF23CA05EC042EC4CA1F3DE424063EE5A423EA05EC042EC4CA1F3DE4240
> (1 row)
> 
> gisttest2=# select 
> ST_expand(setsrid(makepoint(-122.50367,37.74189),4326), 0.00004) && 
> ST_expand(setsrid(makepoint(-122.50376,37.74185),4326), 0.00004);
>  ?column?
> ----------
>  f
> (1 row)
> 
> gisttest2=# select 
> '0103000020E61000000100000005000000C32ADEC83CA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0ECF5EE8FF7DE424035D252793BA05EC0ECF5EE8FF7DE424035D252793BA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0D044D8F0F4DE4240'::geometry 
> &&
> gisttest2-# 
> '0103000020E6100000010000000500000063EE5A423EA05EC042EC4CA1F3DE424063EE5A423EA05EC05E9D6340F6DE4240D595CFF23CA05EC05E9D6340F6DE4240D595CFF23CA05EC042EC4CA1F3DE424063EE5A423EA05EC042EC4CA1F3DE4240'::geometry;
>  ?column?
> ----------
>  t
> (1 row)
> 
> - alex
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html