2

I have a CSV-file with all the facilities in an area, some sites have multiple facilities so at some locations there will be multiple points e.g. sports hall and swimming pool.

Is there a way to join all the points at one location (they have a unique ID) but retain the information so that if I click on the point it will show that there is a sports hall and a swimming pool not two points one for sports hall and one for swimming pool?

Taras
  • 32,823
  • 4
  • 66
  • 137
Phoebe
  • 21
  • 1

2 Answers2

3

It is expected that you have already imported your CSV file into QGIS and possess a basic point vector layer, for more details check references.

In QGIS I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let's assume there is one point layer 'points' (3 features at the same location) with its corresponding attribute table, see image below.

input

With the following query, it is possible to join all the points at one location (they have a unique ID) but retain the information.

SELECT DISTINCT geometry,
       GROUP_CONCAT("id") AS ids,
       GROUP_CONCAT("Name") AS Names,
       COUNT(geometry) AS Patloc
FROM "points"
GROUP BY geometry

where

  • "Patloc" is a number of points at one location.

The output Virtual Layer will look like

result

References:

Taras
  • 32,823
  • 4
  • 66
  • 137
  • I seem to be having an issue with the virtual layer using the following query SELECT DISTINCT geometry, GROUP_CONCAT(Site Id) AS Ids, GROUP_CONCAT(Site Name) AS Name, GROUP_CONCAT(Facility Type) AS Facility, GROUP_CONCAT(Facility Sub Type) AS SubTypes, GROUP_CONCAT(Unit) AS Units, GROUP_CONCAT(Number) AS Numbers, COUNT() AS Pactloc FROM "points" GROUP BY geometry

    When I test I get the execution error: 1 - near "Id": syntax error I can't work out where I have gone wrong, any advice would be appreciated.

     – Phoebe Jul 01 '20 at 11:26
  • What kind of error do you get? What does your data look like ? – Taras Jul 01 '20 at 11:27
  • When I test I get the following: Query execution error on CREATE TEMP VIEW _tview AS SELECT DISTINCT geometry, GROUP_CONCAT(Site Id) AS Ids, GROUP_CONCAT(Site Name) AS Name, GROUP_CONCAT(Facility Type) AS Facility, GROUP_CONCAT(Facility Sub Type) AS SubTypes, GROUP_CONCAT(Unit) AS Units, GROUP_CONCAT(Number) AS Numbers, COUNT() AS Pactloc FROM "points" GROUP BY geometry: 1 - near "Id": syntax error – Phoebe Jul 01 '20 at 11:32
  • Put your fields in “”, e.g. “Site id“ – Taras Jul 01 '20 at 11:34
  • I then get: Referenced table points in query not found! I have added the CSV with the points already and tried importing it to the embedded layers, the error is present whether the CSV is imported or not – Phoebe Jul 01 '20 at 11:36
1

Let's assume there is one point layer "points" (3 features at the same location) with its corresponding attribute table, see image below.

input

Step 1. In the Field Calculator create a new attribute, e.g. "Names" and fill it using the following expression

concatenate("Name", group_by:=geom_to_wkt($geometry), concatenator:=', ')

step_1

Step 2. Apply "Delete duplicate geometries".

step_2

Step 3. Delete irrelevant fields or add new fields.

step_3

Taras
  • 32,823
  • 4
  • 66
  • 137