Useful Plugin for QGIS!!

PgQuery is a plugin for QGIS which allows you to execute sql with postgis functionality, and load the results directly as layers with geometry.

a longer writeup can be found here,

As a test, I executed the following query to get all the building footprints in a given neighborhood

SELECT "BLDG_ID","Buildings".the_geom,hood.pri_neigh
FROM
(SELECT * FROM neighboorhoods WHERE neighboorhoods.pri_neigh = 'Lincoln Park') AS hood,
"Buildings"
WHERE st_contains(hood.geom,"Buildings".the_geom) = true

Check it out! this should be really useful for you!!!

Query Example From Class

Here is the query sans-comments

SELECT nname, COUNT(nname) AS qty
FROM (
SELECT n.pri_neigh AS nname,
n.shape_area AS narea,
s.longname AS sname,
ST_Distance(n.geom,s.geom) AS distance
FROM neighboorhoods AS n
CROSS JOIN cta_railstations AS s
WHERE ST_Distance(n.geom,s.geom) < 1000
) GROUP BY nname;

explanatory comments included

--in the select portion we are pulling out the name
--from the sub-query below
SELECT nname, distance, COUNT(nname) AS qty
FROM
--this begins the sub-query
(
--selecting from two joined tables
--using alias for clarity
SELECT n.pri_neigh AS nname,
n.shape_area AS narea,
s.longname AS sname,
--postgis function to get the distance
--between two geometry objects
ST_Distance(n.geom,s.geom) AS distance
FROM neighboorhoods AS n
--joins neighboorhoods and railstations
--creates m*n rows
--for comparison of every neighboorhood
--to every railstation
CROSS JOIN cta_railstations AS s
--clause to reduce the m*n set to only
--those which fall within a certain distance
WHERE ST_Distance(n.geom,s.geom) < 1000
)
-- from w3 schools ...
--The GROUP BY statement is used in conjunction
--with the aggregate functions to group the result-set
--by one or more columns.
GROUP BY nname;