With this viewer you will have a sandbox space to try different PostGIS capabilities using several datasets. Change the SQL and press Control+S or Command+S and check the results on the map on the right.
You can also play with the styles on the CartoCSS pane.
- Transform to a different projection
- Get the number of points inside a polygon
- Know wether a geometry is within the given range from another geometry:
- Create a buffer from points:
- Get the difference between two geometries:
- Create a straight line between two points:
- Create great circles between two points:
- Generating Grids with CDB functions
SELECT
cartodb_id,
ST_Transform(the_geom, 54030) AS the_geom_webmercator
FROM
ne_50m_landAbout working with different projections in CARTO and ST_Transform.
Using GROUP BY:
SELECT
e.cartodb_id,
e.admin,
e.the_geom_webmercator,
count(*) AS pp_count,
sum(p.pop_max) as sum_pop
FROM
ne_adm0_europe e
JOIN
ne_10m_populated_places_simple p
ON
ST_Intersects(p.the_geom, e.the_geom)
GROUP BY
e.cartodb_idUsing LATERAL:
SELECT
a.cartodb_id,
a.admin AS name,
a.the_geom_webmercator,
counts.number_cities,
counts.sum_pop
FROM
ne_adm0_europe a
CROSS JOIN LATERAL
(
SELECT
count(*) as number_cities,
sum(pop_max) as sum_pop
FROM
ne_10m_populated_places_simple b
WHERE
ST_Intersects(a.the_geom, b.the_geom)
) AS countsAbout ST_Intersects and Lateral JOIN
Note: Add this piece of CartoCSS at the end so you have a nice coropleth map:
#layer['mapnik::geometry_type'=3] {
line-width: 0;
polygon-fill: ramp([pp_count], ("#edd9a3","#f99178","#ea4f88","#a431a0","#4b2991"), quantiles(5));
}This is using the new turbo-carto feature on CARTO to allow creating ramps from data without having to put the styles directly
Note: You know about the EXPLAIN ANALYZE function? use it to take a look on how both queries are pretty similar in terms of performance.
SELECT
a.*
FROM
ne_10m_populated_places_simple a,
ne_10m_populated_places_simple b
WHERE
a.cartodb_id != b.cartodb_id
AND ST_DWithin(
a.the_geom_webmercator,
b.the_geom_webmercator,
150000
)
AND a.adm0name = 'Spain'
AND b.adm0name = 'Spain'In this case, we are using the_geom_webmercator to avoid casting to geography type. Calculations made with geometry type takes the CRS units.
Keep in mind that CRS units in webmercator are not meters, and they depend directly on the latitude.
About ST_DWithin.
SELECT
cartodb_id,
name,
ST_Transform(
ST_Buffer(the_geom::geography, 250000)::geometry
,3857
) AS the_geom_webmercator
FROM
ne_10m_populated_places_simple
WHERE
name ilike 'trondheim'Compare the result with
SELECT
cartodb_id,
name,
ST_Transform(
ST_Buffer(the_geom, 2)
,3857
) AS the_geom_webmercator
FROM
ne_10m_populated_places_simple
WHERE
name ilike 'trondheim'Why this is not a circle?
About ST_Buffer.
SELECT
a.cartodb_id,
ST_Difference(
a.the_geom_webmercator,
b.the_geom_webmercator
) AS the_geom_webmercator
FROM
ne_50m_land a,
ne_adm0_europe b
WHERE
b.adm0_a3 like 'ESP'About ST_Difference.
SELECT
ST_MakeLine(
a.the_geom_webmercator,
b.the_geom_webmercator
) as the_geom_webmercator
FROM (
SELECT * FROM ne_10m_populated_places_simple
WHERE name ILIKE 'madrid'
) as a,
(
SELECT * FROM ne_10m_populated_places_simple
WHERE name ILIKE 'barcelona'AND adm0name ILIKE 'spain'
) as bAbout ST_MakeLine.
SELECT
ST_Transform(
ST_Segmentize(
ST_Makeline(
a.the_geom,
b.the_geom
)::geography,
100000
)::geometry,
3857
) as the_geom_webmercator
FROM
(SELECT * FROM ne_10m_populated_places_simple
WHERE name ILIKE 'madrid') as a,
(SELECT * FROM ne_10m_populated_places_simple
WHERE name ILIKE 'new york') as bAbout Great Circles.
SELECT
row_number() over () as cartodb_id,
CDB_RectangleGrid(
ST_Buffer(the_geom_webmercator,125000),
250000,
250000
) AS the_geom_webmercator
FROM
ne_adm0_europe
WHERE
adm0_a3 IN ('ITA','GBR')About CDB_RectangleGrid
WITH grid AS
(SELECT
row_number() over () as cartodb_id,
CDB_HexagonGrid(
ST_Buffer(the_geom_webmercator, 100000),
100000
) AS the_geom_webmercator
FROM
ne_adm0_europe
WHERE
adm0_a3 IN ('ESP','ITA'))
SELECT
grid.the_geom_webmercator,
grid.cartodb_id
FROM
grid, ne_adm0_europe a
WHERE
ST_intersects(grid.the_geom_webmercator, a.the_geom_webmercator)
AND a.adm0_a3 IN ('ESP','ITA')About CDB_HexagonGrid