Database Reference
In-Depth Information
postgis_cookbook=# UPDATE chp03.countries
SET the_geom = ST_MakeValid(the_geom)
WHERE ST_IsValid(the_geom) = false;
Tip
A smart way to not have invalid geometries in the database at all is by
addinga
CHECK
constraintonthetabletocheckforvalidity.Thiswillin-
creasethecomputationtimewhenupdatingorinsertingnewgeometries,
but will keep your dataset valid. For example, in the
countries
table,
this can be implemented as follows:
ALTER TABLE chp03.countries
ADD CONSTRAINT geometry_valid_check
CHECK (ST_IsValid(the_geom));
Many times in this recipe, though, you will need to remove such a con-
straintinordertobeabletoimportrecordsfromadifferentsource.After
makingvalidationswiththe
ST_MakeValid
function,youcansafelyadd
the constraint again.
How it works...
Thereareaseriesofreasonswhyaninvalidgeometrycouldresultinyourdatabase;
forexample,ringscomposingpolygonsmustbeclosedandcannotselfintersector
share more than one point with another ring.
After importing the
country
shapefile using the
ST_IsValid
and
ST_IsValidReason
functions, you will have figured out that four of the imported
geometries are invalid all because their polygons have self-intersecting rings.
At this point, agood way to investigate the invalid multipolygon geometry isbyde-
composingthepolygontoitscomponentringsandcheckingouttheinvalidones.For
thispurpose,wehaveexportedthegeometryoftheringcausingtheinvalidity,using