Habitually Good

Recently I was helping a colleague out with some Data Analysis work. He had some Geographic Coordinate data in a MySQL database on which he wanted to run some calculations. I proposed we write the code in C# and he agreed.

He had stored his data in a table with the coordinate data in a column of type POINT. POINT basically allows you to store an X and a Y value in the same field. Now, while I agreed to help him out, he had to take off and I was left alone to figure out how to read this data into a DataReader in C#. The problem was that I was getting an Unknown Data Type Exception every time I tried to read it. When I peered through the references of MySQL, I found that POINT wasn’t even a documented DataType (or so I thought) in the MySQL reference. Googling was not giving me any answers and I did try a lot. Finally after my colleague got back he gave me the solution (which he assures me is not very intuitive and easy to find). You see the solution is to make a function call in the SQL query which then converts the POINT data to double and returns is as part of the query results.

So to get both the X and the Y coordinates, your query has to look like this:

SELECT X(coordinates), Y(coordinates) FROM t_geoCoordinate

Here, ‘coordinates’ is the name of the table column with data type POINT.

[tag]Coding[/tag], [tag]Tips and Tricks[/tag], [tag]MySQL[/tag], [tag]C#[/tag]

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.