GEOG 868
Spatial Database Management

PostGIS Spatial Function Practice Exercise Solutions

PrintPrint

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.