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) 

enter image description here

What can I do to correct this?

  • I regularly intersect tens of millions of features with ArcGIS, which is far more intensive than a spatial join, so I wonder about your reasoning, but I also use the result of such processing in PostgreSQL, and don't see any obvious error in your query. How did you load the tables, and what are their SRIDs? Note that ST_Centroid is fine for regular polygons, but you will need to use ST_PointOnSurface for concave shapes. – Vince Nov 14 '18 at 11:59
  • Your query works fine here. Maybe the two layers have a different CRS? (no join match and no error) – JGH Nov 14 '18 at 13:24
  • Nothing wrong with your query at all (as JGH says, maybe a CRS issue). One thing you can do to speed up such things is to create a functional index, in this case, on ST_Centroid(geom). ie, CREATE INDEX ix_spatial_grid_polygon ON grid_polygon USING GIST (ST_Centroid(geom)); – John Powell Nov 14 '18 at 14:47
  • yes! the issue seems to be with CRS/SRID. The layer with grid polygons has SRID 30001. In ArcGIS however it shows as 25832. And the one with a large polygon has SRID 25832. Now the grid polygon has geometry column of type st_geometry and i am not able to change the SRID in postgis with UpdateGeometrySRID or SetSRID or St_Transform. – Jio Nov 14 '18 at 14:57
  • 1
    You can, see the USING clause at the end of UpdateGeometrySRID – John Powell Nov 14 '18 at 15:55

as @Vince commented I would use ST_PointOnSurface instead of ST_Centroid for moon shaped/irregular polygons or if the shapes are multipolygons(which you should break up into singleparts if thats the case -- using ST_Dump). you should also calculate the point in a CTE rather than in the ST_Intersects/ST_Within/ST_Contains clause to not kill the index.

if you are having a problem with the SRID then check out this question PostGIS Shapefile Importer Projection SRID

WITH grid
  AS(SELECT *,ST_PointOnSurface(shape) point_geom FROM grid_polygon
SELECT g.*, p.ags, p.rs
FROM grid as g  
LEFT JOIN large_polygon as p
    ON ST_Intersects(g.point_geom,p.shape) 
| improve this answer | |
  • As the polygon in question is a grid, I think PointOnSurface is probably overkill. But the CTE point is a very valid. – John Powell Nov 14 '18 at 16:17
  • true to the grid,. ST_PointOnSurface does take longer to execute – ziggy Nov 14 '18 at 16:28
  • 1
    be careful with CTEs; AFAIK PostgreSQL still materializes the CTE statements, rendering any index on the thus queried relations useless. it also prevents the planner from optimizing the query; simple table selections should go into a subquery instead. in fact, there is nothing useful to do here with a CTE, or with indexes on the geom column, as the filter predicates a newly created geometry! using a functional index as @JohnPowell suggested would be among the few options. – geozelot Nov 15 '18 at 9:34
  • @ThingumaBob. I was just been explaining expression indexes and sargability to a colleague when you comment arrived :-). CTEs do have to be used with care, it is true, but they do have a case, even if sometimes, it is more readability than performance. – John Powell Nov 15 '18 at 10:17
  • 1
    @JohnPowell sure, e.g. if some more intense calculations have to be used in multiple places. data manipulation CTEs are pretty sweet. and readbility. but here, I doubt it has any benefit over a subquery, and I think it might actually decrease performance slightly, and with a functional index drastically. – geozelot Nov 15 '18 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.