In this article we will discuss, in detail, how to detect and rectify geometric problems in a PostgreSQL \/ Postgis<\/a> table using SQL queries. You can promptly detect geometric errors using QGis. Just load the layer and launch the menu Vector – > Geometry tools – > Check the validity <\/strong> <\/em> The GEOS method is faster but only indicates the first error encountered for each object. It returns a file ‘Invalid output’ which is a layer of polygons completed by a column a column _errors:\u00a0 The Qgis method can return multiple errors per object. This method returns in the ‘Invalid output’ file a layer of polygons completed by a _errors column with a less standardized message than by the GEOS method: Meanwhile, \u00a0if there are errors, I go directly to pgAdmin 4 (or failing in the database manager of QGis) to work in SQL.<\/p>\n The first step is the identification of errors. Note that PostGIS complies with the OGC OpenGIS Specifications, and therefore the validity is checked against GEOS. A last type of error, usually of no consequence, is the presence of duplicated nodes (two points of the entity located exactly in the same place). They do not disturb space operators, but that’s no reason not to fix them.<\/p>\n Here is a query that allows you to detect the first three types of errors in one pass:<\/p>\n
\n<\/p>\nError checking with QGis.<\/h2>\n
\n![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/01\/g1.png?resize=640%2C428\")
<\/a>
\nYou can choose one of the following \u00a0methods:<\/p>\n\n
\n
![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/01\/g3.png?resize=640%2C170\")
<\/a><\/p>\n
\n![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/01\/g4.png?resize=640%2C238\")
<\/a>
\nPersonally, I only use this tool to check for errors. I execute both methods and, if they do not return any error I consider that the layer as valid.<\/p>\nDetection of Geometric Errors with Postgis<\/h2>\n
\nTherefore, we will perform a query that will search the three main types of errors:<\/p>\n\n
\n
<\/div>
\nUNION
\nSELECT ‘geom nulle’ AS nb, count(*) FROM table WHERE the_geom is null
\nUNION
\nSELECT ‘collection’ AS nb, count(*) FROM table WHERE not ST_IsValid(the_geom)
\nAND ST_GeometryType(ST_MakeValid(the_geom))=’ST_GeometryCollection’;<\/div><\/div>\n![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/01\/g5.png?resize=640%2C352\")
<\/a>
\nYou can obtain details of GEOS errors with the following query:<\/p>\n
![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/01\/g6.png?resize=640%2C455\")
<\/a><\/p>\n