Getting list of distinct values from shapefile field using QGIS

Question

I have a shapefile whose features I can filter by specifying a where clause in the query dialogue. F.x. I have a field named "city_name" and by stating "city_name" = 'London' in the where clause only London is displayed. What I would like to do now is to fetch all values in "city_name" from the attribute table.

Something like that:

select distinct city_name
from [attribute table]

I found several tools in QGIS apparently deal with SQL and I also had a quick look at the different Plugins. But I fail at connecting to a database or the table name ... what database or which table in that case?

In the end, I want to export the result list and use it for further processing.

I am using QGIS 1.8.

Answer 1

Update answer (QGIS Version >= 2.14)

Since QGIS 2.14, you can use run SQL statements on any loaded vector layer using Virtual layers.

1. Having the layer loaded in QGIS, go to Layer > Add Layer > Add/Edit Virtual Layer;

2. In the Create virtual layer dialog, enter your SQL statement in the Query field. Something like:

   SELECT DISTINCT city_name
   FROM layer_name
   

3. For the geometry set No Geometry

4. Click Ok and a table will load in QGIS with the desired unique values.

Note: this table will be updated if new values are added to the city_name column.

Legacy answer (QGIS Version < 2.14)

You have a few choices to do what you ask.

1. Import your shapefile in a Spatialite or Postgis database, and then you can query your table using complete SQL statements;
2. Use the Dissolve tool (Vector > Geoprocessing Tools > Dissolve), to dissolve your shapefiles using the field "city_name". Although is a strange method, the .dbf file of the resulting shapefile will provide the list you need;
3. Take a look at group stats plugin (1.6), you can use "city_name" as a classification field, and press calculate. It will calculate some stats about each city, you can then copy the result and extract the city list.

---

I have just noticed that in the Vector > Analysis Tools, there is a List unique Values tool that is precisely what one needs for this task. So easy... no workarounds and no need for Plugins.

Answer 2

QGIS now has a tool that will list unique values in one or more fields in a layer. "This algorithm generates a report with information about the unique values found in a given attribute (or attributes) of a vector layer."

Can be found in main menu Vector> Analysis Tools> List Unique Values. Generates a table with unique values.

Answer 3

Use the QGIS DB Manager and access your shapefile via 'virtual layers'

You can then use the SQL window and write your query:

Answer 4

Use a virtual layer:

select "district", "thedatefield"
from yourtable
group by "district", "thedatefield"

Answer 5

I just discovered an alternate way to solve my question by using the Delete Duplicates by Attribute tool. Simply select both the inspection date and district fields in the Field to match duplicates by section. Voila! The output exactly matches the table generated by BERA's answer.

Answer 6

Try including something like:

where city_name is NOT NULL or not equal to NULL

Answer 7

2023 you can do this by 3 different ways

Step by step walk through each method click here

https://transparentgov.net/cleargov1/1180/how-to-find-unique-value-part-5-qgis

I prefer SQL method,

Answer 8

Use query builder in QGIS, go to city name field in fields in query builder. Go to values, add all. Delete any nulls.

Build a script similar to city name = "london" and city name = "paris" until you have the complete list of names and run the script. Now all city names are selected. You can export selected as a shape file and import into a postgresql database.

To use a postgresql backend to QGIS install a postgresql stack with geoserver, postgresql, and a postgis enabled databse in postgresql. You will need to connect QGIS to the server. Play around a bit and you will figure it out.