PostGIS Spatial Function Practice Exercise Solutions

PostGIS Spatial Function Practice Exercise Solutions

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));
   

   OR

   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 the boundaries of 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 U.S. 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';

   OR

   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;

   OR

   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_DistanceSphere(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) AND (cityA.name != cityB.name);

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.