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

[Robert_Clift at doh.state.fl.us]

Hi Matt:

Believe you can cast your geography AS geometry within a query, like:

SELECT ST_X(myGeogColName::geometry), ST_Y(myGeogColName::geometry)
  FROM myTable;


-Rob

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of W. Matthew Wilson
Sent: Wednesday, January 05, 2011 11:30 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] rookie question: how to extract latitude and longitude values from geography(POINT) column?

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users