ICON

Help - Webservice Select

Help Center Integrations Webservice Select

Webservice Select

1 MIN EXPERT DATA INTEGRATION, WEBSERVICE, API, INTEGRATE DATA

Introduction


The select data request can be used to obtain data from one of the datasets available in your Spotzi Mapbuilder Account. You can obtain a single record or the dataset as a whole. You can make a select data request by adding an SELECT request with the ‘q’ parameter.

Basic Select Query


A typical select query is generally of the following form:

SELECT *
FROM name_dataset
WHERE type = 'A'

The above statement is divided into:

  • a select list (the part that lists the columns to be returned)
  • a table list (the part that lists the tables from which to retrieve the data)
  • an optional qualification (the part that specifies one or more restrictions)

Select list

The select list:

SELECT *

States which columns the select statements needs to return. By using the * character you will retrieve all columns. You can retrieve specific columns by naming them explicitly, for example:

SELECT name, description

Table list

The second part of the select query, the table list:

FROM name_dataset

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

Optional Qualification

The last part of the query is optional:

WHERE type = 'A'

It specifies which rows are requested. All rows that meet the requirements of the WHERE statement are returned.

Examples

Below you will find some example queries that can be used with the Spotzi Webservice. The tablenames and fields that are used in these examples need to be available in the requested webservice account to show a valid result.

SELECT *
FROM neighbourhood

The above statement returns all fields of the table neighbourhood.

SELECT neighbourhood_name, population
FROM neighbourhood

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

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

The above statement returns a calculated percentage of the male percentage of the inhabitants of 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 percentage of the inhabitants of 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 statement. The ORDER BY statement orders the result by the requested field. The LIMIT statement limits the number of results.

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

Related blog posts

Interested in our solutions? Please contact our sales team.

Whether you are new to data analytics or already an expert. We offer solutions for everyone. You don’t need to be a data expert to work with Spotzi Location. For more tailor made solutions we offer Spotzi Studio. With this plan you cannot only manage the data yourself but also connect to our API and manage users for the dashboards you create. Or work with our Market Ready Dashboards and drive on autopilot.