PostGIS Spatial Function Practice Exercises

Before moving on to Project 4, try your hand at the following practice exercises. Solutions can be found at the bottom of the page.

Add Times Square to the nyc_poi pts table (located at 40.757685,-73.985727).
Report the number of points in the geometries of states with a 2010 population over 10 million.
Perform an on-the-fly re-projection of your nyc_poi pts data into the New York East NAD27 state plane coordinate system.
Output the U.S. cities coordinates in human-readable format, including the SRID.
Select the names and centroids of states where the 2010 male population is greater than the female population.
List the states that contain a city named Springfield (based on points in the cities table).
List the cities that are found in ‘Soda’ states. Sort the cities first by state name, then city name.
Select features from the nyc_poi lines table that are within 1 mile of Madison Square Garden.
Calculate the distance in kilometers (based on a sphere) between all of the state capitals.
Find the SRID of the Pennsylvania North NAD83 state plane feet coordinate system.
Using data in the 'usa' schema select states containing cities that have a 'popclass' value of 4 or 5. Pretend that the stateabb column in the cities table doesn't exist. Don't fret if your results include duplicate states. How to handle duplicate results is demonstrated in the solution.