Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Created December 8, 2013 18:53
Show Gist options
  • Save andrewxhill/7862128 to your computer and use it in GitHub Desktop.
Save andrewxhill/7862128 to your computer and use it in GitHub Desktop.
Fun SQL statements for CartoDB
--PART 2
-- a.
SELECT * FROM tornados
SELECT cartodb_id FROM tornados
-- b.
SELECT * FROM tornados LIMIT 1
SELECT * FROM tornados LIMIT 1 OFFSET 1
SELECT * FROM tornados ORDER BY damage DESC LIMIT 10
-- c.
SELECT * FROM tornados WHERE cartodb_id < 30
SELECT * FROM tornados WHERE damage > 1000 ORDER BY damage ASC
-- d.
SELECT count(*) FROM tornados
SELECT sum(damage) FROM tornados
SELECT avg(damage) FROM tornados
--PART 3
-- ST_GeometryType
SELECT st_geometrytype(the_geom) geomtype FROM tornados
SELECT st_geometrytype(the_geom) geomtype FROM us_counties
SELECT st_getsrid(the_geom) srid FROM tornados
SELECT st_getsrid(the_geom_webmercator) srid FROM tornados
SELECT st_astext(the_geom) astext FROM tornados
SELECT st_xmax(the_geom) xmax FROM tornados
SELECT CDB_LatLng(0,0) the_geom
-- Geometry Calculations
SELECT ST_Area(the_geom) area FROM us_counties
SELECT ST_NumGeometries(the_geom) n FROM us_counties
SELECT * FROM us_counties WHERE ST_NumGeometries(the_geom) > 1
SELECT ST_Distance(the_geom, CDB_LatLng(0,0)) FROM tornados
SELECT * FROM counties ORDER BY ST_Distance(the_geom, CDB_LatLng(0,0)) ASC LIMIT 1
SELECT * FROM counties ORDER BY the_geom <-> CDB_LatLng(0,0) ASC LIMIT 1
-- Geography
SELECT ST_Area(the_geom::geography) area FROM us_counties
SELECT ST_Area(the_geom::geography)/1000000 area_sqkm FROM us_counties
SELECT ST_Distance(the_geom::geography, CDB_LatLng(0,0)::geography)/1000 distance_km FROM tornados
--PART 4
SELECT * FROM tornados WHERE ST_DWithin(the_geom, CDB_LatLng(kansas city), 1)
SELECT * FROM tornados WHERE ST_DWithin(the_geom::geography, CDB_LatLng(kansas city)::geography, 50000)
SELECT * FROM us_counties WHERE name = 'Bedford city'
SELECT count(*) FROM tornados WHERE ST_Intersects(the_geom, (SELECT the_geom FROM us_counties WHERE name = 'Bedford city'))
SELECT (SELECT count(*) FROM tornados WHERE ST_Intersects(the_geom, u.the_geom)) FROM us_counties u WHERE name = 'Bedford city'
-- create new column in us_counties 'tornado_count'
UPDATE us_counties u SET tornado_count = (SELECT count(*) FROM tornados WHERE ST_Intersects(u.the_geom, the_geom))
-- create new column, normalized tornados 'tornado_sqkm'
UPDATE us_counties SET tornado_sqkm = tornado_count/(ST_Area(the_geom::geography)/1000000)
@andrewxhill
Copy link
Author

SELECT
ST_Intersection(
ST_Union(the_geom_webmercator),
ST_Transform(ST_Buffer(CDB_LatLng(43, -118)::geography, 1000000)::geometry, 3857)) the_geom_webmercator
FROM us_counties
WHERE
ST_Distance(the_geom::geography, CDB_LatLng(43, -118)::geography)/1000 < 1000

@andrewxhill
Copy link
Author

tornadoes has an 'e'. i know

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment