1. Home
  2. MyGeodata Converter
  3. Conversion of XY or LAT/LON tabular data to GIS formats using CSV or Excel

Conversion of XY or LAT/LON tabular data to GIS formats using CSV or Excel

There are a lot of various sectors of human activities using spatial data stored as a set of XY coordinate pairs in various formats – most often as tabular data in MS Excel (.xlsx, .xls), LibreOffice / OpenOffice Spreadsheet (.ods) or coma-separated values (.csv). Such a data is usually acquired by GPS unit recorders, determined from the topographic maps, automatically generated in regular grid or found on the internet. XY data usually contains coordinate pairs, some identifier of each record and mostly some measured attribute or more attributes, alternatively some other attributes like name, description, note and so on. Many software tools intended for spatial data processing or visualization require these input data to be in a specific file format – commonly used in GIS (geographic information system), having XY pairs converted to proper point geometry and assigned correct coordinate system. This article will guide you how to properly prepare and convert your XY data to various GIS file formats.

How to format XY data

Your XY data should have at least two columns – for X (easting / longitude) and Y (northing / latitude) coordinates. In this case the data can be used at least for visualization on a map or for computation of some spatial relationship. It is desirable to be able to clearly identify each record (coordinate pair) – for example by adding a column like ID. Then it is possible to recognize each point and perform more advanced spatial tasks like clustering and so on. Other attribute data, if available (like measured values, name, description and others), may be in additional columns – see this example:

The most common way to prepare or modify XY data is some kind of spreadsheet – such as MS Excel, LibreOffice, Google Docs Spreadsheet or similar. Data can be saved as XLS(X), ODS or CSV. The CSV file might look like this:

ID,X,Y,Name,Value
1,-110.90501638852,36.9813119931835,"WP1",0.65
2,-110.92060631657,36.9852067957623,"WP2",0.63
3,-110.923470299102,36.9940599494987,"WP3",0.55
4,-110.90944281697,37.0172331183309,"WP4",0.58
5,-110.887594568301,37.0261770876165,"WP5",0.61
6,-110.88045469348,37.0155087171382,"WP6",0.62
7,-110.887791341627,37.0403935156402,WP7,0.6
8,-110.875933818138,37.0402704979379,WP8,0.54

When exporting to CSV, it is recommended to use double quotes to delimit text attributes – otherwise commas contained in the text could be falsely detected as column separator. Decimal separator must be point – not comma (which is common in some countries). If any text in your data contains some non-ASCII characters (eg. diacritics / accents, …) then it is recommended to use UTF-8 encoding when saving your data. It is strongly recommended not to use special characters in attribute names – as well as spaces. Also, the attribute name shouldn’t start with number.

Coordinates

There are several thousands of coordinate systems that can be used to record the coordinates of points. In general, any coordinate system can be used for your XY data – geographic coordinate system (lat/lon), projected coordinate system (x, y) – in meters or feet, or even some other methods can be used. First, it is important to format your coordinates correctly. Each point location must be defined by two coordinates, each in a separate data column. These columns must be labeled correctly. Different software requires different ways to name these columns – for example MyGeodata Converter accepts the following names for the x / easting / longitude coordinate:

x, xcoord, x-coord, x-coordinate, x_coordinate, coordinate-x, coordinate_x, xcoordinate, coordx, coordinatex, longitude, long, lon, eastings, easting

And these for y / northing / latitude:

y, ycoord, y-coord, y-coordinate, y_coordinate, coordinate-y, coordinate_y, ycoordinate, coordy, coordinatey, latitude, lat, northings, northing

Coordinates using the geographic coordinate system (latitude and longitude) must be formatted as decimal numbers (eg. -72.9766666667, 42.4463888889) – not in degrees, minutes and seconds (42d 26′ 47″ N, 72d 58′ 36″ W). You can use CS2CS tool to convert geographic coordinates to the correct decimal format – select WGS 84 coordinate system for both input and output data, enter your input coordinates in the input box and use the Transform button. You will get correctly formatted coordinates in the output box:

Conversion to GIS formats

When XY data is prepared properly, you can use MyGeodata Converter to convert your data to various GIS / CAD formats including ESRI Shapefile, GeoJSON, KML / KMZ, GPX, GML, DXF, DGN and many others. Just upload your prepared .xls(x), .ods or .csv file to the Converter and follow the instructions. The most important thing is to correctly assign the coordinate system of your input data. If it is assigned incorrectly, the resulting dataset may be projected to different location on the map. If the coordinate system is correctly assigned, you should see a rectangle defining data spatial extent at the correct location on the conversion page overview map. Then just select the required output file format or enter the output coordinate system and click the Convert button. That’s all.

Data visualization

To create a simple visualization of your GIS data, you can convert it into KML format, which is compatible with tools like Google Earth. This allows for a more detailed and interactive representation of your spatial data, including custom styling options and attribute data. After converting, simply open the KML file in a supported viewer to explore your data in 3D or share it with others for enhanced visual presentations.

For a complete introduction on how to get started, check out our Getting Started with MyGeodata Converter guide.

Updated on October 3, 2024

Was this article helpful?

Related Articles

Close Bitnami banner
Bitnami