Yesterday I had the pleasure of doing a talk exploring Core Location at the 4th annual Chicago Code Camp. Really enjoyed the audience participation and hopefully everyone left with a better understanding of Core Location.
Favorites from February 2012
A collection of some of my favorite links, tweets and quotes from February. While these aren't a comprehensive look at or summary of February 2012, they do capture the things that I found interesting, thought provoking, and/or cool over the past month.
Favorite Links
- War Time Golf Rules
- Why Publishers Should Give Away - eBooks
- Mapresent, a new mapping presentation tool for the iPad
- Yale Discovers a Fungus That Eats Plastic
- Edible Balloon from Alinea
- How Target Figured Out A Teen Girl Was Pregnant Before Her Father Did
- L.E.D. Surfer
- Mardi Gras 1907
- Chicago Past photoblog
- An amazing Starbucks store on the approach to a Shinto shrine in Dazaifu, - Japan
- Welcome to the Internet - Advice to a 13 year old.
- "This text and the one beside it are equal."
Favorite Quotes
- “Chaos was the law of nature, order was the dream of man.” – Henry Adams
- "All men will benefit, if we can invoke the wonders of science instead of its terrors." – JFK
Favorites from January 2012
A collection of some of my favorite links, tweets and quotes from January. While these aren't a comprehensive look at or summary of January 2012, they do capture the things that I found interesting, thought provoking, and/or cool over the past month.
Favorite Links
- Wilco, Nick Lowe & Mavis Staples rehearse The Weight
- TED Talk: How To Make Choosing Easier
- × as the visual symbol for multiply
- Wes Anderson from above
- Blue Marble: NASA's Incredible New High Resolution Photograph of Earth
- MEME PIC: I have no idea what I am doing
- Amazing sleuth work done to determine Ice Cube's 'Good Day'
- New FFT calculation could be 10x faster
- Now THIS is a firework
- Gut: HTTP Unix pipes for Open Data
- Typographica's Favorite Typefaces for 2011
- Beavis and Butthead, as sculpted by a special FX makeup artist.
Favorite Tweets
- @dansinker:"The first rule of John Kerry fight club…" contains at least 75 subclauses."
- @citizenparker: MUTABILITY! From hell’s heart I stab at thee (until another operation inadvertently changes my state to “not stabbing”)
Quotes
- "Data is the new raw material of the 21st century" - Tim Berners Lee & Nigel Shadbolt
Sometimes location data isn't neatly packaged in a SHP file, and it requires some massaging before it can be used in spatial queries. In this post I'll take a look at importing latitude and longitude data from a CSV file into our PostGIS database and transforming those coordinates into geometry data that can be spatially queried against.
Step 0 - Initial Starting Point
Get a CSV dataset. For this example I'll be using the Individual Landmarks dataset exported as CSV. You can get a copy of the CSV file that I am using for the example here. We already have the latitude and longitude and the extra location field isn't something that we'll use so I removed the location column prior to exporting the dataset. You can hide the column by selecting the arrows on the column heading in the table (shown here) or via the manage screen and deselecting the location column from the list of available columns (shown here).
A PostgreSQL database with PostGIS installed.
Step 1 - Create the table in the database
Use the following SQL to create a table to hold the landmark data.
Step 2 - Copy the CSV data into the database
To run the copy command, login to the database and execute the following command :
There are a couple of things to note with the copy command:
- The first argument passed to the copy command specifies the tablename (landmarks) and the columns which the CSV files map to. Our table includes a column gid that is an auto-incremented id which is used as a primary key as well as the_geom geometry column. The CSV does not include either of these columns in the data we are copying. If we don't specify the specific columns that the fields in the CSV map to, errors will occur when attempting to copy the data.
- The DELIMITERS argument specifies your delimiter.
- The CSV argument at the end lets PostgreSQL know that the file is CSV.
- The HEADER argument lets PostgreSQL know that the file includes the headers on the first line.
Here is a look at our data after the import. Note that the_geom has no data. We'll take care of that in the next step.

Step 3 - Translate latitude and longitude into POINT geometry
Now that we have the data imported, we need to convert the latitude and longitude columns to a PostGIS POINT geometry. The following SQL will update the_geom column in the landmarks table with a POINT geometry created from the row's latitude and longitude columns. In order to create the POINT, the ST_GeomFromText method is used. This method allows us to create a POINT, which is a PostGIS geometry type, textually with the latitude and longitude values being specified in the construction of the type.
Here is a look at the data with the the_geom column populated with POINT data.

Step 4 - Run some queries
Now that all the POINT geometries have been created for our landmark rows we can write some spatial queries against our data. Here are just a few of the types of queries we can run.
Using the ward geometry we have from a previous example, we can run a query that returns all the landmarks in the 43rd Ward.
This query returns the 5 closest landmarks to a given latitude and longitude :
Favorite Links and Quotes of 2011
A quest to find the name and url of a gadget review site that I had tweeted about a few months ago led me to the TwimeMachine web app. TwimeMachine lets you read your old tweets and, while I was looking for the gadget website, I got a chance to take a stroll down memory lane and revisit some of my favorite links and quotes from the past year. I pulled out some of my favorites and have shared them below. While these lists aren't a comprehensive look at or summary of 2011, they do capture the things that I found interesting, thought provoking, and/or cool over the past year.
My Favorite Links from 2011
Imaginative, provocative video from students on the need for (r)evolution in teaching and learning
German designer David Hanauer creates dizzying carpets from aerial Google Earth images.
My Favorite Quotes from 2011
"I’m impressed with the people from Chicago. Hollywood is hype. New York is talk. Chicago is work." -Michael Douglas
"The arts are not a way to make a living. They are a very human way of making life more bearable." -- Kurt Vonnegut
"Another flaw in the human character is that everybody wants to build and nobody wants to do maintenance." -- Kurt Vonnegut
@zachklein: Just told to me: "Schools are the coal-fired power plants of education."
My Favorite Funny Tweets from 2011
@jonlech "Next year HTML5 will replace native apps" is the new "Next year will be the year of Linux on the desktop".
@hubs: What doesn't kill you makes you smaller. ~Super Mario
@dcurtis: The Container Store is like a strip club for people with OCD.
@larrybraverman: GUYS IF EGYPT CAN TAKE DOWN MUBARAK WE CAN TAKE DOWN TICKETMASTER
Last Few Posts
- Favorites from February 2012 29 Feb 2012
- Favorites from January 2012 31 Jan 2012
- Importing CSV data with location information into PostGIS 09 Jan 2012
- Favorite Links and Quotes of 2011 02 Jan 2012