Visualizing SQL layers as web maps using Carto JS

Carto (formerly known as CartoDB) layers have SQL capabilities as they are stored in a cloud based PostGIS server. Users can initiate SQL expressions and visualize

the result as web maps. This post will demonstrate how to query Carto JS visualizations and  create web maps.

* You should have a Carto Account to follow this practice.

 

We will be carrying out two queries – i) Query on attributes ii) Spatial query

 

i) Attribute query will select from the layer which of the features have the value=pry in the attribute column- Type

var query = {
sql: “SELECT * FROM schoolmapping where type = ‘pry'”,
cartocss: “#schoolmapping{marker-fill:transparent; marker-width: 15; marker-line-color:blue; marker-line-width: 2;}”
}

Below is the screenshot of the visualization on a leaflet powered web map using carto JS.  On the visualization, the selected features from the

query are styled to be transparent so they will show as transparent circle markers with blue outline overlaying the original layer that was queried.

12

View the web mapby clicking here:http://preview.github.io/?https://github.com/mayotunde/CartoSql/blob/master/simplequery.html

Below is the full source code that created the web map on the screenshot above:

SQL query by attribute CartoDB - Notepad

Note that the carto layer was loaded twice as var points and var vizjson_url so as to still retain a viz of the original layer for explanation purposes.

You can download the plain text version of the source code by clicking here:

https://www.dropbox.com/s/ss02c22rtkx8geo/SQL%20query%20by%20attribute%20CartoDB.txt?dl=0

 

ii) Spatail Query

In this section of the post, we will perform a spatial query by creating a buffer about a carto layer and vixualizing it as a web map

using carto JS.

var buffer = {
sql:(“SELECT ST_Buffer(the_geom_webmercator,0.3*1609) AS the_geom_webmercator,cartodb_id FROM dam”),
cartocss:”#dam{polygon-fill:transparent; line-color:#f45; line-width: 2}”
}

The buffer has a distance 0f  0.3 converted to miles is created around the carto layer ‘dam’. This layer is located in our carto cloud

PostGIS database. Below is the visualization of the result of the query. Note that the red boxed area in the centre highlights the buffer

created around the original layer-dam.

13

 

View the live web map at:http://htmlpreview.github.io/?https://github.com/mayotunde/CartoSql/blob/master/sqlbuffer.html

The source code for the web map above is thus:

bufferanalysis - Notepad-001

You can download the plain text version of the source code by clicking this link:

https://www.dropbox.com/s/5hob186iym26rrh/bufferanalysis.txt?dl=0

 

Thank you for following the blog.

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