[R-sig-Geo] PostGIS and sp integration

[Edzer Pebesma]

Barry Rowlingson wrote:
> Hi All,
>
>  I'm now very impressed with PostGIS and what it can do with spatial
> data. Given the easy setup with Ubuntu, the easy admin with pgAdmin3,
> and the great interface with Qgis, I'm won over totally.
>
>  Of course I still need to read my spatial data into R (until I do all
> my analysis in Python....).
>
>  I'm trying to follow best practice with my database. So for any set
> of polygons I have one table with the geometry and other tables with
> data.
Why is this better practice than having a single 100x61 table with 
monthly rainfall in the usual columns and a geometry column?
>  I can create maps of the data by creating views where the data
> table is linked to the geometry table via the polygon id. However some
> of my views have many entries per polygon...
>
>  For example I have monthly rainfall for 100 regions in Ethiopia over
> 5 years - so that's a data table with 12*100*5 rows. If I read that as
> an sp object (via readOGR) I will end up with a 6000-row sp-object,
> containing 60 copies of each of the 100 polygon boundaries. That
> doesn't sound good to me.
>
>  What I want to do with this data is draw 60 choropleth maps - one for
> each month. I can think of four approaches:
>
>  1. Suck it up - read the whole thing into an sp object and then
> subset that by month for the map. Hope R doesn't fall over.
>
>  2. Read the map (without attached data) into an sp object and read
> the data (without map geometry) into a data frame (using RPgSQL). Then
> attach each month to the sp object.
>
>  3. As (2), but attach the whole dataframe to the sp-object and then
> subset. Probably not much different from (2) really.
>
>  4. Can I apply an SQL select to the readOGR call, so I only select a
> map with a single month's rainfall? I've just thought of this one. I'm
> not sure if the specification of a PostGIS layer allows it.
>
> Any thoughts?
>   
Sounds as if you've not seen the spplot light. If you had a single table 
with 60 columns and 100 rows, all that's needed would be

rain = readOGR("bla")
spplot(rain)

given that the rain columns would be useful and fit in the conditioning 
plot text boxes (so-called "strips")
--
Edzer
> Barry
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>   

-- 
Edzer Pebesma
Institute for Geoinformatics (ifgi), University of Münster,
Weseler Straße 253, 48151 Münster, Germany.  Phone: +49 251
8333081, Fax: +49 251 8339763  http://ifgi.uni-muenster.de/
http://www.springer.com/978-0-387-78170-9