ICON
Help Center / Webservice Select /

Webservice Select

Introduction

The "select" data request can be used to obtain data from datasets available within your Spotzi Account. You can obtain either a single record or the entirety of your selected dataset. "Select" data requests can be made by adding a SELECT request to the "q" parameter.

Basic Select Query

A typical select query generally follows the structure below:

SELECT *
FROM name_dataset
WHERE type = 'A'

The above statement is divided into:

Select list

The first section of the select query:

SELECT *

States which columns the select statements needs to return. Using the * character allows you to retrieve all columns; you can also retrieve specific columns by naming them explicitly. For example:

SELECT name, description

Table list

The second section of the select query:

FROM name_dataset

Specifies which dataset is being requested. The columns described in the first part of the query must exist within this dataset for this query to work.

Optional Qualification

The final — and optional — section of the select query:

WHERE type = 'A'

This specifies which rows are being requested. All rows meeting the requirements of the WHERE statement will be returned.

Examples

Below you will find some example queries that can be used via the Spotzi Webservice. The table names and fields used in these examples must be available in the requested webservice account to display valid results.

SELECT *
FROM neighbourhood

The above statement returns all fields within the table "neighbourhood".

SELECT neighbourhood_name, population
FROM neighbourhood

The above statement returns the neighbourhood_name and the population of the neighbourhood table.

SELECT neighbourhood_name, (100 * total_males / population) as male_percentage
FROM neighbourhood

The above statement returns a calculated percentage of male inhabitants in each neighbourhood.

SELECT neighbourhood_name, (100 * total_males / population) as male_percentage
FROM neighbourhood
WHERE type = 'urban'

The above statement returns a calculated percentage of the male inhabitants within all urban neighbourhoods.

SELECT neighbourhood_name, 100 * total_males / population as male_percentage
FROM neighbourhood
WHERE type = 'urban'
ORDER BY neighbourhood_name
LIMIT 10

The above statement uses the ORDER BY and LIMIT statements. The ORDER BY statement orders the result by the requested field; The LIMIT statement limits the number of these results.

For more information about SQL SELECT statements, please consult the SQL section of our help center or see the official PostgreSQL tutorial.

Related blog posts

Access the Free Spotzi Explorer

Gain access to our free catalog of maps and dashboards. Find Postal Codes, learn more about global income differences or just get inspired by our community maps. Spotzi Explorer is our free solution for every marketer, researcher, student and beyond. Available as a stand alone solution and part of our price plans.

Get free access to Spotzi Explorer Read more
thumbnail Increase Campaign Effectiveness with Near Real-Time OOH Measurement

Blog

Increase Campaign Effectiveness with Near Real-Time OOH Measurement