The most common need for extraction of some raster data from larger dataset is to reduce computing time. Let's look into spatial relationship and extraction functions in PostGIS. For getting results in raster space, we should use ST_Clip() and ST_Intesection() for vector space results.

In the previous example, we extracted one raster cell value for vector point. Now, we need to query for multiple point values. There we should use the ST_Intersection() function. As a standard, this function returns geoval type in vector space, that we need to join with our vector points. Let's look at an example:

SELECT  foo.name, 
  (foo.geomval).val AS height 
FROM ( 
  SELECT 
    ST_Intersection(A.rast, g.geom) As geomval, 
    g.name 
  FROM eudem.clip AS A 
  CROSS JOIN ( 
    SELECT geom, name FROM raster_ops.places WHERE type='village' 
  ) As g(geom, name) 
  WHERE A.rid = 4 
) As foo; 
    name    |      height       
------------+------------------ 
 Koniaków   |           742.25 
 Jaworzynka | 629.849975585938 
 Istebna    | 661.260009765625 
(3 rows) 

Here, (geomval).val syntax is used, as it is composite data type, and in our case we don't need feature geometry.

This function is very computing expensive, as it converts raster coverage with DumpAsPolygons, and intersection is checked in vector space. For that reason, we should restrict raster extent or even clip it to vector AOI envelope. In my case, it was 51 seconds for relative small DEM tile. How to do it? As mentioned earlier, ST_Clip() comes to the rescue. Take a look at this:

SELECT ST_Clip(rast::raster, 1, (SELECT geometry FROM raster_ops.admin LIMIT 1),true) FROM eudem.dem d; 

The query resulted in 80 tiles, 79 blank, and 43 seconds execution time. For sure, we must use some spatial extent filtering. Most query execution time effective is && (bounding box).

SELECT ST_Clip(rast::raster, 1, (SELECT geometry FROM raster_ops.admin LIMIT 1),true) FROM eudem.dem d  WHERE (SELECT geometry FROM raster_ops.admin) && d.rast; 

Better, 1 row and 0.8s execution. Let's look into resulting clipped raster. But, if you look at the following figure, you will see that everything outside of admin polygon is filled with NODATA.

SELECT ST_Clip(rast::raster, 1, (SELECT ST_Envelope(geometry) FROM raster_ops.admin LIMIT 1),true) 
   FROM eudem.dem d   
   WHERE (SELECT geometry FROM raster_ops.admin) && d.rast; 

When we use ST_Envelope, not a natural border polygon, execution time is even better (0.5s in our case).