Labels leading lines with QGIS and Postgis

EN | PT

Recently I had the need to add labels to features with very close geometries, resulting in their collision.

Capturar_3

Using data-defined override for label’s position (I have used layer to labeled layer plugin to set this really fast) and the QGIS tool to move labels, it was quite easy to relocate them to better places. However, in same cases, it was difficult to understand to which geometry they belonged.

Capturar_2

I needed some kind of leading lines to connect, whenever necessary, label and feature. I knew another great plugin called “Easy Custom Labeling“, by Regis Haubourg, that did what I needed, but it would create a memory duplicate of the original layer, wish meant that any edition on the original layer wouldn’t be updated in the labels.

Since the data were stored in a PostgreSQL/Postgis database, I have decided to create a query that would return a layer with leading lines. I used the following query in DB manager:

SELECT
  gid,
  label,
  ST_Makeline(St_setSRID(ST_PointOnSurface(geom),27493), St_setSRID(St_Point(x_label::numeric, y_label::numeric),27493))
FROM
  epvu.sgev
WHERE
  x_label IS NOT NULL AND
  y_label IS NOT NULL AND
  NOT ST_Within(ST_Makeline(St_setSRID(ST_PointOnSurface(geom),27493), St_setSRID(St_Point(x_label::numeric, y_label::numeric),27493)),geom))

This query creates a line by using the feature centroid as starting point and the label coordinate as end point. The last condition on the WHERE statement assures that the lines are only created for labels outside the feature.

Capturar_1

With the resulting layer loaded in my project, all I need is to move my labels and save the edition (and press refresh) to show a nice leading line.

guidelines

Advertisements

17 thoughts on “Labels leading lines with QGIS and Postgis

    1. Olá Felipe,

      As coordenadas X e Y da label, são escritas na tabela de atributos automaticamente quando se usa a ferramenta “mover label”. Mas para essa opção ficar activa na barra de ferramentas das labels, temos de definir que campos queremos usar para guardar as coordenadas. Podia fazê-lo manualmente (criar dois campos e depois nas opções das labels usá-los na posição), mas preferi usar o plugin layer to labeled layer que faz essa configuração toda de forma automática.

      Liked by 1 person

  1. Como faço para exportar a autocad os atributos ? preciso fazer um mapa com o nome de todas as ruas de uma cidade e poder exportar no autocad pra um projeto de ftth

    Like

  2. Hallo Alexander, I am making a map of pottery distribution for the project I am working for and I am facing a similar problem with labels, so I tried your solution but it is not working. I am for sure making some mistakes and maybe you have some time to help me understanding where.
    – I transformed the shapefile (which is a point shapefile) into a postgres table and imported it in my QGIS-project (which is in WGS84)
    – I used the “Layer to labelled layer” plug-in to create the fields for the labels
    – then I copied your SQL-query in the DB-Manager and changed the “gid”, “label” / 27493 / “epvu.sgev” in your query to match my fields / coordinate system / “table name” and pressed “execute”

    and it returned me the following errors:
    – sintax error: in line 10 the last round paranthesis was marked as a mistake and I removed it
    – error: Relation „qpw_sites“ does not exist (qpw_sites is the name of my table)

    and I do not really understand what the problem is here. I searched on-line and it seems a problem related to the spelling of the table name but the spelling is correct (it is auto-completed by DB manager 😀 ). I am sure I am missing something very basic since I am quite new to SQL. Thanks for your time and for sharing your work which is always a great inspiration.
    Ciao
    Sebastiano

    Like

      1. Hallo Alexander, of course:
        This is the query:

        SELECT
        id,
        site_name,
        ST_Makeline(St_setSRID(ST_PointOnSurface(geom),4326), St_setSRID(St_Point(LblX::numeric, LblY::numeric),4326))
        FROM
        qpw
        WHERE
        LblX IS NOT NULL AND
        LblY IS NOT NULL AND
        NOT ST_Within(ST_Makeline(St_setSRID(ST_PointOnSurface(geom),4326), St_setSRID(St_Point(LblX::numeric, LblY::numeric),4326)),geom)

        I did not write a query for the table since I imported the shapefile with the DB Manager. These are the table fields onve imported. I am wondering if the multipoint in the geometry table could be a problem.

        #  Name  Type  Length 
        1  id  int4  4 
        2  geom  geometry (MultiPoint,4326) 
        3  site_name  varchar (254) 
        4  precision  varchar (254) 
        5  e  varchar (254) 
        6  n  varchar (254) 
        7  sherds  int4  4 
        8  sherds_pre  varchar (254) 
        9  dating  varchar (254) 
        10  visibility  int4  4 
        11  vis_min  varchar (15) 
        12  vis_max  varchar (15) 
        13  LblSize  int4  4 
        14  LblColor  varchar (7) 
        15  LblBold  int4  4 
        16  LblItalic  int4  4 
        17  LblUnderl  int4  4 
        18  LblStrike  int4  4 
        19  LblFont  varchar (100) 
        20  LblX  numeric (20,5) 
        21  LblY  numeric (20,5) 
        22  LblSclMin  int4  4 
        23  LblSclMax  int4  4 
        24  LblAlignH  varchar (15) 
        25  LblAlignV  varchar (15) 
        26  LblRot  numeric (20,5) 

        thanks for helping!
        Sebastiano

        Like

  3. “I am wondering if the multipoint in the geometry table could be a problem”

    It certainly does!!

    The function ST_PointOnSurface(geom) only works in Polygons and Multipolygons. Therefore, the query must be adapted. If the layer was of single points, you could just replace the “ST_PointOnSurface(geom)” for “geom” only. Since you mention multipoints, try replacing it by ST_Centroid(geom).

    Like

    1. Ciao Alexander, it took me some time but I solved it. There were two problems, the first being of course what you pointed out (replacing ST_PointOnSurface with geom when working with point geometry) and the second being related to the use of uppercase in the field names of the table. I quoted the field name (“LblX” instead of plain LblX for example) and everything went smooth.
      I wanted to thank you again for your help and for sharing the query. I really like it especially because (as you told) you do not have to duplicate a layer to create the labels as in the “easy custom label plug-in”. Furthermore its working in WGS84 as well (“easy custom label plug-in” is not creating the connectors in WGS84, or at least not for me 🙂 ).
      It would be great if it were possible to change the anchor point of the connector (sometimes the line passes trough the text; I saw that you solved the problem adding a buffer around it but sometimes buffers are too heavy for the maps I make).
      Thanks again and I am looking forward for your next post!
      Ciao
      Sebastiano

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s