Hi list,
I've been looking around trying to solve this problem; unfortunately I
haven't found a way to solve it.
I have a database with sampling points with an associated sampling date
and would like to plot the density of sampling points on a monthly basis
for one year using spplot. However, I need to query the database and
import the result of the query into R to visualize the data. I can
import the query on a column-wise basis but the WKB data are imported as
chars into R (as expected). Is there a way by which I could read these
data (WKB geometry) once imported into R? Or is there a way by which
ReadOGR can read the outcome of the query?
I tried creating a temporary table through the query, but it seems that
ReadOGR can't read it.
Thanks for your help,
Jaime
As an example:
> db.execute("select geo.id as id,geo.wkb_geometry as geom from wells
as geo, gwhead as h where geo.id=h.id and date_part('year',h.date)=2001
and h.gwhead<700",clear=F)
> ids<-db.read.column("id",as.is=F)
> geom<-db.read.column("geom",as.is=F)
Warning message:
In rpgsql.cast.values.default(data.col) :
Coercing PGSQL type 16437 to character
> geom
[1] "0101000020FF7F00000000000088FD2141000000C09BB95741"
[2] "0101000020FF7F00000000000088FD2141000000C09BB95741"
[3] "0101000020FF7F00000000000088FD2141000000C09BB95741"
[4] "0101000020FF7F00000000000088FD2141000000C09BB95741"
[5] "0101000020FF7F00000000000088FD2141000000C09BB95741"
reading WKB geometry
2 messages · Jaime Carrera, Edzer Pebesma
Jaime, I've been successful in reading points from a PostGIS data base through OGR; see http://wiki.intamap.org/index.php/PostGIS -- Edzer
Jaime Carrera wrote:
Hi list, I've been looking around trying to solve this problem; unfortunately I haven't found a way to solve it. I have a database with sampling points with an associated sampling date and would like to plot the density of sampling points on a monthly basis for one year using spplot. However, I need to query the database and import the result of the query into R to visualize the data. I can import the query on a column-wise basis but the WKB data are imported as chars into R (as expected). Is there a way by which I could read these data (WKB geometry) once imported into R? Or is there a way by which ReadOGR can read the outcome of the query? I tried creating a temporary table through the query, but it seems that ReadOGR can't read it. Thanks for your help, Jaime As an example:
db.execute("select geo.id as id,geo.wkb_geometry as geom from wells
as geo, gwhead as h where geo.id=h.id and
date_part('year',h.date)=2001 and h.gwhead<700",clear=F)
ids<-db.read.column("id",as.is=F)
geom<-db.read.column("geom",as.is=F)
Warning message: In rpgsql.cast.values.default(data.col) : Coercing PGSQL type 16437 to character
geom
[1] "0101000020FF7F00000000000088FD2141000000C09BB95741" [2] "0101000020FF7F00000000000088FD2141000000C09BB95741" [3] "0101000020FF7F00000000000088FD2141000000C09BB95741" [4] "0101000020FF7F00000000000088FD2141000000C09BB95741" [5] "0101000020FF7F00000000000088FD2141000000C09BB95741"
_______________________________________________ R-sig-Geo mailing list R-sig-Geo at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Edzer Pebesma Institute for Geoinformatics (ifgi), University of M?nster, Weseler Stra?e 253, 48151 M?nster, Germany. Phone: +49 251 8333081, Fax: +49 251 8339763 http://ifgi.uni-muenster.de/