0

I have about 14000 points with coordinates EPSG2438 and want to convert the coordinates into WGS84 (EPSG4326). I want to know that is there a way to make Excel formula to convert these coordinates. In QGIS (and I suppose in other GIS packages as well) when we want to change CRS, we can see certain information associated with each EPSG. For Example for EPSG4326

Extent: -180.00, -90.00, 180.00, 90.00
Proj4: +proj=longlat +datum=WGS84 +no_defs

and for EPSG2438

Extent: 121.50, 28.22, 124.50, 53.56
Proj4: +proj=tmerc +lat_0=0 +lon_0=123 +k=1 +x_0=500000 +y_0=0 +ellps=krass 
+towgs84=15.8,-154.4,-82.3,0,0,0,0 +units=m +no_defs

Can this associated information be used to convert from EPSG2438 to EPSG4326 and vice versa?

  • 2
    it would be easiest to use ogr2ogr on a csv file – Ian Turton Nov 26 '18 at 8:53
  • This seems promising and more convenient. But I am confused by the complex syntex of this command line took. – datakeen Nov 26 '18 at 19:05
  • Thank you all for giving a hand for help. I finally landed on Surfer 13 for the conversion. For the benefit of others who are not proficient with command line tool like ogr2ogr or through other complex methods, I am writing it here. Open your CSV or spreadsheet, containing coordinates data, using File|Open. Then go to Data|Coordinate System|New Projected Coordinates. Rest of things are straightforward. Just choose source X and Y columns, Target (empty) columns for converted X and Y. Also choose source source and target Coordinate Systems and press OK. – datakeen Nov 27 '18 at 15:33
0

QGIS uses a package called "PROJ4" to do the computation based on those coordinate reference specifications. The mathematics of transforming from X-Y coordinates to lat-long on an ellipsoid based on wrapping a cylinder round the Earth (which is what +proj=tmerc means, basically) are quite complicated.

But a quick search reveals the Ordnance Survey may have a spreadsheet to help you, which claims to support transverse mercator coordinate transforms:

https://www.ordnancesurvey.co.uk/business-and-government/help-and-support/navigation-technology/os-net/coordinate-spreadsheet.html

But as mentioned in comments, saving as a CSV and using ogr2ogr or using QGIS might be easier.

  • Thank you for your answer. However , 1) this excel sheet and pdf guidance does not offer conversion between the mentioned EPSGs, 2) as explained on there website the conversion using this sheet is not very accurate. The math of CRS conversation involves much more complexity than I anticipated. I am now working on ogr2ogr tool. It so far i am perplexed by its complex syntaxing. – datakeen Nov 26 '18 at 19:17
  • I think that spreadsheet can do the computation between those CRS specifications, but you would have to look up the specific parameters for those CRS to put into the spreadsheet, such as the earth radius and eccentricity of the Krass ellipsoid - its all on the EPSG CRS web site database. – Spacedman Nov 26 '18 at 19:24

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.