rpostgresqltable-functions

Is there any way to call PostgreSQL tablefunc module in R?


I have some PostgreSQL/PostGIS tablefunc queries that I want to call in R environnement. Is there any way to perform this?

Ex :

dbGetQuery(con, "SELECT * FROM CROSSTAB(
'SELECT factor1, factor2, ROUND(SUM(ST_AREA(geom))::numeric, 3) FROM table GROUP BY factor1, factor2 ORDER BY 1,2',
'SELECT factor2 FROM table GROUP BY factor2 ORDER BY factor2 '
) AS ct("factor1" varchar, "factor2value1" varchar, "factor2value2" varchar, "factor2value3" varchar)
")

Solution

  • Any compliant Postgres query can work if you connect successfully to Postgres. Hence, if an SQL statement works in Postgres (i.e., psql, pgAdmin) it should work in a client like R that connects to it. Note: the crosstab requires an extension. Simply escape the double quotes in SQL statement with backslashes to compile as a character in R:

    sql <- "SELECT * 
            FROM CROSSTAB(
               'SELECT factor1, factor2, 
                       ROUND(SUM(ST_AREA(geom))::numeric, 3) 
                FROM table 
                GROUP BY factor1, factor2 
                ORDER BY 1,2'
               ,
               'SELECT factor2 
                FROM table 
                GROUP BY factor2 
                ORDER BY factor2 '
             ) AS ct(\"factor1\" varchar, \"factor2value1\" varchar,
                     \"factor2value2\" varchar, \"factor2value3\" varchar)"
    
    dbGetQuery(con, sql)