17

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.

32

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.

6

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

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

enter image description here

  • 1
    This is now (QGIS 2.14+) the correct answer. – Alexandre Neto May 17 '17 at 0:12
0

Try including something like:

where city_name is NOT NULL or not equal to NULL
-1

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.

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.