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)
")
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)