Spatial Queries using Postgres/Postgis

This web-page demonstrates spatial queries from chapter 3 in Prof. Shekhar's book. using Postgresql/PostGIS SDBMS and a spatial dataset downloaded from ESRI's web-site.

It provides a multi-step process to download spatial datasets, convert those to sql, load into postgres/postgis SDBMS, and run queries.

Note that one may skip the first two steps for a quick start using the following sql files:

• Cities (city.sql, city.gif)
• Countries(country.sql, country.sql)
• Rivers (river.sql, river.gif)
• Water (water.sql, water.gif)
• Streets (street.sql. street.gif)

Complete Multi-step Process

1. Download suitable spatial datasets in shapefile format from ESRI's web-site : http://www.esri.com/data/download/basemap/index.html
2. Convert shapefiles into sql files

   shp2pgsql [<options>] <shapefile> [<schema>.]<table> > sqlfilename.sql
   
   Example:
   shp2pgsql -c  street.shp  street_table my_db > street.sql
   

3. Import the sql files into PostGIS

   psql [options]... [dbname [username]]
   
   Example: 
   psql -d my_db -f country.sql
   

4. After import the data login into Postgresql

   pgsql my_db 
   
   Example: (sample session output)
   

5. List all tables.

    Example: sample session.
   

6. You may look at the schema and contents of various table using other postgres commands.

   Example Query: List all cities and the country they belong to in the CITY table 
   Example Postgis expression and output: script
   

7. Try spatial queries. Following are example of spatial queries from chapter 3 of the Spatial Database book.
   • Query: List the names of the capital cities in the CITY table (script)
     
   • Query: Find the names of all countries which are neighbors of the United States (USA) in the Country Table (script)
   • Query: For all the rivers listed in the River table, find the countries through which they pass (script)
     
   • Query: Which city listed in the City table is closest to each river listed in the River table? (script
   • Query: List the name, population, and area of each country listed in the Country table (script)
     
   • Query: List the length of the rivers in each of the countries they pass through (script)
     

8. One may consider taking the results of a query back to Arc Explorer for visualization using pg2shp.exe converter available with postgis software.