Resolving problems with Colombian coordinate reference systems

June 6, 2016 OpenGis advanced colombia epsg igac sql crs

Last Friday they started working in an entity of Colombia with models of the sensor ADS40 in Digi3D.NET. The sensor ADS40, works in the coordinate reference system EPSG:4979 as explained in this page of the help of Digi3D.NET, however the cartography they want to register in the coordinate reference system MAGNA-SIRGAS / Colombia West zone. Initially, we only have to indicate when creating the drawing file that the horizontal coordinate reference system is MAGNA-SIRGAS / Colombia West zone, but when loading existing cartography we were told that the coordinates X and Y were exchanged. With modern versions of Digi3D.NET the solution is very simple, we just have to indicate to Digi3D.NET that we want the axles as East/North, and that it ignores what is indicated in the database EPSG, as shown in the following screenshot: Axes in projected CRSs ...but unfortunately the client has installed a version that does not allow to force the orientation of the axes, so the coordinate reference systems are always created in a standard way, and the standard as I will show you a little later indicates that first goes the coordinate Y, and then the coordinate .X. The first thing that came to our mind was to check the axes of the system MAGNA-SIRGAS / Colombia West zone in Digi3D.NET. To do this, we opened the pane Drawing file and clicked with the right mouse button on the drawing file and selected the option Show horizontal reference coordinate system..., which made appear the dialog box Parameters of the coordinate reference system horizontal that I show you below: Magna-Sirgas CRS parameters These parameters come from the database EPSG that includes Digi3D.NET in its installation, and you can clearly see that in Coordinate system type it indicates that the axes are North, East (comes in English), and not East, North. Actually, as you can see in the video at the end of this blog post, that text "Cartesian 2D CS. Axes: ...." does not dynamically create Digi3D.NET, but is extracted as a text that appears in the database. To see the order of the axes, we can refer to the string WKT that creates Digi3D.NET for that coordinate system:

PROJCS["MAGNA-SIRGAS / Colombia West zone",
GEOGCS["MAGNA-SIRGAS",
DATUM["Marco Geocentrico Nacional de Referencia",
SPHEROID["GRS 1980",6378137,298.257222101,
AUTHORITY["EPSG","7019"]],
AUTHORITY["EPSG","6686"]],
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["grados",0.01745329251994328,AUTHORITY["EPSG","9122"]],
AXIS["Lat",North],AXIS["Long",East],AUTHORITY["EPSG","4686"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",4.596200416666666],
PARAMETER["central_meridian",-77.07750791666663],
PARAMETER["scale_factor",1],
PARAMETER["false_easting",1000000],
PARAMETER["false_northing",1000000],
PARAMETER["semi_major",6378137],
PARAMETER["semi_minor",6356752.314140356],
UNIT["metros",1,AUTHORITY["EPSG","9001"]],
AXIS["N",North],
AXIS["E",East],
AUTHORITY["EPSG","3115"]]

The user there assures us that in the system MAGNA-SIRGAS / Colombia West zone the first coordinate is X, and the second is Y, so something must be doing wrong Digi3D.NET, or not. Let's analyze it... If you create an account at http://epsg.org/ and download the dataset, you will see that they distribute the database as a Microsoft Access database. Digi3D.NET cannot use a database in Microsoft Access format because 64-bit programs are forbidden to access this type of database, so years ago we converted that database into a Microsoft SQL Server Compact database, which has the feature that it can be accessed from 64-bit programs (and 32-bit obviously). These databases have the extension .sdf and there are several versions. The one that is installed with Digi3D.NET is the latest version, the 4.0. The installer of Digi3D.NET stores this database in the folder %ProgramData%[versión del programa]\OpenGis and has as name: epsg_es_ES.sdf if our installation is in Spanish or epsg_en_US.sdf if our installation is in English. We can open .sdf files version 4.0 with a free Microsoft tool called Microsoft WebMatrix 3.0. Install the program and follow the steps below:

  1. Run the Microsoft WebMatrix program. It will show you a home page with three buttons: My sites, New and Open.

  2. Press the Open button. A context menu with several options will appear.

  3. Select the option Folder from the context menu, a dialog box will appear to select the folder to open.

  4. Select the folder in which the EPSG database is located and press OK. You will notice that the files available in that folder, including the .sdf file, appear in the left sidebar.

  5. Double click on the .sdf file and you will see that the menu changes and shows you database related icons.

  6. Click the New Query button and a window will be created that will allow you to type SQL queries.

  7. Copy the following SQL query and press the Execute button:

    SELECT * FROM [Coordinate Reference System] WHERE [COORD_REF_SYS_CODE]=3115

    You will see below the parameters of the coordinate reference system MAGNA-SIRGAS / Colombia West Zone, including the code of the associated coordinate system, which is 4500.

  8. Now let's see the axes of the coordinate system 4500, To do this type the following query:

    SELECT * FROM [Coordinate Axis] WHERE COORD_SYS_CODE=4500

    You will see that there are two axes: the E axis and the N axis. At first it appears in the order in which the user is interested, that is, first the X coordinate (east) and then the Y coordinate (north), but in databases the order in which the records appear does not link anything, perhaps this same query with another database gesture system shows information in another order. The gentlemen of EPSG know this, so they added a field named ORDER in table COORD_SYS_CODE, and if you notice, the order is wrong: In the East axis appears a 2 and in the North axis appears a 1. In fact, Digi3D.NET does not use the SQL query that I indicated in this step to find out the name of the axes, but it uses the following:

    SELECT * FROM [Coordinate Axis] WHERE COORD_SYS_CODE=4500 ORDER BY [Order]

    If you run it, you will see that the first axis is North and the second is East. This is the reason why Digi3D.NET is creating the axes incorrectly. In the EPSG database it is wrong. Let's fix it.

  9. We have to change the ORDER value of the records 44 (the record for the North axis of the reference system 4500 representing the North axis) and change the value to 2, and put a value of 1 in the record 43 representing the East axis for the coordinate system 4500. For this we will execute the following SQL queries:

    UPDATE [Coordinate Axis] SET [ORDER]=1 WHERE COORD_AXIS_CODE=43
UPDATE [Coordinate Axis] SET [ORDER]=2 WHERE COORD_AXIS_CODE=44

and issue solved. You can see how I do it in the following video:

Back to the blog