[postgis-devel] Re: geometry stats

[David Blasby]

> 	3) is there a test suite to help tuning the estimator ?
> 	   [ this is for Dave ]

Not really - I tested it by looking at the data in the histogram (to 
make sure the histogram was being calculated properly - see below), then 
looking at the actual stats (EXPLAIN ANALYSE) vs actual results.

I have *no* idea how well it will work based on a sample size of 3000.
You could have it do the old-style calculates as well as the new-style 
and have it spit out both results ( "elog(NOTICE,"...",..,..,..);").

There was some work done with random bounding boxes and checking the 
estimates, but I cannot find it.

SELECT count(*) FROM <table> WHERE the_Geom && <test bbox>
SELECT estimate_histogram(<histogram>, <test bbox>);


> 	5) how do we compose the bits we have: cell_area, box_area, AOI,
> 	   cell_value, avgFeaturesArea to get a nice picture ;) ?
> 
> 	5bis) how do we see the nice picture ? (see point 3)

The original stats package had an explode_histogram() function that 
would create a table with the contents of the histogram.  I used 
pgsql2shp and loaded the histogram and dataset into arc-view and checked 
the results.

If you use arc-view (or jump) you can colour the histogram polygons 
based on the # of items in the box - this makes it easy to view.  I used 
a roads dataset to test since its so highly skewed.  Turning both layers 
  on at the same time should show a dark (lots of geometries) box around 
the vicinity of the cities, and light-coloured in other areas.

//explode_histogram2d(histogram2d, tablename::text)
// executes CREATE TABLE tablename (the_geom geometry, id int, hits int, 
percent float)
// then populates it
//  DOES NOT UPDATE GEOMETRY_COLUMNS

The geometry is a polygon (representing the box).  Make sure you drop 
the exploded histogram table before you run the function or you'll get 
an error.

dave