0

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?

  • You'll need to provide the SRID or .prj contents before we could tell you what projection that might be (it could be any projected coordinate system) – Vince Jul 14 '16 at 11:26
  • 1
    using prj2epsg.org, result are: 3763 - ETRS_1989_TM06-Portugal – Joao Castanheira Jul 14 '16 at 11:32
  • 1
    The coordinates are X and Y meters in a local Portugal projection. If you want something different, you'll need to reproject. – Vince Jul 14 '16 at 12:13
  • what you mean with 'reprojet' ? – Joao Castanheira Jul 14 '16 at 13:06
  • 2
    ST_Transform, from one coordinate reference system to another. – John Powell Jul 14 '16 at 14:33
2

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.