Translating, scaling, and rotating geometries – advanced

Often, in a spatial database, we are interested in making explicit the representation of geometries that are implicit in the data. In the example that we will use here, the explicit portion of the geometry is a single-point coordinate where a field survey plot has taken place. In the following screenshot, this explicit location is the red dot. The implicit geometry is the actual extent of the field survey, which includes 10 subplots arranged in a 5 x 2 array and rotated according to a bearing. These subplots are the purple squares in the following screenshot:

Getting ready

There are a number of ways for us to approach this problem. In the interest of simplicity, we will first construct our grid and then rotate it in place. Also, we could, in principle, use an ST_Buffer function in combination with ST_Extent to construct the squares in our resultant geometry. But, as ST_Extent uses floating-point approximations of the geometry for efficiency...