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

Advertisements

Monday Help Session

I will be in on Monday from as close to 1:00pm as I can be … until the last of the student meetings are finished.  I have a couple students from other courses that will also be looking for time.  This will be first come first served.  Please have all of your questions neatly packaged and please have tried to develop the queries you need or have questions about before arriving.  

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

Data Modeling Workshop at SOM

This Friday, 2/3, we will meet downtown at the SOM offices in the Santa Fe Building 224 S Michigan Avenue.  Please meet by 10:30 in the lower level atrium lobby where the CAF city model is located.  There are a few different exhibits to see at the CAF which are all publicly accessible.  Feel free to come early, we have reserved a conference room upstairs for 11:00-1:00 so we can talk for a bit downstairs and then head up.  Please bring markers and trace just in case, as I am not certain about the availability of white boards, etc.

Project 1: Case Study

The first project this semester is a short case study presentation on a matter relevant to Urban Information Modeling.  Consider the presentation from last week which introduced projects, software, hardware, standards, cities, etc.  Select from something you saw or something new you have discovered.

Please bear in mind that this is not necessarily the same as the type of project you are going to do.   However they might be related some how or another in the end.  For example, you may do a case study on a standard such as cityGML, and you might decide to use it to help organize a custom data structure for your project.  Another example would be to select a city, almost any major city will have one or more examples (like those in recent lecture) of projects or initiatives to model the city.  Topics may be historic, contemporary or progressive in nature.

Potential topics

Please select a topic and prepare a 5-10 min presentation which explains the problem and solution spaces pertinent to the topic.  You may work in small groups, in which case multiply the length above by the number in your group to determine adequate length.

Due for presentation in class 2/10/2012

 

Anatomy of a Blog Post

How do you define a succesful blog post???  You can browse through this very website and see countless examples of bad posts.  You know the ones, some image stolen from some place and not attributed, with a couple poorly written sentences that basically say … how cool is this?  Might as well just have been a link shared on del.icio.us would have been more effective.

do a quick search on google for “anatomy of a blog post” and you will find a number of people, most who are probably more legitimate bloggers than myself, giving you there [N] tenants of what constitutes an [enter adjective here] blog post.  You should look at these, create your ruleset, and use it.  Here I will break down not a complete spec for how to do a post, but instead a few key do’s and don’ts.

#1 Attribution

You must attribute anything and everything you reference as thoroughly possible.  This ecosystem that we are trying to participate in is heavily dependent on attribution, its currency and Karma is a bit

#2 Have something to say

Especially in the context of a course such as this where the focus is on critically exploring these technologies, your posts are more or less meaningless with saying something unique, something personal.  Blogs are quite editorial in nature, take advantage of this reality.  Nobody wants to hear that something is cool, they want to hear about what actually makes it cool.  Make some comparisons, put it in perspective, establish a context.

#3 engage the reader

Many bloggers seem to agree that ending with or at least embodying a certain amount of questioning back to the reader is a great way to engage them in the post.  Perhaps to comment and explicitly participate in the dialog.  Its generally the case that the big powerhouse blogs, the blogs with engaging and interesting content, and those that have things to say are generally the blogs/posts with huge collections of comments and dialog and debates following them.

#4 Use media

In most cases you are going to be referencing something someone else has already blogged about or something you saw in the wild.  Link to the content, reference the images (with attribution and a link of course), embed the youtube clip, make the diagram, bottom line, get multimedia content into your blog post to get the reader to stay and to help the reader understand.

#5 USE the cms

For this class, any post that is published without proper categorization and tagging of the content of the post, will be discredited.  The usefulness of the blog as a catalog of research and investigation is dependent on a good system of browsing. Tagging content with topical tags enables us to quickly and powerfully filter and target the types of content we are most interested in.  Furthermore, all options and aspects that are possible for you to contribute within the content management system(cms) the richer the potential experience will be.

the class blog

Welcome to the class blog for Professor Kearns‘ Urban Information Modeling seminar in the College of Architecture, at the Illinois Institute of Technology.  Here you will find the resources, assignments, references, and basic administration of course information.  Together with student posts and course information the blog will grow as a resource for the college at large.

If you are a student in the course please subscribe to the blog by email or rss feed linked at the bottom of the page.  This will let you stay up to date with the happenings of the class and important information.