In the previous article (GIS Tools for validating geometries (1)) we
processed the Italian Communes layer (com2011.shp) with the ArcMap geometry
checker. We did not detect any anomalies.
Now, let’s load this layer into a Spatialite database and see what we
find. As previously stated, this applies to a PostGIS database; the SQL queries
used being strictly the same.
Validating geometries with Spatialite and PostGis
You will find the few available references for validating geometries with
Spatialite in this page: https://www.gaia-gis.it/fossil/libspatialite/info/0978ac2017454733
. Regarding PostGis there’s a little bit more in the following page http://postgis.net/docs/using_postgis_dbmanagement.html#OGC_Validity
GEOS (and therefore SpatiaLite) supports the SQL ST_ IsValid ( )
function. By using this function, you can easily identify all deceiving
geometries in your tables. But this function does not solve the problems.
SpatiaLite supported, in previous versions, an SQL ST_SeritizeGeometry ( )
function. But this function made it possible to treat a small number of
anomalies.
Now, thanks to the integration of the library liblwgeom , SpatiaLite
can support the function ST_ MakeValid ( ), identical to that supported by
PostGIS.
For our example, we have created an empty spatial basis, loaded the
com2011.shp layer and defined the geometry field. If you need help to do this,
please refer to this article: Creating a spatial Spatialite database if using
QGis or this article Share the data between ArcGis and QGis if using
ArcGis.
For the rest of the example, we will first use QGis to execute SQL
queries in the Spatialite database, then we will explain how to do it when
using ArcMap.
Detecting geometry anomalies with QGis
The first SQL query to execute is the one that allows you to check and
detect geometry anomalies. The ST_IsValid function performs this detection. SELECTCount (*)
FROM com2011
WHERE ST_IsValid (geometry) = 0;
You will find that 19 municipalities have anomalies.
To find out which communes are concerned, just run the query: SELECT nome_com
FROM com2011
WHERE ST_IsValid (geom) = 0;
SQL queries are not very explanatory. If you want to know what the
detected error is, you must search in your GIS.
The first mistake concerns the commune of
Sannicandro di Bari.
The anomaly is ” visible “. This commune polygon
contains another polygon included. Most likely the meaning of both polygons is
not opposite.
For the Trieste commune, the next one, it is
more difficult to find at first sight.
Here, as well, we find an inner polygon. Instead of two polygons, one
for the outer ring and another for the inner ring, it has been built with a
single ring. This is possible because the edge of the inner polygon consists of
a single point.
Detecting anomalies with ArcMap
In QGis you have an SQL window to execute queries, available in the
Database Manager panel.
In ArcMap, there is nothing similar. So at first glance you cannot
execute SQL queries in the Spatialite database.
Remind that you load a Spatialite layer directly into ArcMap without
going through any database connection.
But if we think about it more thoroughly, the
attribute queries we perform with ArcMap are actually SQL queries. Even if it
is not documented, you can run the ST_ IsValid ( ) query simply from an Attribute
Selection window!
You will notice the result of the query: the 19 communes with anomalies
are the same ones we found when using QGis.
Automatic correction of errors
First, let’s get things straight. Automatic correction, being automatic,
does not involve the human operator. The solution that will be applied is not
necessarily the right one, but it is one that will make the geometry valid.
In the case of the commune of Trieste, it is obvious that at the time of
the digitization of the bottom of the sound, the operator has selected two
points too close that have merged, closing the last small end of the sound. The
correct solution would be to duplicate the point in question and to separate
these two points, so that there is no inner polygon. But this cannot be
determined by an automatic correction.
What the automatic correction will do will be to respect the placement
of the existing points. It will duplicate the point in question by assigning
one to the outer polygon, the other to the inner polygon. Then it will reverse
the order of the inner points so that they respect the rule that the direction
of the interior polygons must be counter clockwise (and the outer rings clockwise).
By applying this solution, even if we do not approach the reality of the
field, at least the geometry will respect the rules and will no longer be
considered as erroneous.
To correct the geometries you have an SQL query using the function ST_
MakeValid ( ) :
UPDATE com2011 SET geom = ST_ MakeValid ( geom)
WHERE ST_IsValid (geom) = 0;
But depending on the software used, this function will or will not be
implemented.
When using ArcMap, the SQL is limited to SELECT clauses. Since we want
to make an UPDATE, this is not possible in the Attribute Selection windows.
Under QGis it is not straight away with the Spatialite database manager.
You have to go through a plugin: ” Processing LWGEOM provider
“. Once installed, you will have at your disposal lwgeom as
an algorithm provider in the Treatment Toolbox.
Small, or rather big, hic: No way in version 2.8 to correct the existing
table. You have to create a new layer … of shape type. Even if you select the
spatialite format as output, it is bugged and you have a shape.
While waiting for it to be corrected, anyway what we are looking for is
to correct an existing table. Here is the simplest, although there must be
other solutions.
Download the spatialite_gui executable. This is provided by the project
that develops Spatialite and therefore has all the necessary implementations,
up to date. It does not need installation; you download and run the .exe file
by double-clicking.
The download address is http://www.gaia-gis.it/gaia-sins/windows-bin-x86/
and the file to download is the one that starts with spatialite_gui_
…
Currently, you can download it by clicking here directly: spatialite_gui-1.8.0-devel-win-x86.7z
If we execute the geometry verification SQL
query, we see the same result as before:
We now execute the geometry correction request:
Finally,
we re-execute the geometry validation request:
The 19 geometries containing anomalies have been corrected.
In the next article we will see how to do the same for a Spatialite
database, but this time with any GIS format. We will be using Safe Software’s
FME software.
