1. Add Times Square to the nyc_poi pts table (located at 40.757685,-73.985727).
INSERT INTO nyc_poi.pts (name, geom) VALUES ('Times Square', ST_SetSRID(ST_MakePoint(-73.985727,40.757685),4269));
INSERT INTO nyc_poi.pts (name, geom) VALUES ('Times Square', ST_GeomFromText('POINT(-73.985727 40.757685)',4269));
2. Report the number of points in states with a 2010 population over 10 million.
SELECT name, ST_NPoints(geom) FROM states INNER JOIN census2010 ON states.name = census2010.state WHERE census2010.total > 10000000;
3. Perform an on-the-fly re-projection of your nyc_poi pts data into the New York East NAD27 state plane coordinate system (SRID 32105).
SELECT name, ST_Transform(geom,32015) FROM nyc_poi.pts;
OR for human-readable geom:
SELECT name, ST_AsText(ST_Transform(geom,32015)) FROM nyc_poi.pts;
4. Output the US cities coordinates in human-readable format, including the SRID.
SELECT name, ST_AsEWKT(geom) FROM usa.cities;
5. Select the names and centroids of states where the 2010 male population is greater than the female population.
SELECT name, ST_AsText(ST_Centroid(geom)) FROM states INNER JOIN census2010 ON states.name = census2010.state WHERE male > female;
6. List the states that contain a city named Springfield (based on points in the cities table).
SELECT states.name FROM states CROSS JOIN cities WHERE ST_Contains(states.geom, cities.geom) AND cities.name = 'Springfield';
SELECT states.name FROM states CROSS JOIN cities WHERE ST_Covers(states.geom, cities.geom) AND cities.name = 'Springfield';
7. List the cities that are found in ‘Soda’ states. Sort the cities first by state name, then city name.
SELECT cities.name, cities.stateabb FROM states CROSS JOIN cities WHERE ST_Within(cities.geom, states.geom) AND states.sub_region='Soda' ORDER BY cities.stateabb, cities.name;
SELECT cities.name, cities.stateabb FROM states CROSS JOIN cities WHERE ST_Intersects(cities.geom, states.geom) AND states.sub_region='Soda' ORDER BY cities.stateabb, cities.name;
8. Select features from the nyc_poi lines table that are within 1 mile of Madison Square Garden.
SELECT lines.name FROM lines CROSS JOIN pts WHERE pts.name = 'Madison Square Garden' AND ST_DWithin(ST_Transform(lines.geom,2260),ST_Transform(pts.geom,2260),5280*1);
9. Calculate the distance in kilometers (based on a sphere) between all of the state capitals.
SELECT cityA.name, cityB.name, ST_Distance_Sphere(cityA.geom, cityB.geom) / 1000 AS dist_km FROM cities AS cityA CROSS JOIN cities AS cityB WHERE cityA.capital = 1 AND cityB.capital = 1;
10. Find the SRID of the Pennsylvania North NAD83 state plane feet coordinate system.
SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%Pennsylvania North%';
The correct spatial reference has an SRID of 2271.
11. Select states containing cities with a 'popclass' value of 4 or 5.
SELECT DISTINCT states.name FROM states CROSS JOIN cities WHERE ST_Contains(states.geom, cities.geom) AND cities.popclass >= 4;
If you completed this exercise on your own you probably had Texas appear in your results 3 times. Remember that a cross join creates a result set that combines all the rows from table A with all the rows from table B. The WHERE clause narrows down the cities in the output to those 9 having a popclass of 4 or 5, but 3 of those 9 cities are in Texas. That explains why the query returns Texas 3 times. The answer in a case like this is to insert the DISTINCT keyword after SELECT. This ensures that none of the output rows will be duplicated.