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)

--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
(SELECT * FROM neighboorhoods WHERE neighboorhoods.pri_neigh = 'Lincoln Park') AS hood,
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
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
--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;


Shapefile to PostGIS Import

I’m trying to import the Shapefile for building footprints in Chicago to PostGIS using Spit on qGIS, but I’ve forgot what was the SRID I was supposed to use.

Could anyone help me with that? Thanks (sorry for this kind of post but I figured it was the quickest way to get an answer and to get the thing going)


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