QGIS Shapefile – Getting List of Distinct Values from Field

qgisshapefilesql

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.

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

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

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

    SELECT DISTINCT city_name FROM layer_name

  3. For 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 an 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 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.

Related Question