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 dealing 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.

Alexandre Neto · Accepted Answer

Update answer (QGIS Version >= 2.14)

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

Having the layer loaded in QGIS, go to Layer > Add Layer > Add/Edit Virtual Layer;
In the Create virtual layer dialog, enter you SQL statement in the Query field. Something like:

SELECT DISTINCT city_name FROM layer_name
For geometry set No Geometry
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.

Import your shapefile in a Spatialite or Postgis database, and then you can query your table using complete SQL statements; 
Use the Dissolve tool (Vector > Geoprocessing Tools > Dissolve), to dissolve your shapefiles using the field "city_name". Although is an strange method, the dbf file of the resulting shapefile will provide the list you need;
Take a look at group stats plugin (1.6), you can use "city_name" as 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.

nigellaw · Answer

Try including something like:

where city_name is NOT NULL or not equal to NULL

lewis · Answer

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.

DPSSpatial_BoycottingGISSE · Answer

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

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

David Bartecchi · Answer

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.

Getting list of distinct values from shapefile field using QGIS?

5 Answers

Add your own answers!

Ask a Question