Carto Cosmos PostGIS && GIS Talk Notes

postgis-gis.md

PostGIS && GIS

cdb-manager

A simple browser-based terminal for running SQL against Carto using the SQL API

• http://github.com/cartodb/cdb-manager
• git clone git@github.com:CartoDB/cdb-manager.git
• When you're done cloning, enter the directory and run ./httpserv.py
• Point your browser at http://locahost:8000
• Click on "Current endpoint:" to get a form for connection details
• Click on the "New" button
• Enter your Carto login name in "Account name"
• Enter your API key in "API key"
• Enter a memorable name in "Endpoint name", it's just the display name for the connection
• Click "Update" button

Example Data

Three files from the City of Victoria open data site.

• Trees
• Neighbourhoods
• Schools

Queries

All The Columns in the Table

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'neighbourhoods'
AND table_catalog ~ current_user
ORDER BY ordinal_position;

Largest Neighbourhood

SELECT neighbourhm, ST_Area(the_geom_webmercator)
FROM neighbourhoods
ORDER BY ST_Area(the_geom_webmercator) 
DESC

Area of Neighbourhoods

Naive area:

SELECT 
  neighbourh, 
  round(ST_Area(the_geom_webmercator)) AS area
FROM neighbourhoods

Adjusted area:

SELECT 
  neighbourh,  
  round(ST_Area(the_geom_webmercator)) AS area,
  round(ST_Area(the_geom_webmercator) *
   pow(cos(
    radians(ST_Y(
     ST_Centroid(the_geom)
   ))),2)) AS adjusted_area
FROM neighbourhoods

Exact area:

SELECT 
  neighbourh,
  round(ST_Area(the_geom::geography)) AS geography_area,  
  round(ST_Area(the_geom_webmercator)) AS area,
  round(ST_Area(the_geom_webmercator) *
   pow(cos(
    radians(ST_Y(
     ST_Centroid(the_geom)
   ))),2)) AS adjusted_area
FROM neighbourhoods

Trees in Neighbourhoods

SELECT n.neighbourh, Count(*)
FROM neighbourhoods n
JOIN parkstrees t
ON ST_Intersects(n.the_geom, t.the_geom)
GROUP BY n.neighbourh

Tree Heights

SELECT Avg(t.height) AS height,
       s.schoolname
FROM parkstrees t 
JOIN schools s
ON ST_DWithin(
  s.the_geom_webmercator,
  t.the_geom_webmercator, 
  1000/cos(radians(48.426)))
GROUP BY schoolname
ORDER BY height DESC

Nearest Trees

SELECT cartodb_id, diameterat, height, species
FROM parkstrees
ORDER BY the_geom_webmercator <->
(SELECT the_geom_webmercator
 FROM schools
 WHERE schoolname = 'Margaret Jenkins')
LIMIT 5