Lessons in teaching: teaching exploratory data analysis with R

Last spring, I took over a course labeled as information systems engineering.  This is aimed at sophomores in engineering.  Historically, this course focused on using the MS Access database.  I was asked by the department to take this over after several years of commenting that our engineering seniors have inadequate computer programming skills, as evidenced by the amount of effort they spend on their senior projects doing tasks that would have been much simpler if they tried programming.  Last year some of the faculty tried experiments in their classes where they had students code in an assignment (generally they asked for C). In every case this went very badly.  So they asked me to take this course and change it so that it covered programming and specifically to use R. (I am effectively the primary data analysis faculty here).  In keeping with the course title, I chose to focus the course on data analysis, with one month focusing on databases and how to think about data problems (and giving them time to gradually learn R), the rest on exploratory data analysis.  I used as the primary text Data Manipulation with R by Phil Spector, and as supplements GGplot2 by Hadley Whickam and An Introduction to Data Cleaning by Edwin de Jonge and Mark Van Der Roo. I presented the CONVO framework for thinking about data problems based on Thinking about Data by Max Shrum.

As freshmen, they would have has CS0 (the Association for Computing Machinery designation of introduction to computer science for non-computer science/electrical engineering majors) material covered over a two course sequence that also covers mathematics for engineering (primarily linear algebra).  The language of instruction is primarily Matlab, but they also cover C and, depending on instructor, Python (there is one module that is sometimes covered by Physics faculty, and they like to use Python).  For databases, there is another course on databases taught by an adjunct faculty who used to teach databases for information systems.

For tools I used SQLite (more on why this and not MS Access later), SQLite Manager, R, and R Studio. Prior to the end of the previous semester I sent everyone an email with links to videos introducing them to R and R Studio and encouraged them to introduce them to R through typing out a tutorial (I explained that they would actually learn R over the semester, the typing exercise was to ensure they had seen everything once before we actually needed it in class.).

For assessments, there were weekly labs for computer knowledge, exams mostly covered how to think through data problems. A semester project with two milestones (plus completed project) was the main way to assess how well they developed computer programming competency.  Each week, we covered one

We had three datasets that I used as teaching and lab examples throughout the course.

1. Titanic survivors
2. National Survey of Family Growth
3. American Community Survey (U.S. Census, Pittsburgh North PUMA)

Some observations and notes

1.  SQLite vs MS Access.  I was surprised to find out that MS Access has a relatively low size limit on databases. It was not able to handle either the National Survey of Family Growth (expected) nor could it handle a single PUMA for the American Community Survey (this was a surprise).  That meant we had to use SQLite for the entire course. (my Mac students were happy since this put them on equal footing with the PC students). Next time I will just use SQLite. (and use MS Access only to explain why we are not using MS Access)

2.  Learning R.  In a pre-class survey, the entire class indicated complete lack of confidence in programming to fulfil a task (expected).  I think that the standard programming language belief that it is always easier to learn a second programming language failed in this case, because I did not realize just how bad their first experience was.  While the first month was very intentionally a confidence building exercise, I think that for a portion of the class, they really needed to start from scratch.  Next time around, I will spend an entire period doing nothing but walking the class through R.

3.  Data manipulation.  This included covering data structures (text, dates, dataframes), regular expressions, plyr, reshape, and missing values imputation.  Essentially the Hadleyverse v. 1.  One issue here was the wide variety of potential topics. While I think every topic got used by someone in their semester project, some of the student evaluations complained about my teaching topics that were not on the exam.  Essentially, for people who are only used to computing on numbers, the entire topic of data manipulation seems to be a heavy cognitive load.

4.  Visualization.  I taught qplot, but I think that I should have gone straight to ggplot.  I think that either I go the traditional route and build every type of plot as an individual entity, or I present the grammar of graphics approach and build plots. Either way, now that I've taught it, I don't think qplot helps in either, and it is a lot less capable. (every groups final project pretty much had to transition to ggplot)

5.  Projects. I let  the students find their own datasets and questions, subject to the fact that they had to write the project purpose using the guidelines we covered in thinking about data.  The big division in quality of the projects was the richness of the dataset.  Next time, I will be a lot more strict on the dataset, in particular, I had a subjective guideline that they should not consider it practical to look at the whole dataset. In some cases, this still was a very small volume, and it made for a trivial and uninteresting report.

6.  Thinking about data. I used Max Strum Thinking about data framework where for a data project, one should identify the COntext, Need, Vision, Outcome.  Every week we read a contemporary news article that included a data component (mostly from the fivethirtyeight.com website)  Each discussion opened up with class discussion to summarize the article into this CONVO framework, then a discussion of the analysis in the article itself.  This actually worked out pretty well.  Each exam had at least one CONVO focused question, and generally they did well (and of the people who did not, there were no surprises based on class participation)

7.  News articles.  I had a wide range of news articles that we covered in a weekly discussion, drawn mostly from fivethirtyeight.com, the Upshot column from the New York Times, and the data series from the Washington Post.  Each article was assigned at the end of the week, for discussion in the Tuesday morning lecture.  Discussion opened up with a summary based on the CONVO framework, then we evaluated the data analysis presented in the article, followed by how we could change it to make it better or to answer a different question.  These class periods were fun. My goal was to take 15 minutes for each article, in a few cases we were on a roll so we let it go to 30 minutes.  I had good participation.  And it showed in the CONVO question on exams, and generally people did well when I asked them to imagine a data analysis based on data presented on a test (this was the last part of a multi part question, where the other parts were about the data presented). One disappointing thing was that when it came time for course evaluations, I was rated poorly with how the class material relates to the everyday world (like all engineering courses do). So I have to figure this one out.

8.  Course evaluations. When course evaluations came in, they were roughly a uniform distribution, which makes them very hard to interpret. In addition, comments that expressed weaknesses were mirrored in the comments that expressed strengths. So that meant that I had terrible averages and a chat with my department chair.  Fortunately for me, the generally accepted belief is that the broad diversity in the teaching evaluation is due to pushing the students harder (i.e. making them do programming again) and that this is part of improving the department as a whole. Hopefully when he meets the dean to review the faculty the dean agrees with this assessment as well.

9.  Class projects.  About a quarter of the projects (teams of1, 2, or 3) were genuinely impressive. Many projects with 100,000s of records, a few with millions of records, several dimensions, and data analysis that used layered visualizations to explore.  Most projects were a little more modest, thousands of data points and reasonable visualizations. Some projects were personal in nature (looking at issues in their home towns), others were fun (several projects revolved around music or sports) A number showed evidence of lack of confidence, shown in very unambitious data sets.  The issue with this group is how hard to push. One of the known problems with CS0 or CS1 is that they complete destroy people's confidence in programming, and a substantial portion of those who take one of their courses completely leave the field, or in the case of engineers, avoid programming at all costs in the future.

Next time around:

1.  Using a framework like CONVO (Max Strum) works. I am pretty sure everyone at least learned how to think about problems and settings.
2.  Skip MS Access.  I think I probably spent too much time on databases and working with the MS Access interface.  Next time, going straight to SQL is probably enough, given that the limits on MS Access means that we cannot do interesting datasets.
3.  I liked using three datasets the entire course.  Actually, some of them used the American Community Survey for their semester projects (after reading in multiple PUMA, e.g. an entire metropolitan area instead of only one PUMA).
4.  One question that I will have to think about is how much of a do-over of CS0 this course will be.  Clearly, as it is most of the class seems to get it the second time around and a good portion are pretty impressive. But there is a pretty large fraction that finished CS0 absolutely convinced that programming is forever beyond them.

Book Review: PostgreSQL: Up and Running by Regina Obe and Leo Hsu

PostgreSQL: Up and Running by Regina Obe
My rating: 4 of 5 stars

Compared to most books that introduce a full featured relational database management system, PostgreSQL: Up and Running is surprisingly short. It achieves this by being focused on its purpose, and doing it well. It is aimed at someone who knows a little bit about databases, although not necessarily a full featured client-server one, and brings them up to speed on both database server administration in general and PostgreSQL administration in general. It continues to introduce you to features that makes PostgreSQL special, making this a very valuable book to someone coming in to PostgreSQL from some other database, whether that database is simpler or a peer competitor. And since my background is from simpler databases, it works for someone like me.

First, this is not a book for someone who knows nothing about databases. Things such as database design, basic database concepts, and SQL is not covered, with the assumption that you get this knowledge somewhere else before coming here. (In my case, I learned on MS Access and SQLite) So what someone with this background may not have is how to work with a server based database management system.

This also has many sections that discuss some special features of PostgreSQL. The one that I thought was most interesting was the fact that you could write functions in one of many languages. Since I do much of my technical programming in Python, seeing how it is done was intriguing. (I've known that PostgreSQL has PL/Python, but the descriptions that I had seen in the past were obtuse and I had no idea how it would work.)

But this book also knows where to stop. Once I understand a topic to a base level, I'm used to looking to the web to learn more. And the authors consider the PostgreSQL Online Journal as an extension of the book, introducing an advanced topic, then pointing the reader to a quality source as a step in learning more. One advantage of this is that it keeps this book from becoming an intimidating door stopper.

In short, PostgreSQL: Up and Running has decided it will serve as a market that are not complete neophytes, but only bring people up to the point where they are better served by a rich set of advanced resources available on the web. And since this is probably a pretty good description of me, I found this to be a very good resource.

Note: I received a free electronic copy of PostgreSQL: Up and Running as part of the OReilly Blogger Program

View all my reviews

Building PostGIS on Ubuntu from source

I wanted to revisit PostGIS for an update on a project.  In the current version, we built a data table of distances, but I really want to be able to build this table based on a road network, i.e. if I have a road shapefile, pick two nodes and calculate the distance.  And sending many thousands of requests of this type over the internet to build the distance matrix is not very practical, especially if there are multiple road networks that need to be considered as well.

There are pre-built installers for Postgresql for all the major operating systems (Windows, OS X, Linux), but they seem to be version dependent.  i.e. it matters what version of the operating system you have, because the pre-built versions make assumptions about shared libraries and such (I think that is what is happening).  Plain Postgresql does not seem to be that much of a problem, but when adding in extensions (like PostGIS) it is.  So instead of fighting it, I decided to build the PostGIS stack on Linux.

Some issues along the way.

1.  Using ./configure -> make -> make install seemed to work smoothly (Postgresql 9.1, PostGIS 2.0.  note that I already have GEOS and PROJ libraries on my system)  But I needed to take a careful look at the config.log, because then I would have noticed that it could not find GTK+, dblatex and CUnit.

2.  To build the graphical shp2pgsql-gui plugin for Postgresql pgAdminIII, configure requires a --with-gui option.

3.  For GTK+, I had this on my system, but it was GTK3, and the pgAdmin actually requires GTK 2 development libraries.

4.  For CUnit, it requires the CUnit development libraries.

5.  After it is installed, shp2pgsql-gui is in the /usr/lib/postgresql/9.1/bin/ directory, not /usr/sbin as some places suggest.  So I needed to make a symbolic link using
   sudo ln -s /usr/lib/postgresql/9.1/bin/shp2pgsql-gui /usr/bin/shp2pgsql-gui

6.  Then I needed to add appropriate entries to the plugins.ini file, which is actually in /usr/share/pgadmin3/plugins.d directory

I took the entry found at GIS Stackoverflow
http://gis.stackexchange.com/questions/16181/how-can-i-enable-shapefile-gui-loader-in-pgadmin3

Now after this, it builds, all tests pass and the PostGIS graphical loader is working.

Book Review: SQL and Relational Theory: How to Write Accurate SQL Code by C. J. Date

SQL and Relational Theory: How to Write Accurate SQL Code by C. J. Date
My rating: 4 of 5 stars

Date's book is almost two books in one. First is a book on relational theory. As such, it is meant for deep reading. The second is SQL, the Good Parts. And as such it can get pendantic. But for someone who already knows something about working with data, it can be a good discussion on what you can and cannot get away with.

The first part of the book is an introduction to relational theory, mixed with the author's discussion of what is wrong with the SQL specification and the various implementations in database management systems that are currently available. While I appreciate the discussions from the point of view of making definitions clear, the numerous digressions into the failings of SQL become distraction after a point.

The second part is dominated by how to think in relational terms, and often the implementation within SQL. This is probably the valuable part of the book. Actually, because this is approached as much from a relational theory point of view as an SQL one, it becomes quite applicable to non-SQL forms of manipulating data (I usually manipulate data through programming languages such as R or Python). What this book becomes is a illustration of how to think when digging into data, and what transformations are reasonable and which gets you into trouble.

This is not light reading, or a reference to use when learning how to use SQL, or data manipulation tools in general. If you get around the discussion of the faults of implementations, it is a book on how to think when manipulating data as the first steps in data analysis.

Note: I received a free electronic copy of this book through the O'Reilly Blogger program

View all my reviews

Installing pyspatialite

Goal: To use spatial databases to perform operations such as centroid, nearest point, and distances to generate a table of distances along a road network from area to area, using the nearest node to the centroid of an area to represent the area.

Backup plan: Create a list of nearest node to centroid of areas. Use some code I have previously written in R to implement Dijkstra's algorithm.

Preferred method: Programmatically access Spatialite and perform all functions in spatialite.

Barrier: Most distributions of SQLite (including the sqlite3 library included in Python) do not enable loading of extentions.

Solution: Install Python module pyspatialite.

Installing pyspatialite is done through easy_install: sudo python setup.py install

The documentation mentions that you need geos and proj libraries installed. What it does not tell you is that you need the development versions (which include header files) of these libraries, as well as the Python headers. After installing the developmental files, then you can install pyspatialite.

Creating spatial data files

At my school, there is a class on Geographic Information Systems (GIS). When you complete the class, you now know how to analyze data using ArcGIS. But you do not know how to create the data files you used. And you do not know how to do anything that ESRI did not include in ArcGIS. As a researcher, I find that unacceptable. This year, there is a senior project team including someone who has taken this class doing a project with the local chapter of the Red Cross that will involve using spatial data. But since they cannot create a spatial file, I will create the data sets that they will use to populate their models. If I am going to do something, I may as well learn something along the way. Those somethings are Spatialite and Google Maps API for geocoding.

Step 1. Given a list of addresses, find their latitude and longitude. The official way to do this is to get the TIGER line files of street maps. These encode each block by name and the end points of addresses for each block along with their geospatial coordinates. Then you can interpolate. The more pragmatic way of doing this is to find someone who has already processed the TIGER line files and created an API or other interface to work with them. There are many that will then create Keyhole Markup Language (KML) files as output, and I have used one before.

The way I will do it is to use the Google Maps API. There is a Python package called googlemaps that will find lat/long given a properly formulated address. (note: long is a keyword in Python, so we use lng for longitude)

from googlemaps import GoogleMaps

def getlatlong(gmaps, streetaddress, city, state, zipcode):

address = streetaddress + " "+ city + ", "+ state +" "+ zipcode

lat, lng = gmaps.address_to_latlng(address)

return lat, lng

# get api_key from http://code.google.com/apis/maps/signup.html

api_key = "APIKEY"

gmaps = GoogleMaps(api_key)

. . .

lat, lng = getlatlong(gmaps, streetaddress, city, state, zipcode)

Step 2. Create an Spatialite database. Spatialite is the SQLite database with extensions for Simple Features for SQL, i.e. a relational database that allows for queries on geometric objects. The other options are Oracle Spatial, DB2 Spatial, PostGIS and MS Server with spatial extensions. There are also spatial extensions to MySQL, but they do not meet the SFSQL standard, and they are simplistically implemented (which would lead to many errors if you actually needed correct answers instead of approximations.) Of these, Spatialite does not require an administrator and contains its data in a single file, both of which simplify their use. To use Spatialite, the PROJ and GEOS libraries should already be on your machine. (see Spatialite installation instructions)

To create a database, use spatialite the same way as you would have used sqlite. But after creating the database, load init_spatialite.sql This SQL file loads some required tables. Sometimes, Spatialite will create the tables on its own, but sometimes not (especially if the database was originally an SQLite database).

Next, load the data (using any of a variety of different methods for SQLite databases.) Given address data, determine lat/long and enter each of them into their own fields. (you probably have to create these fields after importing the address data.

Step 3. Create the geometric representation of the data. What makes data spatial is the connection of a geometric representation of the data and the information about the spatial object. Before you do this, it would help to look at other datasets you are using. Spatial data is usually distributed as ESRI Shapefiles. Each Shapefile is actually a collection of files that include the polygons of the geographic objects, the data associated with those polygons, and a projection that describes how to make a representation of a surface of a sphere into the two dimensions of the computer screen (or piece of paper). The projection of a shapefile is usually found in a *.prj file in the same directory as the *.shp file. Most GIS viewers allow you to look at the shapefile properties from within the GIS viewer. The value needed is an SRID, usually a 4-digit number (sometimes 5). From here, a set of spatial queries are run from within Spatialite to add the geometry to the database. See prior post on loading lat/long data in Spatialite. When these spatial functions/queries are run, Spatialite will perform a series of operations that use the information in tables loaded by the init_spatialite.sql to generate the geometry representation or convert representations between projections. At this point, the data is now spatially enabled.

Step 4. View/Export. At this point, only a few GIS viewers can use Spatialite databased directly. One is QGIS. So after performing any wanted spatial queries or other operations you should export the data to another format, such as Shapefiles. The OGR/GDAL tools of the Geospatial Data Abstraction Library are able to do this. Within Spatialite you can use the .dumpshp function to create a shapefile.

The format of .dumpshp is

.dumpshp table Geometry sitelocations CP1252 POINT

table is the TABLE to be exported. I think this could have been a VIEW as well. Geometry is the name of the field within the table that has the geometric representation. Note that Spatialite stores this as a BLOB object. sitelocations is the name of the shapefile to be created. CP1252 is the name of a character set. Presumably there are others, but I have been using CP1252 for now. POINT is the spatial data type. Other types include LINESTRING, POLYGON, and MULTIPOINT.

What would be very useful right now is if I could automate steps 3 and 4 so I could run them from within Python instead of doing them inside Spatialite. However, this only works if SQLite was compiled with the ability to load extensions. Most distributers do not do so. One workaround is to install the pysqlite2 extension and compile it so that it can load extensions (as opposed to using the sqlite3 extension that is included in Python).

Loading Lat/Long data into Spatialite

The task: Load spreadsheets with Longitude and Latitude data into a spatial format. Preferably in a way that can be done by people who are not good programmers but can follow directions to use command line tools.

Initial data: Spreadsheets with addresses and Longitude and Latitude. Note: this can be created using the Google Maps API accessed through various programming interfaces.

Tools: Spatialite and SQLite Manager (Firefox plug-in)

Steps:

1. Save spreadsheet as a comma separated format
2. Import into SQLite using SQLite Manager plug in
3. Open Spatialite using command line interface.
4. Add the geometry to the table using: SELECT AddGeometryColumn('addresstable', 'the_geom', 4326, 'POINT', 2);
5. Note that 'addresstable' is the table within the database that contains the long/lat data. 'the_geom' will be the new field that contains the spatial data. 4326 represents the SRID for the projection to be used (in this case WGS 84). 'POINT' is the spatial data type. 2 indicates this is in a 2-D space.
6. Calculate and insert the spatial data using: UPDATE 'addresstable' SET the_geom = GeomFromText('POINT('||Longitude||' '||Latitude||')', 4326);
7. Longitude and Latitude are the field names containing the longitude and latitude data in the table. The names do not matter. But they order does. And they are separated by a single space.
8. Method is taken from PostGIS in Action by Obe and Hsu. Spatialite function names are similar to PostGIS function names. In many (but not all) cases, the 'ST_' is removed from the beginning of the function name. Note that for the exceptions, the Spatialite documentation references two function names, one without 'ST_' and one with.

Using shp2pgsql to import Shapefiles to PostGIS

I covered some of this in a previous post on Converting Lat/Long data to PostGIS. But now my problem is to import Shapefiles into PostGIS.

First, why do this. While Shapefiles are good for distributing and looking at data, it is difficult to do any analysis. To do anything, you end up using OGR/GDAL to examine the shapefile and then do something with it. PostGIS takes the spatial and data components of the Shapefile and exposes them in database format, allowing anything that can be done with a database to be done with the Shapefile. In addition, it enables Simple Features for SQL which allows for using set operations on geographic objects (as opposed to only sets). Think of all the Venn diagram pictures back in High School. And other spatial operations that may come to mind when dealing with points, lines and shapes.

The key, of course, is to get the PostGIS database started. So, first, within PostgreSQL, create a database

CREATE DATABASE PA;

Next, load PostGIS onto the database. The first step is to load PL/PGSQL

CREATE LANGUAGE plpgsql;

Next, within the database, load the lwpostgis.sql and spatial_ref_sys.sql into the database. I used the pgAdmin III GUI.

Next, use the shp2pgsql tool The format is:

shp2pgsql [] > .sql

This should be run from the directory with the .shp file. refers to the name of the shapefile. is the name of the table to be created within the database. You can also enter this in schema.tablename form if you want. database name is the name of the database on the local server that it is being entered into. Usual practice is to pipe this to an SQL file that will be read in later (with all the necessary permissions. For option, the common one is to enter the -s of the EPSG (if known).

So, for example, using the NHPN shapefile for Pennsylvania (FIPS: 42), which is to be imported into a database named PACities, in the 'public' schema as the table 'NHPN' with the projection NAG 83 (ft) (Pennsyvania South) EPSG=2272, I get:

shp2pgsql -s 2272 S42NHPN public.NHPN PACities > nhpnImport.sql

And now it is in.

Book Review: Desktop GIS: Mapping the Planet with Open Source by Gary Sherman

Desktop GIS: Mapping the Planet with Open Source by Gary Sherman

My review

rating: 4 of 5 stars
Desktop GIS covers Open Source software for use as a Geographic Information Systems (GIS). In particular, it covers the following programs and libraries:

- Quantum GIS
- uDig
- GRASS(and its Java front end JGrass http://www.jgrass.org)
- PROJ.4
- GDAL/OGR
- PostGIS
- FWTools
- GMT

At the beginning of the book, the author outlines three classes of users. A casual user who only needs to look at data found from elsewhere, an intermediate user who visualizes but also creates or converts data, and an advanced user who has the need to do spatial analysis. To cover all of these is an ambitious goal, which is further diluted by the authors felt need to cover the entirety of open source mapping in one book.

What the author does well is to identify tools, and explains what can do what with enough to get you started. So Quantum GIS and uDig are the viewers, able to read almost any GIS format (in particular the readily-available ESRI Shapefiles as well as PostGIS). GDAL and OGR that can convert anything to anything (including delimited text. These are often distributed as FWTools). PROJ.4 that converts projections from one to another (and is embedded in everything). PostGIS which is the spatial database that enables spatial analysis. And GRASS, which is the full-fledged can-do-everything-but-is-hard-to-learn tool. And then some random programs that either do something completely different (e.g. OSSIM, which is an imagery analysis tool) or can make a picture of a map with lots of options (e.g. GMT).

What he provides are the basics for the casual or intermediate user. The advanced GIS analyst would only have a taste of what GRASS can do, but would not know what can be done with it. Similarly, while the intermediate user will get a sense of what PostGIS can do, the lack of space to cover spatial extensions to SQL supported by PostGIS loses its value to the advanced user. What could have made this book better was more focus. If the author was compelled to have a survey of all open source mapping, it may have gone into an appendix with a few paragraphs for each of the miscellaneous tools. But for the book, one good focus would have been the GIS stack comprising of data storage, data analysis and data viewing. Basically the open source counterpart to the ArcGIS/Oracle with Spatial Extensions. And everything that does not play a role in the stack, gets pushed into the appendix.

What could have been done? The book tended to be organized by tool. But once past the casual user, almost all tasks required multiple tools. I would have gone:

1. Viewing data (raster, vector, introduction to QGIS, uDig, GRASS)
2. Converting data/data formats (GDAL/OGR, Maybe PROJ.4)
3. Creating/Editing data (digitizing, importing)
4. Spatial databases (PostGIS)
5. Geoprocessing/spatial analysis - GRASS, PostGIS, R-spatstats
6. Tools integration (QGIS/uDig with GRASS/PostGIS)
7. Scripting
8. Customization

and everything that did not fall into this gets a page in an appendix.

Knowing where to start is a big help. Most of the websites either focus on one product, or try to teach everything as being equally important. Gary Sherman at least identifies the main building blocks. (QGIS or uDig, GDAL/OGR/PROJ.4, PostGIS, GRASS) and gives enough to get started. And this can be very helpful, so at least the starting analyst knows where to start.

What would be next? For the person still working within the GIS stack (as opposed to a completely different topic, like imagery analysis which is OSSIM's territory) there are a few obvious topics.

1. PostGIS - Spatial databases with SQL. Maybe even connections with ArcGIS. Even a short (10 pg) appendix would have done wonders here.
2. GRASS - The author devotes an additional appendix to this. But to do this right, you probably need to refer to Open Source GIS: A GRASS GIS Approach
3. R spatial statistics packages. Applied Spatial Data Analysis with R would cover this.

Mostly, a good book to get started in GIS using Open Source tools. Casual users would be well served. Intermediate users would get started and can find the rest using the internet. Advanced users are going to miss alot (to the point they don't even realize that these tools were a worthy alternative).



View all my reviews at Goodreads.

Loading lat/long data into PostGIS

Base scenario. I have a datatable with longitude and latitude data that I want to look at using spatial tools. And I have shape files for the metropolitan area that I want to use them with. So, in other words I have the following.

1. ESRI shapefiles
2. Data table with latitude and longitude

The plan is to load the data table into PostGIS, then I can pull them into a GIS system like uDig or Quantum GIS or various ESRI products. Because PostGIS can do transforms, but ESRI shapefiles are what they are, the procedure is as follows:

1. Identify SRID (spatial reference ID) for the ESRI shapefiles. This should include units (feet, meters, or degrees)
2. Identify a corresponding SRID that used lat long degrees
3. Load data table into PostGIS
4. Add geometry (points) data to datatable, including transformation from degrees to feet.
5. Optional: export PostGIS to ESRI Shapefile if needed.

1. Identify SRID (spatial reference ID) for the ESRI shapefiles. This should include units (feet, meters, or degrees)

Shapefiles usually come as a set of files inside a directory. When they come from an official source, one of these should be a *.prj file. This is an example of one

PROJCS["NAD_1983_StatePlane_Pennsylvania_South_FIPS_3702_Feet",
GEOGCS["GCS_North_American_1983",
DATUM["D_North_American_1983", SPHEROID["GRS_1980",6378137.0,298.257222101]],
PRIMEM["Greenwich",0.0],
UNIT["Degree",0.0174532925199433]], PROJECTION["Lambert_Conformal_Conic"], PARAMETER["False_Easting",1968500.0],
PARAMETER["False_Northing",0.0],
PARAMETER["Central_Meridian",-77.75],
PARAMETER["Standard_Parallel_1",39.93333333333333],
PARAMETER["Standard_Parallel_2",40.96666666666667],
PARAMETER["Latitude_Of_Origin",39.33333333333334],
UNIT["Foot_US",0.3048006096012192]]

The first line identifies the projection, using something that should approach a WKT ("Well Known Text") name of a projection. In PostGIS, when the PostgreSQL database was geographically enabled, a table "spatial_ref_sys" was created that has a list of all SRID with names. Searching on this table should reveal a projection that has a name very similar to this one. That will define the target SRID for the whole project. The quick way to do this is to write a query:

SELECT srid, auth_name, auth_srid, srtext, proj4text
FROM spatial_ref_sys
WHERE srtext LIKE '%Pennsylvania%'
ORDER BY srtext;

In this case, we find:

2272; "PROJCS["NAD83 / Pennsylvania South (ftUS)", GEOGCS["NAD83", DATUM["North_American_Datum_1983",SPHEROID["GRS 1980", 6378137,298.257222101, AUTHORITY["EPSG","7019"]],

Note that in this case the key parts were NAD83, Pennsylvania South, and ft.

2. Identify a corresponding SRID that used long lat degrees
Another search through the "spatial_ref_sys" table finds only projections that are associated with feet or meters. So a broader search should be used to get


SELECT srid, auth_name, auth_srid, srtext, proj4text
FROM spatial_ref_sys
WHERE srtext LIKE '%NAD83%'
ORDER BY srtext;

This eventually reveals one projection that

4269;"GEOGCS["NAD83",DATUM["North_American_Datum_1983",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]], AUTHORITY["EPSG","6269"]], PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4269"]]"

The key here is that the 'UNIT' is "degree" as opposed to "Survey feet" or "Meters".

3. Load data table into PostGIS

First, by this point the PostgreSQL database was created using a template that has PostGIS enabled. I created an SQL file with all the Create Table commands ahead of time. They look like this:

CREATE TABLE table2004
(
case_no character(7) NOT NULL,
week integer,
"month" character(3),
eventdate date,
bg_lat numeric(12,6),
bg_long numeric(12,6),
serial2004 serial NOT NULL,
CONSTRAINT table2004_pk PRIMARY KEY (serial2004)
)
WITH (OIDS=TRUE);
ALTER TABLE table2004 OWNER TO userid;

Key point is that I need something an integer field that is a primary key, or at some points indexing becomes a problem. So I made a serial field to be an index (because my case_no field is a text field so was unsuitable). Another issue is the geographic indexing also likes an OID field, so I set OIDS = TRUE. Most current PostgreSQL documentation mentions that this is deprecated, but PostGIS databases would be an exception.

Next, a geographically (geometry) enabled field is added. PostGIS has a PL/pgsql function named "AddGeometryColumn" that does this. Note that earlier, I established that the target SRID is 2272. Next, because there will be spatial searches, I will create an index on the geometry column.

SELECT AddGeometryColumn('public', 'table2004', 'longlat', 2272, 'POINT', 2);

CREATE INDEX idx_table2004_longlat ON table2004
USING GIST (longlat);

Next, the data is loaded. I had exported the data table to a csv file, then used VIM to surround each line with an INSERT INTO statement so it looked like this:

INSERT INTO table2004 (week, month, eventdate, case_no, bg_lat, bg_long) VALUES( 1,'JUL','07/01/2004', 6666097,40.382649,-79.803297);

This loads the data into PostgreSQL. The next step is to actually create the geographic data.

4. Add geometry (points) data to datatable, including transformation from degrees to feet.

Now that there is a geometry column, and the lat/long information in the database as a source, the point needs to be added as a point. This is done through an UPDATE statement, using the PL/pgsql function "transform" "PointFromText". Note that the SRID that corresponded to a latlong representation was 4269. Also note that POINT is expressed in x, y. So longitude comes before latitude.

UPDATE table2004 SET longlat = transform(PointFromText('POINT(' || bg_long || ' ' || bg_lat || ')', 4269), 2272);

Now, this is done, and the table can be viewed using a GIS viewer that can connect to a spatial database.

Some of the data is messy, in particular, I was not able to get lat/long for all the addresses. So I created a View that removed these from the result (or I get a bunch of points at 0, 0

CREATE OR REPLACE VIEW table2004work AS
SELECT table2004.week,table2004.month,table2004.eventdate, table2004.case_no,table2004.bg_lat,table2004.bg_long, table2004.serial2004, table2004.longlat
FROM table2004
WHERE table2004.bg_lat > 1 AND table2004.bg_long < (-1);

ALTER TABLE table2004work OWNER TO userid;
COMMENT ON VIEW table2004work IS 'Remove 0,0 data from map';

So this provides a cleaner dataset, and it is also accessible from a GIS viewer.

5. Optional: export PostGIS to ESRI Shapefile if needed.

To export from the PostGIS database to an ESRI Shapefile (if it needed to be given to someone who only need to look at it), the PostgreSQL database should be running. Then pgsql2shp can be run. This should be done after creating a data directory for the shape file to exist. From the terminal, cd into the target directory and run

pgsql2shp -f table2004working.shp datatable table2004work

This creates a shapefile with .shp, .dbg and .shx files. Note that you may need to create a .prj file as well, but this can be done from within a GIS viewer.


Note: Much of this was found through the great documentation available at Refractions PostGIS site and the Boston GIS website, which has multiple tutorials on PostGIS and PostgreSQL.

[Edit]  In the 'srtext' field, the PROJCS identifies the SRID as belonging to a 'projection' which is a curved surface put on a flat plane.  When there is no projection, it is a pure GEOGCS (geographic coordinate system) and it is in degrees lat/long

Represent (The Open Blog at the New York Times)

Represent (from Open: All the news that's fit to printf() )

More than eight million people live in the five boroughs of New York City. They have more than 150 elected legislative representatives, from the local level to Congress. Keeping tabs on the people who represent you is a difficult task.
Represent

That’s the idea behind Represent, an interactive feature we launched in beta last week. Using your address as a starting point, Represent figures out which political districts you live in and who represents you at different levels of government. It draws maps that show how where you live fits into the political geography of the city. And using information collected from around the Web, it presents a customized activity stream that tracks what the people who represent you are doing.

Hat tip to Paul Ramsey (The Clever Elephant) for pointing to this.

I've been teach myself the use of Geographic Information Systems (GIS). Specifically, the use of spatially enabled database. The general idea is take a dataset that includes locations (such as addresses) and display and analyze the spatially. The display part is actually pretty easy. From a list of addresses there are many tools to get a longitude and latitude, then create an ESRI shapefile. I've been using uDig to view shapefiles.

But the real goal is to do analysis. You could link a GIS viewer with a database to view items based on some parameter. uDig, QGIS, and ArcView all have this capability. But the real way to do this is to connect this to a spatial database so you can use all of your standard reporting tools. And you could produce reports based on any set of criteria (such as buffers or overlays) Commercially, the provider of GIS tools is ESRI with their ArcGIS family of systems. And if you want to do more then just look at the data, there are a very few database management systems that could handle spatial data. Oracle, Informix, DB2, SQL Server (I think the very latest version has spatial capability), and PostGIS. For the commercial systems, they don't come fully spatially enabled out of the box, you have to buy the more expensive versions. PostGIS is the exception. It is freely distributable. Which is important if you are just exploring and don't have money. Or you are looking at applications that are for someone who does not have money.

Anyway, it turns out the technology team at the New York Times has been doing a variety of projects with the website, using technology to provide many different visualizations of the information (not just stories) in the New York Times. Some of the good ones are the Faces of the Dead project, which provides stories about individual servicemen and women killed in Iraq, and the Election Results Map, which graphically displays the election results in a variety of different ways, which tells a story much differently then you can using only words.



The Represent project is another project. It takes an address, then determines what districts you live in (local, state federal) and who represents you. Then it goes and searches the New York Times, and a variety of data sources to let you know what they have been up to, how they have voted recently, and what is coming up (in their public schedule. And the tools they use was driven by the fact they needed to use an open source (freely available) spatial database, which means PostGIS. (They use GeoDjango web framework to build the website, which includes extensions to Django web framework that allow the Object Relational Mapper (ORM) to work with spatial data fields.

Why is this cool? Well, it is what some call a mash-up, it is a way to re-purpose and reuse data, in this case news articles. And in the case of the New York Times, create something that cannot exist anywhere else, to deliver timely, local information. Just what a newspaper is supposed to do.

Importing spreadsheet data into Derby/JavaDB

Adding spreadsheet data to Apache Derby

I have a number of Spreadsheets with data in them, and the trick is to get them into a database so I can make some sense out of them (or be able to tell that they do not make sense, as the case may be). And I figured this would be a decent time to work on a new database. I could have used SQLite that I've used in the past. But I decided to use a server-class database, just because I've never used one. So I choose Derby (because I can still use it as if it were a single-site database). Also, there is some sensitive data, so I needed to password protect it.

Import CSV files

Creating CSV files
The first trick is always getting the data into the database. I opened up the spreadsheet in OpenOffice.org, then copied the row names to text file. That text file turned into a CREATE TABLE statement. The first problem was some of the names were reserved words, in particular 'Case'.

Next, I removed extra rows from the top of the database, including some instructions and the column headers. To make the comma-separated-values text work, I checked the formatting for all the columns. In particular, I formated the dollar figures to remove currency symbols, and insured that fields intended to be numbers were explicitely formated as numbers.


Then I exported the data to text (CSV) with double-quotes (") to delimit text, and semi-colons (;) to delimit fields.

From the CSV file, I removed empty rows and some extraneous text. The big one was where a space " " was in a cell that should have been empty. I got rid of those by searching for [;" ";] where there should have been a [ ;; ] that represented an empty field.

Import CSV files using ij

Finally, was the actual import. I had earlier added the Derby scripting tool, ij, into my path by putting

# add DERBY environmental variables
export DERBY_HOME=/usr/share/javadb
export PATH="$DERBY_HOME/bin:$PATH"

into my .bashrc file. In my script file, I had added the following above the CREATE TABLE SQL text:

connect 'jdbc:derby:Sample;create=true';
DROP TABLE Sampletable;

Then at the bottom, I entered the data into the newly created table by adding the following after the CREATE TABLE statement

CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'Sampletable','Sampledata.csv',';',null,null,0);

At this point, I ran

Select * from Sampletable;

in ij just to make sure it got in.

Setting a password

Next question was could I put a password on this. From within ij I created a database user with password, then set the database to require a password.

call syscs_util.syscs_set_database_property ('derby.user.testuser', 'test');
call syscs_util.syscs_set_database_property ('derby.authentication.provider', 'BUILTIN');
call syscs_util.syscs_set_database_property ('derby.connection.requireAuthentication', 'true');

Exiting from ij, then reconnecting using

connect 'jdbc:derby:Sample' user 'testuser' password 'test';

confirmed that the connection worked.

Connecting through Eclipse

The next task was to ensure that I could use the data through other tools, specifically Eclipse and OpenOffice.org. In Eclipse, I had BIRT installed. It gives a few choices for perspectives and I chose the Choose Report Design perspective. The perspective then gives the choice of 'Databases' or 'ODA Data Sources'. I right-clicked on 'Databases' and then chose 'Derby Embedded Database.' The next screen asked for a connection profile name and description. Next was the driver details. I clicked on the option box with the three dots, and looked at available drivers. These were not set properly, so I set the embedded driver to /usr/share/javadb/lib/derby.jar and a client driver to /usr/share/javadb/lib/derbyclient.jar. Filling in the Username 'testuser' and password 'test', then clicking on 'Test Connection' showed that this succedded.

In Eclipse, under 'Databases', the 'Sample' database shows up. Under 'Sample' was 'Schemas'. Under the 'APP' schema, undert 'Tables' was my 'SampleTable', waiting to be found.

Connecting using OpenOffice.org

Before starting, I opened OpenOffice.org and selected Tools -> Options. In the window that comes up, in the right I opened up the OpenOffice.org tree (click on '+' if it does not show) then selected 'Java'. On the right, there is a button for 'Class Path' Click on that button, and I added the derby.jar and derbyclient.jar archives to the Classpath. These were at /usr/share/javadb/lib/derby.jar and /usr/share/javadb/lib/derbyclient.jar respectively.

Using OpenOffice.org, I selected File -> New -> Database. In the Database Wizard that comes up, I picked 'Connect to an existing database', with 'JDBC' as the chosen option.



In the 'Set up a connection to a JDBC database' screen I entered

Datasource URL: jdbc:derby:/path/to/Sample
JDBC driver class: org.apache.derby.jdbc.EmbeddedDriver

Clicking on 'Test Class' confirmed the driver was in the Classpath as set earlier. The next screen is 'Set up the user authentication' I leave the 'User name' blank, but check the box labeled 'Password required'

On the next screen, 'Decide how to proceed after saving the database', click 'Yes, register the database for me' which allows OpenOffice.org to access the database (or you just have an odb file that includes database connection parameters) and 'Open the database for editing' ('Create tables using the table wizard' is not useful now since there already are datatables).

OpenOffice.org then asks for a name for the .odb file it will create to maintain the connection to the database (as well as any queries created in OpenOffice.org). The database can be accessed by clicking on any icon ('Tables', 'Queries', 'Forms', 'Reports'). OpenOffice.org will then connect to the database (asking for the database username and password along the way. Entering 'testuser' for username and 'test' for password does it). Then the database can be used from the GUI, which includes a graphical Query Design view or the usual SQL.