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.”

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:

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'

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'

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'

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'

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:

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.

Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful. For more information about our cookie statement, please visit our Privacy Policy.

You can adjust all of your cookie settings by navigating the tabs on the left hand side.

Functional/Analytical Cookies

Functional Cookies should be enabled at all times so that we can save your preferences for cookie settings.

These cookies let us operate certain functions of the Spotzi Service in line with the choices you make. These cookies mean that when you continue to use or come back to the Spotzi Service, we can provide you with our services as you have asked for them to be provided, such as knowing your username, remembering how you have customised our services, and reminding you of content you have enjoyed.

We also use analytical cookies to analyse how the Spotzi Service is accessed, is used, or is performing. We use this information to maintain, operate, and continually improve the Spotzi Service.

disable

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.

Marketing Cookies

We use these cookies that to track the pages that you have visited. Based on this information we can serve you offers that are completely adjusted to your interests.

disable

Please enable Strictly Necessary Cookies first so that we can save your preferences!