Spatialite and R Geo

[Noli Sicad]

Hi,

I have been trying to create maps from my forest carbon estate linear
programming (LP) model using rgdal and maptools in R. I use spplot for
mapping. My LP model reads area from shapefile as input and I am
trying to reconcile the results back to the corresponding polygon in
the shapefile. It run from 1 to 70 time period. However, when I join
table result to dbf of the shapefile, it produces a one-to-many
relationship with the polygon. The result table has been transposed,
so the periods (1 to 70 year) are in columns. This is the problem
since as you probably know, the shapefile could not be read / open and
rendered. I think the spatialite can solve this one-to-many
relationship problem.

I search for the spatialite and R and this is the result.

http://www.mail-archive.com/r-si...@stat.math.ethz.ch/msg00304.html

http://www.mail-archive.com/r-si...@stat.math.ethz.ch/maillist.html#00304

Sandro, any opinion how to solve the problem mentioned in the posting.
How can we use spatialite with R Geo?

Thanks, Noli

[Silas Toms]

Have you considered scripting using Python and the rpy2 module (for interaction with R) with the pysqlite2 module (which allows you to read/write to a Spatialite database)?

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To post to this group, send email to spatiali...@googlegroups.com.
To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.

[Alex Mandel]

I use spatialite and R all the time. The one thing I haven't gotten
working yet is loading the spatialite extension while in R(As mentioned
in the link above). So my usual workflow is to create some views/tables
in my db with a little data preprocessing and then to use Rsqlite to
pull what I need and run some stats. The only thing I can't do is pull
geometries or have and spatial operation in SQL that I run from R.

A few possible fixes for this issue might be
rgdal - newer version of gdal are starting to support spatialite
load extension - figuring out how to load sqlite extensions in R
system sqlite always loading spatial
running AsText on spatial columns ahead of time so that regular SQL can
pull them

I would skip the rpy2 solution for now as that would seem to just add a
lot of complication, unless you need automation in which case that would
be the same option I would choose.

Alex

[Noli Sicad]

Hi Alex.

Thanks for your reply.

> I use spatialite and R all the time. The one thing I haven't gotten
> working yet is loading the spatialite extension while in R(As mentioned
> in the link above). So my usual workflow is to create some views/tables
> in my db with a little data preprocessing and then to use Rsqlite to
> pull what I need and run some stats. The only thing I can't do is pull
> geometries or have and spatial operation in SQL that I run from R.

I don't know if this would affect what I am trying to do. I am going
to read spatialite / sqlite data with geometries to be render in
spplot.

For example
~~~~~~~~~~~
spplot(forest, c("P_1", "P_2","P_3","P_4", "P_5", "P_6",
"P_7","P_8","P_9", "P_10", "P_11", "P_12"), names.attr =
c("2010","2011","2012","2013", "2014","2015","2016","2017","2019",
"2020", "2021", "2012"),col.regions=veg.colors2,
colorkey=list(space="bottom"), scales = list(draw = TRUE), main =
"Spatial Forest Estate Linear Programming Modelling", as.table = TRUE)
~~~~~~~~~~~~

forest is join / merge table of harvest table and dbf of the
shapefile. It has one-to-many relationship.

> A few possible fixes for this issue might be
> rgdal - newer version of gdal are starting to support spatialite
> load extension - figuring out how to load sqlite extensions in R
> system sqlite always loading spatial
> running AsText on spatial columns ahead of time so that regular SQL can
> pull them

I am going to look at this as well. I hope rgdal would support spatialite soon.

> I would skip the rpy2 solution for now as that would seem to just add a
> lot of complication, unless you need automation in which case that would
> be the same option I would choose.

Yes, I have been trying to do some python but my python is quite
limited. I like R since I accomplish a lot in just few days.

Thanks a lot. Noli

[Noli Sicad]

Hi Alex,

The latest Rgdal was just updated 3 January 2010. Are using this
current version? Have you updated your Rgdal package lately?

Please try to update and see if spatialite is properly supported in RGDAL.

Thanks. Noli

[Alex Mandel]

Actually it has to do with GDAL/OGR. Looking at
http://gdal.org/ogr/drv_sqlite.html
I believe it will be supported in version GDAL/OGR 1.7, I don't think
any changes to Rgdal are necessary for it to work once that happens.

I'm still on 1.6 and don't have time to build GDAL from svn (adds too
many complications for QGIS, GRASS etc to build from scratch)

It looks like a partial implementation in 1.6 exists but requires custom
compilation and the spatialite libs to be available. 1.7 appears to be a
native driver.

Alex

[Jose Gómez-Dans]

Hi!

On Monday 11 Jan 2010 23:57:36 Noli Sicad wrote:
> I don't know if this would affect what I am trying to do. I am going
> to read spatialite / sqlite data with geometries to be render in
> spplot.

I don't use R, but python. I can access data from spatialite using the
standard OGR python bindings. It's basically transparent with respect to a
shapefile or spatialite DB, provided your version of GDAL/OGR supports
spatialite (I use 1.6.3, and it seems to work). Check readOGR in rgdal.

If you try python, you won't be going near R anytime soon ;-)

Jose
--
RSU ■ Dept. of Geography ■ University College ■ Gower St, London WC1E 6BT UK
EMM ■ Dept. of Geography ■ King's College ■ Strand, London WC2R 2LS UK

[Noli Sicad]

Hi Jose,

I am trying to create 10 to 30 maps in one page pdf file using rgdal
and spplot as result of my forest carbon estate model in one script
run. The workflow involves joining dbf table and shapefile and

> I don't use R, but python. I can access data from spatialite using the
> standard OGR python bindings. It's basically transparent with respect to a
> shapefile or spatialite DB, provided your version of GDAL/OGR supports
> spatialite (I use 1.6.3, and it seems to work). Check readOGR in rgdal.

http://www.mail-archive.com/r-si...@stat.math.ethz.ch/msg06757.html

> If you try python, you won't be going near R anytime soon ;-)

I am using python - matplotlib in some of graphs. For years now, I
have been trying to use python. Don't worrry, I am a fan of python -
pyqt4 and pyside. But if you can show me some python script to do 10
to 30 maps rendering in one go. I will be happy to python with this
job.

Thanks. Noli

[Noli Sicad]

Hi Jose,

Thanks,

Matplotlib is not so friendly with various file inputs (e.g. xls, dbf,
shapefile, csv). plotfile is csv friendly. The rest is hard to use.

> Well, I tend to use subplots from a matplotlib figure. Attached is something
> similar to what I understand you want, using matplotlib and gdal In this
> case, it's just an imshow (these are raster datasets), but i could use
> basemap to draw countries and other stuff, or I could plot arbitrary vector
> data too. Is this what you want? Same units, maybe ;-)
>

I actually did what I want see the attached png. I am not happy with
the approach of joining tables with shapeflie dbf since I have results
which has one to many relationship. 3 polygons, 3x12 relationship = 36
match with 3 polygon forest stand. This is not possible with shapefile
to be draw in GIS and read in R or probably python module - using qgis
module in python " import qgis".

I have to find solution to this. Postgis and Spatialite might offer
solution to this problem.

Thanks, Noli

[Silas Toms]

Noli-  

I've been using the python module Reportlab to create PDFs 'on the fly' from data stored in a SpatiaLite database. It could be statistically processed with R using rpy2 and rendered using the Reportlab module. I recommend Reportlab- it's intuitive and powerful. Check it out if you have a chance.

here is a devshed tutorial:

http://www.devshed.com/c/a/Python/Python-for-PDF-Generation/