feature request: rpostgresql does not support wkb/wkt

[GoogleCodeExporter]

I have a database, which uses Postgis for spatial data.
The data is kept according to OpenGIS standard.

When I load a table containing spatial data using RPostgreSQL,
then I am getting

    Warning message:
    In postgresqlExecStatement(conn, statement, ...) :
      RS-DBI driver warning: (unrecognized PostgreSQL field type 34799 in column 0)

Would it be possible to enhance RPostgreSQL to support
Postgis types?

I believe spatial data (the table columns, which contain
spatial data) should be converted to appropriate R spatial
types, see http://cran.r-project.org/web/packages/sp/index.html

Then such simple code would be possible:

require('RPostgreSQL')
require('sp')

cn <- dbConnect(PostgreSQL(), dbname='gis')

d <- dbGetQuery(cn, "select location from table')

pdf('map.pdf')
plot(data)
dev.off()


Or other project should be started in order to
support spatial types of data fetched from
Postgresql databases enhanced with Postgis?

Original issue reported on code.google.com by [email protected] on 9 Sep 2010 at 10:07

[GoogleCodeExporter]

For a point gemoetry try the following instead:

select ST_X(location) as x, ST_Y(location) as y from table

Original comment by [email protected] on 8 Oct 2010 at 12:22

[GoogleCodeExporter]

> For a point gemoetry try the following instead:

> select ST_X(location) as x, ST_Y(location) as y from table

above will not convert data to R spatial objects (see 'sp' package description)

Original comment by [email protected] on 8 Oct 2010 at 3:34

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

>above will not convert data to R spatial objects 

My point: first start by using a properly formed PostGIS query, using PostGIS 
functions to extract the data you need into the correct format understood by 
RPostgresql and you will not get a field type error. As_Text, for example, 
returns WKT as a text field, which RPostgresql certainly can handle and R sp 
should, but doesn't, which is, in my opinion, the real problem. In any case, it 
is trivial to convert the data yourself into R sp using R and sp functions. 

Else use rgdal.

library(rgdal)
mydf <- readOGR("PG:dbname=gis", "table")

Original comment by [email protected] on 8 Oct 2010 at 8:28

[GoogleCodeExporter]

> >above will not convert data to R spatial objects 

> My point: first start by using a properly formed PostGIS query, using PostGIS
> functions to extract the data you need into the correct format understood by 
> RPostgresql and you will not get a field type error. As_Text, for example, 
returns
> WKT as a text field, which RPostgresql certainly can handle and R sp should, 
but 
> doesn't, which is, in my opinion, the real problem. In any case, it is 
trivial to 
> convert the data yourself into R sp using R and sp functions. 

Sorry, maybe I was not clear but I really know, how to use ST_* functions
and I really understand that I can convert the data by myself. :)

I was just wondering if it would be possible for RPostgreSQL to support
PostGIS -> sp conversion.

> Else use rgdal.

> library(rgdal)
> mydf <- readOGR("PG:dbname=gis", "table")

At the moment rgdal is out of option as 'table' contains
few millions rows, while only few hundred of them are
needed - rgdal does not support query parametrization.

Original comment by [email protected] on 9 Oct 2010 at 12:40

[GoogleCodeExporter]

*If* you contribute code to do *type matching* it can be done as both 
PostgreSQL and R know the data type.

But because this is not an atomic "common" type like float or char, no support 
exists right now.  We welcome well-designed, documented and tested additions.

Original comment by dirk.eddelbuettel on 9 Oct 2010 at 12:46

[GoogleCodeExporter]

Original comment by [email protected] on 13 Oct 2010 at 3:00

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

>At the moment rgdal is out of option as 'table' contains
>few millions rows, while only few hundred of them are
>needed

Then simply create a temporay view of the few hundred rows you need and use 
that view with rgdal.

Original comment by [email protected] on 14 Oct 2010 at 10:03

[GoogleCodeExporter]

> > At the moment rgdal is out of option as 'table' contains
> > few millions rows, while only few hundred of them are
> > needed

> Then simply create a temporay view of the few hundred rows you need and use 
that 
> view with rgdal.

Creating view is an option but IMHO creating one
every time new parameters are needed is a not so
nice workaround.

Original comment by [email protected] on 14 Oct 2010 at 10:22

[GoogleCodeExporter]

I feel like I'm having similar issue with enums. Is it possible to recognize 
enums and treat them as characters without the warning? Or how can I suppress 
this warning? 

Original comment by [email protected] on 22 Sep 2011 at 4:10

[GoogleCodeExporter]

Because each time you make a new enum, the enum will have different type id. 
Thus, to properly handle them a query to pg_type is required on the fly.  If 
you just want to suppress the warning, you may delete the code emitting the 
warning, recompile and install. 

The relevant code is:

            snprintf(errMsg, 128, "unrecognized PostgreSQL field type %d in column %d", internal_type, j);
            RS_DBI_errorMessage(errMsg, RS_DBI_WARNING);

Original comment by [email protected] on 23 Sep 2011 at 6:08

[GoogleCodeExporter]

> Because each time you make a new enum, the
> enum will have different type id. Thus, to
> properly handle them a query to pg_type is
> required on the fly.

In Python (i.e. psycopg module for postgresql [1]
and sqlite module for... sqlite [2] :) you can set
custom type adapters and converters.

Would it be possible to enable such functionality
in rpostgresql?

[1] 
http://initd.org/psycopg/docs/extensions.html#sql-adaptation-protocol-objects
[2] 
http://docs.python.org/library/sqlite3.html#converting-sqlite-values-to-custom-p
ython-types

Original comment by [email protected] on 23 Sep 2011 at 2:07

[GoogleCodeExporter]

r184 removes the warning on enum and array though array conversion to native 
vector is not done.

While the idea of having adapters sounds nice, I am not able to 
implement it.
Perhaps anyway you need to know the type from the oid of the type.
This is also implemented in r184. Suppressing warning is easy as above.
But custom type adapter and converters is completely new. So, 
1 data structure to store the registered type and functions pair 
2 a function to register the adapter 
3 a routine to select the adapter and to call the function. 
is necessary.  That's theoretically doable, but not a light task.
The performance will not better than getting table of string values and
perform apply the conversion. Then the conversion may be parallelized.

Original comment by [email protected] on 24 Sep 2011 at 10:43