Transform geometry to coordinates

Question

I need transform with postgis a geometry of type

010600002031BF0D0001000000010300000001000000050000008FEF9C07089AFEC0B90A9856E87251410F355DB1B395FEC0DCCEADDCED72514194BE3130A693FEC0DFD23127D072514114797186FA97FEC094E0C313CB7251418FEF9C07089AFEC0B90A9856E8725141

to

-1.130497 37.995302,-1.128874 37.995732,-1.128153 37.994190,-1.129663 37.993819,-1.130497 37.995302

Answer 1

If you want to transform coordinates to another SRID, you will use ST_Transform:

SELECT
    ST_AsText(
         ST_Transform('010600002031BF0D0001000000010300000001000000050000008FEF9C07089AFEC0B90A9856E87251410F355DB1B395FEC0DCCEADDCED72514194BE3130A693FEC0DFD23127D072514114797186FA97FEC094E0C313CB7251418FEF9C07089AFEC0B90A9856E8725141'
   ::geometry,4326))

which produces:

"MULTIPOLYGON(((-1.12598881799973 37.9623844064892,-1.1253665455084 37.9625408936206,-1.12507150251677 37.9616992426904,-1.1256937750081 37.9615554420694,-1.12598881799973 37.9623844064892)))"

Answer 2

You can use the ST_AsText function to get the WKT representation of your geometry, this will return the coordinates in whatever projection you have. (You will only get the latitude and longitude if your data is stored in a Geographic Coordinate system, SRID 4326 works for most cases)

You can use this query to change SRID

ALTER TABLE table 
   ALTER COLUMN geom 
   TYPE Geometry(MultiPolygon, 4326) 
   USING ST_Transform(geom, 4326);

And get the cordinates with:

 
select ST_AsText(geom)
from table