Monthly Archives: September 2016

Detecting spikes in geometries using angle threshold

Hi there,

We all know that search for errors in geometries can be quite a journey. One of the errors we need to fix is the presence of spikes in our geometries. One way to determine the location of those spikes is to determine the angles and check if them are smaller than a predefined threshold.

Ok them, one way to tackle this problem is to load your data into a PostGIS layer and use the available ST functions.

In this post I’ll show you guys a SQL query to solve this.

Let’s suppose we have geometries with problems like these here:


To solve this problems we will use the following ST PostGIS functions:

  • ST_Azimuth
  • ST_PointN
  • ST_NPoints
  • ST_Boundary
  • ST_Dump
  • ST_ForceRHR

For more information about these functions check this:

The following query can show us where the problems are located, let’s use a limit of 10 degrees here to determine the spikes:

WITH result AS (SELECT, points.anchor, (degrees
                                ST_Azimuth(points.anchor, points.pt1) - ST_Azimuth(points.anchor, points.pt2)
                            )::decimal + 360) % 360 as angle
                  ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as pt1,
                  ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1) %  (ST_NPoints(geom)-1)+1) as anchor,
                  ST_PointN(geom, generate_series(2, ST_NPoints(geom)) %  (ST_NPoints(geom)-1)+1) as pt2,
         as id
                  (SELECT id as id, ST_Boundary((ST_Dump(ST_ForceRHR(geom))).geom) as geom
                   FROM only my schema.mylayer -- enter your layer name here
                   ) AS linestrings WHERE ST_NPoints(linestrings.geom) > 2 ) as points)
select distinct id, anchor, angle from result where (result.angle % 360) < 10 or result.angle > (360.0 - (10 % 360.0)) -- the 10 here is our threshold

With the results from this query we can locate our spikes using the “anchor” value returned in the query. Something like this:


Very nice right?

Have you ever heard of pgModeler?

Someone has given you a logical model with over 500 tables to implement it in PostgreSQL, where to begin? Should I cry first?  Should I just start typing create table statements like there is no tomorrow? No! I present you pgModeler!


pgModeler is a modelling open software developed by Raphael Araújo e Silva and it allows you to build a database just using a nice graphical interface and after you finish your design, you can just deploy it and there you go! Your database is ready!

For instance, suppose you have a table called road with an text attribute “name”, geometry column “geom” with type MultiLinestring, with epsg 4326. To implement this table you just have to click around and there you go, you have the following table:


To deploy this, just go to export and choose between .png, sql or direct deploy into postgres. The sql for the table above is:

CREATE TABLE public.road(
id serial NOT NULL,
name text,
geom geometry(MULTILINESTRING, 4326) NOT NULL,


Another great feature of pgModeler is reverse engineering! Just define a database connection and it shows you all tables and relationships.

By the way, I was the guy that was given the huge database to implement and if wasn’t by pgModeler, I think I’d still be crying… =]

Deaggregate geometries with pyqgis

Have you ever needed to explode multi geometry layer into a single geometry layer, using in each new geometry the attributes of the original multi one?  If you were working with FME, for instance, you basically would just use the transformer Deaggregator. Let’s learn how to solve this problem with python and QGIS!

The following code snippet teaches you how to work with QgsVectorLayers, it’s attributes and how to manipulate geometries.

from qgis.core import QgsVectorLayer, QgsFeature, QgsMapLayerRegistry

#fill in your input layer name. In this example, our inputLyrName is input_layer
inputLyrName = 'input_layer'
inputLyr = QgsMapLayerRegistry.instance().mapLayersByName(inputLyrName)[0]

#fill in your output layer name. In this example, our outputLyrName is output_layer
outputName = 'output_layer'
outputLyr = QgsMapLayerRegistry.instance().mapLayersByName(outputLyrName)[0]

#tests type of output: if it is a multi parted geometry or
#a single parted geometry
if outputLyr.wkbType() in [QGis.WKBPoint, QGis.WKBLineString, QGis.WKBPolygon]:
     isMulti = False
     isMulti = True

addList = []
for feat in inputLyr.getFeatures():
     #gets all parts of geometry as an individual single geometry
     parts = feat.geometry().asGeometryCollection()
     #checks if it isMulti, if it is, convert each
     #part in geometryCollection to multi
     if isMulti:
          for part in parts:

     #for each part, get original set of attribute and create a new feat
     #with this set
     for i in range(0,len(parts)):
          #new feature constructor. newFeat has all atributes of feat
          newFeat = QgsFeature(feat)
          #set geometry with part
          #get field id and get defaultValue from provider
          idx = newFeat.fieldNameIndex('id')

Hope you guys like it!

Using GRASS with pyqgis to clean up geometries

We all know that GRASS is a great GIS software. Combined with QGIS it is even more great!

Using GRASS from within QGIS is very useful to deal with daily GIS problems. Everyone that works with geospatial data knows how annoying is to clean up geometries full of errors. The manual process demands lots of time and we can always forget something in the end. Do this kind of job automatically is faster and safer.

Let’s se how to do this using pyqgis. Imagine that we have a database layer like this:


A good way to clean problems like those shown above and at the same time solve snapping problems is to use the following tools in v.clean.advanced provided by GRASS:

  • break
  • rmsa
  • rmdangle

If you want a description on how those tools work, take a look at:

To clean it using grass we can use the following piece of code:

#choosing the algorithm
alg = 'grass7:v.clean.advanced'

#getting the vector layer we want to clean
input = iface.activeLayer()

#setting tools
tools = 'break,rmsa,rmdangle'
threshold = -1
#getting mapcanvas extent (bounding box) supposing we can see our data
e = iface.mapCanvas().extent()
xmax = e.xMaximum()
ymax = e.yMaximum()
xmin = e.xMinimum()
ymin = e.yMinimum()

extent = '{0},{1},{2},{3}'.format(xmin, xmax, ymin, ymax)

#setting parameters: choose them according to your data
snap = 100.0
minArea = 0.001

#running the grass algorithm
ret = processing.runalg(alg, input, tools, threshold, extent, snap, minArea, None, None)

#getting output layer
outputLayer = processing.getObject(ret['output'])
#Adding to registry

#getting error flags
errorLayer = processing.getObject(ret['error'])
#Adding to registry

After running, we can see results like this:


Quite good, right?