I have two overlapping polygon layers as shown in figure below. One with small grid polygons and another with a large irregular polygon. I want to join them such that attributes of the large polygon is joined to the each grid polygon whose centroid falls inside the large polygon. So in most cases it does but at the boundary it may not be so.
This can be accomplished for example in ArcGIS with spatial join where match criteria is Input feature "HAVE THEIR CENTER IN" join feature. However, since the data is huge and not manageable with ArcGIS I prefer to use PostGIS.
Is there any function in PostGIS which fulfills the match critera 'HAVE THEIR CENTER IN'. I could not find one so I tried the below query but it does not return any join result.
select g.*, p.ags, p.rs from grid_polygon as g left join large_polygon as p on st_intersects(st_centroid(g.shape),p.shape)
What can I do to correct this?