1

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?

2

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 achieve the result, i.e. 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 as following

result


References:

| improve this answer | |
  • 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 1 at 11:26
  • What kind of error do you get? What does your data look like ? – Taras Jul 1 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 1 at 11:32
  • Put your fields in “”, e.g. “Site id“ – Taras Jul 1 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 1 at 11:36
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.