[postgis-tickets] [PostGIS] #4463: PostGIS raster ST_Intersects regression with pg12

[PostGIS]

#4463: PostGIS raster ST_Intersects regression with pg12
---------------------+---------------------------
 Reporter:  robe     |      Owner:  Bborie Park
     Type:  defect   |     Status:  new
 Priority:  blocker  |  Milestone:  PostGIS 3.0.0
Component:  raster   |    Version:  trunk
 Keywords:           |
---------------------+---------------------------
 Our PostGIS raster still relies on inlining, so the costing we added to
 functions in PostgreSQL 12 is making raster performance poor as many uses
 of ST_Intersects (raster variant are no longer inlining)


 Need to change raster ST_Intersects functions to be support function aware


 Example from Paul's data:

 DEMS https://pub.data.gov.bc.ca/datasets/175624/92g/092g06_e.dem.zip
 https://pub.data.gov.bc.ca/datasets/175624/92g/092g06_w.dem.zip
 buildings https://data.vancouver.ca/datacatalogue/buildingFootprints.htm>



 {{{
 raster2pgsql -I -F -s 4269 -t 56x56 092g06_e.dem dem092g06e | psql
 yvr_raster
 shp2pgsql -s 26910 -D building_footprints_2009 buildings
 }}}


 {{{
 ALTER TABLE buildings
 ALTER COLUMN geom
 TYPE geometry(MultiPolygon, 4269)
 USING ST_Transform(ST_Force2D(geom), 4269);

 CREATE INDEX ix_buildings_geom_centroid ON buildings USING
 gist(ST_Centroid(geom));
 }}}


 --this doesn't use an index on PostgreSQL 12 (though it has option of a
 centroid functional index and a raster ST_ConvexHull(rast) index
 -- TODO: test on 11, we suspect it will be able to use the indexes

 {{{
 SELECT b.*
 FROM buildings b
 JOIN dem092g06e d
 ON ST_Intersects(ST_Centroid(b.geom), rast)
 WHERE ST_Value(d.rast, ST_Centroid(b.geom)) <= 30
 }}}

 The issue as RhodiumToad explained on IRC is the
 ST_Intersects(raster,geometry), geometry,raster  is still relying on
 _ST_Intersects which doesn't use the fancy function costing so is
 suffering from the high costs we've put in place.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4463>
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.