Go on I’m game, pray elucidate as to why?
Heh heh, made me grin. I must say though in years gone by I’ve had a couple of “squeeky bum” moments in whiteouts in the Cairgorms, working very hard to avoid stepping off a cornice into a several hundred metre drop. I still primary navigate with the map and compass, but a quick back check against the GPS is belt and braces. Take my hat off to the seafarers of yesteryear (well - not that long ago really!).
Doesn’t your database engine support the ‘decimal’ data type
The database engines - and we are talking here of both MS SQL Server and MySQL - certainly support the decimal type; and, in the case of MS SQL Server, also the T-SQL numeric type, which is basically the same. Both types are actually stored internally as strings, so it might seem to be a moot point as to whether one should use them, or even bother to use them. However, a well-designed database should always use the data type most appropriate to the range, or type, of data expected to be placed in any particular field in a table. It makes things a little (a lot?) easier to manage.
There might, of course, be very specific reasons why one would choose to place a number in a CHAR or VARCHAR field - a HEX value, for instance, rather than a decimal value. Anyway, the SOTA database works the way it is, and one might be tempted to say “why change it?”. The short answer is that it would be more appropriate that way; and, it’s actually a trivial matter to change an existing field data-type from CHAR/VARCHAR to DECIMAL(n, m) - one just has to make sure the (n, m) values are chosen correctly, to ensure that the expected data values are not truncated. They’d still be strings, but they would be “better” strings.
In any case, arguing that a database should be left with possibly inappropriate data types, in order to please the programmers, is simply to put the cart before the horse…
That’s enough from this cranky old ham…
Because it’s a number, so you should use a numeric data type. You’ve already decided float is no good (for the right reason, although I think it’d be adequate for this purpose). Integer is no good, unless you specify units of 1/10000 degree. Decimal was specifically developed for this.
Questioning the programmers over what constitutes an appropriate data type is not what this thread was ever about. The programmers are doing a fantastic job, and have asked nothing in return. Who can possibly fault that? If they want to store the internal database coordinates as Roman Numerals, that is exclusively their prerogative.
I came here with a few misconceptions. Those were cleared up as the discussion expanded. A local ARM update was probably a direct consequence of this discussion. My original observation (coordinate truncation in at least one application) is still there. It turns out it doesn’t really matter. If I want to plant a flag precisely at the “Database Summit”, I now know where to look for the data. If I want to activate a Summit, I also now realize that when I get close I will recognize it.
As an amateur programmer I do find it interesting to read the opinions and experiences of professional programmers. But our job here should be to point out any bugs we might perceive, for their evaluation. It most certainly is not to micro manage their gift to us. A gift that as I see it is working perfectly. And thank you for that.
Glenn - AB3TQ
Glenn, as you say, the discussion has expanded and, as these things develop, other themes make their entrance. That’s the nature of these things. The discussion has changed, and so the thread is no longer just about accuracy of coordinates. Andy has told us that these numbers are stored as strings, and somebody raised the question of whether storing these coordinates in a different form might be more appropriate. At that stage, I entered the discussion and gave my $0.02 worth.
You started the topic, Glenn, but other people added their view on the subject under discussion: the topic evolved to become something else: again, it’s par for the course.
Personally, I don’t think it’s entirely appropriate that you tell users of this forum what their “job” here should be. As I understand it, anybody here can give an opinion on things, as long as it doesn’t get out of hand. In pointing out a few salient facts about this or that data type, I’m not criticizing the programmers here - who, as you say, are doing a great job - but simply sharing a little knowledge and experience, such as it is, and that can’t be a bad thing.
And when it’s in the format 56.1223N or 123.678W?
That format is supported, we just don’t use it at present.
For a long, long time, all we did with a lat & long was fetch it and show it. They could have been saved as JPG pictures and displayed!
I could understand using decimal or numeric if the maths was done on the db server using T-SQL. But it’s done in the app running on the web server. The normal sequence being start with a SQL statement for the query, customise that from the webpage controls (dropdowns. user etc.), open a connection, run query, close connection. The data returned ends up in C#/ASP.NET data containers such DataSet, DataTable etc. and any post processing then runs in C# code on those container classes and not on the database server. After that the data container is bound to the ASPX (futzzed up HTML) and the data appears on someone’s browser.
Irrespective of whether the data is a decimal or string, it gets cast to some kind of float for the distance calculations. It’s possible to convert the string representation of a lat or long into a Maidenhead locator using merely string slicing and lookup tables of strings but again it’s converted to a floating point type. The reason is the code for both Maidenhead and distance comes from some C written in 1990 for VHF contest scoring, it was simply polished a little to compile as C# not C.It’s got 10 years of real world use so I’m happy that old code gets the right results. (OT: I have on the disk in this PC a folder called “Old”. It contains the work area/development area of my last PC as an archive. Old contains a folder “Old” which is the previous PC’s work area. Old contains… you’ve guessed it. So it was merely a case of descending the tree to find the ham radio development area from a 1987 vintage Amstrad PC1512.) The only improvement being addition of some exception handlers. In the contest scoring code if the data was bad the code blew up. As I was sat in front of the computer I would see that and fix the data. In the SOTA case there are thousands of users and it’s possible that the input could be bad. Any exceptions are caught and some “safe beige” numbers are returned. Everything should keep working rather than filling the users screen with red stacktraces.
A fixed size decimal would truncate any lat or long that was too precise when updated. But that truncation is handled in the data preparation for an association’s summit data and in the association uploading tool. The observant types will notice some summits have more that 4 decimal places of data. These have been hand edited from the MSSQL SSMS console.
So using such a type would achieve little to nothing over what we have.
Correct, but beside the point. Databases and their data are not, and should not be, concerned with application logic. Shoving numbers into a string field just because it seems to help the application logic is to ignore the whole point of having numerical data types in the first place - the point being that they are more easily managed using such types, not only for existing administrators of the database, but also their successors. In a professional context, numbers being thrown into character fields would be frowned upon to say the very least, but hey - we’re all amateurs here, so anything goes.
Am I the only one finding this discussion rather esoteric? I’m the pragmatic type, if it works, its good!
If I say “This year we had less apples on the tree than last year” you would know what was going on. The correct expression is “This year we had fewer apples on the tree than last year”. That is because you use fewer when you can actually count the number of apples and less for an uncountable amount. Most people know what you mean and some know if you have used the right word!
We have a similar situation here with what is the correct type to use to describe the item latitude. Either type works but one is more correct for the current data. But it doesn’t matter because whichever type is used the data is converted to another representation before it is used or displayed.
Of course you can take these things all the way if you want. For many years, the high speed checkout lane at Tesco supermarkets was identified by the sign “10 items or less.” This is obviously wrong as less implies uncountable yet here we are knowing the max count is 10. The people complaining wanted the sign changing to “10 items or fewer” which is so uncommon people would have noticed. Finally Tesco agreed and changed the sign to “Up to 10 items”. The complainers won by getting the sign changed but lost by not getting their preferred wording.
I think I may change the system to store the data in Roman numerals (IVXLCDM). They should be stored as strings and nobody can complain the wrong type is being used. The web app will get the data as Roman numerals and convert them to whatever it needs. Nobody can complain the data is being saved in a way to suit the application logic. Everyone should be satisfied and delighted at that plan
I could tell you about Bosch Exxcel Logic Error 18 which I came home to find being displayed along with a concerned Mrs. FMF. After removing the water and filter I’m now 13p richer (1x10p, 1x2p, 1x1p), the owner of 3 plectrums, 2 ty-wraps and enough hair to make Telly Savalas and Yul Brynner a collection of wigs. Error 18s are no more also!
OK, Andy - I’m throwing in the towel. I can see I’m wasting my time in trying to pass on some useful tips to a friend, which might perhaps have helped you in managing the database in a more efficient manner. I’ve certainly learned my lesson.
I did a swap this afternoon ALTER TABLE blah ALTER COLUMN bluh decimal(7,4) and it took a few seconds. The C# code itself throws some exceptions here and there but nothing massive. The real issue is there are no regressions for the existing code. I have no way of verifying everything still works the same as before. I do have tests for the additions I’ve made such as a set of CVS files for checking uploading and the microwave scoring. So I can check they work when something changes.
But the old code I inherited has nothing to verify it. Other than running it and selecting options by hand. So I don’t want to push changes if they are not fully tested. As nothing ever changes down in the old code most of the time and I don’t bother looking in some of it, who really knows what lurks waiting to bite. Hence the reluctance to change something that works to something more correct that may not work.
A well known phrase or saying seems appropriate here; if it ain’t broke don’t fix it.
Inelegant but distilled wisdom.
Getting back to your original questions, I’d like to add that it’s not rare for the stated and actual position of a summit to be one or two numbers different in the fourth decimal place (position in degrees). In addition to truncation and rounding there is resolution of reading the position off the map, which is maybe 0.00004 degrees in my case. However some maps place the same summit in different places. I have used four different on-line maps and there are differences of 0.0001 or more quite frequently.
But that’s all hairsplitting stuff. Many Australian summits do not have surveyed spot heights marked on the maps and have fairly flat summits, leaving the plotter with typically a pear shaped contour line around the summit. I have taken the approach that, in the absence of other profile info, the peak is at the point in the centre of the fattest part of the contour line area. So if there is a steep rocky outcrop in the skinny area I can be quite a few metres out. However the activation zone will still be the same even if the summit is 50 m from where I said it was. So the error while real is inconsequential. I consider any position with an error less than 30 m perfection for SOTA.
There is no minimum walk distance required as you now know and the web page that says so is in error. I am one of the “purists” who if parked in the activation zone goes for a walk out of the zone and back to my activation point. I’ve had long and involved discussions about activation zone errors and minimum walking distances and have come to the conclusion that the tried and tested General Rules cover the situation pretty well. As every summit is different making very concise rules may create more problems than they solve. That’s not to say there shouldn’t be a FAQ page somewhere answering all these queries which are recurrent.
To get back on topic, I’d go further and specify 6 decimal digits for the coordinate fields, thus removing any possibility of rounding error.
I agree this discussion has wandered a bit, but different people are interested in different aspects of SOTA, so it’s nice to have the opportunity to talk about some of the more esoteric parts.
Most databases now have specific data types for geographic coordinates.
HOWEVER, this is a recent development and the data types in use at the moment are therefore technical debt. I can understand this not changing as db dev here is voluntary and we’d all be up in arms should such a change break anything. Also bear in mind that such things often have undesirable side-effects which are often worse than those you are trying to fix!
From my point of view I check the coords again and again against a real map with real contours before leaving home. I use my brain to decide where I’m going and precisely where MY correct place is to activate. Are we saying this isn’t happening???
I now have visions of hundreds of SOTA amateurs around the world simply whacking coordinates into their sat navs and blindly following them
Yes, some interesting points raised but I’m not convinced 6 decimal places would be an advance. You will always have rounding errors and mapping errors. The 6th decimal digit of a degree equates to 111 mm, on average. Most maps (or GPS devices) will not give that level of accuracy. Only the main geodetic references are know to mm accuracy and then it’s ephemeral due to continental drift, etc. The continents move at about the same rate as fingernails grow, a few cm per year so after about 5 years the 6th decimal place is wrong. Four decimal places is a good practical degree of resolution.
As has been said by others, the summit is the highest bit. Often easily found by eye and marked with a cairn. No GPS required to find it, although the GPS or map and compass will get you close enough.
Up to a point. If something is done wrong, but works nonetheless, there’s a greater risk that it won’t work tomorrow than if it were done right. Every experienced programmer has come across things that appear to work despite having no business doing so. Sometimes two horrible bugs cancel each other out; sometimes it’s more subtle.
This is why the purists sometimes make a fuss about what they perceive to be the correct way to do something, even if it apparently makes no difference.
There must be analogies in the physical world. I’m no mountaineering expert, but I suspect there are ways of tying a rope that may appear to work, but can go disastrously wrong if something apparently unrelated changes.
This is a general remark about things in general, not a comment on this particular case.