{"id":6873,"date":"2018-11-07T00:32:31","date_gmt":"2018-11-06T23:32:31","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=6873"},"modified":"2021-03-09T08:05:10","modified_gmt":"2021-03-09T07:05:10","slug":"gis-tools-for-validating-geometries-2-spatialite-and-postgis","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/gis-tools-for-validating-geometries-2-spatialite-and-postgis\/","title":{"rendered":"GIS tools for validating geometries (2): Spatialite and PostGis"},"content":{"rendered":"\n

In the previous article (GIS Tools for validating geometries (1)<\/a>) we\nprocessed the Italian Communes layer (com2011.shp) with the ArcMap geometry\nchecker. We did not detect any anomalies. <\/p>\n\n\n\n

Now, let’s load this layer into a Spatialite database and see what we\nfind. As previously stated, this applies to a PostGIS<\/a> database; the SQL queries\nused being strictly the same. <\/p>\n\n\n\n\n\n\n\n

Validating geometries with Spatialite<\/strong> and<\/strong> PostGis<\/strong> <\/p>\n\n\n\n

You will find the few available references for validating geometries with\nSpatialite in this page: https:\/\/www.gaia-gis.it\/fossil\/libspatialite\/info\/0978ac2017454733<\/a>\n. Regarding PostGis there’s a little bit more in the following page http:\/\/postgis.net\/docs\/using_postgis_dbmanagement.html#OGC_Validity<\/a>\n<\/p>\n\n\n\n

GEOS (and therefore SpatiaLite) supports the SQL ST_ IsValid ( )\nfunction. By using this function, you can easily identify all deceiving\ngeometries in your tables. But this function does not solve the problems.
\nSpatiaLite supported, in previous versions, an SQL ST_SeritizeGeometry ( )\nfunction. But this function made it possible to treat a small number of\nanomalies.
\nNow, thanks to the integration of the library liblwgeom<\/em><\/strong> , SpatiaLite\ncan support the function ST_ MakeValid ( ), identical to that supported by\nPostGIS. <\/p>\n\n\n\n

For our example, we have created an empty spatial basis, loaded the\ncom2011.shp layer and defined the geometry field. If you need help to do this,\nplease refer to this article: Creating a spatial Spatialite database<\/a> if using\nQGis or this article Share the data between ArcGis and QGis<\/a> if using\nArcGis. <\/p>\n\n\n\n

For the rest of the example, we will first use QGis to execute SQL\nqueries in the Spatialite database, then we will explain how to do it when\nusing ArcMap. <\/p>\n\n\n\n

Detecting geometry anomalies with QGis<\/strong> <\/p>\n\n\n\n

The first SQL query to execute is the one that allows you to check and\ndetect geometry anomalies. The ST_IsValid function performs this detection. SELECT<\/em><\/strong>Count (<\/em><\/strong>*)<\/em><\/strong>
FROM com2011<\/em><\/strong>
WHERE ST_IsValid (geometry) = 0;<\/em><\/strong><\/p>\n\n\n\n