Find polygon in grid line network PostGIS

Question

I have a grid line table to use as the blade for splitting my polygon table in PostGIS with a ST_Split method. It works fine but now I want to find which parts of polygons are located within my grid.I thought about using ST_Centroid to find the centroid of split parts and then applying ST_Within. My question is: can I apply these two steps in one SQL query or should I compute centroid first and then use ST_whitin? Or is there anything else to do this? Maybe something like this:

SELECT 
    gridpoly.*
FROM
    (SELECT 
        st_centroid(gridpoly.geom) 
    FROM gridpoly) as center
JOIN 
    ( SELECT geom FROM gridline As cells WHERE col=1 AND row=2 ) as line
ON ST_Within(center.geom,line.geom)

The gridpoly table is the result of the split function which contains polygons. But in this code I don't have any column as 'center'.

Answer 1

To refer to the centroid geometry, you must name it otherwise the column is called st_centroid

st_centroid(gridpoly.geom) as geom

Note that you can't select gridpoly.* because gridpoly is not in the from clause. You can only use center.* and/or line.*. To get access to the gridpoly data, you need to join it. Maybe fetch the gridpolyID at the same time as the centroid so you can join on it.

At last, the query will likely return nothing as the polygon centroid is typically not within the cut line. It would work if you have cells (as polygons) instead of lines.

Answer 2

To find which polygon sections are within my grid line, I created another grid with polygon type and then used them to check polygon contained in my new grid network.

SELECT
    *
FROM
    gridpoly
JOIN
    ( SELECT * from gridpolygon WHERE col=1 AND row=2 ) as line
ON ST_Within(gridpoly.result, line.geom)