Working with SQL statements in CartoDB enables the user to query his or her dataset in various ways and create an entirely new selection or layer from it. In this post, we will demonstrate using SQL statements to create new selection or layer from an existing dataset.
– We will sum attribute values from the attribute field of the layer;
– Perform a Join between the attribute Table of the two layers;
Sign into CartoDB account and click on new map.
Connect Dataset and Click on Data file. Upload the sample datasets you downloaded.
Select and Add the layer- for the demo sw_licensing1
In the CartoDB map page, click on the DATAVIEW to inspect the attribute fields and their values.
On the screenshot example below, you will observe that the table has no location data (the_geom field is null).
Our first goal is to get the sum of the total number of plates sold per state and then
join the layer to a spatially reference data.
Click on the sql icon (cursor finger position on the screenshot)
as seen on the screenshot below. This is a Custom SQL query.
Click on create dataset from query (cursor finger position on the screenshot below).
The new dataset created from the query now includes attribute fields defined in
the expression- sales and state_id. For the demo, we decide to rename our new
dataset- license sales by state_id.
Next Add the second layer – point feature layer- license points.(this is included in the sample data download)
Once loaded into cartodb as a layer, click on its DATAVIEW. Notice that this layer has
values in its geometry attribute field, state_id and a name attribute field
Click on the MAPVIEW and the Point features will be displayed in their geographical
positions on a map.
Next we will perform a join between the table (sw licensing_1) and the point
feature layer (license points) using sql statements.
This will perform a spatial join between the table and layer.
View the example on the CartoDB screenshot below. Click Apply query.
Click on create query from dataset to produce a new selection. Click on DATAVIEW to verify
the two objects are now linked. See example in screenshot below. Notice that the license points
table now has assimilated some of the attribute fields from the sw_licensing table.
Thank you for following the demonstration.