[R-sig-Geo] PostGIS and sp integration

[Roger Bivand]

On Thu, 9 Oct 2008, 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. 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.

Could you look at how an SQL query to ogr2ogr might work? Of course, 
temporary files are messy, but you might be able to have ogr2ogr select 
the data you need for the fixed regional features? Your original Rmap code 
allowed selection of features, but not IIRC. Adding an sql="" argument to 
readOGR() might be fun (probably based on what ogr2ogr does)!

Roger

>
> Any thoughts?
>
> Barry
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

-- 
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: +47 55 95 93 55; fax +47 55 95 95 43
e-mail: Roger.Bivand at nhh.no