On this page

Callysto.ca Banner

Open in Callysto

4. Filtering Datasets

We have experimented with visualizing open source data in a browser. However, lets broaden our perspective and consider how designing queries is an important part of designing any data-drive applications, including many of those that run on phones and tablets. For example, consider a mobile app that plots schools within walking distance to the user on a map. We want to plot only a small subset of all schools in Calgary. How should this application filter the data?

There are two general approaches, named after the location where the data actually gets filtered: the server (i.e. where the data is stored) or the client (where the data is used). These may be on same device, or they may be separated. They are also very likely running on different technologies. Filtering on either side has its pros and cons:

Server-side filtering

You have already seen this type of filter through the use of tht WHERE clause in SODA. This approach reduces the amount of data that you receive from wherever that data is stored. The data is likely being sent across a network (in the case of cloud-based data) or from a different application (in the case of a more traditional database system). If the speed of the data transmission is limited (e.g. slow internet) or if the data transmission is costly (e.g. the user’s data plan is limited), then you may very well wish to use a server-side filter.

For example, below is our familiar SODA query for obtaining all schools within Calgary. I’ve created a WHERE clause that finds all schools within a ‘square’ around the ‘current’ location, which we will assume to be William Aberhart High School (51.07866950960592°, -114.11533747920511°). To keep things simple, I will make my square 2 \(\times\) 0.01 degree latitude by 2 \(\times\) 0.01 degree longitude.

if you are curious, according to this calculator, 0.01 degree latitude equals about 1.112 km, and one minute longitude at Calgary’s latitude equals about 0.702 kilometres, so this would be a box about 2.2 by 1.4 km

import requests as rq
import pandas as pd
import io as io

domain = "https://data.calgary.ca/resource/"
uuid_school_locations = "fd9t-tdn2"

def run_query(domain, uuid, query):
    session = rq.Session()
    results = session.get(domain + uuid +".csv?$query=" + query)
    dataframe =  pd.read_csv(io.StringIO(results.content.decode('utf-8')))
    return dataframe

latitude = 51.07866950960592
longitude = -114.11533747920511

query_template = """
SELECT
    *
WHERE
    latitude BETWEEN '{0}' AND '{1}' AND
    longitude BETWEEN '{2}' AND '{3}'
"""
#    longitude BETWEEN '-114.10533747920511' AND '-114.12533747920511'

query = query_template.format(latitude - 0.01, latitude + 0.01, longitude + 0.01, longitude - 0.01)

local_schools = run_query(domain, uuid_school_locations, query)
local_schools
name board type name_ab address_ab city province grades postsecond data_source ... ecs elem junior_h senior_h process_dt postal_cod structure_id longitude latitude location
0 Banff Trail The Calgary School Division Public School Banff Trail School 3232 Cochrane Road N.W. Calgary AB Elementary N Provincial Data ... N Y N N 2020-05-31T00:00:00.000Z T2M4J3 1383034 -114.116759 51.081639 \n, \n(51.08163947732653, -114.11675875440444)
1 Branton The Calgary School Division Public School Branton School 2103 - 20 Street N.W. Calgary AB Elementary/Junior High N Provincial Data ... N Y Y N 2020-05-31T00:00:00.000Z T2M3W1 1381141 -114.109030 51.071247 \n, \n(51.07124691272822, -114.10903047499593)
2 Senator Patrick Burns The Calgary School Division Public School Senator Patrick Burns School 2155 Chilcotin Road N.W. Calgary AB Elementary/Junior High N Provincial Data ... N Y Y N 2020-05-31T00:00:00.000Z T2L0X2 1383123 -114.115537 51.088211 \n, \n(51.08821109631006, -114.11553670016748)
3 William Aberhart High The Calgary School Division Public School William Aberhart High School 3009 Morley Trail N.W. Calgary AB Senior High N Provincial Data ... N N N Y 2020-05-31T00:00:00.000Z T2M4G9 1382288 -114.115337 51.078670 \n, \n(51.07866950960592, -114.11533747920511)

4 rows × 21 columns

Note

  • We can easily re-create the query by changing the latitude and the longitude, and creating a newly formatted query using the query_template.

  • But then we would have to resubmit the query, and this may take quite some time.

Client-side filtering

The opposite approach is to obtain an entire dataset and to then programatically filter using your native language. The pandas library provides various tools that allow you to easily select specific records in a dataframe. In this case, we will use the panda dataframe’s loc function on the entire dataset. The output should be equivalent to the server-side filter’s output

query = """
SELECT
    *
"""

all_schools = run_query(domain, uuid_school_locations, query)

local_schools = all_schools.loc[(all_schools ['latitude'] > latitude - 0.01) 
                                & (all_schools ['latitude'] < latitude + 0.01)
                                & (all_schools ['longitude'] > longitude - 0.01)
                                & (all_schools ['longitude'] < longitude + 0.01)]
local_schools
name board type name_ab address_ab city province grades postsecond data_source ... ecs elem junior_h senior_h process_dt postal_cod structure_id longitude latitude location
20 Banff Trail The Calgary School Division Public School Banff Trail School 3232 Cochrane Road N.W. Calgary AB Elementary N Provincial Data ... N Y N N 2020-05-31T00:00:00.000Z T2M4J3 1383034 -114.116759 51.081639 \n, \n(51.08163947732653, -114.11675875440444)
280 Branton The Calgary School Division Public School Branton School 2103 - 20 Street N.W. Calgary AB Elementary/Junior High N Provincial Data ... N Y Y N 2020-05-31T00:00:00.000Z T2M3W1 1381141 -114.109030 51.071247 \n, \n(51.07124691272822, -114.10903047499593)
396 Senator Patrick Burns The Calgary School Division Public School Senator Patrick Burns School 2155 Chilcotin Road N.W. Calgary AB Elementary/Junior High N Provincial Data ... N Y Y N 2020-05-31T00:00:00.000Z T2L0X2 1383123 -114.115537 51.088211 \n, \n(51.08821109631006, -114.11553670016748)
451 William Aberhart High The Calgary School Division Public School William Aberhart High School 3009 Morley Trail N.W. Calgary AB Senior High N Provincial Data ... N N N Y 2020-05-31T00:00:00.000Z T2M4G9 1382288 -114.115337 51.078670 \n, \n(51.07866950960592, -114.11533747920511)

4 rows × 21 columns

Visualization

So, with all that work done, let’s do the visualization.

import plotly.express as px

figure1 = px.scatter_mapbox(local_schools, lat="latitude", lon="longitude",
                            color_discrete_sequence=["blue"],
                            zoom=13, height=600,
                            size='latitude',
                            size_max = 15,
                            hover_name="name",
                            hover_data=["type", "grades", "address_ab"],)

figure1.update_layout(mapbox_style="open-street-map")
figure1.update_layout(margin={"r":0,"t":0,"l":0,"b":0})

But if we then changed our current location to be Sir Winston Churchill High School (51.10011269545306°, -114.1399900255147°), we can very quickly change our results without having to requery the City of Calgary server! It may still take some time to replot the data, but latency is not a concern.

local_schools2 = all_schools.loc[(all_schools ['latitude'] > 51.09011269545306) 
                                & (all_schools ['latitude'] < 51.11011269545306)
                                & (all_schools ['longitude'] < -114.1299900255147)
                                & (all_schools ['longitude'] > -114.1499900255147)]
local_schools2
name board type name_ab address_ab city province grades postsecond data_source ... ecs elem junior_h senior_h process_dt postal_cod structure_id longitude latitude location
53 Varsity Acres The Calgary School Division Public School Varsity Acres School 4255 - 40 Street N.W. Calgary AB Elementary N Provincial Data ... N Y N N 2020-05-31T00:00:00.000Z T3A0H7 1383570 -114.148218 51.091014 \n, \n(51.09101439261288, -114.14821824727312)
353 Simon Fraser The Calgary School Division Public School Simon Fraser School 5215 - 33 Street N.W. Calgary AB Elementary/Junior High N Provincial Data ... N Y Y N 2020-05-31T00:00:00.000Z T2L1V3 1383906 -114.136550 51.100319 \n, \n(51.10031940846632, -114.13655045217867)
357 St. Jean Brebeuf The Calgary Roman Catholic Separate School Div... Separate School St. Jean Brebeuf School 5030 Northland Drive N.W. Calgary AB Junior High N Provincial Data ... N N Y N 2020-05-31T00:00:00.000Z T2L2J6 1369132 -114.140229 51.097893 \n, \n(51.09789338393484, -114.14022892058524)
370 Dr. E. W. Coffin The Calgary School Division Public School Dr. E. W. Coffin School 5615 Barrett Drive N.W. Calgary AB Elementary N Provincial Data ... N Y N N 2020-05-31T00:00:00.000Z T2L1W4 1383141 -114.134799 51.103753 \n, \n(51.103752632520376, -114.13479912704769)
444 Captain John Palliser The Calgary School Division Public School Captain John Palliser School 1484 Northmount Drive N.W. Calgary AB Elementary N Provincial Data ... N Y N N 2020-05-31T00:00:00.000Z T2L0G6 1000998 -114.136016 51.096365 \n, \n(51.09636485434405, -114.13601610820098)
467 Sir Winston Churchill High The Calgary School Division Public School Sir Winston Churchill High School 5220 Northland Drive N.W. Calgary AB Senior High N Provincial Data ... N N N Y 2020-05-31T00:00:00.000Z T2L2J6 1383224 -114.139990 51.100113 \n, \n(51.10011269545306, -114.1399900255147)

6 rows × 21 columns

figure1 = px.scatter_mapbox(local_schools2, lat="latitude", lon="longitude",
                            color_discrete_sequence=["blue"],
                            zoom=13, height=600,
                            size='latitude',
                            size_max = 15,
                            hover_name="name",
                            hover_data=["type", "grades", "address_ab"],)

figure1.update_layout(mapbox_style="open-street-map")
figure1.update_layout(margin={"r":0,"t":0,"l":0,"b":0})

Note

There are many many functions within the pandas dataframe class that allow you to filter, combine, and transform data. Learning these would take time, and is outside of our scope here. This course is about learning how to query for data.

Which approach is best?

That depends, of course!

If your application will show multiple views of the data set within a short time, it makes sense to download it once in its entirety. Requerying for the data for each view would obviously be inefficient.

However, loading the data in its entirity will cost more bandwith, and may take time up front. Also, your data will at some point go stale; hence the ‘within a short time’ qualifier.

So why not just query for little chunks of data only when needed? Well, you probably have already noticed that executing a query takes some time. The query has to pass from your local device to a remote server, the server has to retrieve the data and format it, and the the server has to pass it back across a network. The lag in time between query and response is known as latency. Latency may make your application appear to freeze. This may be frustrating to your user. Never mind what your user may experience if they lose their network connection completely.

The time gaps in between the various steps may be fractions of seconds, but that is an eternity compared to how fast your local device can process locally stored data. Thus, loading your data up front may be a good design decision.

In the above example, the client-side approach would probably be best. The dataset is relatively small, so data usage is reasonable, unless the data plan really is restrive. Also, the data is highly static, as not many schools will get added in the next month, and none will change location. Thus, it would be good design to download the dataset, and perhaps even store it locally in a csv file. You can then move all around the city, and the map can be updated very quickly and without extra data transmission. Of course, your app will still want to update the dataset once in a while, and may need to load the Edmonton data if we change cities!

An alternate example could be an app that checks if any of your friends are within walking distance. Perhaps this app has a few million users, and their locations are being stored on a server somewhere in Silicon Valley. You would not want to download the entire dataset, as 99.99% of it would be useless to you. Furthermore, the data is highly dynamic, and if you don’t update continuously, you will have a useless app. In this example, the server-side approach would undoubtedly be best.

Final Thoughts

  • Hopefully you appreciate the point that you can ‘massage’ your data both locally and remotely. and that you choose which way you go based on performance.

  • The technology you work with will also have an impact on which approach you take. Other open data APIs do not have the ability to perform server-side filtering (and aggregation), and you may be forced to go client-side.

  • To be quite honest, there will also be times where it is just easier to do your filtering (and aggregation) on the client side, instead of breaking your brain in writing the perfect SQL statement!

  • Whereas the previous lessons dealt with ‘big data’ (aggregating finding patterns in large sets), this lesson dealt with ‘specific data’ (querying for specific information).

Callysto.ca License

3. Joining Datasets Languages notebooks