{"id":449,"date":"2016-12-03T18:11:59","date_gmt":"2016-12-03T23:11:59","guid":{"rendered":"http:\/\/brian.digitalmaddox.com\/blog\/?p=449"},"modified":"2016-12-03T18:12:11","modified_gmt":"2016-12-03T23:12:11","slug":"filtering-data-from-a-geospatial-database","status":"publish","type":"post","link":"https:\/\/brian.digitalmaddox.com\/blog\/?p=449","title":{"rendered":"Filtering Data from a Geospatial Database using QGIS"},"content":{"rendered":"

If you have spatial databases such as the ones I set up in my previous blog posts about GNIS<\/a> and PostGIS, you will likely want to add a few things to them to make them more useful. GNIS and Geonames contain point types of all different classes, from airports to populated places. What if you were only interested in one type of point, such as airports? By default, if you load GNIS data into QGIS, it will display all of the points in your view and look cluttered as the screen shot below demonstrates.<\/p>\n

\"All<\/a>

All GNIS Points Over an Area<\/p><\/div>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

The good news is that you can easily specify what you only want QGIS to show you.\u00a0 There are a couple of ways that you can filter data out in a layer with QGIS: the Set Filter<\/i> button and creating a database view. The Set Filter<\/i> button lets you create a SQL filter by clicking on the field you want to filter with, the relational operator, and what you want to compare with. A database view lets you pre-define your filter and presents it as another table. Whichever one you use is up to you, but there is at least one thing you must do to speed up both methods.<\/p>\n

The Add Filter Method<\/h2>\n

Assuming you followed my previous posts about setting up GNIS, we will use that for this example. First you need to create an index on the feature_class column of GNIS. This will make the query that we will use as an example run much faster. To do this, run the following commands:<\/p>\n

psql -d USGS\r\nUSGS=# create index gnis_feature_class_idx on gnis(feature_class);<\/pre>\n
Once this is done, you will have a new index called gnis_feature_class_idx<\/em>. This allows PostgreSQL find the matching feature classes from the data more quickly by consulting the index instead of manually searching each row in the database.<\/p>\n
Now that this is done, we will next move on to our first example, the Set Filter<\/i> button method. As a refresher, here are the feature classes in GNIS:<\/p>\n
USGS=# select distinct(feature_class), count(*) from gnis group by feature_class order by feature_class;\r\nfeature_class   | count\r\n-----------------+--------\r\nAirport         | 23202\r\nArch            | 720\r\nArea            | 2557\r\nArroyo          | 466\r\nBar             | 5870\r\nBasin           | 4304\r\nBay             | 14094\r\nBeach           | 2409\r\nBench           | 724\r\nBend            | 2797\r\nBridge          | 7356\r\nBuilding        | 160291\r\nCanal           | 21559\r\nCape            | 16417\r\nCemetery        | 145544\r\nCensus          | 11629\r\nChannel         | 4014\r\nChurch          | 231967\r\nCivil           | 64237\r\nCliff           | 4479\r\nCrater          | 246\r\nCrossing        | 13167\r\nDam             | 56931\r\nFalls           | 2499\r\nFlat            | 10559\r\nForest          | 1314\r\nGap             | 8246\r\nGlacier         | 1021\r\nGut             | 3541\r\nHarbor          | 1271\r\nHospital        | 15864\r\nIsland          | 20540\r\nIsthmus         | 28\r\nLake            | 69403\r\nLava            | 168\r\nLevee           | 546\r\nLocale          | 162518\r\nMilitary        | 2860\r\nMine            | 36133\r\nOilfield        | 4863\r\nPark            | 69501\r\nPillar          | 2092\r\nPlain           | 289\r\nPopulated Place | 201065\r\nPost Office     | 66942\r\nRange           | 2480\r\nRapids          | 1062\r\nReserve         | 1276\r\nReservoir       | 74683\r\nRidge           | 15127\r\nSchool          | 216473\r\nSea             | 28\r\nSlope           | 373\r\nSpring          | 38655\r\nStream          | 231462\r\nSummit          | 70614\r\nSwamp           | 7608\r\nTower           | 16800\r\nTrail           | 11047\r\nTunnel          | 750\r\nUnknown         | 186\r\nValley          | 70239\r\nWell            | 38797\r\nWoods           | 684\r\n(64 rows)<\/pre>\n
Both of the examples here will work with the feature class of Airports. These examples also assume you already have some data set up as I previously demonstrated on this blog.<\/p>\n
For the Set Filter<\/i> method, first click on the Add PostGIS Layer button in QGIS. Select the USGS database and select the gnis table. Once you have done this, click on the Set Filter<\/i> button at the bottom right side of the Add Layer dialog.<\/p>\n
 <\/p>\n
\"Creating<\/a>
Creating a Filter in QGIS<\/p><\/div>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
As you can see above, you are presented with a list of Fields<\/i> on the left side, operator buttons in the middle, and Values on the right side. Click on the Feature Class<\/i> field to select it and then click the All<\/i> button under the values window to the right. Since we created an index on the Feature Class<\/i> field, this should quickly show you all the unique values that exist in the database for that field. Now double click Feature Class<\/i> to add it into the Provider specific filter expression<\/i> in the text box at the bottom of the dialog. Then click the =<\/i> button in the Operators group. Now double click Airport from the Values box to add it. Your filter expression should now look like this:<\/p>\n
\"feature_class\" = 'Airport'<\/pre>\n
If you click the Test<\/em> button, QGIS will perform a query and display the number of rows that match your query. You can use this to double check that you did not make any errors during entry. In our case, the query should return around 23,000+ rows depending on the version of GNIS you are using. Click the OK<\/em> button to go back to the Layers<\/em> dialog and then the Add<\/em> button to add it to your project.\u00a0 With the filter in place, your screen should look less cluttered as it is only showing airports from GNIS<\/p>\n
\"Only<\/a>
Only Airports Displayed in GNIS<\/p><\/div>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
You can use this method to filter out data on any type of field in a geospatial database.\u00a0 I recommend, though, that you first create an index on that column to speed up the operation.\u00a0 Otherwise, you may have to wait a while every time you try to load your filtered data.<\/p>\n
Creating a Database View<\/h2>\n
The second method to filter data is to create a database view. Basically all database types can create a view. For the non-database savvy, a view can be thought of as a virtual table that is defined by a database query. This means that whenever you access the view, the data that is returned is generated by a query. For example, if you wanted a table of only airports in GNIS, you could make a view that pretends to be another table but does not take up as much space as a real table would.<\/p>\n
For this example, we will again use Airports. Once you understand this, you can then create views for other classes by replacing the feature class name. However, when working with tools such as QGIS, there is a caveat that you need to first know about. If you are savvy with databases, your might create the view with the following command:<\/p>\n
psql -d USGS\r\nUSGS=# create view view_airports as select * from gnis where feature_class = 'Airport';<\/pre>\n
When you then go to load this into QGIS, you will indeed see the view as a layer, but there will be a problem.<\/p>\n
\"How<\/a>
How a View Appears in QGIS<\/p><\/div>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
As you can see, QGIS will not let you just click on the view to add it. If you hover over the error triangle, you will see it displays a message of Select columns in the \u2018Feature Id\u2019 column that uniquely identify features of this layer.<\/i> If you scroll to the right, you will see that QGIS will let you select a column in the view that is a unique identifier (feature_id<\/i> in the case of GNIS).<\/p>\n
Why does QGIS not automatically know which column to use? If you are not well versed in how QGIS and databases work, tables in a database typically need a unique identifier for each entry so that it can be properly found. With recent versions of PostgreSQL and PostGIS, the view does not have a unique key presented with the view. If QGIS tried to automatically deduce what field to use as the unique key, it would take a lot of processing power and would mean that QGIS would temporarily \u201chang\u201d whenever you tried to access a database. Instead, QGIS gives you an option to tell it what field to use as the unique identifier for each row.<\/p>\n
If you go ahead and select the feature_id field in the Add Layer dialog, you will then be able to select the layer and click Add to load it into QGIS.<\/p>\n
\"Select<\/a>
Select Feature ID Option in QGIS<\/p><\/div>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
So the question you might have is \u201cWhich method is better?\u201d The correct answer is \u201cWhichever method makes more sense to you.\u201d Some people may be OK with setting a filter when they load in data. Others may prefer to have views show up in the Layers dialog to remind them what all is available. A PostgreSQL materialized view<\/a> would likely be the fastest method as it creates a cache of the data, but that is a bit beyond the scope of this post \ud83d\ude42<\/p>\n
Have fun and happy GISing with all Open Source software!<\/p>\n","protected":false},"excerpt":{"rendered":"
If you have spatial databases such as the ones I set up in my previous blog posts about GNIS and PostGIS, you will likely want to add a few things to them to make them more useful. GNIS and Geonames … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-449","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/449","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=449"}],"version-history":[{"count":4,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/449\/revisions"}],"predecessor-version":[{"id":459,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/449\/revisions\/459"}],"wp:attachment":[{"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=449"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=449"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brian.digitalmaddox.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=449"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}