[postgis-users] postgis 2.0 and postgist 2.1 compatibility issues

[Marty O'Neill II]

Thank you for your replies to our query. It does look like the problem
is in the estimated extent. There is a near-zero coordinate in the
estimated extent that is causing the problem. We tried running VACUUM
ANALYZE VERBOSE, but it didn't correct the problem. I've pasted output
from before and after this step here:

------------------------------------------------------
BEFORE VACUUM ANALYZE

>> select st_estimatedextent('public','denton_roads','the_geom');
BOX(-97.8841247558594 32.9822883605957,0.486985713243484 33.427604675293)

>> select st_extent(the_geom) from denton_roads;
BOX(-97.3971400071755 32.9844960542985,-96.8340784514035 33.425398016471)

>> Vacuum analyze verbose denton_roads;
INFO:  "denton_roads": scanned 116 of 116 pages, containing 1856 live
rows and 0 dead rows; 1856 rows in sample, 1856 estimated total rows
Time: 0.071ms
Affected rows: 0

AFTER VACUUM ANALYZE

>> select st_estimatedextent('public','denton_roads','the_geom');
BOX(-97.8841247558594 32.9822883605957,0.486985713243484 33.427604675293)

>> select st_extent(the_geom) from denton_roads;
BOX(-97.3971400071755 32.9844960542985,-96.8340784514035 33.425398016471)
------------------------------------------------------

We are running PostGIS 2.1.0. Here is the full result of the
PostGIS_Full_Version query:
POSTGIS="2.1.0 r11822" 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

Do you have any other suggestions for us? I'm going to try
reinstalling the OS and the database to see if the problem persists or
if something was corrupted during the current installation. I'll let
you know the outcome of the reinstallation.

Thank you,

Marty



Original Message >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Marty,

Which version of PostGIS 2.1 are you running 2.1.0 or 2.1.1?

Do a: SELECT postgis_full_version();


Your example of two seemingly equivalent table creations resulting in
different results is a bit troubling.

When you say extent seems to be an issue but ST_Extent is not, I am assuming
you are confirming this in QGIS or other software.  The only thing I could
think of
Is that the ST_EstimatedExtent answer might be different and I think various
GIS software use that for extent estimation.

Can you check the answer of ST_EstimatedExtent in both problem and
non-problem case

http://postgis.net/docs/manual-2.1/ST_Estimated_Extent.html

If they are different, try running

Vacuum analyze verbose new_roads2;


Hope that helps,
Regina
http://www.postgis.us
http://postgis.net



-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Marty O'Neill II
Sent: Thursday, December 12, 2013 6:28 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] postgis 2.0 and postgist 2.1 compatibility issues

I am running postgresql 9.1 with postgis 2.0 in Windows 7. After creating a
dump from the database, I tried to restore on a machine running Ubuntu with
postgresql 9.3 and postgis 2.1. However, the spatial extent is incorrect
when viewed in QuantumGIS and in other custom GIS software. The spatial
extent in QuantumGIS and our software seems to include a 0.0 coordinate.
However, running ST_Extent on one of the problem tables does not reveal any
such issues. There are no empty records. We have tried PostGIS_DropBBox(),
Populate_Geometry_Columns(), and reindexing the tables. We have tried
generating the dump and restoring it both from pgAdmin and using the command
line.  The projections are correctly set on all records, and the tables have
valid entries in the geometry_columns view.

Let roads be one of the problematic tables. If we execute the following
query

SELECT * INTO new_roads FROM roads;

... then the problem with the spatial extent does not exist on table
new_roads. However, if we execute the query

SELECT * INTO new_roads2 FROM (SELECT * FROM roads) AS temp;

.. then the problem continues to exist on table new_roads2

We suspect that the issue may be due to compatibility issues between PostGIS
2.0 and 2.1. Does anybody have any ideas on why this is happening?

Thank you,

Marty