Geometry Creation SQL


--('schema','name of table to add a column to','name of column to add',srid,'postgis geometry type',dimension)
--SELECT AddGeometryColumn ('public','recycling_dropoff_location','geom',26971,'POINT',2);

--SELECT AddGeometryColumn('public','recycling_dropoff_location','untransgeom',900913,'POINT',2)
--SELECT AddGeometryColumn('public','recycling_dropoff_location','untransgeom2',4326,'POINT',2)

--st_transform(st_setsrid(st_point(lat,long),900913),26971)
--three compound functions
--the innermost st_point(lon,lat) creates a geometry from an x and y location
--... watch the lat/lon distinctions the table im testing with appears to have them flip flopped
--the next outer function st_setserid( geometry to operate on, 900913) establishes the srid of the geometry
--... in this case I am assuming the unprojected google system 900913
--the outermost function st_transform( geometry to operate on, 26971) converts from one projection to another
--UPDATE recycling_dropoff_location
--SET geom = st_transform(st_setsrid(st_point(lon,lat),900913),26971)

--UPDATE recycling_dropoff_location
--SET untransgeom2 = st_setsrid(st_point(lon,lat),4326)

--UPDATE recycling_dropoff_location
--SET geom = st_setsrid(st_point(st_x(st_transform(untransgeom2,26971))*3.2808399,st_y(st_transform(untransgeom2,26971))*3.2808399),26971)

--SELECT st_asewkt( geom )
--FROM recycling_dropoff_location

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;

Links from class

QGIS tutorials by Gandhi
Quantum GIS (QGIS) Tutorials
Quantum GIS (QGIS) Tutorials: Using Tabular Data in QGIS
Quantum GIS (QGIS) Tutorials: Tutorial: Styling Vector Data in QGIS – Basics
Quantum GIS (QGIS) Tutorials: Tutorial: Spatial Analysis – Points in Polygon
Quantum GIS (QGIS) Tutorials: Tutorial: Performing Spatial Queries in QGIS
Quantum GIS (QGIS) Tutorials: Tutorial: Working with Attribute Table – Basic Queries

PostGIS function reference
PostGIS book I mentioned
Chapter 8. PostGIS Reference

Purely SQL
W3 Schools SQL Reference
SQL GROUP BY Statement
SQL: COUNT Function
SQL COUNT() Function

Where we might go time permitting
15 Advanced PostgreSQL Commands with Examples
www.day32.com/MySQL/Meetup/Presentations/postgresql_stored_procedures.pdf