Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Carto Cosmos PostGIS && GIS Talk Notes

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.

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.