SQL Queries

SQL Queries


Spotzi enables you to query data using the Structured Query Language (SQL). We provide particular support for PostGIS SQL commands, which allows you to filter data spatially. Some common uses of PostGIS SQL queries are to answer questions such as, “How many points from one dataset are located within a polygon from another dataset?” Or commands such as, “Give me all my data within 50 kilometers of a certain latitude longitude coordinate.”

Review the PostgreSQL documentation for how to use PostgreSQL flavored SQL, as well as the PostGIS documentation for tips on writing spatial queries.

Perform SQL queries


To run an SQL query in the Editor, open a dataset or map. In the righthand sidebar, click the SQL icon. The sidebar will expand and by default you will see the following query:

SQL Queries

SELECT * FROM tm_world_borders_0_3

You can modify this query and then hit the “Apply query” button or press cmd + s on a Mac OS or ctrl + s on a Windows OS.

You can find most of these SQL queries in the documentation of PostgreSQL and PostGIS. With SQL queries, you have access to a lot of editing options, like setting a radius on the website, but there SQL queries also enable you to make bulk edits in your data.

In this article of the User Guide, we will give you some basic SQL queries to modify your data more easily.

The WHERE Query


The WHERE query allows you to visualize a certain feature (point, polygon or line) of your data. In this example, we use the data with the boundaries of every country around the world. The name of this dataset is: countries_20160407_092628. Let’s try to only visualize the country of the Netherlands.

In this case the SQL query looks like this:

SELECT * FROM dataset WHERE column name = 'value'

When we only want to visualize the Netherlands, we have to use the following SQL query in this example:

SELECT * FROM tm_world_borders_0_3 WHERE country = 'Netherlands'

SQL Queries

Now let’s try to only visualize the country of the Netherlands and Canada.

In this case the SQL query looks like this:

SELECT * FROM dataset WHERE column name = 'value' OR column name = 'value'

When we only want to visualize the Netherlands and Canada, we have to use the following SQL query in this example:

SELECT * FROM tm_world_borders_0_3 WHERE country = 'Netherlands' OR country = 'Spain'

SQL Queries

Only the rows that match this SQL query will appear in the dataset and map view for this dataset. Remember that when applying SQL queries to maps, only the queried data will be shown. Also this will NOT permanently change your data.

The UPDATE Query


The UPDATE query brings out the real data editing magic. Especially when you need to edit data in multiple rows or columns. In this example, we still use the data with the boundaries of every country around the world. The name of this dataset is: tm_world_borders_0_3. In this table, I already added a new column, called continent.

Right now, all countries still have a NULL value in the country_continent. Let’s try to change all country_continent to WORLD.

In this case the SQL query looks like this:

UPDATE dataset SET column name = 'value'

When we want to change all values in the country_continent to WORLD, we have to use the following SQL query in this example:

UPDATE tm_world_borders_0_3 SET continent = 'World'

SQL Queries

Now, all continent values are set to ‘World’. Please note that now you have changed all values permanently. That means that if you have changed data that you use in multiple visualizations. All these visualizations have an updated data table. If you just want to change the data of a certain visualization, be sure that you use a copy of the data and a new visualization.

Only update a few selected rows


Now let’s try to only edit certain countries. In our example, we will try to change the countries Australia and New Zealand from country_continent WORLD to OCEANIA.

In this case the SQL query looks like this:

UPDATE dataset SET column name = 'value' WHERE column name = 'value' OR column name = 'value'

When we only want to update , we have to use the following SQL query in this example:

UPDATE tm_world_borders_0_3 SET continent = 'Oceania' WHERE country = 'Australia' OR country = 'New Zealand'

SQL Queries

Only the rows that match this SQL query will be edited in the dataset. As you can see right now, the country_continent values of the ‘Australia’ and ‘New Zealand’ rows are changed to OCEANIA.

Update multiple rows based on multiple values


It is also possible to edit values of a certain row based on certain column values. This enables you to edit only particular rows in large datasets. Our system will automatically go through this dataset and edit the rows that accord with these particular values.

In this example, we will try to edit the column values ‘country_continent’ and ‘image’ of Monaco based on the columns ‘country’ and ‘iso_3digit’. To achieve this, the general SQL query looks like this:

UPDATE dataset SET column name 1 = 'value', column name 2 = 'value' WHERE column name 3 = 'value' AND column name 4 = 'value'

In this example, the SQL query would look like this:

UPDATE tm_world_borders_0_3 SET continent = 'South America' WHERE country = 'Argentina' AND iso3 = 'ARG'

SQL Queries

How SQL queries can make data editing easier? A few use cases.


We often get questions from businesses that have bought a ZIP code boundary map that they want to divide in different work areas based on multiple ZIP code values. That’s why we created these use cases that will help you to easily create large work areas based on multiple ZIP codes.

In this example, we will use a small 4-digit ZIP code location dataset with an extra work_area column. This dataset has the following name: zip_code_work_area_20161025_155936

Use Case 1: I want to change all ZIP code areas to work area number 45.


Open de SQL query module in the advanced editor and type the following query:

UPDATE zip_code_work_area_20161025_155936

SET work_area = '45'

In this query, you are updating (UPDATE) the dataset (zip_code_work_area_20161025_155936) you are setting (SET) every value in the column work_area to 45.

Use Case 2: I want to change only the ZIP code area 3761 to work area number 30.


You can also do this manually by clicking this particular ZIP code area and editing the data. This is well-described in our user guide article about Info Window Options. If you want to use SQL queries to edit the data, you have to do the following:

UPDATE zip_code_work_area_20161025_155936

SET work_area = '30'

WHERE ZIP = '3761'

In this query, you are updating (UPDATE) the dataset (zip_code_work_area_20161025_155936) you are setting (SET) the value of the ZIP code area (ZIP) 3761 (‘3761‘) in the column work_area to 30.

Use Case 3: I want to change only the ZIP code areas 3741, 3742, 3743 and 3744 to work area number 50.


Open de SQL query module in the advanced editor and type the following query:

UPDATE zip_code_work_area_20161025_155936

SET work_area = '50'

WHERE ZIP in ('3761','3762','3763','3764')

In this query, you are updating (UPDATE) the dataset (zip_code_work_area_20161025_155936) you are setting (SET) the work area value of the ZIP code areas (ZIP) 3761 (‘3741‘,’3742‘,’3743‘,’3744‘) in the column work_area to 50.

Use Case 4: I want to change the ZIP code areas 3762 up to and including 3764 to work area number 55.


Open de SQL query module in the advanced editor and type the following query:

UPDATE zip_code_work_area_20161025_155936

SET work_area = '55'

WHERE ZIP BETWEEN '3762' AND '3764'

In this query, you are updating (UPDATE) the dataset (zip_code_work_area_20161025_155936) and you are setting (SET) the work area values of the ZIP code areas (ZIP) between (BETWEEN) 3762 (‘3762‘) and (AND) 3764 (‘3764‘) in the column work_area to 55.

Use Case 5: I only want to change the ZIP code areas that begin with the number 376 to work area number 70.


Open de SQL query module in the advanced editor and type the following query:

UPDATE zip_code_work_area_20161025_155936

SET work_area = '70'

WHERE ZIP::text LIKE '376%'

In this query, you are updating (UPDATE) the dataset (zip_code_work_area_20161025_155936) and you are setting (SET) the work area values of the ZIP code areas (ZIP) that start with 376 (LIKE 376%‘) in the column work_area to 70. You have to use ZIP::text, because this is a textual function.