PostgreSQL Queries Cheatsheet - CartoDB

readme.md

Common PostgreSQL queries to run in CartoDB Editor or elsewhere. Includes simple table operations to more complex geospatial queries.

sql

// ***** SIMPLE DATA QUERIES ****** //

********* Search for a Dataset by a String *********
SELECT * FROM tablename WHERE columnname = 'BLANK'

******** Convert Year String to Timestamp ************
UPDATE tablename set yearColumn = to_timestamp(yr_compl,'YYYY')

********* Sort Data in Column to ASC / DESC **********
SELECT columntosort
FROM tablename
ORDER BY columntosort DESC

********* Count Data ***********
SELECT COUNT(*) FROM tablename

SELECT COUNT(*) FROM tablename WHERE columnname is null

********* Delete Data **********
DELETE FROM table_name
WHERE cartodb_id = 7260

DELETE FROM tablename
WHERE columname = 'String'


// ***** TABLE OPERATIONS ****** //

********* Join Data from Two Tables **********
SELECT the_geom_webmercator, mag, type, time FROM tableone
UNION
SELECT the_geom_webmercator, vei, type, dates FROM tabletwo

SELECT the_geom, park_borough, created_date, closed_date FROM table_311streetlightsout
WHERE the_geom_webmercator is not null
UNION ALL
SELECT the_geom, park_borough, created_date, closed_date FROM table_311streetlightsoutnull

********* Update Table Column with Data From Another Table Where Two Tables Have Same Data *********
UPDATE nynta
SET pop = census2010pops.pop2010
FROM census2010pops
WHERE nynta.ntaname = census2010pops.ntaname


// ***** GEOSPATIAL OPERATIONS ****** //

********* Select Data within Certain Area *********
SELECT * FROM tablename WHERE ST_DWithin(the_geom,CDB_LatLng(37.783333,-122.416667),0.1) 

********* Select Data Where the_geom_webmercator is null *********
SELECT * FROM tablename WHERE the_geom_webmercator is null

********* Select Data that Matches String Descriptor *********
SELECT * FROM tablename WHERE descriptor LIKE 'Noise: Construction Before/After Hours (NM1)%'

********* Select Data Within Date Range AND matches Descriptor String *********
SELECT * FROM tablename WHERE (created_date >= ('2014-08-14T19:46:40-04:00') AND created_date <= ('2015-02-22T20:48:00-05:00')) AND descriptor LIKE 'Loud Talking%'

SELECT * FROM tablename WHERE (created_date >= ('2014-08-14T19:46:40-04:00') AND created_date <= ('2015-02-22T20:48:00-05:00')) AND descriptor LIKE 'Loud Music/Party%'

********* Add Column Data from Another Table Where the_geom Intersects in Both Tables *********
UPDATE tablename set column = (
  SELECT sum (pop2010) FROM tabletwoname WHERE
  ST_Intersects(the_geom, tablename.the_geom))

********* Convert Multipoint Data to Point Data *********
********* In CartoDB, if the_geom says point, prob actually multipoint *********
********* Point Data would show up as lat, long instead *********
update tablename set the_geom = (ST_Dump(the_geom)).geom

********* Update Geom_column and Set Projection **********
update mytable 
set the_geom = st_setsrid(st_point(my_longitude_field,my_latitude_field),4326)

********* Spatial Aggregation (i.e. make a Choropleth) **********
SELECT ne_states.the_geom_webmercator,ne_states.name,COUNT(*) as count 
FROM ne_states, dams00x020
WHERE ST_Intersects(ne_states.the_geom_webmercator,dams00x020.the_geom_webmercator)
GROUP BY ne_states.the_geom_webmercator, ne_states.name

SELECT sfblocks.the_geom_webmercator,sfblocks.block_num,COUNT(*) as count 
FROM sfblocks, allthephotosagain
WHERE ST_Intersects(sfblocks.the_geom_webmercator,allthephotosagain.the_geom_webmercator)
GROUP BY sfblocks.the_geom_webmercator, sfblocks.block_num

********* Normalize Dataset Based on Census Population Data **********
SELECT s.the_geom_webmercator, s.num_complaints/census2010pops.pop2010 as normed_complaints 
FROM (SELECT 
  nynta.the_geom_webmercator the_geom_webmercator, 
  nynta.ntaname, 
  COUNT(*) as num_complaints 
FROM 
  nynta, 
  table_311noise
WHERE 
  ST_Intersects(
    nynta.the_geom_webmercator,
    table_311noise.the_geom_webmercator
  )
GROUP BY 
  nynta.the_geom_webmercator, 
  nynta.ntaname) s 
JOIN
  census2010pops
ON s.ntaname = census2010pops.ntaname

********* Normalize Dataset Based on Census Population Data **********
SELECT s.the_geom_webmercator, s.num_complaints/census2010pops.pop2010 as normed_complaints 
FROM (SELECT 
  nynta.the_geom_webmercator the_geom_webmercator, 
  nynta.ntaname, 
  COUNT(*) as num_complaints 
FROM 
  nynta, 
  table_311noise
WHERE 
  ST_Intersects(
    nynta.the_geom_webmercator,
    table_311noise.the_geom_webmercator
  )
GROUP BY 
  nynta.the_geom_webmercator, 
  nynta.ntaname) s JOIN
census2010pops
ON s.ntaname = census2010pops.ntaname

*********  Check if multi line strings can be converted to linestring *********  
SELECT COUNT(CASE WHEN ST_NumGeometries(the_geom) > 1 THEN 1 END) AS multi_geom,
COUNT(the_geom) AS total_geom
FROM ne_110m_coastline

*********  Convert multilinestring to linestring *********  
SELECT ST_LineMerge(the_geom_webmercator) AS the_geom_webmercator
FROM tablename

********* Named Map Code **********
cartodb.createLayer(map, {
   user_name: '{your_user_name}',
   type: 'namedmap',
   named_map: {
     name: "namedmap_tutorial",
     layers: [{
       layer_name: "t",
       interactivity: "cartodb_id, name, pop_max"
      }]
    }
   })
   .addTo(map)

********* Normalize Data ********* 
Change 360 to the max range of your data
UPDATE tablename set normed = datacolumn*255/360

********* Round Data ********* 
SELECT round(count::numeric, 2) FROM tablename

********* Format Date to String ********* 
UPDATE tablename
set date  =
to_char(datecol, 'YYYY-MM-DD')

********* Find Max Value of Columns ********* 
SELECT cartodb_id, the_geom, count, date, latitude, longitude, newcolumn, the_geom_webmercator 
FROM table_name
WHERE count=(SELECT MAX(count) FROM table_name)

********* SAMPLE TRIGGER TEST ********* 
1) Define FUNCTION and store in database
CREATE OR REPLACE FUNCTION chandra.inserttest()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN 
update chandra.triggertest 
set iso2 = z.iso2
from worldtriggertest as z
where chandra.triggertest.iso3 = z.iso3;
RETURN NULL;
END
  $function$

2) Define trigger, store in database
drop trigger if exists inserttest_trigger on triggertest;
Create trigger inserttest_trigger
after insert
on triggertest
for each row 
execute procedure inserttest()

3) INSERT SQL statement to test trigger/function
INSERT INTO triggertest
(the_geom, iso3)
VALUES 
(ST_SetSRID(st_point(40.43,47.395),4326), 'AZE')

thumbnail.png