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.


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 
TYPE Geometry(MultiLinestring, 21818) 
USING ST_Transform(geom, 21818);

However, the traslated geometry is impossible for EPSG:4326 because it is inf/inf The same sample point with EPSG:21818

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?

  • 1
    Can you include in your question a sample coordinate pair from your "EPSG:23030" data? If they're showing up in Nigeria, then the metadata information is incorrect. Could they instead be in whatever UTM zone is appropriate for that area of Colombia? – mkennedy Jul 8 '14 at 23:47
  • Thank you mkennedy. I uploaded the files on UPDATE 2014-07-08 20:16 UTC-5 – ederandresan Jul 9 '14 at 1:17
  • I'm just commenting because @paul-ramsey should update his answer as he has the PostGIS info. The data is using EPSG:21897 Bogota 1975 / Colombia Bogota Zone. If you need to convert to WGS84, the tfm is "coordinate frame" dx=221.899 dy=274.136 dz=-397.554 rx=2.808446 ry=-0.448509 rz=-2.810172 s=-2.199943. In ArcGIS the tfm is "Bogota_To_WGS_1984_Region_8_CF". Also, always say where the data is (the city), luckily you'd included the screenshots and I could find out that it was Paipa. – mkennedy Jul 9 '14 at 16:37

@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;


Overlay of roads on basemap?

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

  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.

  • I followed your suggestions on UPDATE 2014-07-08 21:14 UTC-5. Still no solution. Bad shape files? – ederandresan Jul 9 '14 at 2:15
  • Well, the second attempt came closer at least (right continent). Any other clues as to what projection the originators thought it was? – Paul Ramsey Jul 9 '14 at 3:17

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.