Hello all.  I've got census region polygons and populations as separate
tables in a postgis database, and want to read in a subset of these tables
and merge them.  There are a few posts about this in the archives but no
definitive solution.  My code is below, but I'd prefer to either

1) do the subsetting and merging as one long query and read it in as a data
frame, without registering the geometry column.  then convert the data frame
(with it's geometry column) into an spdf in R somehow.

2) get postgres to create a new table of subsetted and merged data, read
that in with readOGR.  This would require giving users write access and
registering a new geometry column every time, I'd rather not do this.  tried
to get around this with dblink and a separate read/write database, but it's
problematic.

any comments on how to make this smoother would be very welcome!

# subset polygons and read into R
system("ogr2ogr -f GML stuff.gml 'PG:dbname=census user=stuff password=stuff
host=localhost' y2006.ca_da_cbf_clp -where \"PRUID like '12'\"")
ns=readOGR("./stuff.gml","y2006.ca_da_cbf_clp")

# read in population data
con <- dbConnect(PostgreSQL(), user= "stuff", password= "stuff",
dbname= "census", host="localhost")
res <- dbSendQuery(con,
"select * from y2006.ca_da_pop where pruid = '12'")
da06pop = fetch(res, -1)

#merge population into spdf by dauid
rownames(da06pop ) = as.character(da06pop$dauid)
notInSpatial = names(da06pop)[!names(da06pop) %in% names(ns)]
ns@data = cbind(ns@data, da06pop[as.character(ns$dauid), notInSpatial])

	[[alternative HTML version deleted]]

