Forgot your password?
typodupeerror
Open Source Canada Databases Microsoft

Ask Slashdot: Open Source vs Proprietary GIS Solution? 316

Posted by Unknown Lamer
from the postgis-to-the-rescue dept.
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?
This discussion has been archived. No new comments can be posted.

Ask Slashdot: Open Source vs Proprietary GIS Solution?

Comments Filter:
  • Some free resources (Score:5, Informative)

    by techtech (2016646) on Monday January 16, 2012 @09:16PM (#38720240)
    GIS and vector data of the world
    http://downloads.cloudmade.com/ [cloudmade.com]
    http://www.naturalearthdata.com/ [naturalearthdata.com]
  • Checkout PostGIS (Score:5, Insightful)

    by hakioawa (127597) on Monday January 16, 2012 @09:18PM (#38720254)

    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)

      by jtnix (173853) on Monday January 16, 2012 @09:24PM (#38720314) Homepage

      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:3, Informative)

      by hakioawa (127597)

      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)

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

    • Re:Checkout PostGIS (Score:5, Informative)

      by Dreben (220413) on Tuesday January 17, 2012 @12:24AM (#38721344)

      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?

  • by JoeMerchant (803320) on Monday January 16, 2012 @09:20PM (#38720270)

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

    • by Vellmont (569020) on Monday January 16, 2012 @10:01PM (#38720588)

      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.

    • by TerranFury (726743) on Monday January 16, 2012 @11:04PM (#38720948)

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

    • by mjwx (966435)

      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.

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

  • by binarstu (720435) on Monday January 16, 2012 @09:21PM (#38720284)
    I would recommend checking out PostGIS [refractions.net], which works with PostgreSQL [postgresql.org]. 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.
    • by binarstu (720435) on Monday January 16, 2012 @09:24PM (#38720310)
      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.
      • by Korin43 (881732) on Monday January 16, 2012 @09:48PM (#38720516) Homepage

        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]

        • by SplashMyBandit (1543257) on Monday January 16, 2012 @10:56PM (#38720906)

          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!

    • by punker (320575)

      +1

      I have used PostGIS and Postgresql extensively for spatial systems. It has rich functionality and can perform very well.

    • by yeltski (1438587) on Monday January 16, 2012 @09:49PM (#38720526)
      PostGIS and other GIS software probably benefits from OSS collaboration more than other traditional software categories, because they are part of a scientific collaboration. This means there are packages like Mapnik, GeoServer, GeoNode, etc, etc, that are build to work together in an ecosystem. And this ecosystem is larger than any closed system by it's nature. So, nothing can really compete with OSS GIS stack, and especially in the long term, and nothing really is meant to compete with PostGIS, just compliment it in the OSS world :)
  • by Anonymous Coward on Monday January 16, 2012 @09:22PM (#38720292)

    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.

  • This is what you end up with proprietary solutions. The proprietary vendor has to make money. They know that you will eventually need stuff, and they want to make you pay them.

    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
  • by jaymz2k4 (790806) <jaymz@NoSPAM.jaymz.eu> on Monday January 16, 2012 @09:23PM (#38720306) Homepage
    Have a look at this post [google.com] 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.
  • PostGIS (Score:5, Informative)

    by SSpade (549608) on Monday January 16, 2012 @09:25PM (#38720326) Homepage

    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.

  • by daboochmeister (914039) <daboochmeister AT gmail DOT com> on Monday January 16, 2012 @09:25PM (#38720328)
    Run, do not walk, to research the Open Source Geospatial Foundation [osgeo.org]'s offerings. And be aware, neo-geos devote their efforts primarily to PostGIS [refractions.net], 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 [lmgtfy.com].
    • by cptdondo (59460)

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

    by sgtstein (1219216) on Monday January 16, 2012 @09:26PM (#38720348) Homepage
    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.
  • by topham (32406) on Monday January 16, 2012 @09:33PM (#38720400) Homepage

    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.

  • by Anonymous Coward on Monday January 16, 2012 @09:35PM (#38720420)

    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.

    • by snsh (968808)

      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

  • This is an excellent question. Do you need to perform arbitrarily complex operations on geometries, or are you looking for very simple geospatial operations? If, for example, you need to find all points within a complex polygon, or calculate overlaps then it's likely to be useful either to buy into a real GIS system, or investigate something like the GDAL OGR library. If, however, all you want to do is the sort of thing you describe, you can probably get away with calculating a bounding rectangle from the
  • by Lordrashmi (167121) on Monday January 16, 2012 @09:57PM (#38720572)
    Disclaimer: I work for Monty Program Ab, the primary developers behind MariaDB.

    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.
    • by ysth (1368415)

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

  • If it gives you a warm fuzzy to use open source, budget what it will take to retrain or replace your programmers and build from scratch what you could get by paying the license fees. Factor in the additional risk and schedule impact of build versus buy. Then when you're done, contribute everything you paid your team to develop back as Open Source code so anyone else who wants to do the same thing can use it.
    • by mvdwege (243851)

      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.

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

    • by mjwx (966435)

      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

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

    • by Bert64 (520050)

      $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

  • 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

  • by spandex_panda (1168381) on Monday January 16, 2012 @10:42PM (#38720818)

    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.

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

    by tragedy (27079) on Monday January 16, 2012 @10:47PM (#38720844)

    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.

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

      • by tragedy (27079)

        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

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

    • 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

    • by gbjbaanb (229885)

      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.

  • by Bob the Super Hamste (1152367) on Monday January 16, 2012 @11:16PM (#38721008) Homepage
    I have had a fair amount of success using open source GIS tools for one of my side project to create a hunting map book. I am not too sure of how well either one would work for what you are trying to do but I would be surprised if they didn't. The 2 tools I have had the most success with are uDig GIS [refractions.net] and GRASS GIS [grass.fbk.eu]. Both of them will run on Window, Mac OS, or Linux just fine, they all can process shape files, and DB input as well as geo tiff files as well as other file formats. Another popular OS GIS program is Quantum GIS [qgis.org]. When I started I found OSGeo4w [osgeo.org] which had a bunch of open source GIS programs compiled for windows with everything you need and tried out a few of them and found 2 that best suited my needs. At the time I know it included GRASS, uDig, QGIS, and a couple of other ones, you might want to check out OSGeo [osgeo.org] as well as there may have been other projects that have started or better meet your needs.

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

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

  • We research issues in Canadian Immigrants, and found that there was a lack of recent, unaggregated information.

    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.

    • by msobkow (48369)

      To paraphrase George Carlin:

      What good are data privacy laws if every schmuck with a $2 database can fuck up your privacy?

  • I responded to your early post before it went "live" here.

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

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

"When it comes to humility, I'm the greatest." -- Bullwinkle Moose

Working...