Skip to content

carto.md

Latest commit

 

History

History
419 lines (322 loc) · 7.94 KB

carto.md

File metadata and controls

419 lines (322 loc) · 7.94 KB

How to visualize spatial queries with CARTO

  1. Go to CARTO Basic Viewer application.
  2. Copy and paste the following queries within the SQL console and click CTRL+S.

Index

Making lines

Simple lines

WITH 
  b as (SELECT * FROM populated_places WHERE name ilike 'barcelona' and adm0name ilike 'spain'),
  m as (SELECT * FROM populated_places WHERE name ilike 'madrid')

SELECT
  ST_Transform(ST_MakeLine(b.the_geom, m.the_geom),3857) as the_geom_webmercator,
  b.cartodb_id
FROM
  b, m

simple-line

Multiple lines

WITH 
  spain as (SELECT * FROM populated_places WHERE adm0name ILIKE 'spain'),
  madrid as (SELECT * FROM spain WHERE name ILIKE 'Madrid')

SELECT
  ST_Transform(ST_MakeLine(m.the_geom, s.the_geom),3857) as the_geom_webmercator,
  s.cartodb_id
FROM
  spain s, madrid m
WHERE
  s.cartodb_id <> m.cartodb_id

multi-lines

Sequential lines

SELECT 
  ST_MakeLine(the_geom_webmercator ORDER BY ST_X(the_geom), ST_Y(the_geom) ASC) as the_geom_webmercator,
  min(cartodb_id) as cartodb_id
FROM
  populated_places
WHERE
  adm0name ILIKE 'spain'

seq-lines

Creating areas of influence

Simple buffer

SELECT
  ST_Transform(
    ST_Buffer(
      the_geom::geography,
      10000*5
    )::geometry,
  3857) As the_geom_webmercator,
  1 as cartodb_id
FROM 
  populated_places
WHERE 
  adm0name ILIKE 'spain'

lines

SELECT
  ST_Transform(
    ST_Buffer(
      the_geom::geography,
      10000*5
    )::geometry,
  3857) As the_geom_webmercator,
  1 as cartodb_id
FROM 
  populated_places
WHERE 
  name ILIKE 'madrid'

  UNION ALL

SELECT
  ST_Transform(
    ST_Buffer(
      the_geom::geography,
      10000*10
    )::geometry,
    3857) As the_geom_webmercator,
  2 as cartodb_id
FROM
  populated_places
WHERE 
  name ILIKE 'madrid'

multiple-buffers

Negative buffer

SELECT 
  cartodb_id,
  ST_Transform(
    ST_Buffer(
      the_geom::geography,
      -10000
    )::geometry,
  3857) As the_geom_webmercator
FROM
  world_borders
WHERE
  name ilike 'spain'

negative-buffers

Getting the closest points

WITH m as (SELECT * FROM populated_places WHERE name ILIKE 'madrid')

SELECT 
  p.* 
FROM 
  populated_places p, m
WHERE
  m.cartodb_id <> p.cartodb_id
ORDER BY 
  p.the_geom <-> m.the_geom /* ST_Distance(m.the_geom, p.the_geom) */
LIMIT 20

closest-points

You can read more about finding nearest neighbor here and here.

SELECT 
  b.cartodb_id,
  b.the_geom_webmercator
FROM
  populated_places a,
  populated_places b
WHERE
  ST_DWithin(a.the_geom_webmercator,b.the_geom_webmercator,1000000)
AND 
  a.name ILIKE 'madrid'
AND 
  a.cartodb_id <> b.cartodb_id

distance-within

Intersecting

Points intersecting polygons

SELECT
  p.*
FROM
  populated_places p, world_borders w
WHERE
  w.name ILIKE 'spain'
AND
  ST_Intersects(w.the_geom, p.the_geom)

intersect

Aggregating points within polygons

SELECT
  w.cartodb_id,
  w.the_geom_webmercator,
  count(p.*)/ST_Area(w.the_geom) as cities_density,
  sum(p.pop_max)/ST_Area(w.the_geom) as pop_density
FROM
  populated_places p, world_borders w
WHERE
  ST_Intersects(w.the_geom, p.the_geom)
GROUP BY 1, 2

You can change the style to create a choropleth based on cities or population density. Replacing polygon-fill: #826DBA; with polygon-fill: ramp([pop_density], cartocolor(Sunset), quantiles); should do the trick.

aggregating

Clipping

Simple clipping

SELECT
  a.cartodb_id,
  ST_Difference(
    a.the_geom_webmercator,
    b.the_geom_webmercator
  ) as the_geom_webmercator
FROM
  ne_50m_land a,
  world_borders b
WHERE
  b.name ILIKE 'spain'

clip

Donnuts

WITH 
  a as (
    SELECT
      ST_Transform(ST_Buffer(the_geom::geography,10000*5)::geometry, 3857) As the_geom_webmercator,
      cartodb_id
    FROM
      populated_places
    WHERE 
      name ILIKE 'madrid'),

  b as (
    SELECT
      ST_Transform(ST_Buffer(the_geom::geography,10000*10)::geometry, 3857) As the_geom_webmercator,
      cartodb_id
    FROM
      populated_places
    WHERE 
      name ILIKE 'madrid')

SELECT 
  b.cartodb_id,
  ST_Difference(
    b.the_geom_webmercator,
    a.the_geom_webmercator
  ) as the_geom_webmercator
FROM
  a, b

donnuts

You can read more about how to ring buffers with several distances here.

Traslating

SELECT
  cartodb_id,
  ST_Transform(ST_Translate(the_geom,5.0,7.4), 3857) as the_geom_webmercator
FROM
  ne_50m_land
WHERE 
 ST_Intersects(
   the_geom, 
   ST_MakeEnvelope(-18.748169,27.571591,-13.342896,29.463514,4326)
 )

traslating

Changing projections

World Robinson

SELECT
  cartodb_id, 
  ST_Transform(the_geom, 54030) AS the_geom_webmercator
FROM
  ne_50m_land

robinson

You can read more about how to change map projections in CARTO here. In addition, you can check and play with map projections here and here.

Great circles

WITH 
  spain as (SELECT * FROM populated_places WHERE adm0name ILIKE 'spain'),
  madrid as (SELECT * FROM spain WHERE name ILIKE 'Madrid')

SELECT 
  ST_Transform(
    ST_Segmentize(
      ST_Makeline(
        m.the_geom, 
        s.the_geom
      )::geography, 
      100000
    )::geometry,
    3857
  ) as the_geom_webmercator,
  s.cartodb_id
FROM 
  spain s, madrid m
WHERE 
  m.cartodb_id <> s.cartodb_id

gc

You can read more about making great circles here.

CARTO spatial functions

CDB_LatLng()

SELECT 
  1 as cartodb_id,
  ST_Transform(CDB_LatLng(0, 0), 3857) as the_geom_webmercator  /* ST_SetSRID(ST_MakePoint(0, 0), 4326) */

cdb_latlng

Grids

WITH grid as (
  SELECT
    row_number() over () as cartodb_id,
    CDB_HexagonGrid(
      ST_Buffer(the_geom_webmercator, 1000000),
      10000
    ) AS the_geom_webmercator
  FROM
    world_borders
  WHERE
    name ILIKE 'spain')

SELECT
  grid.the_geom_webmercator,
  grid.cartodb_id
FROM
  grid, world_borders a
WHERE
  ST_Intersects(grid.the_geom_webmercator, a.the_geom_webmercator)
AND 
  name ILIKE 'spain'

hexagons

WITH grid as (
  SELECT
    row_number() over () as cartodb_id,
    CDB_RectangleGrid(
      ST_Buffer(the_geom_webmercator, 1000000),
      25000,
      25000
    ) AS the_geom_webmercator
  FROM
    world_borders
  WHERE
    name ILIKE 'spain')

SELECT
  grid.the_geom_webmercator,
  grid.cartodb_id
FROM
  grid, world_borders a
WHERE
  ST_Intersects(grid.the_geom_webmercator, a.the_geom_webmercator)
AND 
  name ILIKE 'spain'

rectangles

You can read more about CARTO custom spatial queries here.