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?
Some free resources (Score:5, Informative)
http://downloads.cloudmade.com/ [cloudmade.com]
http://www.naturalearthdata.com/ [naturalearthdata.com]
Distance calculation is trivial... (Score:5, Informative)
If you've got something generating Lat/Lon coordinates to slap into your database, the distance calculations are trivial:
http://en.wikipedia.org/wiki/Haversine_formula [wikipedia.org]
I didn't see anything in a quick skim of your requirements (tl/dr) that looks like it requires a $20K package to meet. There are a number of open source mapping solutions out there, Google Earth is hard to beat for functionality and ease of programming (see: KML).
PostgreSQL with PostGIS (Score:5, Informative)
Why not use a Mixed-Solution. (Score:5, Informative)
In my office we work with GIS Data, but a ESRI Desktop license is just too expensive.
We choose for desktop Manifold GIS and the beauty of that software is that you could use almos any DB Backend to store your geometries. Now we are using MS SQLServer as backend, but Manifold allow you to save everything on WKB or WKT on the database that means that you can use almost any database.
My suggestion is for the server use a combination of Mapserver+PostGIS.
For clients you could use Manifold GIS, it's not ArcMap but believe me, with a license of $900 you could do almost the same things that you could do with a ESRI Desktop license of $5000.
there's a lot of FOSS options (Score:5, Informative)
Re:PostgreSQL with PostGIS (Score:5, Informative)
Re:Checkout PostGIS (Score:5, Informative)
I was going to recommend PostgreSQL as your DBMS as it has plenty of spatial and geometric data types and corresponding functions, although I have never used PostGIS and can't vouch for it.
However, if your devs want to use MS tech, I don't think you are in a position to strong-arm them into something they are not comfortable. Not unless you are comfortable with sourcing an entirely new dev team who wants to do it your way.
PostGIS (Score:5, Informative)
http://postgis.refractions.net/ [refractions.net] - pretty good spatial functions based on top of PostgreSQL, and not tied to Oracle.
There are many, many open source GIS packages that you might find useful rather than implementing everything yourself. http://maptools.org/ [maptools.org] is one place to get some pointers from.
PostGIS and OSGeo.org (Score:5, Informative)
As always, there are significant tradeoffs to evaluate in your situation. Be ready to study the many evaluations and comparisons of the various solutions [lmgtfy.com].
PostgreSQL + PostGIS (Score:5, Informative)
Re:Checkout PostGIS (Score:3, Informative)
Another option is SQL Azure. You get most of the features of on prem SQL Sever, but billed monthly. If you are storing a reatively small amount of data (~10GB or so) it is pretty cheap and get 99.9% up time with littl to no effort.
Only one true FOSS option: PostgreSQL + PostGIS (Score:5, Informative)
Disclaimer: I work for Esri.
In the FOSS GIS world the go to DBMS is PostgreSQL + the PostGIS extensions. Don't even consider MySQL in this regard. PostGIS owns this space and rightfully so.
SQL Server is a great database as well and their spatial types and functions are excellent. Depending on how much data you'll be storing and processing - and if you have Windows Server licenses already - you may be able to use SQL Server Express which is free as in beer for any use.
For someone in your position though there's really only one choice: go with PostgreSQL + PostGIS. It would be silly for someone in your position to pay for this functionality and the PostGIS community would welcome you with open arms.
Re:Checkout PostGIS (Score:3, Informative)
PostGIS+PostgreSQL+QuantumGIS
If you need professional quality maps you'll need to dump your layers to Inkscape via .svg and edit by hand.
Works for me.
Re:PostgreSQL with PostGIS (Score:5, Informative)
This.
I've used SQL Server and Postgres/PostGIS for spatial queries, and PostGIS is much better. SQL Server's spatial indexes are not as good, and require a lot of work to even be acceptable. PostGIS indexes don't require any work and are faster.
https://www.google.com/search?q=sql+server+spatial+slow [google.com]
Re:Distance calculation is trivial... (Score:5, Informative)
This was my first thought as well. Several years ago I did essentially the same thing as the OP is talking about. (Find all the points that meet a criteria within a certain radius of a given point). It took all of an hour or two to research and code.
If your developers are really complaining about lack of some simple calculations that are built into the Microsoft product, then it's time to either smack these guys hard, or fire them. For something this trivial it should take any good developer a few hours research to find a free solution rather than a paid one. Basically it sounds like these guys aren't willing to expand beyond what they already know, and are far too used to throwing money at problems rather than finding something inexpensive. Non-profits live and die by inexpensive solutions.
I'd agree with the general sentiment to ditch MySQL, and use a real database like PostgreSQL. MySQL might be OK for trivial websites to store some data here and there, but GIS requires a real database. PostgreSQL is free, works well, is feature rich, and will let you expand. So tell your devs to either adapt to low cost solutions, or leave. If you let them spend 20 grand every time they don't want to do a little work, you'll soon be bankrupt.
Yet another Canadian immigration scam ... (Score:5, Informative)
Canada is finally cracking down on fake immigrants [www.ctv.ca]. One of the biggest problems is the abuse of the immigrant investor program [cic.gc.ca] which this business apparently is trying to exploit, if you actually look at their web site:
In other words, "buy your way into the country by buying some dead persons' business."
Nice scam - too bad Mr. Martinez isn't licensed [csic-scci.ca]. The Canadian government has required licensing of anyone doing immigration consulting since 2004 because of the number of scams and abuses [about.com].
Re:Yet another Canadian immigration scam ... (Score:5, Informative)
Re:Checkout PostGIS (Score:5, Informative)
MS SQL does not adhere to the OGC's (Open Geospatial Consortium) Types & Features v. 1.1 specification. If you're serious about storing spatial data in an RDBMS it should adhere to that standard. Highly recommend staying away from SQL Server unless you're willing to invest in ESRI's SDE app server, and with PostgreSQL/PostGIS why even bother?
Re:Yet another Canadian immigration scam ... (Score:5, Informative)
First, a quick peek at your web sites over the last 6 years:
It's a one-letter-off typo squat of immigrantnetworks.ca - cheesy or sleazy, take your pick. Your domain has a history of trying to find some way to intermediate into the whole immigration thing.
Now, when you say. "Our focus is to work with rural communities that have ageing (sic) demographics and help them attract immigrants to take over businesses where an ageing (sic) owner is looking to retire but may have no clear successor", that is a problem.
First, such activities have been regulated by federal order since 2011. Neither you nor anyone else can offer to help immigrants with such things as where to locate, or business opportunities, either directly or indirectly, without being licensed.
Second, if there is "no clear successor", then the property or business cannot be sold until probate, and no amount of GIS data can "fix" that problem.
Third, if the problem is that nobody wants to buy a business at a certain price, they can always lower the price. At the right price, everything sells. If it doesn't sell at any price, it's not viable. Conning an immigrant into buying a dying business only works if their real goal is to show that they are establishing roots - it does nothing to stop the "brain drain" - people leaving because they lack opportunities. In fact, it exacerbates the problem because the next generation has to compete against "false bidders" - bidders who are really buying a "ticket to permanent Canadian residency" to buy the local business. In other words - you would be, at best, part of the problem, not the solution.
Fourth, trying to hook up immigrants to buy such businesses, which "coincidentally" help to show that they have established roots, is a regulated practice, and I already checked - you are not licensed.
Fifth, I also noted that you put your name as an image, instead of plain text, to avoid being picked up by search engines. Inquiring minds would wonder about that ... but after looking at the thin gruel you have to offer, I'm not all that surprised.
Sixth, nobody needs GIS data to do this - and trying to sell such a concept, either to communities or to immigrants, is akin to "Search Engine Optimization" scams - anyone can find such properties / opportunities with 10 minutes work on Google. What next - "Oh, pretty graphs?" Oh, wait, your terrible (as in first year high school) "white paper" already does that. Yes, I read it, and it really sucks.
Seventh (yes, I'm on a roll) your original question showed that neither you nor whoever you're working with have much of a clue about databases. You don't need ANY "geometry functions" to do what you are trying to do.
I could go on ... but I think I've made my point, which is that your business activities raise red flags, and the the whole precept on which you claim to operate (to "stop the brain drain") is flawed and will have the exact opposite effect. Cui bono? ... or more to the point - "what's in it for you?"
Re:Yet another Canadian immigration scam ... (Score:3, Informative)