estimated extent and EPSG:5514

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

estimated extent and EPSG:5514

Martin Landa
Hi all,

I got strange estimated extent when working with data in S-JTSK
(EPSG:5514). ST_EstimatedExtent() returns completely wrong extent:

select st_estimated_extent('ruian', 'kraje', 'geom');
BOX(-909107.8125 -1233432.375,4522.92431640625 6136.4794921875)

The correct extent is:

select st_extent(geom) from ruian.kraje;
BOX(-904584.86 -1227295.83,-431726.85 -935236.59)

It's visible that East and North coordinates are completely wrong.
This is also probably reason why 'Zoom to layer' in QGIS do not work
properly.

Any idea? Thanks a lot! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Paul Ramsey
Estimated extent works by looking at the table stats. If you had some extra data and then delete it, the stats might still reflect the original extent of the full table. Re-running ANALYZE might refresh them. 

On Mon, Nov 21, 2016 at 5:07 AM, Martin Landa <[hidden email]> wrote:
Hi all,

I got strange estimated extent when working with data in S-JTSK
(EPSG:5514). ST_EstimatedExtent() returns completely wrong extent:

select st_estimated_extent('ruian', 'kraje', 'geom');
BOX(-909107.8125 -1233432.375,4522.92431640625 6136.4794921875)

The correct extent is:

select st_extent(geom) from ruian.kraje;
BOX(-904584.86 -1227295.83,-431726.85 -935236.59)

It's visible that East and North coordinates are completely wrong.
This is also probably reason why 'Zoom to layer' in QGIS do not work
properly.

Any idea? Thanks a lot! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Martin Landa
Hi,

2016-11-21 15:41 GMT+01:00 Paul Ramsey <[hidden email]>:
> Estimated extent works by looking at the table stats. If you had some extra
> data and then delete it, the stats might still reflect the original extent
> of the full table. Re-running ANALYZE might refresh them.

I am afraid that this not this case. This data has been imported and
not touched in DB. ANALYZE will not help.

ANALYZE ruian.kraje ;
select st_estimated_extent('ruian', 'kraje', 'geom');
                       st_estimated_extent
-----------------------------------------------------------------
 BOX(-909107.8125 -1233432.375,4522.92431640625 6136.4794921875)

Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Paul Ramsey
What does _postgis_stats( 'ruian.kraje', 'geom') do?

On Mon, Nov 21, 2016 at 6:49 AM, Martin Landa <[hidden email]> wrote:
Hi,

2016-11-21 15:41 GMT+01:00 Paul Ramsey <[hidden email]>:
> Estimated extent works by looking at the table stats. If you had some extra
> data and then delete it, the stats might still reflect the original extent
> of the full table. Re-running ANALYZE might refresh them.

I am afraid that this not this case. This data has been imported and
not touched in DB. ANALYZE will not help.

ANALYZE ruian.kraje ;
select st_estimated_extent('ruian', 'kraje', 'geom');
                       st_estimated_extent
-----------------------------------------------------------------
 BOX(-909107.8125 -1233432.375,4522.92431640625 6136.4794921875)

Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Martin Landa
Hi,

2016-11-21 15:52 GMT+01:00 Paul Ramsey <[hidden email]>:
> What does _postgis_stats( 'ruian.kraje', 'geom') do?

select _postgis_stats( 'ruian.kraje', 'geom');
_postgis_stats |
{"ndims":2,"size":[1,1],"extent":{"min":[-909108,-1.23343e+06],"max":[4522.92,6136.48]},"table_features":8,"sample_features":8,"not_null_features":8,"histogram_features":8,"histogram_cells":1,"cells_covered":8}

similar stuff as st_estimatedextent(). Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Sandro Santilli-4
On Mon, Nov 21, 2016 at 04:28:34PM +0100, Martin Landa wrote:
> Hi,
>
> 2016-11-21 15:52 GMT+01:00 Paul Ramsey <[hidden email]>:
> > What does _postgis_stats( 'ruian.kraje', 'geom') do?
>
> select _postgis_stats( 'ruian.kraje', 'geom');
> _postgis_stats |
> {"ndims":2,"size":[1,1],"extent":{"min":[-909108,-1.23343e+06],"max":[4522.92,6136.48]},"table_features":8,"sample_features":8,"not_null_features":8,"histogram_features":8,"histogram_cells":1,"cells_covered":8}

Histogram cells 1 and sample features 8 ?

How many rows do you have in that table ?
Did you change stat targets for the column ?

 SELECT attstattarget
   FROM pg_attribute
  WHERE attrelid = 'ruian.kraje'::regclass
    AND attname = 'geom';

--strk;
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Martin Landa
Hi,

2016-11-21 16:38 GMT+01:00 Sandro Santilli <[hidden email]>:
> How many rows do you have in that table ?

count | 8

> Did you change stat targets for the column ?

No, AFAIK.

>  SELECT attstattarget
>    FROM pg_attribute
>   WHERE attrelid = 'ruian.kraje'::regclass
>     AND attname = 'geom';

attstattarget | -1

Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Sandro Santilli-4
On Mon, Nov 21, 2016 at 04:54:30PM +0100, Martin Landa wrote:

> Hi,
>
> 2016-11-21 16:38 GMT+01:00 Sandro Santilli <[hidden email]>:
> > How many rows do you have in that table ?
>
> count | 8
>
> > Did you change stat targets for the column ?
>
> No, AFAIK.
>
> >  SELECT attstattarget
> >    FROM pg_attribute
> >   WHERE attrelid = 'ruian.kraje'::regclass
> >     AND attname = 'geom';
>
> attstattarget | -1

show default_statistics_target; -- I guess 100 ?

The only thing I can think of is that the "hard deviant removal" phase
is considering the geom in the upper-right corner as hard-deviant, as
they are beyond the 90% of total extent. Are them points ?

--strk;
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Martin Landa
Hi,

2016-11-21 17:00 GMT+01:00 Sandro Santilli <[hidden email]>:
> show default_statistics_target; -- I guess 100 ?

default_statistics_target | 50

> The only thing I can think of is that the "hard deviant removal" phase
> is considering the geom in the upper-right corner as hard-deviant, as
> they are beyond the 90% of total extent. Are them points ?

No, polygons. I will prepare reproducible example. Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Sandro Santilli-4
On Mon, Nov 21, 2016 at 05:06:25PM +0100, Martin Landa wrote:
> Hi,
>
> 2016-11-21 17:00 GMT+01:00 Sandro Santilli <[hidden email]>:
> > show default_statistics_target; -- I guess 100 ?
>
> default_statistics_target | 50

Try setting them to 100, which is the default.
Then ANALYZE again, and re-run

 select _postgis_stats( 'ruian.kraje', 'geom');

Paul: the "histogram_cell:1" seems to be bogus in the code in any
case, as it looks it's always set to "1", used as a flag rather than
as an actual grid size ?

--strk;
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Sandro Santilli-4
On Mon, Nov 21, 2016 at 05:11:23PM +0100, Sandro Santilli wrote:
>
> Paul: the "histogram_cell:1" seems to be bogus in the code in any
> case, as it looks it's always set to "1", used as a flag rather than
> as an actual grid size ?

On further reading, it's only set to 1 when something "goes wrong".
But generally, it's set based on the distribution of values and
using the highest dimension rather than the most variated ?

Martin: is the index a 2D index or ND one ?

--strk;
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Martin Landa
Hi,

2016-11-21 17:15 GMT+01:00 Sandro Santilli <[hidden email]>:

hm, I tested simplified workflow on personal computer with GDAL 2.2dev
and PostGIS 2.4:

$ wget http://vdp.cuzk.cz/vymenny_format/soucasna/20161031_ST_UKSH.xml.gz
$ createdb test_5514
$ psql test_5514 -c"create extension postgis"
$ wget https://epsg.io/5514.sql
$ psql test_5514 -f 5514.sql
$ ogr2ogr -f PostgreSQL PG:dbname=test_5514 20161031_ST_UKSH.xml.gz kraje
$ psql test_5514 -c"select st_extent(originalnihranice) from kraje"
 BOX(-904584.86 -1227295.83,-431724.3 -935236.59)
psql test_5514 -c"vacuum analyze kraje"
psql test_5514 -c"select st_estimatedextent('kraje', 'originalnihranice')"
 BOX(-906949.1875 -1228756.125,-429359.96875 -933776.25)

Then on the server (where I found this issue) I am able to reproduce the error:

sudo psql test_5514 -U postgres -c"select st_estimatedextent('kraje',
'originalnihranice')"
                       st_estimatedextent
-----------------------------------------------------------------
 BOX(-909107.8125 -1233432.375,4522.92431640625 6136.4794921875)

On the server I have GDAL 2.1.2 and PostgreSQL 9.1, PostGIS 2.1.1.

Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Martin Landa
In reply to this post by Sandro Santilli-4
Hi,

2016-11-21 17:11 GMT+01:00 Sandro Santilli <[hidden email]>:
> Try setting them to 100, which is the default.
> Then ANALYZE again, and re-run
>
>  select _postgis_stats( 'ruian.kraje', 'geom');

I didn't seems to help:

set default_statistics_target=100;
vacuum analyze kraje;

Then

select _postgis_stats( 'kraje', 'originalnihranice');

still return wrong extent. Thanks for support, Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Sandro Santilli-4
In reply to this post by Martin Landa
On Tue, Nov 22, 2016 at 09:29:14AM +0100, Martin Landa wrote:

> Hi,
>
> 2016-11-21 17:15 GMT+01:00 Sandro Santilli <[hidden email]>:
>
> hm, I tested simplified workflow on personal computer with GDAL 2.2dev
> and PostGIS 2.4:
>
> $ wget http://vdp.cuzk.cz/vymenny_format/soucasna/20161031_ST_UKSH.xml.gz
> $ createdb test_5514
> $ psql test_5514 -c"create extension postgis"
> $ wget https://epsg.io/5514.sql
> $ psql test_5514 -f 5514.sql
> $ ogr2ogr -f PostgreSQL PG:dbname=test_5514 20161031_ST_UKSH.xml.gz kraje
> $ psql test_5514 -c"select st_extent(originalnihranice) from kraje"
>  BOX(-904584.86 -1227295.83,-431724.3 -935236.59)
> psql test_5514 -c"vacuum analyze kraje"
> psql test_5514 -c"select st_estimatedextent('kraje', 'originalnihranice')"
>  BOX(-906949.1875 -1228756.125,-429359.96875 -933776.25)
>
> Then on the server (where I found this issue) I am able to reproduce the error:
>
> sudo psql test_5514 -U postgres -c"select st_estimatedextent('kraje',
> 'originalnihranice')"
>                        st_estimatedextent
> -----------------------------------------------------------------
>  BOX(-909107.8125 -1233432.375,4522.92431640625 6136.4794921875)
>
> On the server I have GDAL 2.1.2 and PostgreSQL 9.1, PostGIS 2.1.1.

Is "originalnihranice" a raster column ? If not, forget about GDAL
version. Can you reproduce locally by upgrading to PostGIS 2.4 ?

--strk;
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Martin Landa
Hi,

2016-11-22 10:08 GMT+01:00 Sandro Santilli <[hidden email]>:
> Is "originalnihranice" a raster column ? If not, forget about GDAL

no, it's vector data:

select type from geometry_columns where f_geometry_column='originalnihranice'
--------------
 MULTIPOLYGON

> version. Can you reproduce locally by upgrading to PostGIS 2.4 ?

I am afraid that it will be not so easy. It's production server with
very old OS (Ubuntu 12.04) and moreover I am preparing major ugrade to
Ubuntu 16.04 in few weeks.

Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Sandro Santilli-4
On Tue, Nov 22, 2016 at 10:24:15AM +0100, Martin Landa wrote:
>
> I am afraid that it will be not so easy. It's production server with
> very old OS (Ubuntu 12.04) and moreover I am preparing major ugrade to
> Ubuntu 16.04 in few weeks.

So maybe you could try upgrading your 2.1.1 on the server to 2.1.8,
which was released on July 2015. Reading the NEWS file I find, in
the 2.1.2 section:

   - #2615, EstimatedExtent (and hence, underlying stats) gathering
     wrong bbox

--strk;
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: estimated extent and EPSG:5514

Martin Landa
Hi,

2016-11-22 11:05 GMT+01:00 Sandro Santilli <[hidden email]>:
> So maybe you could try upgrading your 2.1.1 on the server to 2.1.8,
> which was released on July 2015. Reading the NEWS file I find, in
> the 2.1.2 section:
>
>    - #2615, EstimatedExtent (and hence, underlying stats) gathering
>      wrong bbox

oh, thanks - upgrade to 2.1.8 helped! Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users