Spatial Queries using Postgres/Postgis
Author: Vania Bogorny
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:
Each sample .sql file is the converted shapefile ready to import into PostGIS using step 3 of the
process. The .gif file is the graphical representation of the shapefiles,
created in ArcExplorer, which is a free software for geographic data visualization.
Complete Multi-step Process
- Download suitable spatial datasets in shapefile format from ESRI's web-site :
- Convert shapefiles into sql files
shp2pgsql [<options>] <shapefile> [<schema>.]<table> > sqlfilename.sql
shp2pgsql -c street.shp street_table my_db > street.sql
- Import the sql files into PostGIS
psql [options]... [dbname [username]]
psql -d my_db -f country.sql
- After import the data login into Postgresql
Example: (sample session output)
- List all tables.
Example: sample session.
- 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
- 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
- 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)
- One may consider taking the results of a query back to Arc Explorer for visualization
using pg2shp.exe converter available with postgis software.