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;

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

Esri Developer Summit 2012

This past week in Palm Springs, CA this annual conference was held. It is a gathering of developers to learn and teach about new tools used to create mapping applications or adding mapping to existing applications. The meetings cover various aspects of Esri’s ArcGIS system and gives insight on several other Esri products/technology.

The video linked here was from the session called “10 Killer Apps”. I only have this one video which is a demo of a UAV(unmanned aerial vehicle) Shark being controlled by a flex mapping system in an app. Although the mapping system isn’t “driving” something very relevant in this demo, you can see how this could be used for intelligence purposes in the future.

I will keep my eye out for more posts or reports on the conference regarding the 10 killer apps.

Loacation-based Services

In the beginning, there was Dodgeball.

No, not that Dodgeball. Dodgeball was a service to which you could text your location and other members of your service would receive the information with the potential of meeting up with you (or robbing your house if you’re not home). You could also receive information such as crushes, friends, friends’ friends and interesting venues nearby. Dodgeball, created in 2000, was purchased by Google in 2005 and discontinued in 2009, superseded by Google Latitude. One of the makers of Dodgeball went on to create Foursquare.

<p><a href=”″>Hi! I want to learn more about foursquare!</a> from <a href=””>foursquare</a&gt; on <a href=””>Vimeo</a&gt;.</p>

Foursquare exists somewhere between reality and the virtual realm. While users can “check in” to physical venues, they can also earn “badges” for certain activities and if someone checks in to the same place more than anyone else they become the “Mayor” of that place. Foursquare has also worked out arrangements with businesses to offer specials to users, frequent visitors and especially the Mayor. Checking in is a manual process, developers citing technical issues such as the i-phone not allowing applications to run in the background as well as the drain on battery power that the GPS has. What is not mentioned on their site, however, are the privacy concerns. was one of the first websites to point out how exposed people were with geo-tagged social media technology.

Foursquare is making use of many of the technologies we’ve discussed in class and some we haven’t. PostGIS, MapBox, OpenStreetMap are used as well as Google’s s2 library to store cell IDs for geo-indexing and dataset to reverse geocode addresses into coordinates.

Opting-in to share your location may be a nice option for some but as we have seen you don’t need to be a Foursquare member or opt-in to anything to unintentionally divulge your location with unintended results.

3D Ripper DX

Although my presentation focused on all forms of translating 2D data into 3D info for modeling cities and buildings, I want to focus on this one.

With the (DirectX version) you can go to Bing maps and rip the mesh data off of your graphics card. . It comes as a mesh into 3ds Max or Maya. The graphics card will read everything in the scene which sometimes gives you some unnecessary information that can be cropped away in the 3D program. Older versions of DirectX and software rendering engines do not support 3D Ripper DX.

I think this was developed for ripping game scenes but it has been successful in getting full models of cities from Bing maps. I have been told that you should find the old developer version of Bing maps though to make this work best. I have attached the best YouTube videos that show how the program works but there is song attached by the original creator, so turn the volume down and the second video shows the tutorial twice.

There is also an alternative for OpenGL which I believe Google Earth uses. I wish I knew more about this but I have only been informed about Bing and will look into this as I move forward this semester.

To use, you must download the 1.8 or 1.8.1 version. Open 3D Ripper DX, pick the executable program you would like rip from and then specify parameters. The website below can give you some more details.

GIS, Twitter Trackers and Mash-ups

Crisis Mapping by ESRI

My initial interest in this subject stems from my Masters Project and a map I found of a social media tracker of Tahrir Square during the uprisings beginning January 25th, 2011. The map is no longer accessible. Official word from ESRI is that it was taken down because the protests are over. I did, however, find these active maps of Libya and the Occupy Movement (look for the OM to pick back up when the weather improves).

Libya Social Media Tracker

Occupy Movement Social Media Tracker

The possibilities of this technology is provocative. Imagine how these maps could be leveraged in the event of a natural disaster when information on the ground is limited and highly disorganized. It relies heavily on Volunteer Geographic Information. I’m not sure how useful this is if communications go down, anyone remember the Detroit/Toronto/New York blackout? This technology can only work if the information being tracked is geo-tagged. Tweets are and you can geo-tag photos and video. Some cameras automatically geo-tag photos.

Esri sent me this link which very roughly lays out how to recreate these maps.

Alternative Mash-ups

While the above information could be considered a mash-up, I was interested if any other similar mash-ups existed and especially if they were on platforms or used technology that was easier to work with or which may provide for a more forward starting point.

Twitter Mash-up

This application allows you to select which map supplier you want to use for the mash-up

Geo Chirp Google Maps Mash-up

This Mash-up is probably my favorite and actually does everything I wanted to do for the class so I’m not sure why I would recreate it. I am looking for alternatives. You can serch by tweet content, and distance from a location. The only change I would make is to move the tweet list to the sidebar.


This application is still in beta but I thought the flood of tweets onto the screen was awesome. Here is the Google apps page where you can find different api’s to mash-up with Google’s products.

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 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.