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.

If you need to contact me, you can use the contact form, I usually answer quickly.

Please be aware that I post on this blog only seldom. My main channel of posting updates, lately, has been twitter.

This is an historical map of my city, Pavia, Italy, scanned from out copyright source. The file is large enough to be printed on a 50 by 75 sheet of paper.