Translating geometry from EPSG:23030 to EPSG:21818, then to lat long, using PostGIS?

Question

I'm from Colombia, South America. I'm a computer programming student and I have to develop a prototype for a GIS system for my small home town using Postgis >= 2.0 as database back-end.

The local public works office gave me the old Arcgis shape files: one for urban roads, one for rural roads, one for mountain streams, one for lakes, and so on. All shape files contain the *.shp, *.shx and *.dbf files, except the *.proj file.

This is the problem: The local goverment made these shape files long time ago using the EPSG:23030 reference system which locates in Western Europe (A very bad mistake with they still work and are not interested to change due to time and budget resources).

However, Colombia is located within the EPSG:21818 Colombia Mainland. Using http //epsg.io/, curiously, I figure that coordinates x,y (in meters since EPSG:23030 is UTM) of the roads, streams and lakes correspond to locations in Nigeria, Africa.

You can find a point located on the central park of my home town http //epsg.io/ using these coordinates: (-10153166.72, 1702714.02).

So my question is: How can I use Postgis, for example, using the st_transform function, to translate the imported shape files data from EPSG:23030 to EPSG:21818? How can I "convert" the EPSG:21818 to lat, lon coordinates later? Is it possible to do all this with Postgis?

---

I uploaded the shapefiles (no *.proj available), sql files (with EPSG:23030) and some pictures showing a sample point in UTM: 1106289.573,1131273.240 which I've taken as an example (it's an upper left corner on the central park).

Since, the original shape files didn't have the projection file, I set it in Postgis with this command:

SELECT UpdateGeometrySRID('viasurb02','geom',23030);

When using QGis we can see some rural roads distort due to low bad quality when capturing the data, so I considered the urban roads layer (viasurb02.shp) for taking the sample point. Huh, That point is located in Benin, Africa.

www.mediafire.com/?s1s6ae8bh8i3kev

---

I've tried the suggestion about importing the shape files by setting the EPSG:23030 SRID:

 shp2pgsql -s 23030 -W LATIN1 -D -I viasurb02.shp viasurb02_b | psql -d mycity -U me

Then updated the SRID of the table:

ALTER TABLE viasurb02 
ALTER COLUMN geom 
TYPE Geometry(MultiLinestring, 21818) 
USING ST_Transform(geom, 21818);

However, the traslated geometry is impossible for EPSG:4326 because it is inf/inf

Then, I've tried the suggestion assuming that the shapefiles already has the EPSG:21818:

shp2pgsql -s 23030 -W LATIN1 -D -I viasurb02.shp viasurb02_b | psql -d mycity -U me

And now, the same sample point is located in North Venezuela i58.tinypic.com/2jdny2x.jpg.

I don't know if the local public works office created in the late 1980s, using a bad projection or using Arcgis as it just it were Autocad.

Are there additional steps I can take to make another traslation geometry?

Answer 1

@MKennedy is right, the data are EPSG:21897, so you just need to declare that at load time, and then everything will magically work.

shp2pgsql -s 21897 -W LATIN1 -D -I roads.shp roads | psql -d mycity

And then see if you can get good geography coordinates out.

SELECT ST_AsText(ST_Transform(geom,4326)) FROM roads LIMIT 1;

Better!

?

If you want to store your data in geographic coordinates, you can reproject them in place using ALTER TABLE:

ALTER TABLE roads
  ALTER COLUMN geom
  TYPE Geometry(MultiLinestring,4326)
  USING ST_Transform(geom, 4326)

I don't necessarily recommend this, unless you're trying to integrate with a global data set, since now you cannot do useful things like measure distances in meters or areas in meters square without transforming back to a cartesian coordinate system first.