Slashdot Mirror
Ask Slashdot: Open Source vs Proprietary GIS Solution?
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?
"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?
It is far superior to MySQL geographic types. I'm partial to MSSQL, but PostGIS is a close second.
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.
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 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.
What if they actually save money by being more productive?
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 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.
The party of stupid and the party of evil get together and do something both stupid and evil, then call it bipartisan.
If your managers are really complaining about spending a few grand for real GIS and database software, then it's time to either smack these guys hard, or fire them. For something this trivial it should take any good manager a few hours research to find an off the shelf solution rather than a homemade kludge. Basically it sounds like these guys aren't willing to spend a little capital, and are far too used to throwing developer hours at a problem others have solved instead of paying for something standard and letting their developers work on real problems. Projects live and die by organizations willingness to actually invest in them.
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?
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?
Karma: Poor (Mostly affected by lame karma-joke sigs)