Ask Slashdot: Open Source vs Proprietary GIS Solution? 316
New submitter rnmartinez writes "As the Project Manager for a non-profit looking to implement a tech project, I am running into a few dilemmas, and as a casual Slashdotter I could really use some help. I'll start with a brief explanation of the project. We research issues in Canadian Immigrants, and found that there was a lack of recent, unaggregated information. As we dug further, we found that some data was available, but there was no central repository. Therefore, we are building a web based service to collect this data, with the intent of having it display in Google Maps and then be downloadable as a CSV file that is readable in GIS software such as ESRI Arcsoft, so that data may be visualized."
The dilemma: "...It seems that MS SQL offers more functions with regards to geometry built in then MySQL, and my developers (good guys, but MS guys at heart) want me to switch to .net NUKE/MSSQL and ditch the open source stuff." Read on for further details.
"To date, we have relied on a LAMP box with Drupal as the frontend to help provide a more social experience. However, it seems that MS SQL offers more functions with regards to geometry built in then MySQL, and my developers (good guys, but MS guys at heart) want me to switch to .net NUKE/MSSQL and ditch the open source stuff. As a believer in open source and as a non-profit, I am having some moral issues with this (I try and run Linux and open source on everything I reasonably can)."
"So here is my dilemma: do I dump $20K into moving everything to an MS solution that in the short-medium term might make the geometry functions (i.e. show me all the hospitals within a 20km radius of this cluster of immigrants) or do I get him to spend the same amount writing something similar for MySQL? The only issue there, is that I am not too fond of Oracle having ownership of MySQL. Should I be directing $20K into replicating these functions into something like MariaDB? Might be a longshot but again, as a non-profit I'd like to see something go back to everyone, not just my group."
"Really, I am open to any flexible, creative open and reliable solutions. Sorry if my knowledge is limited or if I am grasping at straws, and if I am being terribly biased, but I trust Oracle with open source about as much as I trust MS."
MySQL might fare poorly against MSSQL's geometry support, but how does PostgreGIS stack up?
Checkout PostGIS (Score:5, Insightful)
It is far superior to MySQL geographic types. I'm partial to MSSQL, but PostGIS is a close second.
The Obvious answer (Score:4, Insightful)
And why didn't you provide an answer to the submitter, rather than troll?
Oh! Oh! I have an answer. Pick me!
It's because you're trolling just as hard!
You spent 5 of 6 lines attacking the person who wrote the post, 1 line talking about some other topic and zero arguing the merits of the post.
Yay! I solved the puzzle. I'll take the cupcake for $10 and the rest on a gift certificate Pat.
I personally agree with the OP, you use the best tool for the job. If you are designing your own tools just to save money, then you're under-valuing your time as a developer. If in this case MSSQL works, then use it, if in another case MySQL works, use it.
A bit sad. (Score:5, Insightful)
Am I the only one who finds it a bit sad that this is considered a hard problem to solve in house? It depends on how you have your database set up, but, you could store your latitude and longitude in different fields as degrees, minutes, and seconds (do you need resolution finer than about 30 meters?, then add more fields, need coarser resolution, do the same). Then, you start at your center point and draw an appropriately sized circle (more on that after) around that point using an algorithm that gives you all the second^2, minute^2, and degree^2 (and larger and or smaller blocks as precision requires) sized blocks that fall inside or touch the circle. Then you craft a select statement for all sites that match that set of blocks. Then, after you have that set, if you don't care too, too much about precision, you're done. If you do, you take the data set that's been returned and you look at all of the sites whose block intercepts the circumference and calculate their distance to the center and throw out the ones that are too far away. If you're using a flat earth (not flat as in pancake, flat as in a perfect ellipsoid) model, then you're done at this point. If you want to consider three dimensional distances on an earth with mountains and valleys, etc. (the "appropriately sized circle" mentioned above should already be taking care of the perfect ellipsoid model), so that a site 100 meters away horizontally but at the bottom of a 1.5 km cliff isn't considered to be 100 meters away, then you need to do more work and you need the altitude of each site in your database as well. Since you can pretty much rely on a fairly low maximum amount of overhang from cliffs and so forth, all you need to do is have an inner circle and an outer "appropriately sized circle" based on some precalculated constants regarding maximum changes in altitude over the whole earth calculated by coordinate block of some given size (computing or obtaining those constants is the trickiest part, put it's not hard, it just requires the appropriate GIS data and some number crunching) and stored in a table. When you compute your inner and outer circles, you just take the local terrain into account and draw the outer circle as far out as any sites in those blocks could possibly be from the center, and the inner circle as far in as they could possibly be. Then you work the blocks from the outside of the max circle to the inside of the min circle (not bothering to search blocks bounded on the outside by other blocks where you've already determined all the sites are inside your max area).
For calculating the "appropriately sized circle" in the first place, you make use of the Haversine Formula [movable-type.co.uk] or an appropriately modified (for altitudes) version thereof and some safe margin around the edge. Voila. Now, I know I've fudged past some of the math here, especially for the more complicated cases, but this is still pretty simple stuff, especially for the simpler cases. This is CS Major Sophmore or Junior year stuff.
Re:MS SQL is better (Score:3, Insightful)
Re:Obvious answer (Score:5, Insightful)
This may be too obvious to be helpful, but since the submitter mentioned that they use Drupal on top of their database, why not just use one of the Drupal Google Maps modules [drupal.org] as a starting point?
Google Maps Tools provides the very proximity function he's looking for (among other things) and there are many other easy integrations with Google Maps he can use as well.
That said, if he really wants to do the calculations at the DB layer, then switch the installation to Postgres GIS as mentioned elsewhere.
Re:Distance calculation is trivial... (Score:1, Insightful)
Re:Yet another Canadian immigration scam ... (Score:3, Insightful)
which says more about the right wing jingoistic xenophobic jerks which are currently running the Canadian government than anything else.
thought experiment: extend "immigrants" back to anyone who is not first nations. making it illegal to help people in need... geez, has it gone from terry fox to this in just 30 short years? completely and utterly disgusting behaviour.
I reckon this fellow has both a responsibility and a moral obligation to help his fellow immigrants, immoral laws should be broken at every opportunity. (in a moral and responsible way of course, I've no idea about his particular business practices)
since 2011? so it's at least 2 weeks old?
why all the hate?
Re:The Obvious answer (Score:5, Insightful)
I personally agree with the OP, you use the best tool for the job. If you are designing your own tools just to save money, then you're under-valuing your time as a developer. If in this case MSSQL works, then use it, if in another case MySQL works, use it.
The OP does not know what they are talking about, and their point about the database and bug testing is inane. It's a vast oversimplification of the problem.
Code development is going to happen one way or another here. Saying custom code costs hundreds of times a proprietary boxed solution is an incredible generalization, indicates that they have not even read or considered the problem, and have thus been simplistic and given rash counsel.
The question at hand is one of platform.
Do you develop on a proprietary database platform with non-trivial licensing costs to gain certain features and decide to use a coding platform that also has non-trivial costs in the form of tool sets, etc.?
Or...
Do you develop on an open source platform, that is finally maturing to a point it might be considered seriously for enterprise use (MySQL), and choose a coding platform that is also open source with quite possibly cheaper tools?
There are many things to consider here. Time To Market. That's a big one. If you need to bring the solution online and in production within 90 days, and it is not possible with the budget and personnel to do it yourself, than you really should consider a "boxed" solution. Some of those are even built on open source platforms as well.
Generally, doing it yourself will not cost hundreds of times more. That really varies. If you have the time and manpower to build a project yourself, and possibly open source it, you could easily do it. The cost of maintaining it though means that you will need to keep some developers on staff. With this particular situation that does not sound like an issue. They are not reinventing the wheel here. Ongoing support is going to be required no matter what.
In this particular instance it seems it is the choice between MS and open source. This guy just has to evaluate what his true costs really are. How much over the expected life time of the service will MS licensing cost? How much will MS experienced coders (both MSSQL and .NET) cost to keep on staff versus open source coders? If he had to modify MySQL to gain some features of MSSQL how much will that cost him over the long haul?
Choosing MSSQL does not mean you are locked into .NET either.
You're right about your time as a developer and choosing the best tool for the job, but there are many other important considerations too. The OP was being vastly simplistic.
Personally, I would need an extremely good reason to choose anything that requires MSSQL. That is not a trivial cost. It, in fact, has "dependency" licensing costs that are non-trivial as well. If you really need enterprise features and your project and production environment absolutely must have it, then that's that. Pay the money, budget for it, and get cracking.
That is what I really think his concern is. Going MS means a commitment, and a serious financial commitment at that. Are those features really really worth the tens of thousands of dollars per year MS is going to cost?
Open source not only allows code changes, but the licensing costs are zilch. You could buy support contracts, but otherwise, hardware costs are pretty much a wash.
Re:The Obvious answer (Score:4, Insightful)
If you are designing your own tools just to save money, then you're under-valuing your time as a developer.
This statement has to be qualified by comparison to the cost of the tools.
If the (additional) tools required take less than one man-month to develop as compared to the purchased package, well, I'd like to be worth $20K/month, but I'm not. Also, the initial purchase price is often the camel's nose under the tent flap, any organization I have ever been in would spend at least $10K of management handwringing, accounting, comparison shopping, independent consultant referencing and other time to decide if and when to pull the trigger on a $20K purchase. (Not true, one place I worked had a $25K/quarter discretionary budget at the department level, keep it under that and you just had to convince your Director - does your shop have $25K/quarter quick access discretionary money left in the current budget?) Back to the stinking camel - now that you've paid $20K for it, what are the "hidden costs" of maintenance and upgrades for the package?
Most times, it's less effort on my part to code the algorithm in question than it is to pull the levers required to shake loose more than $1000 from the money tree - so, am I serving my organization better by using my time and skills as a programmer, or as a lobbyist convincing them to spend money?
Re:MS SQL is better (Score:4, Insightful)
If you ignore them, and you admit you're ignorant when it comes to them, why are you commenting on them, and providing advice regarding them? Maybe you'd be better off providing advice regarding platforms you're knowledgeable about?