How to convert Geom object from PostGis in R in latitude and longitude?

Question

I have a database that has "location" attribute and it is saved as geom object and looks like this: 0101000020E6100000000000603D1D5EC0000000A06D424740

Is there a way to extract coordinates from it in R? These values are loaded in R as strings. Also it gave a warning:

Warning message:
In postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized PostgreSQL field type geometry (id:18832) in column 2)

Any help appreciated.

Answer 1

Have you tried using the rgeos functions? One option would be reading WKT directly from the database (using ST_AsText)...

SELECT ST_AsText('0101000020E6100000000000603D1D5EC0000000A06D424740');
                st_astext                 
------------------------------------------
 POINT(-120.456871032715 46.518970489502)
(1 Zeile)

In R, using readWKT you can then extract the coordinate pair:

> readWKT("POINT(-120.456871032715 46.518970489502)")
SpatialPoints:
          x        y
1 -120.4569 46.51897
Coordinate Reference System (CRS) arguments: NA 

Another option would be to read x,y directly from the database using ST_X and ST_Y:

 SELECT ST_X('0101000020E6100000000000603D1D5EC0000000A06D424740'),
        ST_Y('0101000020E6100000000000603D1D5EC0000000A06D424740');
       st_x        |      st_y       
-------------------+-----------------
 -120.456871032715 | 46.518970489502
(1 Zeile)