[postgis-tickets] [PostGIS] #3669: make raster max extent constraint apply faster at some lose of correctness

[PostGIS]

#3669: make raster max extent constraint apply faster at some lose of correctness
--------------------+---------------------------
 Reporter:  robe    |      Owner:  robe
     Type:  defect  |     Status:  new
 Priority:  medium  |  Milestone:  PostGIS 2.2.4
Component:  raster  |    Version:  2.3.x
 Keywords:          |
--------------------+---------------------------
 In #3501 we determined that ST_Extent solves the issue of max array sizes
 when computing the constraint extent for a raster table.

 It conveniently is just as fast as the old way of ST_Collect(..).

 The problem is ST_Extent returns a box and not a geometry and
 raster_columns needs a geometry.

 So the easiest both programmatically and performance wise solution is to
 just set the SRID ST_SetSRID(ST_Extent(,first_srid_in_table)).


 That works all fine in at least 90% of cases.  The issue arises is if you
 have a raster table with mixed set of srids.

 The most correct thing to do would be to not to set a extent constraint at
 all in the mixed srid case.

 However to determine that you do have mixed would require inspecting each
 row to determine there is only one srid.

 With a query something like:

 {{{
 SELECT MAX(ST_SRID(rast))
 FROM some_table
 HAVING COUNT(DISTINCT ST_SRID(rast) ) = 1;
 }}}


 This query is much more costly than:


 {{{
 SELECT ST_SRID(rast)
 FROM some_table
 LIMIT 1;
 }}}

 So in r15243, I took the easier, but granted  (some like strk would say
 less correct way).

 I also set the constraint to NOT VALID, because for a big table, do we
 really need to check if we violated the constraint we formulated from
 taking the extent of the data. And future would be checked even when
 constraint is marked as not valid.

 Even in a mixed srid case, the constraint would not be violated.

 Interestingly I noticed, the SRID constraint and some other do need the
 default VALID check.  Reason being is they take the lazy way too.

 SRID extent, simply tries to apply the SRID constraint based on the first
 raster tile.  and the constraint gracefully fails in the check process if
 you've got mixed and doesn't get created.  Same with some others.

 In this particular case, the only way the constraint would ever fail is if
 the extent fails to get created because the extent coordinates are out of
 bounds of the srid we happened to pick.

 So what to do?  Does anyone care that if they have a mixed back of spatial
 ref rasters, they get an extent which kinda works, but may not reflect the
 true mixed upness of the spatial ref of the table?

 By kinda works I mean the checking done with @ ( geometry_within function)
 from what I can tell just looks at boxes and dispenses with the srid
 anyway.

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3669>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.