[postgis-users] rookie question: how to extract latitude and longitude values from geography(POINT) column?

[W. Matthew Wilson]

On Wed, Jan 5, 2011 at 10:09 AM, David Fawcett <david.fawcett at gmail.com> wrote:
> I haven't tried them specifically with the geography type, but I would
> assume that ST_AsText(), ST_X(), and ST_Y() should work.
>
> Something like:
>
> SELECT ST_X(myGeogColName), ST_Y(myGeogColName)
>   FROM myTable;

Apparently ST_X wants a geometry point, not a geography point.  I
tried this and it failed:

dev=> select ST_X(ST_GeogFromText('POINT(11 12)'));
ERROR:  function st_x(geography) does not exist
LINE 1: select ST_X(ST_GeogFromText('POINT(11 12)'));
               ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

So then I converted the geography point into a geometry point, and it works OK:

dev=> select ST_X(ST_GeomFromText(ST_AsText(ST_GeogFromText('POINT(11 12)'))));
+------+
| st_x |
+------+
|   11 |
+------+
(1 row)

What is the right way to convert a geography point to a geometry
point?  Is there something better / faster / safer than dumping to
text and then reloading?

Thanks for the help.

Matt