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]
Checkout PostGIS (Score:5, Insightful)
It is far superior to MySQL geographic types. I'm partial to MSSQL, but PostGIS is a close second.
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.
Re: (Score:2)
Huh? I don't know about where you are or the OP's country, but here in the USA (home of Silicon Valley), people get pay cuts all the time when companies hit hard times. It doesn't happen all that often I guess, since usually they just lay people off, but it happens often enough.
The other responder also had a good idea: fire some of the engineers, as these wonderful tools should make it so you don't need so much manpower. Tell them they'll have a lottery to decide who gets the ax.
I think the boss should j
Re: (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.
Re: (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: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: (Score:3)
You mean this: http://www.codeplex.com/wikipage?ProjectName=MsSqlSpatial [codeplex.com] ?
Re: (Score:2)
According to OGC's list of compliant products, no Microsoft products comply with the Simple Features: Types and Functions v.1.1 specification... ...regardless what codeplex says. OGC has a fairly rigorous specifications test.
http://www.opengeospatial.org/resource/products/byspec [opengeospatial.org]
Re: (Score:3)
http://www.opengeospatial.org/resource/products [opengeospatial.org]
Re: (Score:3)
Actually, SQL 2005 was the first version of SQL Server that supported .NET CLR. It should also run on 2008, 2008R2, and 2012.
OGC recognizes, it. It is even listed on their website, and no I'm not one of the developers.
Re: (Score:2)
Sorry, here's the link: http://www.opengeospatial.org/resource/products#MsSqlSpatial [opengeospatial.org]
Re: (Score:2)
You do realize that the OGC is really run by refractions research, a super small company that's only 25 people, of which only 4-5 are likely developers? Doesn't sound all that much better TBH. In both cases you have very limited backing. IMHO, I'd probably use either MSSQL's geospacial types which likely cover what the vast majority of people need from them, and then use .NET CLR if necessary to add any additional functionality that you want. For this place anyhow.
If the developers were an Oracle shop,
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).
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.
Re:Distance calculation is trivial... (Score:5, Interesting)
...which raises the question: What is the most efficient way to store points on the sphere for lookup? Computationally? And in terms of storage?
1.) You can store lat/long, and use the Haversine formula, as you suggested. This requires trig functions, and has O(n) complexity; you need to iterate through all the points. You also have varying resolution over the surface, which makes bounding and early-outs a bit harder.
2.) A great many other coordinate charts also exist, and it's hard to say why you should choose one over the other without looking in detail at how the distance calculations are performed, etc.
3.) By using multiple charts -- e.g., a cube projection -- you can avoid issues with singularities, at the cost of branching. The complexity of distance calculations depends on the projection, but, without looking too carefully, my bet is that, in terms just of raw speed, cubemap vs. lat/long is probably a wash.
4.) Why use a coordinate chart at all, when you can use an embedding? If you store points in 3d, proximity calculations (since the points are on the sphere) just become a dot product. Much faster! It also opens up the possibility of, e.g. (if you will be doing many lookups but few insertions), storing indexes sorted along the three axes (or more!) to speed bounding-box (or more generally, sweep-'n-prune) calculations. Bins, bounding volume hierarchies, and the other standard tricks of computational geometry come into play. On the other hand, you're wasting a lot of codewords on points that don't actually lie on the sphere.
5.) Is there a more efficient use of codewords? Perhaps a (nearly-)-constant-resolution encoding scheme? If you start with the idea that a node in an octtree can be thought of as an octal number, you can see how you can encode points as real numbers in the interval [0, 1] -- e.g. "octal: .017135270661201") Of course, this still wastes codewords on points not on the sphere, so let's consider a refinement of this idea: At each level of the octree, discard any cube that does not intersect the sphere, and use arithmetic encoding, with the base varying between 8 and 2 depending on the number of cubes that intersect the sphere. This now seems like a (memory)-efficient way to encode points on the sphere -- but it is surely not computationally efficient. On the plus side, this same idea works for any manifold embedded in any Euclidean space, so at least it generalizes.
6.) Since #5 is a mapping from [0,1] to the sphere, one wonders if there are space-filling curves on the sphere. Of course there are -- e.g., the Hilbert curve in 2d, composed with any inverse coordinate chart. Not that this helps much!
I think my favorite of these is #5, but, practically, #1 or #4 are probably better choices.
So how do the real GiS systems do it?
Re: (Score:2)
Google Earth is hard to beat for functionality and ease of programming (see: KML).
Google Earth is not Open Source, but for basic GIS it's a hell of a lot cheaper then an ESRI setup.
Re: (Score:2)
Google Earth is hard to beat for functionality and ease of programming (see: KML).
Google Earth is not Open Source, but for basic GIS it's a hell of a lot cheaper then an ESRI setup.
Yep - and while there are a number of Open Source GIS solutions out there, it's still hard to beat Google Earth for functionality and ease of programming.
PostgreSQL with PostGIS (Score:5, Informative)
Re:PostgreSQL with PostGIS (Score:5, Informative)
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:PostgreSQL with PostGIS (Score:4, Interesting)
Well put.
Plus, Postgresql has things like "text" columns that work, and can be proper Unicode (not MS SQL-Server's crummy UCS-2, or Unicode haxx blobs where the normal text functions won't work anymore). It also doesn't rape your wallet like Oracle.
In short, Postgresql does Internationalization right. It is easy for MS Sql-Server to be considered fast, when it doesn't have to do many things properly (like proper UTF or arbitrary length text columns, or spatial indexing, or having source available, or costing nothing :) etc etc).
Use Postgresql, it will make hot girls like you!
Re: (Score:2)
You are showing your anti-MySQL bias...real MySQL programmers use char(0) columns all the time.
Re: (Score:2)
+1
I have used PostGIS and Postgresql extensively for spatial systems. It has rich functionality and can perform very well.
Re:PostgreSQL with PostGIS (Score:5, Interesting)
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.
Little cost today, huge cost tomorrow ? (Score:2)
Go for the open source one. Find a way to be free of vendors as much as possible. go find a proprietary solution that sits in front of a mysql db. so that in future, you may just ditch the proprietary solution and jump to another one, with the same db behind it. or, you can use that setup, while in the meantime developing your cust
there's a lot of FOSS options (Score:5, Informative)
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].
Re: (Score:2)
+1
Also, if you hope to attract a following and contributors, then MSSQL is the death knell.
As a non-profit, I'd hope your business model would be something like openstreetmap or some such; you aggregate the data, provide a portal, and allow others to build on it. Proprietary software is not the basis for sharing info.....
PostgreSQL + PostGIS (Score:5, Informative)
MySQL and Drupal are fine (Score:5, Interesting)
I can safely say that MySQL handles GIS data sufficiently for the type of scenario you've described on Slashdot. I've got a database I periodically play with that is all the cities / major towns of the world and can quickly query it with distance data. (as in: Give me everything within 100 miles of Lat,Long)
MySQL doesn't make all of it as easy as it should be, but with some careful design decisions it can work surprisingly well for it.
I previously had created a distance function that worked pretty good, but more recent versions of MySQL have better internal support and I was able to ditch some of my custom routines.
(Note: my routines performed efficiently, but gathered extraneous data that could later be filtered out to be more precise. The extra data was maybe 10% more than necessary, but meant the query was very fast.)
Perhaps you should be looking to upgrade your MySQL instance, or getting some procedures/functions written for MySQL to handle some of the calculations.
(It's pretty easy to calculate a rectangle for the query to run against, and then follow up with a more precise distance calculation to filter out a few stranglers that slip into the dataset. (better to include a few that are dropped in later stages than to exclude valid data up front)).
As for the moral issues: Stuff them. They aren't what you should be focusing on.
Re: (Score:2)
Re: (Score:2)
Your current best bet is: http://www.geonames.org/
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: (Score:2)
Since licensing cost is a major concerns with MSSQL, the question turns on which license you would need to pay for. For a small instance you might do okay with a free express download. You could save money by picking up a (used) SQL2005 or SQL2008 standard license. Maybe you need the features of SQL2008R2 which would be more money. Maybe down the road you will need something that can scale really big in which case you would need to budget $$$$$ for enterprise licenses instead of standard.
And of course
Work out what you need first. (Score:2)
GIS improvements already are in MariaDB (Score:5, Interesting)
MariaDB 5.3.3 [askmonty.org] (currently in RC status) has improved GIS functionality [askmonty.org] versus MySQL. While sponsoring new features could improve it even further what we are releasing right now might be enough for you. Please give it a try, and talk to us about how we could make it fit your needs better.
Re: (Score:2)
Those not familiar with Monty Program's business model can read about it here:
http://montyprogram.com/hacking-business-model/ [montyprogram.com]
If the missing features are not already in MariaDB, it certainly provides a nice opportunity to take that $20K and use it to benefit everyone.
(Though the OP doesn't identify the missing features, so it's not clear to me that they even exist. In his/her place, I'd be thinking about replacing the reluctant devs, not the target platform.)
Warm Fuzzy (Score:2)
Re: (Score:2)
If the programmers are that limited that they need that much budget for retraining, he's going to run into trouble eventually, so he might as well save the time and do it now.
Your developers (Score:2)
You say that your developers are MS guys at heart? Then go with the MS stack to develop this. Your developers are your biggest expense, and Microsoft licensing for nonprofits is completely reasonable. As much as you might want to do everything open source, sometimes it's more important to get the job done in a timely fashion.
Re: (Score:2)
You say that your developers are MS guys at heart? Then go with the MS stack to develop this. Your developers are your biggest expense, and Microsoft licensing for nonprofits is completely reasonable. As much as you might want to do everything open source, sometimes it's more important to get the job done in a timely fashion.
MS licensing is cheap.
No, seriously, especially as a non-profit they'll get SQL Enterprise licenses for pennies.
However that is not the OP's problem. If you go with the MS stack, you're practically choosing to go with ESRI and ESRI licensing is not cheap. You may be looking at $20 K for a floating ArcInfo licence. You're looking at $4K just for a single use ArcMap license. Lets not even look at ArcGIS server
charity (Score:2)
you might want to contact your local MS sales rep. I believe, if you're a charity, you can get MS SQL Standard edition for around $3K.
Re: (Score:2)
$3k? Plus the cost of the windows license to run it on...
For $3k+ i could buy significantly more powerful hardware, several additional servers or a much better hosting plan.
And this is supposed to be CHARITY pricing?
I also assume that "standard edition" isn't the highest end version, and is therefore artificially crippled relative to the even more expensive versions?
I'd rather take my more powerful servers. and use them to run a free full featured copy of postgres on top of a free full featured copy of linu
PostGIS (app server for PostgreSQL) (Score:2)
I've been doing geospatial development since 1992 when Genasys (defunct) released the worlds first web mapping server, Web Broker (ESRI's IMS wasn't released until 5 years later). At the time the only COTS option for managing spatial data within an RDBMS was Sybase's Spatial Query Server, which provided slightly more geometry options inherent within MySQL today. MySQL geometry features should be considered primitive at best, far from a full-featured implementation. On the other hand, PostGIS, is full fea
GIS Stackexchange (Score:3)
Firstly, you can post questions here: http://gis.stackexchange.com/ [stackexchange.com] and they will be answered. It seems to be a pretty good community, I have been posting on there for a little while.
Secondly, I don't use databases a lot, but I recommend that you do what you suggest. Make a Google Map and make a CSV for folks to download.
One additional datum: is this the last question? (Score:2)
I'd throw in a good work for PostgreSQL as well. But an additional question: is this the last question you'll be asked of this geographic data? Over the long run, if the questions keep coming, you'll want a stable base from which to work. "Stable" means "popular" to some extent. Can you keep finding developers to work on your problems with the base of data and software that you have to work with, or does it depend on a few people knowing all the not-so-popular products you're working with?
I'm a big ope
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.
You are not the only one. (Score:3)
How effing accurate does such a thing even need to be in a social networking context? Still, it wouldn't be hard to get this down to sub-meter accuracy. It could be hired out if need be on one of those programming task sites for under $1000 (far less that the $20000 initial outlay to put up a decent couple of machines to do it in an MS-SQL way).
I suspect there's a band of "we are scared of maths" programmers somewhere near the OP.
Re: (Score:2)
I was just trying to cover the what ifs for anyone who says I'm oversimplifying and that the geographical functions in mssql provide this or that feature that I haven't covered. This is overall a pretty easy problem to solve. Optimizing it would, of course, be more of a problem, but optimizing it probably isn't necessary without a really massive data set. The approach I outlined has the up side that the circumference of a circle grows linearly in relation to its radius and the Earth itself is close enough t
The hard part of this ... (Score:2)
... to me is the cost of obtaining all the data in a usable form. The logic itself would be easy.
It could be done very easily in a no-SQL solution. Doing it through SQL because some SQLover is making decisions would be only slightly harder. The SQL lookup would be delivering 4/3.14159 times as much data as actually needed for a circular result, and then that would be filter by whatever front-end or back-end code to cleanly clip off the corners, of the SQL implementation didn't have the math to do it (and
Re: (Score:2)
Partially true. Generally obtaining the data is one of the biggest efforts - at least if you have to start from scratch or pay for it. But you may find open or free sources as well. Openstreetmap has data exports that can be imported into PostgreSQL/PostGIS. Some government entities,like my county, provide GIS data for their areas as well (http://www.ccmap.us/).
The "easy" part isn't as trivial as you aledge. Provided your data includes the necessary information, geospatial queries can allow you to write a s
Re: (Score:2)
first you say SQL would search for the data in squares and then clip off the corners for circular results, and then you say you want to organise all the data for your method into grid squares....
I think you'd be surprised at what the SQL GIS functionality can do. It isn't doing a query "between x and y" and then running a Pythagoras calculation on each point.
My $0.02 on GIS tools (Score:3)
Here is an example [blogspot.com] of some of what I have done. It is a map of the MN deer areas showing the antlerless deer harvest per square mile for each area in relation to all the others during the archery season. This map is a 10 year snapshot where the darker area indicated more deer were taken per square mile.
Seriously... (Score:2)
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?
If this is how you make your technical decisions, no amount of advice will help.
geokit (Score:2)
I worked for a large location-based mobile / web startup. Pretty much every web request dealt with a lat / lng. We used geokit ( http://geokit.rubyforge.org/ [rubyforge.org] ) and it worked great.
20k to move to a completely different platform over one trivial problem is just wrong.
MySQL does have spatial feature support (Score:2)
MySQL does have spatial feature support. [mysql.com] The underlying data structure can do point-in-rectangle tests cheaply. More complex geometry is supported, but the query engine will generally construct a bounding rectangle for index lookup purposes, then sequentially test the hits against the more complex geometry.
I've tried this, and it does work. It's good enough for efficient "all gas stations within N miles of here" queries, for example.
Right to privacy, and use PostgreSQL (Score:2)
Could that be because it's ILLEGAL to identify individual immigrants as it's an invasion of their right to privacy?
FFS, sprout a brain! The government HAS the detailed data, but you are NOT allowed to access it directly.
As to GIS, check out PostgreSQL. I hear their GIS support is pretty powerful, but I've never coded for it. Certainly I'd recommend PostgreSQL over MySQL any day of the week.
Re: (Score:2)
To paraphrase George Carlin:
BIG REPEAT (Score:2)
Keep in mind that the following answer only applies if you're not doing a full-blown GIS application, but only calculations like "find all the X that are there within Y miles of location Z," and "How far is it from point X to point Y?"
And if that is the case, there are software libraries (I know of some in Ruby, I know there are others) that, in conjunction with Google Maps (or Yahoo Maps and even a couple of other services), mean YOU DO NOT NE
Postgres (Score:2)
Take a look at PostgreSQL with PostgreGIS as mentioned... Not only will this integrate nicely with Drupal and replace MySQL with minimal fuss, but it also avoids the issue of being associated with Oracle.
I believe there are also existing drupal modules for working with map data and specifically google maps.
Incidentally, if you're worried about MySQL because its owned by Oracle, then surely moving to MS would be even worse? At least MySQL can be forked under the terms of the GPL, MS products cannot be so you
In other words (Score:2)
You should be fired for putting your own personal wants over that of the needs of the project.
You said it yourself "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". Oh, and that quote also shows your anti-Microsoft bias, a bias which seems to extend to Oracle.
Re: (Score:2)
There's a saying about stupid questions and answers, but never mind ... Your answer is based on several assumptions:
You're right that custom code is usually more expensive than already available boxed solutions, under the assumption that the generic solution implements only the needed code and does not need to be modified. If the generic solution implements much wider range of functionality than you actually need, the custom code may be actually much cheaper. And if you need/want to change the internals, i
Re: (Score:2)
Also consider the existing code that they already have, most of which would need to be rewritten...
Re: (Score:3)
Sounds reasonable until you consider that both approaches are going to require custom work. Even though the whole point of languages like SQL is that you're supposed to be able to swap out the back end easily, in the real world, you have to do all kinds of work. So the choice isn't between some kinds of simple switchover or doing a lot of custom work, it's between doing a bunch of custom work or doing a different bunch of custom work. Oh, and the third option of finding a prepackaged solution that does what
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.
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: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: (Score:2)
Cui bono? ... or more to the point - "what's in it for you?"
I'm going to go out on a limb here and take a wild guess: money.
Re: (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
Re: (Score:3)
Re: (Score:3, Informative)
Re: (Score:2)
There real question is why this crap is accepted as a story and especially why is allowed that he put a link to his "non-profit organization". He just wants free advertisement and to generate traffic to his site.
Obvious answer ... Who pwns your data? (Score:2)
Who owns your data? Id there a lifecycle to consider? Will the data/information/results be used for collaboration, web-services ...?
Anyone can buy software and wait for patches. Anyone can own software and prioritize patches. Anyone can buy L/FOSS support for a stable product from a foundation/company.
These days the Q&A (IMO): Are you a responsible owner? What are the valuable assets for your business; virtual-material (information, data, content ...) or hardware/software... products?
Staying market
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.
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: (Score:2)
For someone claiming to be against the sort of trolling and puerile content that you accuse the OP os posting, you certainly don't practice what you preach, do you?
He did actually provide an answer - perhaps not one that you want to hear and thus doesn't count as the "right" answer I guess, so must be trolling, right? It's certainly one of the possible outcomes for the original question.
Re: (Score:3, Interesting)
I say pay whatever it takes to switch everything to MS-only technologies, since your developers prefer that. Then, to make the budget work out, cut all their paychecks by that same amount. (Actually, you should cut their paychecks by quite a bit more, since you have to account for the continual license fees needed, and the fact that some of them may quit.)
Or offer them the choice between this and doing something lower cost that works as well and doesn't require continual license fees.
Re: (Score:3, Insightful)
Re: (Score:2)
Others here have already pointed out which components to use (PostgreSQL and PostGIS) to use to do what the Asker wanted. Why chain yourself to yearly expensive license costs when there's much cheaper stuff available that's just as good? It's not like he's trying to do WinPhone7 development or .NET development or Outlook/Exchange or something else where MS stuff is obviously the way to go.
If the MS stuff is really that much more productive (I doubt it), then what the heck does he need all these MS-fan eng
Re: (Score:2)
I long for the day when windows guys will be relevant only for windows desktop development. But as long as Microsoft is still having a stranglehold on most comp sci degrees by offering free everything to the students I only see more and more boys coming and applying for a job as web devs believing that Microsoft products are a suitable solution. ...I'm sad now.
Re: (Score:3)
Re: (Score:2)
From what I'm reading here, this is really about putting existing tools into place, namely a database with GIS extensions. There's no cost difference involved here either way; every SQL database in existence uses approximately the same language, and can be accessed from any application program, since SQL is, after all, a standard (with proprietary extensions, but again, those don't matter since you can write your application to use them). For instance, if you want to write a .NET application that accesses
Re: (Score:2)
if you want to write a .NET application that accesses a PostgreSQL database, there's nothing stopping you
except there are some non-standard SQL extensions that Microsoft has lovingly developed. If you use the Entity Framework for example, you will find that complex queries start to automatically use these invalid clauses and your code will suddenly stop working on all but SQLServer DBs.
But yes, I agree - get Postgresql instead and use that, there's no need to use hugely expensive DBs nowadays unless you rea
Re: (Score:2)
It seems the developers are already successfully using the current (mysql/drupal) setup...
To move to mssql and .net would basically require ripping all this out and replacing it, so you have not only the license costs but also the costs to replicate any existing functionality before you can even consider implementing the new functionality thats required.
Also if you go MS then your application will be locked in, being locked in and beholden to a third party is not a good situation to be in. Just ask all the
Re: (Score:2)
It has? Sorry, it's closer to a decade since I lost interest in Microsoft server products and for what I know they haven't managed to come up with something comparable to a Linux/BSD server anyway, so I pretty much ignore them.
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?
Re: (Score:3)
The problem, as I see it, is that the original poster is comparing the wrong things; namely, he's looking at MySQL for some odd reason, when several other people have here already pointed out that the de-facto OSS solution to this is PostgreSQL and PostGIS. Sounds pretty much like a drop-in solution to me.
Re: (Score:2)
He works for a non-profit. As such ethics and morals and beliefs weigh a lot more than in a for-profit corporation. Non profits do things because they believe in them, not necessarily because they make the most money.
Re: (Score:2)
He's got a solution that works, but that is less than ideal. His developers, like developers everywhere, want to chuck the existing software and start from scratch in a different language, using a completely different platform. Even worse, switching to the new platform will require a substantial upfront investment in licensing that can not be recouped if the project fails.
Heck, the new platform isn't even removing Free Software fromt he equation as DotNetNuke is not commercial software. So the new plat
Re: (Score:3)
Both solutions are going to require custom programming. One of them chucks the existing (mostly working) solution, the other builds on the solution. That is a fairly substantial difference.
Heck, I could understand if the developers wanted to simply chuck MySQL and replace it with MS SQL Server (which apparently also works with Drupal). I personally would probably use the existing Drupal solution with PostgreSQL, but if the developers feel more comfortable with MS SQL Server I could see that being consi
Re: (Score:2)
Re: (Score:2)
As someone who works and has a view into both open source and proprietary code - I can without a doubt say you're absolutely wrong.
The correct solution is to look at the problem, identify possible solutions, evaluate them and choose the best product which covers your needs best.
There is *PLENTY* of bad code out there, both COTS and OSS - there is also some amazing and brilliantly good code out there as well.
Re: (Score:2)
Has the article submitter actually identified the software they need? It's trivial to implement something that will draw a circle on a grid, identify all the grid squares that might have points in that circle, submit an sql query for all sites inside those grid squares, then pick off all the points that don't fit in just the edge squares. The fact that they don't recognize that and think that they they need some vendors special solution to do it all in the database engine suggests that they either don't hav
Re: (Score:2)
(accidentally posted as AC above... thought I was logged in...)
I've run ESRI products on Linux and Windows platforms, and even though I'm a linux admin first, I would have to recommend Windows/MSSQL if you do choose to go with their products. Although they technically support Linux platforms, I found that their support was abysmal, and when we moved to ArcGIS on Windows, suddenly things worked much better, and their support actually had answers when we needed it.
Re: (Score:3)
He makes good points...
If you lack technical skills then buying pre packaged software is for you, but you certainly shouldn't go around tricking people with even less technical skills into thinking that you're somehow an expert.
If however you are technically competent, then it is almost always better to have software that you can modify to suit your needs, and which you can understand inside out.
Similarly if you're a competent and well taught developer you should have little problem adapting to different pl