-
-
Save mroswell/feed75db74eed5af9866 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--PART 2 | |
-- a. | |
# select all the tornadoes | |
SELECT * FROM tornados | |
# select the cartodb_id for all the tornadoes | |
SELECT cartodb_id FROM tornados | |
-- b. | |
# select the first tornado | |
SELECT * FROM tornados LIMIT 1 | |
# select the second tornado | |
SELECT * FROM tornados LIMIT 1 OFFSET 1 | |
# top 10 most damaging tornadoes | |
SELECT * FROM tornados ORDER BY damage DESC LIMIT 10 | |
-- c. | |
# select the the first tornadoes whose id is less than 30 | |
SELECT * FROM tornados WHERE cartodb_id < 30 | |
# tornadoes where the damage was more than 1000 | |
SELECT * FROM tornados WHERE damage > 1000 ORDER BY damage ASC | |
-- d. | |
# how many tornadoes are in the dataset | |
SELECT count(*) FROM tornados | |
# total damage from tornandoes | |
SELECT sum(damage) FROM tornados | |
# average tornado damage | |
SELECT avg(damage) FROM tornados | |
--PART 3 | |
-- ST_GeometryType | |
# determine geometry type of tornadoes | |
SELECT st_geometrytype(the_geom) geomtype FROM tornados | |
# determine gemotry type of counties (presumably polygons or multipolygons) | |
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 | |
# Return the Well-Known Text (WKT) representation of the tornado geometry/geography | |
SELECT ST_AsText(the_geom) astext FROM tornados | |
# select the Eastern-most tornado | |
SELECT ST_XMax(the_geom) xmax FROM tornados | |
# reproject to 4326 (?) | |
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) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment