dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #16363
[Branch ~dhis2-documenters/dhis2/dhis2-docbook-docs] Rev 459: Added a section on the use of RPostgresql for production of maps with R
------------------------------------------------------------
revno: 459
committer: Jason P. Pickering <jason.p.pickering@xxxxxxxxx>
branch nick: dhis2-docbook-docs
timestamp: Wed 2012-03-07 19:20:12 +0200
message:
Added a section on the use of RPostgresql for production of maps with R
added:
src/docbkx/en/resources/images/r/OPDAttendance.png
modified:
src/docbkx/en/dhis2_r.xml
src/docbkx/en/dhis2_user_manual_en.xml
--
lp:~dhis2-documenters/dhis2/dhis2-docbook-docs
https://code.launchpad.net/~dhis2-documenters/dhis2/dhis2-docbook-docs
Your team DHIS 2 developers is subscribed to branch lp:~dhis2-documenters/dhis2/dhis2-docbook-docs.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-documenters/dhis2/dhis2-docbook-docs/+edit-subscription
=== modified file 'src/docbkx/en/dhis2_r.xml'
--- src/docbkx/en/dhis2_r.xml 2012-03-05 08:13:55 +0000
+++ src/docbkx/en/dhis2_r.xml 2012-03-07 17:20:12 +0000
@@ -197,4 +197,114 @@
</imageobject>
</mediaobject>
</section>
+ <section id="dhis2_r_maps">
+ <title>Mapping with R and Postgresql</title>
+ <para>A somewhat more extended example, will use the RPostgreSQL library and several other libaries to produce a map from the coordinates stored in the database. We will define a few helper functions to provide a layer of abstraction, which will make the R code more reusable. </para>
+ <programlisting>#load some dependent libraries
+ library(maps)
+ library(maptools)
+ library(ColorBrewer)
+ library(ClassInt)
+ library(RPostgreSQL)
+
+#Define some helper functions
+
+#Returns a dataframe from the connection for a valid statement
+dfFromSQL<-function (con,sql){
+ rs<-dbSendQuery(con,sql)
+ result<-fetch(rs,n=-1)
+ return(result)
+}
+#Returns a list of latitudes and
+ longitudes from the orgunit table
+dhisGetFacilityCoordinates<- function(con,levelLimit=4) {
+sqlCoords<-paste("SELECT ou.organisationunitid, ou.name,
+substring(ou.coordinates from E'(?=,?)-[0-9]+\\.[0-9]+')::double precision as latitude,
+substring(ou.coordinates from E'[0-9\\.]+')::double precision as
+ longitude FROM organisationunit ou where ou.organisationunitid
+ in (SELECT DISTINCT idlevel",levelLimit, " from _orgunitstructure)
+ and ou.featuretype = 'Point'
+ ;",sep="")
+ result<-dfFromSQL(con,sqlCoords)
+ return(result)
+ }
+
+#Gets a dataframe of IndicatorValues,
+# provided the name of the indicator,
+# startdate, periodtype and level
+dhisGetAggregatedIndicatorValues<-function(con,
+indicatorName,
+startdate,
+periodtype="Yearly",
+level=4)
+{
+ sql<-paste("SELECT organisationunitid,dv.value FROM aggregatedindicatorvalue dv
+where dv.indicatorid =
+(SELECT indicatorid from indicator where name = \'",indicatorName,"\') and dv.level
+ =", level,"and
+ dv.periodid =
+(SELECT periodid from period where
+startdate = \'",startdate,"\'
+and periodtypeid =
+(SELECT periodtypeid from periodtype
+ where name = \'",periodtype,"\'));",sep="")
+ result<-dfFromSQL(con,sql)
+ return(result)
+ }
+
+#Main function which handles the plotting.
+#con is the database connection
+#IndicatorName is the name of the Indicator
+#StartDate is the startdate
+#baselayer is the baselayer
+plotIndicator<-function(con,
+IndicatorName,
+StartDate,
+periodtype="Yearly",
+level=4,baselayer)
+{
+myDF<-dhisGetAggregatedIndicatorValues(con,
+IndicatorName,StartDate,periodtype,level)
+coords<-dhisGetFacilityCoordinates(con,level)
+myDF<-merge(myDF,coords)
+myDF<-SpatialPointsDataFrame(myDF[,
+c("longitude","latitude")],myDF)
+IndColors<-c("firebrick4","firebrick1","gold"
+,"darkolivegreen1","darkgreen")
+class<-classIntervals(myDF$value,n=6,style="quantile"
+,pal=IndColors)
+colCode<-findColours(class,IndColors)
+myPlot<-plot.new()
+plot(baselayer)
+points(myDF,col=colCode,pch=19)
+title(main=IndicatorName,sub=StartDate)
+return(myPlot) }
+
+
+</programlisting>
+ <para>Up until this point, we have defined a few functions to help us make a map. We need to get the coordinates stored in the database and merge these with the indicator which we plan to map. We then retrieve the data from the aggregated indicator table, create a special type of data frame (SpatialPointsDataFrame), apply some styling to this, and then create the plot. </para>
+ <programlisting>
+#Now we define the actual thing to do
+con <- dbConnect(PostgreSQL(), user= "dhis", password="SomethingSecure", dbname="dhis")
+#Define the name of the indicator to plot
+MyIndicatorName<-"Total OPD Attendance"
+MyPeriodType<-"Yearly"
+#This should match the level where coordinates are stored
+MyLevel<-4
+#Given the startdate and period type, it is enough
+#to determine the period
+MyStartDate<-"2010-01-01"
+#Get some Some Zambia district data from GADM
+con <- url("http://www.filefactory.com/file/c2a3898/n/ZMB_adm2_RData")
+print(load(con))#saved as gadm object
+#Make the map
+plotIndicator(con,MyIndicatorName,MyStartDate,MyPeriodType,MyLevel,gadm)</programlisting>
+ <para>The results of the plotIndicator function are shown below.</para>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="resources/images/r/OPDAttendance.png"/>
+ </imageobject>
+ </mediaobject>
+ <para>In this example, we showed how to use the RPostgreSQL library and other helper libraries(Maptools, ColorBrewer) to create a simple map from the DHIS2 data mart. </para>
+ </section>
</chapter>
=== modified file 'src/docbkx/en/dhis2_user_manual_en.xml'
--- src/docbkx/en/dhis2_user_manual_en.xml 2012-03-05 08:13:55 +0000
+++ src/docbkx/en/dhis2_user_manual_en.xml 2012-03-07 17:20:12 +0000
@@ -55,6 +55,7 @@
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude"; href="dhis2_r.xml" encoding="UTF-8" xpointer="dhis2_r_intro"/>
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude"; href="dhis2_r.xml" encoding="UTF-8" xpointer="dhis2_r_odbc"/>
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude"; href="dhis2_r.xml" encoding="UTF-8" xpointer="dhis2_r_mydatamart"/>
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude"; href="dhis2_r.xml" encoding="UTF-8" xpointer="dhis2_r_maps"/>
</appendix>
<appendix>
<title> DHIS Technical Architecture Guide</title>
=== added file 'src/docbkx/en/resources/images/r/OPDAttendance.png'
Binary files src/docbkx/en/resources/images/r/OPDAttendance.png 1970-01-01 00:00:00 +0000 and src/docbkx/en/resources/images/r/OPDAttendance.png 2012-03-07 17:20:12 +0000 differ
