Let’s say we have a file like the following, coming from a local mapping agency. It is a Microsoft Excel/LibreOffice where we have two columns for the X and Y coordinates represented in the Gauss-Boaga coordinate system, plus several informational columns containing non-geographic data.
Here an hypotetical tabular data file:
| POSX | POSY | height | type |
|---|---|---|---|
| 1722813.000 | 5102614.000 | 4.0 | Lanterne |
| 1722818.000 | 5102572.000 | 3.5 | Lanterne |
| 1722825.276 | 5102541.078 | 3.5 | Sfere |
| 1722838.000 | 5102546.000 | 3.5 | Sfere |
| 1722851.848 | 5102550.524 | 3.5 | Sfere |
| 1722814.747 | 5102522.739 | 4.0 | Sfere |
| 1722809.241 | 5102501.789 | 4.0 | Sfere |
We need to convert it to KML in order to visualize the data in Google Earth. Google Earth makes use of the WGS84 coordinate system, so we are going to use the excellent OGR/GDAL command line software both for the coordinate conversion process (Gauss-Boaga –> WGS84) and the data format conversion (CSV –> KML).
It is a multi step process:
- Write the supporting files
- Create a CSV out of the spreadsheet
- Create the data access VRT file
- Create the Gauss-Boaga rototranslation file
- Converting the files
- from CSV to SHP (still Gauss-Boaga)
- from SHP Gauss-Boaga to SHP WGS84
- from SHP (WGS84) to KML
Let’s start: we need to prepare some support files to help us convert the data. First of all we need to export the data from the spreadsheet software to a Comma Separated Values (CSV) file. Which will look like this:
POSX,POSY,height,type 1722813.000,5102614.000,4.0,Lanterne 1722818.000,5102572.000,3.5,Lanterne 1722825.276,5102541.078,3.5,Sfere 1722838.000,5102546.000,3.5,Sfere 1722851.848,5102550.524,3.5,Sfere 1722814.747,5102522.739,4.0,Sfere 1722809.241,5102501.789,4.0,Sfere
OGR supports a virtual format which allows specification via an XML file. If you convert your worksheet into a CSV, you can generate a VRT file to instruct GDAL on how to access the data. I’ve created my VRT file like the following, and saved it on the disk as example.vrt:
<OGRVRTDataSource>
<OGRVRTLayer name="example">
<SrcDataSource>/home/simone/Desktop/test_data/example.csv</SrcDataSource>
<GeometryType>wkbPoint</GeometryType>
<LayerSRS>WGS84</LayerSRS>
<GeometryField encoding="PointFromColumns" x="POSX" y="POSY"/>
</OGRVRTLayer>
</OGRVRTDataSource>
This line in example.vrtis the important one:
<GeometryField encoding="PointFromColumns" x="POSX" y="POSY"/>
it tells GDAL which culumn is what. Which column is the east-west and north-south coordinate. We are now ready to generate a SHP, still in Gauss-Boaga coordinates.
ogr2ogr -f "ESRI Shapefile" example.shp example.vrt
Now we can convert example.shp to WGS84, using this PROJ file (gauss-boaga.prj):
PROJCS["Monte Mario / Italy zone 1", GEOGCS["Monte Mario",DATUM["Monte_Mario", SPHEROID["International 1924",6378388,297,AUTHORITY["EPSG","7022"]], TOWGS84[-104.1,-49.1,-9.9,0.971,-2.917,0.714,-11.68], AUTHORITY["EPSG","6265"]], PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4265"]], UNIT["metre",1,AUTHORITY["EPSG","9001"]], PROJECTION["Transverse_Mercator"], PARAMETER["latitude_of_origin",0], PARAMETER["central_meridian",9], PARAMETER["scale_factor",0.9996], PARAMETER["false_easting",1500000], PARAMETER["false_northing",0], AUTHORITY["EPSG","3003"], AXIS["X",EAST],AXIS["Y",NORTH] ]
and by executing the following code:
ogr2ogr -s_srs gauss-boaga.prj -t_srs EPSG:4326 example_wgs84.shp example.shp
We are ready for the final step: convert the SHP file to KML:
ogr2ogr -f "KML" wgs84_GE.kml wgs84_example.shp
wgs84_GE.kml is our final output file.
