Transcript Spatial Query Handson - CyberInfrastructure and Geospatial

Geog 480: Principles of GIS
Guofeng Cao
CyberInfrastructure and Geospatial Information Laboratory
Department of Geography
National Center for Supercomputing Applications (NCSA)
University of Illinois at Urbana-Champaign
Spaital Query (PostGIS) Handson
Connecting to Geog480 server
Connecting to Database
• psql -U username -d database_name
o username = geog480
o database_name = tutorial
o Enter passwd when prompted (same as username)
• Postgres Commands
o
o
o
o
\l List all accessible databases
\dt List all the tables in current DB
\? Help
\q Quite
Import Shape Files into Databases
• Quit PostGIS by typing ‘\q’
o Step 1: shp2pgsql -c -i -s 4326 /srv/cigi/example/states/states.shp
public.postgis_states_your_netid > $HOME/states.sql
o Step 2: psql -U your-net-id -d tutorial -f $HOME/states.sql
• Login in PostGIS by psql –U your_net_id –d tutorial
• Query: select state_name, state_fips, ST_AsText(the_geom)
from postgis_states_your_netid where state_name like
‘Illinois';
•
Spatial Query
• Find all objects with 5 unit distance of POINT(-128 49.948)
o SELECT gid FROM postgis_your_netid WHERE ST_DWithin(the_geom,
GeomFromText('POINT(-128 49.948)',4326), 5.0);
o SELECT state_name FROM postgis_states_your_netid WHERE ST_DWithin(the_geom,
GeomFromText('POINT(-128 49.948)',4326), 5.0);
• Sort the states by Area
o select state_name, ST_Area(the_geom) as area from postgis_states_your_netid
ORDER BY area desc;
Spatial Query II
• Find centroid of all states
o select state_name, ST_AsText(ST_Centroid(the_geom)) from
postgis_states_your_netid;
• Find all the states adjacent to Illinois
o SELECT states.state_name FROM postgis_states_your_netid AS states,
postgis_states_your_netid AS il WHERE il.state_name like 'Illinois' AND
ST_Touches(il.the_geom,states.the_geom);
Spatial Query III
• Try other states for the adjacent case
• Try coming up with your own spatial queries:
o Examples of more complex queries can be found:
http://postgis.refractions.net/documentation/manual-1.3/ch04.html#id2572795
o PostGIS reference functions: http://postgis.refractions.net/documentation/manual1.3/ch06.html
• End of this topic