1

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'.

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.

  • Thanks, @JGH. You are right, the result of ST_Whitin is always false. So how can I find polygons in my grid? – nasser tahani Jul 6 '17 at 13:44
  • You can use ST_Envelope(ST_Collect(line.geom)) to create a polygon with the bounding box of your lines. This should work if they form a regular closed grid. Otherwise, look at ST_Polygonize(). – amball Jul 9 '17 at 0:43
0

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)

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.