Performing SQL queries and Spatial Join in CartoDB

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.

Capture-4

 

Click on the sql icon (cursor finger position on the screenshot)

Capture-5

 

image Write out this sql statement on the image to the left into the window

as seen on the screenshot below. This is a Custom SQL query.

Capture-7

 

Click on create dataset from query (cursor finger position on the screenshot below).

Capture-8

 

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.

Capture-9

 

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

Capture-11

 

Click on the MAPVIEW and the Point features will be displayed in their geographical

positions on a map.

Capture-12

 

Next we will perform a join between the table (sw licensing_1) and the point

feature layer (license points) using sql statements.

image Write the sql statement on the image on the left.

This will perform a spatial join between the table and layer.

View the example on the CartoDB screenshot below. Click Apply query.

Capture-14

 

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.

Capture-20

 

Thank you for following the demonstration.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s