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?

Comments

Some free resources

[techtech]

GIS and vector data of the world
http://downloads.cloudmade.com/
http://www.naturalearthdata.com/

Distance calculation is trivial...

[JoeMerchant]

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

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).

Re:Distance calculation is trivial...

[Vellmont]

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.

PostgreSQL with PostGIS

[binarstu]

I would recommend checking out PostGIS, which works with PostgreSQL. PostGIS adds functions, data types, and projection definitions to PostgreSQL that turn PostgreSQL into a powerful spatially-enabled database server. I'ved used this combination for a number of projects, and they work great. Both are fully open sourced.

Re:PostgreSQL with PostGIS

[binarstu]

I should have mentioned also that I don't know specifically how PostGIS stacks up in comparison to Microsoft's offerings. I can tell you, though, that for the sort of thing you describe (e.g., find out how many objects lie within a given radius of some other object), PostGIS works great.

Re:PostgreSQL with PostGIS

[Korin43]

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

Why not use a Mixed-Solution.

[Anonymous Coward]

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

[jaymz2k4]

Have a look at this post from Sebastian Delmont on google plus. I found an excellent eye-opener to whats out there related to GIS tech that you can "roll on your own". If you are doing simply radial distance calculations than as mentioned the Haversine forumula is your friend. I added a radial search to a dealer locator for an online store in under a day with some python and a bit of time to geocode and cache all the address data via google.

Re:Checkout PostGIS

[jtnix]

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

[SSpade]

http://postgis.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/ is one place to get some pointers from.

PostGIS and OSGeo.org

[daboochmeister]

Run, do not walk, to research the Open Source Geospatial Foundation's offerings. And be aware, neo-geos devote their efforts primarily to PostGIS, which builds on the very capable PostreSQL database, adding in geospatial capabilities that by many accounts rival the best that that Oracle Spatial and MS SQL provide.

As always, there are significant tradeoffs to evaluate in your situation. Be ready to study the many evaluations and comparisons of the various solutions.

PostgreSQL + PostGIS

[sgtstein]

At my place of employment we use PostgreSQL and PostGIS extensively for the exact or similar problems as you describe. We recently contributed back to a portion of the PostGIS project by extending the TSP solver for a different ending than the beginning. I'm not the one who is generally writing stuff like this, but I maintain the servers and I know how much performance can be gained. Plus, the PgSQL and PostGIS guys are very close with lots of code and advancements being contributed directly into PgSQL from the PostGIS team. We have also looked at the MS solutions and found them to be ridiculously expensive to host and scale services targeted at business with real-life budgets and not huge corporations. We have tools used in nearly all of the counties in Wisconsin processing many requests per day and second(not allowed to give numbers) with only a few servers. Personally, stay open source and stick with PostgreSQL. They have a track record for extremely stable systems that can be upgraded as advancements are made with very little downtime. You can tune the internal performance metrics to tweak everything you need with online research or many books and even consultants such as EnterpriseDB. Good Luck with your developers, go with PostgreSQL and you won't look back.

Re:Checkout PostGIS

[hakioawa]

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

[Anonymous Coward]

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

[Anonymous Coward]

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.

Yet another Canadian immigration scam ...

[tomhudson]

Canada is finally cracking down on fake immigrants. One of the biggest problems is the abuse of the immigrant investor program which this business apparently is trying to exploit, if you actually look at their web site:

About Rodolfo Martinez

I am the Executive Director of Ontario Immigrant Network, and currently we are working to connect newcomers to rural business succession opportunities.

In other words, "buy your way into the country by buying some dead persons' business."

Nice scam - too bad Mr. Martinez isn't licensed. The Canadian government has required licensing of anyone doing immigration consulting since 2004 because of the number of scams and abuses.

As of April 2004, the only immigration and citizenship consultants who will be recognized by the federal government department of Citizenship and Immigration Canada are those who are members in good standing of CSIC or lawyers who are members of a Canadian provincial or territorial law society.

Re:Yet another Canadian immigration scam ...

[rnmartinez]

Hi Tom, just a bit about our group. We don't run any immigraton scams, and certainly aren't looking to have people buy dead people's businesses. We don't even deal directly with immigrants (i.e. we are not immigration consultants). Our focus is to work with rural communities that have ageing demographics and help them attract immigrants to take over businesses where an ageing owner is looking to retire but may have no clear successor. Our ideal immigrant is one that has already been in the country for a period of about 2 years. Essentially, we are trying to stop smaller communities from suffering mass business closure, and this technology piece will play a large part. I hope that this helps to clarify.

Re:Yet another Canadian immigration scam ...

[tomhudson]

First, a quick peek at your web sites over the last 6 years:

Finally, an online networking service that connects you with influential people who can reshape your career, business, and personal life

Immigrant networking
Talk to your peers
Find inside jobs
Make new friends
Solve your problems
Best schools to go for
Finding a good doctor
What car to buy
How to buy a house
And much moreâ¦

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 ...

[rnmartinez]

Hi Tom, I appreciate your concerns, and I hope I can address them here. It sounds like you may be in the US (the poster below is right, "ageing" is correct spelling in Canada) so I will point out a few differences as well. Our group came into existence in 2009 and that is when we took our domain. I don't know what it was used for previous to that, but obviously it was some type of spam. In terms of being one letter off of another group, I admit, we probably didn't research it enough but we were a non-profit starting up on a volunteer basis. It fits our name, and we have also added oinweb.ca for URL shortening and to help alleviate confusion. Yes, there is federal regulation surrounding these activities, however we do not work directly or offer any services to any immigrants or individuals. We are not involved in the settlement process, and we are not involved in any business transactions. The bulk of what we do is research, and from there come recommendations that communities are welcome to take or not. These licensing requirements do not apply to us, and I doubt that we would even be allowed to become licensed because of the nature of our work. We simply do not have any contact with any legal or business transactions. Second, business succession may have a different meaning in the US. In Canada, the process is meant to take place while the entrepreneur is alive. Were we dealing with estates, then yes, you would be correct. This example may help to clarify: Imagine that you were a successful business owner for 20+ years, but because of skewed demographics the bulk of your towns population is too old or too young to buy it. If you do have kids, they have likely left for a larger city like Toronto. This means that a profitable business, that could be a great opportunity for an immigrant could disappear, and potentially even trigger a domino effect (i.e. more businesses closing). We have seen this in some communities, particularly those hit by the decline in the auto industry. This doesn't mean that the towns are dying or dead, but it certainly can make things challenging. The GIS data portion doesn't relate to this, but certainly helps us in research. Canadian census data is not as rich, un-aggregated or recent as US census data appears to be (although my experience with US data is highly limited; I know that in Canada we used to have one long form census every 5 years that was recently abolished by our government). Also, until recently, this data was highly expensive. To obtain 2006 data for Ontario used to cost approximately $6000, which is quite a bit of money for a non-profit. While GIS does not solve or replace this, it is a great analytical tool. Third, I agree completely, that businesses will only sell for that they are worth. The issue is not value, but rather a highly skewed demographic. And again, our intended audience are those already in Canada, the majority would already have a legal status or be well on their way to obtaining it. Without at least permanent residency, I imagine that just buying the business itself would be incredibly difficult, as I can't think of a bank that would finance the deal. In terms of brain drain, we are not looking to stop, but rather bring new people in. I can't stop youth exodus, but I can try to promote rural regions as opportunities. There aren't really false bidders, but rather no bidders. Fourth, again, we do not offer any services directly, are not funded to do so, and do not receive any type of cut from any transactions. We are not realtors, lawyers or anything similar, simply researchers offering our ideas to interested communities. Fifth, I am not sure if you mean my name personally or my groups. This is a product of our website. If you can clarify, I can certainly look into it. Sixth, as researchers, we can choose what tools we want to use, period. You cannot decide what we do and do not need. And many communities already use GIS, but not in this fashion. Currently, we are not charging any of them for our work, so I don't see the harm in visualizing

Re:Checkout PostGIS

[Dreben]

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?