I have multiple (381) high resolution GeoTIFF's (2x2 meter resolution, 1 band, 700 MB each) that I somehow wanna upload to a PostGIS-server to be able to run ST_intersects or ST_3Dintersects queries to. I need the resolution preserved since every 2x2 meter square holds a height value in meters that new buildings can have before they are visible from a number of cultural heritage places. And to be extra clear the desired intersect operation I wanna run is in the z-dimension, not only based on the coordinates. Ideally I would like to be able to create a polygon shape file with a z-attribute for a planned building and run a st_intersect query to see from which of the 381 points the new building would be visible from.
When asking about this at Stackexchange before, the question got closed because it was to broad(which it indeed was). One user questioned the need to load the raster's to postgis when the footprint of the image was all I needed. Since then I've been trying to understand what a raster footprint is, but I'm not sure I've figured it out yet:
I've tried to Polygonize a raster with QGIS (Raster>Conversion>Polygonize) which worked and stores the z-values I wanna use BUT the vector version of the 700 MB GeoTIFF takes up 4,33 GB of storage. So I'm guessing this approach is even worse then uploading the raster straight to PostGIS with raster2pgsql? So I'm guessing this is not what the user had in mind when suggesting footprints as a way forward?
I've also tried a plugin for QGIS called Image Footprint which from the plugin description sound like just the plugin I need:
This plugin create a catalog layer from directories of images, how a memory layer, where each feature has metadata and footprint of a image. See presentation in http://pt.slideshare.net/LuizMotta3/qgis-ibama-imagefootprint
The plugin gives me two options: 1. Bounding Box or, 2. Valid Pixels.
The bounding box-shape file is only 176 KB big which is handy, has two columns "filename" and "meta_html", but no values from the raster is present in the shape file which only contains one feature and some meta data about the area of valid pixels, number of holes, projection etc.
The Valid pixels-shape file is also 176 KB and has almost the same meta data except for more info about pixel size and extent.
But none of the above results from this plugin gives me any actual info from the 2x2 meter cells in the original raster. Although the attributes contain a path to where the actual raster is stored on the hard drive. So this can not be the way forward either?
My question is how would you store these high resolution GeoTIFF's on a PostGIS-server to be able to run the above mentioned st_intersects query with the fastest possible performance?
Server runs Ubuntu 17.10 with Postgresql 9.6.6/PostGIS 2.4.1 on a Intel i5-6600k CPU with 4 cores and 4 logical processors, 16 GB om physical RAM and a 1 TB SSD harddrive.
Metadata from one of the rasters:
