PostGIS Unknown Coordinates Format

Question

I've turned a shapefile into a postgis table using shp2pgsql-gui.

Then, i wanted to get the coordinates of geom column, for that i converted geom to WTK throught ST_AsText(geom) function.

The problem is, the coordinates format is unknown for me :

-69674.5303999996,-294276.2939

Anyone?

Answer 1

First, when you load the data using shp2gpsql-gui, ensure that you've set the SRID on the data correctly: you have determined your data are EPSG:3763, so use that number in the SRID field of the GUI.

Now that your data is correctly loaded, you can get back geographic coordinate by using the ST_Transform function to convert them from their local system into a longitude/latitude system, like

SELECT ST_AsText(ST_Transform(geom, 4326)) FROM mytable

(4326 is the most common EPSG number used for geographic coordinates, and it's probably what you want for everyday use.)

As a side note, we can confirm that your SRID number guess is correct by running a quick transformation using your example coordinates:

select st_astext(
  st_transform(
    st_setsrid(
      st_makepoint(-69674.5303999996,-294276.2939),
    3763),
  4326));

POINT(-8.91600862367417 37.0146103059524)

Looks like it's in Portugal, so great!