Palindromic place names in New South Wales

Here’s a couple of goofy programs to identify towns in New South Wales that have palindromic names. I used it as a little test of R-PostgreSQL-and-Perl. I went to high school in Tumut, so thought this might be a nice application of the palindrome function I saw on O’Reilly’s website.

My first attempt was this:

library(XML)

# Load in a little program I wrote to put tables to PostgreSQL
source("~/Dropbox/AGL/Code/R/dbWriteTable.fast.R")

# Download a list of towns from the internet
# Based on code taken from here
# http://stackoverflow.com/questions/1395528
theurl <- "http://en.wikipedia.org/wiki/List_of_places_in_New_South_Wales_by_population"
tables <- readHTMLTable(theurl)
n.rows <- unlist(lapply(tables, function(t) dim(t)[1]))

# Extract the relevant data (the fifth column is irrelevant)
nsw_towns <- tables[[which.max(n.rows)]]
nsw_towns <- nsw_towns[,1:4]

# Load up the database
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
pg <- dbConnect(drv, user="iangow", dbname="crsp", host="localhost")

# Put the table from R into PostgreSQL
rs <- dbWriteTable.fast(pg,"nsw_towns",nsw_towns, 
    overwrite=TRUE, row.names=FALSE, fix.names=TRUE)

# Create a little Perl "palindrome" function
# (See http://oreilly.com/pub/a/databases/2005/11/10/using-perl-in-postgresql.html
# for the source for this)
dbGetQuery(pg,paste("
    CREATE OR REPLACE FUNCTION palindrome(text)
    RETURNS boolean AS
        $BODY$
            my $arg = shift;
            my $canonical = lc $arg;
            $canonical =~ s/\\s+//g;
            return ($canonical eq reverse $canonical) ? 'true' : 'false';
        $BODY$
  LANGUAGE plperl IMMUTABLE
  COST 100;
"))

# Now pull in the towns in NSW that are palindromes
dbGetQuery(pg,"SELECT * FROM nsw_towns WHERE palindrome(location)")

This yielded the following output

  uc_l_name  location population_2006_census source
1 Arrawarra Arrawarra                          [12]
2     Tumut     Tumut                  5,925  [453] 

But my brother Sam pointed out that I was only picking up places with more than 1,000
people and suggested a more comprehensive data source. These data were a little trickier to work with.

# Identify all places in NSW whose names are palindromes

# Download the data (6.8 MB) and unzip it
f <- tempfile() 
download.file("http://www.gnb.nsw.gov.au/__gnb/gnr.zip", f) 
file.list <- unzip(f,list=TRUE)

# A helper function to read in the special CSV files
read.csv.plus <- function (i) {
    read.csv(unzip(f, files=as.character(file.list[i,1])),
    header=TRUE, skip=5, blank.lines.skip=TRUE, stringsAsFactors=FALSE,encoding="latin1")
}

# There are two tables; combine them
table.1 <- read.csv.plus(1)
table.2 <- read.csv.plus(2)
nsw.towns.all <- as.data.frame(rbind(table.1, table.2))
rm(table.1, table.2, myData, file.list)
unlink(f)

# Load up the database
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
pg <- dbConnect(drv, user="iangow", dbname="crsp", host="localhost")

# Create a little Perl "palindrome" function
# (See http://oreilly.com/pub/a/databases/2005/11/10/using-perl-in-postgresql.html
# for the source for this)
dbGetQuery(pg,paste("
    CREATE OR REPLACE FUNCTION palindrome(text)
    RETURNS boolean AS
        $BODY$
            my $arg = shift;
            my $canonical = lc $arg;
            $canonical =~ s/\\s+//g;
            return ($canonical eq reverse $canonical) ? 'true' : 'false';
        $BODY$
  LANGUAGE plperl IMMUTABLE
  COST 100;
"))

# Put the table from R into PostgreSQL
rs <- dbWriteTable.fast(pg,"nsw_towns_all",nsw.towns.all, 
    overwrite=TRUE, row.names=FALSE, fix.names=TRUE)

# Now pull in the towns in NSW that are palindromes
dbGetQuery(pg, paste(
    "SELECT DISTINCT trim(both ' ' from placename) as placename,",
        "designation, status",
    "FROM nsw_towns_all",
    "WHERE palindrome(placename)"))

This code yielded a longer list:

   placename   designation       status
1     Narran        PARISH     ASSIGNED
2     Narran        PARISH     RECORDED
3     Lallal        PARISH     RECORDED
4      Alala   RURAL PLACE DISCONTINUED
5     Narran        COUNTY     ASSIGNED
6      Ulalu        PARISH     RECORDED
7    Alagala   RURAL PLACE     ASSIGNED
8                                      
9        Gog TRIG. STATION     ASSIGNED
10     Tumut TRIG. STATION     ASSIGNED
11                                     
12       Gog      MOUNTAIN      VARIANT
13      Anna        PARISH     ASSIGNED
14     Tumut          TOWN     ASSIGNED
15   Glenelg        PARISH     RECORDED
16   Glenelg      LOCALITY     ASSIGNED
17     Anona   RURAL PLACE     ASSIGNED
18    Narran TRIG. STATION     ASSIGNED
19 Arrawarra        SUBURB     ASSIGNED
20     Ulalu TRIG. STATION     ASSIGNED
21    Hannah TRIG. STATION     ASSIGNED
22     Naman        PARISH     ASSIGNED
23     Tumut      LOCALITY     ASSIGNED
24     Tumut        PARISH     ASSIGNED
Advertisements
This entry was posted in R and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s