ST_Area(the_geom, false) and other geography functions may change column value

[fursten]

Points in polygon with abs(longitude) > 90 get their longitude mirrored over longitude 90 east or west.

An example:

select st_astext(areas)  from place where id = 362;
"MULTIPOLYGON(((-8.62650874577284 115.346832275391,-8.58441599597068 115.309066772461,-8.68013625652618 115.214309692383,-8.72629051135205 115.198516845703,-8.76225972524743 115.307693481445,-8.64348030291466 115.343399047852,-8.62650874577284 115.346832275 (...)"

But after executing

select st_area(p.areas, false) from place p where p.id=362;

The column value has changed.

select st_astext(areas)  from place where id = 362;
"MULTIPOLYGON(((-8.62650874577284 64.6531677246094,-8.58441599597068 64.6909332275391,-8.68013625652618 64.7856903076172,-8.72629051135205 64.8014831542969,-8.76225972524743 64.6923065185547,-8.64348030291466 64.6566009521484,-8.62650874577284 64.6531677246 (...)"

This has been observed on the two set-ups we have:

"POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="UNKNOWN" RASTER"

"PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit"

and

"POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="UNKNOWN" RASTER"

"PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit"

[robe]

Okay this is very disturbing. I was able to replicate the issue on my 2.1.3 install. I'll retest in 2.1.4. So something with the autocast from geometry to geography is doing an address pointer switch instead of a copy. A bug I presume in the ST_Area(geography) function.

CREATE TABLE test_weird AS SELECT 'MULTIPOLYGON(((-8.62650874577284 115.346832275391,-8.58441599597068 115.309066772461,-8.68013625652618 115.214309692383,-8.72629051135205 115.198516845703,-8.76225972524743 115.307693481445,-8.64348030291466 115.343399047852,-8.62650874577284 115.346832275391)))'::geometry  As areas;

SELECT ST_AsText(areas)
FROM test_weird;

-- outputs --
MULTIPOLYGON(((-8.62650874577284 115.346832275391,-8.58441599597068 115.309066772461,-8.68013625652618 115.214309692383,-8.72629051135205 115.198516845703,-8.76225972524743 115.307693481445,-8.64348030291466 115.343399047852,-8.62650874577284 115.346832275391)))

-- then run
SELECT ST_Area(areas, true)
FROM test_weird;
--outputs 77811189.1983376  (since it falls into the autocast to geography)

-- then run 
SELECT ST_AsText(areas)
FROM test_weird;

-- outputs 
MULTIPOLYGON(((-8.62650874577284 64.653167724609,-8.58441599597068 64.690933227539,-8.68013625652618 64.785690307617,-8.72629051135205 64.801483154297,-8.76225972524743 64.692306518555,-8.64348030291466 64.656600952148,-8.62650874577284 64.653167724609)))

[strk]

Unless ST_Area(geography, bool) [or whatever gets called] runs UPDATEs via SPI there's no way the underlying table data could change. This smells like a memory error to me.

Anyway, I could reproduce with POSTGIS="2.1.4dev r13042"

[robe]

Same issue on my

POSTGIS="2.2.0dev r13129" GEOS="3.5.0dev-CAPI-1.9.0 r4034" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="0.12" RASTER

I think it IS changing data as improbable as that seems. To test, I shut down my PostgreSQL service and restarted it and reran the ST_Astext and the same changed value showed.

[strk]

Then the first read was bogus ?

[robe]

yikes as I feared the problem is deeper (or at least at our geometry::geography caster). Same behavior with doing this:

DROP TABLE IF EXISTS test_weird; 
CREATE TABLE test_weird AS SELECT 'MULTIPOLYGON(((-8.62650874577284 115.346832275391,-8.58441599597068 115.309066772461,-8.68013625652618 115.214309692383,-8.72629051135205 115.198516845703,-8.76225972524743 115.307693481445,-8.64348030291466 115.343399047852,-8.62650874577284 115.346832275391)))'::geometry  As areas;

SELECT ST_AsText(areas)
FROM test_weird;
-- yields --
MULTIPOLYGON(((-8.62650874577284 115.346832275391,-8.58441599597068 115.309066772461,-8.68013625652618 115.214309692383,-8.72629051135205 115.198516845703,-8.76225972524743 115.307693481445,-8.64348030291466 115.343399047852,-8.62650874577284 115.346832275391)))

SELECT ST_Distance(areas,areas, true)
FROM test_weird;
--returns 0

SELECT ST_AsText(areas)
FROM test_weird;

-- yields --
MULTIPOLYGON(((-8.62650874577284 64.653167724609,-8.58441599597068 64.690933227539,-8.68013625652618 64.785690307617,-8.72629051135205 64.801483154297,-8.76225972524743 64.692306518555,-8.64348030291466 64.656600952148,-8.62650874577284 64.653167724609)))

[robe]

okay yap its at our caster watch this:

{{{DROP TABLE IF EXISTS test_weird; CREATE TABLE test_weird AS SELECT 'MULTIPOLYGON(((-8.62650874577284 115.346832275391,-8.58441599597068 115.309066772461,-8.68013625652618 115.214309692383,-8.72629051135205 115.198516845703,-8.76225972524743 115.307693481445,-8.64348030291466 115.343399047852,-8.62650874577284 115.346832275391)))'::geometry As areas;

SELECT ST_AsText(areas) FROM test_weird;

-- outputs -- MULTIPOLYGON(((-8.62650874577284 115.346832275391,-8.58441599597068 115.309066772461,-8.68013625652618 115.214309692383,-8.72629051135205 115.198516845703,-8.76225972524743 115.307693481445,-8.64348030291466 115.343399047852,-8.62650874577284 115.346832275391)))

-- now for the eh magic -- SELECT areas::geography from test_weird;

-- followed by -- SELECT ST_Astext(areas) from test_weird;

-- yikes MULTIPOLYGON(((-8.62650874577284 64.653167724609,-8.58441599597068 64.690933227539,-8.68013625652618 64.785690307617,-8.72629051135205 64.801483154297,-8.76225972524743 64.692306518555,-8.64348030291466 64.656600952148,-8.62650874577284 64.653167724609)))

}}}

[pramsey]

Fixed in 2.1 at r13149, trunk at r13150. 2.0 did not have wrap-around behavior