GEOG 868
Spatial Database Management

PostGIS Spatial Function Practice Exercise Solutions

PrintPrint

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

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_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.