PostgreSQL: I have a PostGIS multipolygon table containing millions of features. It covers 95% of the country, however there are gaps between individual polygons. How can I fill these gaps with new polygons? Preserving and not changing the existing features.

See image below. I’d want to fill in that gap in the middle. And all the slithers and smaller polys on the right (potentially ignoring very small polys below an area of Xm2). Afterwards I can dice the shape up for better performance if it’s too big.

note: these empty areas may very well extend all the way to the coast. I’d need to use the outline of the country to restrict this operation. I don’t need new polygons appearing over the sea!

EDIT:

So the blue polys below are the shapes I’d like to create in this example

Geographic Information Systems Asked on November 14, 2021

2 AnswersSo my colleague came up with this solution. It works great for small areas, but I'd like to find a solution for millions of shapes across an entire country. I can see the ST_UNION here causing a blockage in this respect. The ST_BUFFER is just to close out thin slithers.

```
SELECT ST_DIFFERENCE(foo.geom, bar.geom)
FROM (SELECT ST_CONVEXHULL(ST_COLLECT(shape::geometry)) as geom FROM schema.polytable) as foo,
(SELECT ST_BUFFER(ST_UNION(shape),0.5) as geom FROM schema.polytable) as bar
```

result:

If anyone has suggestions for larger tables, I'm all ears.

**UPDATE**: I have found a solution for the entire country whereby I execute a similar process to that above, but using a gridded version of the country and iterating through each grid using ST_Intersect.

(optional) Before we start we may want to make a grid that does not extend beyond the country outline. So we'll take the entire 25x25km square grid table and a simple outline polygon of the country, then create a new table using `SELECT (ST_DUMP(ST_INTERSECTION(a.geom,b.geom))).geom as geom`

to produce:

standard grid or country outline defined grid, we can then use:

```
SELECT ST_SUBDIVIDE(ST_DIFFERENCE(a.geom, b.geom)) as geom
FROM
(SELECT ST_BUFFER(ST_UNION(b.geom),0.5) as geom
FROM schema.polytable b, schema.gridtable a
WHERE ST_INTERSECTS(b.geom,a.geom) AND a.grid_id = [use id number as a iteration variable here]) as b, schema.gridtable a
WHERE a.grid_id = [use the same id number as a iteration variable here];
```

So slightly different from the previous SQL statement. There's no need for ST_CONVEXHULL this time as we're using a square grid to contain the output. Also again we use ST_BUFFER 0.5 to remove any thin inter-polygon slithers from the output. For better rendering and performance of the output, we use ST_SUBDIVIDE to divide up the resulting, and potentially huge, multipart polygon.

I need to put this in a python pipeline using the psycopg2 library, then I'll post the results on here. Testing on one grid (out of 500) takes 30secs. So could be 4 hours to run in total.

Answered by Theo F on November 14, 2021

this code will fill the gaps and holes in the polygons. adjust according to your data

```
SELECT id, ST_Collect(ST_MakePolygon(geom)) As geom
FROM (
SELECT gid, ST_ExteriorRing((ST_Dump(geom)).geom) As geom
FROM layer
) s
GROUP BY id
```

Answered by ziggy on November 14, 2021

1 Asked on September 17, 2020 by gisgis

1 Asked on September 17, 2020 by developerman1234

3 Asked on September 17, 2020 by megan

2 Asked on September 16, 2020 by robert-buckley

2 Asked on September 16, 2020 by iowf

1 Asked on September 16, 2020 by doubtful_noob

1 Asked on September 16, 2020 by cdr815

1 Asked on September 16, 2020 by joseph-nolan

2 Asked on September 15, 2020 by the-phd-gamer

1 Asked on September 15, 2020 by vincent-br

0 Asked on September 14, 2020 by raghav

1 Asked on September 14, 2020 by h-salman

3 Asked on September 14, 2020 by franois-leblanc

0 Asked on September 14, 2020 by kelsey

3 Asked on September 13, 2020 by staley

1 Asked on September 12, 2020 by ivo

Get help from others!

Recent Questions

- Computing the matrix differential/derivative of the matrix$rightarrow$scalar function $log det(BCB^T)$
- Characteristic function of random variable is always integrable
- Invariant $SU(3)$ subgroup for ${bf 8}$ in ${bf 3}^* otimes {bf 3} ={bf 1} oplus {bf 8}$
- Proof that a continuous function with continuous right derivatives is differentiable.
- Finding the volume when a parabola is rotated about the line $y = 4$.

Recent Answers

- user10354138 on Proof that a continuous function with continuous right derivatives is differentiable.
- greg on Computing the matrix differential/derivative of the matrix$rightarrow$scalar function $log det(BCB^T)$
- Kavi Rama Murthy on Characteristic function of random variable is always integrable
- N. Bar on Finding the volume when a parabola is rotated about the line $y = 4$.
- Gavin Ball on Invariant $SU(3)$ subgroup for ${bf 8}$ in ${bf 3}^* otimes {bf 3} ={bf 1} oplus {bf 8}$

© 2021 InsideDarkWeb.com. All rights reserved.