EN | PT
Recently I had the need to add labels to features with very close geometries, resulting in their collision.
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.
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.
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.
Bem lragal, Alexandre! Vc estão salvou a coordenada x e y da label (x_label, y_label), usando algum desses plugins? Ou fez isso através no banco de dados, mesmo?
Abs
LikeLike
oops.. quis dizer: “Bem legal…” 🙂
LikeLike
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.
LikeLiked by 1 person
Thanks for sharing!
LikeLike
E será que o princípio aplica-se a uma base de dados spatialite, por exemplo com o makeline ?
LikeLike
Paulo, creio que sim. Mas numa experimentei.
LikeLike
obrigado. tenho que testar. muito útil o que fez.
LikeLike
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
LikeLike
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
LikeLike
Ciao Sebastiano,
Can you please send your final query? as well as the creation query of your table.
Ci vidiamo
Alexandre
LikeLike
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
LikeLike
“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).
LikeLike
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
LikeLike