Recently I was faced with 2D-geometries that should be “spatially clean” but they weren’t. Postgres/Postgis to the rescue, the data could be cleaned and the problem was solved.

“Spatially clean” in the project meant:

- no geometry should overlap another geometry
- the geometries should touch at least one other geometry

Let’s have a look at an example. First we’ll create a table with polygons and insert 4 triangles that together build a rectangle:

```
CREATE TABLE translate_polygons (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" char(1) NOT NULL,
"the_geom" geometry NOT NULL
);
-- the data
INSERT INTO translate_polygons (name, the_geom) VALUES (
'A',
'POLYGON((2 1, 0 3, 2 3, 2 1))'::geometry
), (
'B',
'POLYGON((0 3, 2 5, 2 3, 0 3))'::geometry
), (
'C',
'POLYGON((2 5, 4 3, 2 3, 2 5))'::geometry
), (
'D',
'POLYGON((4 3, 2 1, 2 3, 4 3))'::geometry
);
```

Those geometries are displayed in the following image.

The data inserted matches the criterias noted above:

```
-- no overlapping
SELECT t1.name || ' overlaps ' || t2.name || ' = ' ||
```**ST_Overlaps(t1.the_geom, t2.the_geom)::text** AS overlap_test
FROM translate_polygons AS t1,
translate_polygons AS t2
WHERE t1.name <> t2.name
ORDER BY t1.name, t2.name;

overlap_test ---------------------- A overlaps B = false A overlaps C = false A overlaps D = false B overlaps A = false B overlaps C = false B overlaps D = false C overlaps A = false C overlaps B = false C overlaps D = false D overlaps A = false D overlaps B = false D overlaps C = false

```
-- in this case every triangle touches all other triangles:
SELECT t1.name || ' touches ' || t2.name || ' = ' ||
```**ST_Touches(t1.the_geom, t2.the_geom)::text** AS touch_test
FROM translate_polygons AS t1,
translate_polygons AS t2
WHERE t1.name <> t2.name
ORDER BY t1.name, t2.name;

touch_test -------------------- A touches B = true A touches C = true A touches D = true B touches A = true B touches C = true B touches D = true C touches A = true C touches B = true C touches D = true D touches A = true D touches B = true D touches C = true

Let’s use ST_Translate to move two of the triangles:

```
-- triangle to the lower left
-- move it to "southwest"
-- this produces a gap
UPDATE translate_polygons
SET the_geom =
```**ST_Translate(the_geom, -0.4, -0.2)**
WHERE name = 'A';
-- triangle to the upper right
-- move it to "southwest"
-- this produces overlapping geometries
UPDATE translate_polygons
SET the_geom = **ST_Translate(the_geom, -0.1, -0.4)**
WHERE name = 'C';

Now the geometries look like this:

We now have geometries that overlap and are not touching at least one other geometry:

touch_test --------------------- A touches B = false A touches C = false A touches D = false B touches A = false B touches C = false B touches D = true C touches A = false C touches B = false C touches D = false D touches A = false D touches B = true D touches C = false overlap_test ---------------------- A overlaps B = false A overlaps C = false A overlaps D = false B overlaps A = false B overlaps C = true B overlaps D = false C overlaps A = false C overlaps B = true C overlaps D = true D overlaps A = false D overlaps B = false D overlaps C = true

How can we correct the geometries when we do not want to ST_Translate()-calls with (manually) adjusted Parameters? Postgis provides a handy function **ST_SnapToGrid()**:

Function signatures:

- geometry ST_SnapToGrid(geometry geomA, float originX, float originY, float sizeX, float sizeY);
- geometry ST_SnapToGrid(geometry geomA, float sizeX, float sizeY);
- geometry ST_SnapToGrid(geometry geomA, float size);
- geometry ST_SnapToGrid(geometry geomA, geometry pointOrigin, float sizeX, float sizeY, float sizeZ, float sizeM);

ST_SnapToGrid — Snap all points of the input geometry to the grid defined by its origin and cell size. Remove consecutive points falling on the same cell, eventually returning NULL if output points are not enough to define a geometry of the given type. Collapsed geometries in a collection are stripped from it. Useful for reducing precision.

(From the Postgis-SVN-Manuals)

We can snap the geometries to a 1 by 1-grid when calling

```
UPDATE translate_polygons
SET the_geom =
```**ST_SnapToGrid(the_geom, 1)**

Now the geometries look neat again:

2008-12-15: fixed typos.

2009-11-19 at 22:02

Nice article. I am new to Postgres/gis and modified the code above replacing st_touches with st_equals to remove all polygons that were identical in order to run a very large merge (3 million buffered points) using st_union as explained here http://blog.cleverelephant.ca/2009/01/must-faster-unions-in-postgis-14.html.