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:

1. 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.
2. The DELIMITERS argument specifies your delimiter.
3. The CSV argument at the end lets PostgreSQL know that the file is CSV.
4. 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 :

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

• No more fish in the sea

• "The correct use of a semicolon is a big red flag for me"

• Canada's new money is pretty amazing, eh?

• How to SEO for the iTunes App Store

• The Tragedy of Suburbia

• Dean Martin makes a burger.

• Vitamin String Quartet – The Suburbs

• Imaginative, provocative video from students on the need for (r)evolution in teaching and learning

• 404

• 10 Unmissable TED Videos For Designers

• How does one become a brogrammer?

• Preschool: The Best Job-Training Program

• Internet High Five

• Shifting the Suburban Paradigm

• The Wirecutter

• Persist - Letters of Note

• German designer David Hanauer creates dizzying carpets from aerial Google Earth images.

• Tufte's Data Analysis for Politics and Policy

• Apple's Three Laws of Developers

• "A Brief Rant on the Future of Interaction Design"

• The Shadow Superpower

• Chicago Type

• Weapons of Mass Creation

• Woody Gutherie's "New Years Rulin's"

• “OK, but there are two rules: 1. You can’t change the recipe. 2. You can’t do anything that anyone else has done before.”

• Restrictions will set you free.

• 4 Star General, 5 Star Grace

• Possibilians vs Agnostics by Dave Eagleman.

• Sir Ken Robinson on passion

• How to write faster

• The Elements of Typography Style Applied to the Web

• Combining Type with Helvetica

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

Apps Shipped : 7

4 Android apps with one in the can set for release in a few weeks. 3 iOS apps. Not 100% sure what I can and can't disclose due to client agreements, but I am very proud and happy with the apps that I helped produce last year. Lots more mobile work on the agenda for 2012.

High School Programming Courses Taught: 1

In early 2011 I taught Android development to high schoolers participating in a pilot program for the 21st Century Youth Project. It was an amazing experience and I hope to work with them again in the future as the program matures.

Speaking Engagements : 4

I had the honor of being selected to speak at WindyCityGo and Chicago Code Camp last spring as well as speaking to user groups in Rockford and Milwaukee.

Jobs Changed : 1

In July I joined Redpoint Technologies and became a consultant for the first time in my career. While consulting isn't for everyone, I have enjoyed the diversity of the projects/clients I had the privilege of working on/with.

Side Hustles : Numerous

Along with maintaining a few sets of MonoTouch bindings, I put together a wiki for mobile app developers and started working with some of the data that the City of Chicago is publishing. I was able to help out some friends working on some stealth startups by offering technical advice and mentoring and have a few more tricks up my sleeve for the upcoming year.

Trips : 7

In addition to usual trips to Minneapolis to visit the in-laws and the guys trip out to Phoenix for the PGA event, I was able to squeeze in a "business" trip to Las Vegas, a vacation to southern California, and attend an amazing wedding in Greece.

Lucky Guy: 1

In reflecting back over the last year I am left feeling a tremendous amount of gratitude and appreciation for the amazing people, places and opportunities that I encountered over the past year. I am truly blessed.

The most common spatial format that geographic data is distributed in is ESRI shapefiles. The geographic datasets provided at the City of Chicago's open data portal are no different. In this post, I'm going to walk through converting an ESRI shapefile into SQL, importing that SQL into a PostGIS enabled PostgreSQL database, and querying the geographic data.

Prerequisites

Before we get started you will need to download and/or install the following data and software:

1. PostGIS/PostgreSQL database
2. The GDAL libraries
3. A dataset from the City of Chicago Data Portal. For this example I am going to use the Ward data provided by the city.
4. pgAdminIII - A PostgreSQL admin and management tool that is optional but highly recommended.

Step 1: SHP file to SQL file

The data published by the city uses a spacial reference system (SRS) called NAD 1983 StatePlane Illinois East FIPS 1201 Feet to store data. This SRS falls into the State Plane category which is typically great for measurement and maps covering a limited geographic area like a state or city. For most contexts this is perfect but, if you want to use this spatial data with mapping layers or geocoding services from Google or Bing, it will cause problems. Mapping providers like Google and Microsoft Bing expect data to be stored using the WGS 84 (EPSG code 4326) SRS. This means that if we want to use latitude and longitude coordinates from these services or utilize their mapping layers we will have to convert our data from State Plane to WGS 84. Thankfully the GDAL tools provide a utility to transform the data.

ogr2ogr -f "ESRI Shapefile" -s_srs "EPSG:102671" -t_srs "EPSG:4326" Wards4326.shp Wards.shp

Once the shapefile has been converted from State Plane to WGS 84, we can then use the shp2pgsql tool to generate the SQL statements necessary to add the geography data to our database.

shp2pgsql -s 4326  -I -c -W UTF-8 Wards4326.shp wards > wards.sql

Here is a sample of one of the insert statements generated by the shp2pgsql with geometry column (the_geom) abbreviated:

INSERT INTO "wards" ("data_admin","perimeter","ward","alderman","class","ward_phone","hall_phone","hall_offic","address","edit_date1","shape_area","shape_len",the_geom) VALUES ('111078823.37393899','98206.78600274','20','WILLIE COCHRAN','1','773-955-5610','312-744-6840','121 N LASALLE ST, RM 300 OFFICE 19, 60602','6357 S COTTAGE GROVE','20030527','111200062.771999999','98166.05290559999','0106000020E6100...'

Step 2: Insert Data into DB

Now that the dataset has been transformed and the SQL has been generated, it is time to add it to the database. You can do so by running the following command. (NOTE: you will have to swap in your own database name and user for the $DB_NAME and $DB_USER arguments).

psql -d $DB_NAME $DB_USER < wards.sql

Step 3: Write Queries

With the data entered into the database we can now write queries against it. Here is a query that returns information about the ward that contains the point specified by latitude and longitude arguments.

SELECT ward, alderman 
FROM wards 
WHERE ST_Contains(the_geom, ST_GeomFromText('POINT(41.927064 87.645658)', 4326));

The WHERE clause is calling the spatial relationship function ST_Contains. Spatial relationship functions in PostGIS take two input geometries and return either a boolean or another geometry. In our case, ST_Contains returns a boolean indicating if the point geometry passed as the second argument is contained in the geometry stored in the the_geom column of the wards table. The point geometry is constructed from another spatial function ST_GeomFromText. This function takes a textual representation of the geometry along with a spatial reference id (SRID) specifying the SRS that the geometry should be created in. In our case, we are using latitude and longitude values obtained from Google so we are creating the point geometry in the EPSG 4326 SRS.

Additional Resources

I found the following links useful as I started learning about PostGIS.

• Boston GIS's - Getting Started With PostGIS: An almost Idiot's Guide
• Kyng Chaos' PostgreSQL, PostGIS, and pgRouting installers for Mac OS X
• PostGIS in Action book

AppDevWiki

Earlier this week I put together a wiki to centralize the links, libraries and miscellania that I have been collecting regarding mobile development. I did this mainly for personal use and to provide a reference for coworkers and colleagues getting into mobile dev but hopefully others will find it useful. Check it out and feel free to add your favorite tools, libraries and links to it.