Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Ask Slashdot: Open Source vs Proprietary GIS Solution?

Unknown Lamer posted more than 2 years ago | from the postgis-to-the-rescue dept.

Open Source 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?

Sorry! There are no comments related to the filter you selected.

Obvious answer (-1, Troll)

TechGuys (2554082) | more than 2 years ago | (#38720222)

This is a stupid question. Of course you are going to buy product that is ready to do what you want. Writing custom code is always hundreds of times more costly. These commercial products can sell them cheaper because they sell thousands, even millions of them. But if you need someone to write that code for you (which you will be doing regardless of putting it open source), it will cost.

So take the product that is required to get the job done, right now. Don't waste the money on getting some expensive coders on other product. You would also have headaches with testing and bugs. All that is done for you with MSSQL. Take it.

Re:Obvious answer (2)

fuzzytv (2108482) | more than 2 years ago | (#38720470)

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, it's going to be very expensive (outweighing the prior savings) or even impossible. Plus you usually have zero influence on future of the product.

I really don't see how the choice of MSSQL (or any other database) eliminates the need for proper testing ...

Re:Obvious answer (2)

tragedy (27079) | more than 2 years ago | (#38720484)

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 you want without swapping out such a major part of your application stack.

Re:Obvious answer (4, Insightful)

_Sharp'r_ (649297) | more than 2 years ago | (#38720896)

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.

Re:Obvious answer (0, Troll)

mrclisdue (1321513) | more than 2 years ago | (#38720538)

Thanks for taking a minute of your time to first-post your seemingly ubiquitous pro-MS anti-Google (mostly, followed by foss, apple) *viewpoints* under your umpteenth alias of the past few months (or has it been seconds? eons? whatever....)

You failed to answer my query in a previous thread as to how you omitted Microsoft from a first-post-same-timestamp-as-story rant regarding patents. Allow me to refresh:

You said:

Jeez. Companies like Google and Apple are collectively abusing the system and patenting every single thing they can think of, most of which are outright obvious

I asked you why you omitted Microsoft. May I ask you again? You are aware that Microsoft boasts of having *licensing agreements* with, oh, didn't I read somewhere around 85% of Android phone manufacturers? Would patents be involved?

Why do you troll here? Why do you need more than one user account? Why do you need to first-post?

And why didn't you provide an answer to the submitter, rather than troll?

The Obvious answer (4, Insightful)

Joe U (443617) | more than 2 years ago | (#38720698)

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 (-1, Troll)

mrclisdue (1321513) | more than 2 years ago | (#38720816)

Oh! Oh! I have an answer. Pick me!

It's because you're trolling just as hard!....Yay! I solved the puzzle. I'll take the cupcake for $10 and the rest on a gift certificate Pat...blah, blah, blah

bzzzzt.

You fail.

For your answer to have been correct, the OP would need the ability to foresee the future.

Nice try, tho', thanks for playing.

Re:Obvious answer (-1)

Anonymous Coward | more than 2 years ago | (#38720712)

I dare say Microsoft were omitted because their relevance is dwindling fast.

Re:Obvious answer (-1)

Anonymous Coward | more than 2 years ago | (#38720924)

Nobody is under any obligation to respond to your dumb shit trolling. GTFO.

Other solutions? (1)

Anonymous Coward | more than 2 years ago | (#38720244)

There are other solutions you may want to look into. Solr has decent geography support and is built to be a search system.

MS SQL is better (1, Redundant)

wasabii (693236) | more than 2 years ago | (#38720252)

MS SQL is a better product, all around. Both as an engine, and the management UI. The integrated security features, integrated XML support, and of course the GIS functions.

But it costs money.

Cost benefit, dude. That's all there is to it.

Re:MS SQL is better (2, Interesting)

Grishnakh (216268) | more than 2 years ago | (#38720480)

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:MS SQL is better (3, Insightful)

ShnowDoggie (858806) | more than 2 years ago | (#38720880)

What if they actually save money by being more productive?

Re:MS SQL is better (2)

jellomizer (103300) | more than 2 years ago | (#38720882)

Umm there is a cost to retrain your developrs to use new tools. If the developers like .net it is probably worth a few K to give them the tools to do what they need. .NET doesn't suck neither does MS SQL (Microsoft has upped its game in product quality the last few years). The time and cost of finding an appropriate Open Source tool and get your coders up to speed will more often then not be more expensive then using the first decent solution that is available and liked by your staff.

Lower costs? (-1)

Anonymous Coward | more than 2 years ago | (#38720892)

From the original poster:

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.

OK, what makes you think that the F/OSS stuff will all work together and integrate fine?

F/OSS isn't necessarily the cheapest way to go. You mentioned the licensing fees that MS will charge but did you mention the additional developer costs with F/OSS?

You see, the F/OSS stuff never plays nice with one another. Sure, you got source code - so you need extra developers to go and plow through the source code and figure out how it works.Then, I guarantee there will be a dependency problem somewhere. There's always dependency problems and incompatibilities with F/OSS installations. I take that back, almost - LAMP has had its ass kicked so much, that's pretty much been ironed out. BUT we're back to the geometry and working that in. Then, there's the custom coding that will be required with a F/OSS installation; as the poster said there would.

So, I guess the numbers to crunch are:

1. MS and licensing fees.

2. F/OSS and extra help.

Sorry, as much of a F/OSS fan that I am, I can't honestly say that it will be cheaper: short or long run.

Re:MS SQL is better (1)

fuzzytv (2108482) | more than 2 years ago | (#38720524)

Better than what? And why is the XML stuff important (the original poster did not mention that at all)?

Checkout PostGIS (5, Insightful)

hakioawa (127597) | more than 2 years ago | (#38720254)

It is far superior to MySQL geographic types. I'm partial to MSSQL, but PostGIS is a close second.

Re:Checkout PostGIS (5, Informative)

jtnix (173853) | more than 2 years ago | (#38720314)

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:Checkout PostGIS (0, Troll)

Grishnakh (216268) | more than 2 years ago | (#38720506)

It's easy: just offer them the choice between MS tech and something else. With the latter, they keep their current paychecks, and maybe get a raise if they do a good job. If they choose the former, then they get a giant pay cut to pay for the MS licenses. Ask them where their loyalty lies: if they like MS so much, then they should be happy to give part of their pay to them.

And if you, the boss, prefers open-source solutions, maybe you need to do a better job in hiring people that agree with you.

Re:Checkout PostGIS (1)

Anonymous Coward | more than 2 years ago | (#38720714)

I don't know how it works in the OP's country, but in most industrialized nations you'll find that making significant negative changes to compensation and position requirements post-hire is going to likely result in litigation.

Re:Checkout PostGIS (1)

Anonymous Coward | more than 2 years ago | (#38720796)

Of just fire one or more engineers.

Since you're using expensive tools you don't need that much manpower.

Re:Checkout PostGIS (0)

Anonymous Coward | more than 2 years ago | (#38720828)

Funny, I do the same thing to the freetards at work.

Just kidding. You're still a fucking idiot, though.

Re:Checkout PostGIS (1)

Braedley (887013) | more than 2 years ago | (#38720918)

I'm with ^. We use Postgres in association with dynamic location data and haven't had any major issues. We haven't integrated PostGIS, but have definitely contemplated it, as it would make many of our queries an order of magnitude easier to write and (at least from what I hear) wouldn't compromise on speed when post query filtering is also accounted for. The only reason we haven't done it is that we haven't had the time and budget to make sure it was bullet proof, and we've been able to make do without the finer control offered by PostGIS. I believe another project in my office has been using PostGIS, and as far as I can tell, it's been paying off for them. While we're generally happy with a flat earth NW to SE bounding box, this other project needs much finer control for determining if a point is within an area.

Re:Checkout PostGIS (2, Informative)

hakioawa (127597) | more than 2 years ago | (#38720378)

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:Checkout PostGIS (1)

Anonymous Coward | more than 2 years ago | (#38720418)

I'd highly recommend benchmarking your solution on MSSQL and PostGIS, determine how much CPU you need to scale out and then factor in the licensing costs for each product. At my job we're switching to PostGIS because it's 3x faster for our particular spatial problem, and the licensing cost is hard to beat.

Re:Checkout PostGIS (3, Informative)

Anonymous Coward | more than 2 years ago | (#38720462)

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 (1)

FilthCatcher (531259) | more than 2 years ago | (#38720940)

I'll second this combination.

We actually looked into getting Mapinfo but the licensing costs were heading into tens of thousands and that's before getting any actual map tiles.
Happily using Quantum GIS. This has a plugin that can save shapes directly into a Postgis column.
Postgis took a little patience the first time I installed - I'm running postgres 9.0 on centos 5.4 which requires third-party repository so had to do a few steps by hand - a more recent distro may be easier.
We're using it to map a bunch of franchise areas and redirect new customers after a geolocation lookup.

Distance calculation is trivial... (5, Informative)

JoeMerchant (803320) | more than 2 years ago | (#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).

Re:Distance calculation is trivial... (5, Informative)

Vellmont (569020) | more than 2 years ago | (#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.

Re:Distance calculation is trivial... (4, Interesting)

TerranFury (726743) | more than 2 years ago | (#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?

Re:Distance calculation is trivial... (1)

mjwx (966435) | more than 2 years ago | (#38720960)

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:Distance calculation is trivial... (1)

tillerman35 (763054) | more than 2 years ago | (#38720982)

Second (or apparently third) that opinion. Here is a tip to make queries based on distance MUCH faster:

First, divide your territory into equally-sized "squares" based on the maximum "within N miles of" distance or some other convenient size. Give each an X and Y coordinate starting at 0,0=westernmost,northernmost and continuing Eastward,and Southward with incrementally larger values of X and Y. Store them as individual columns with a bitmap index.

When joining the table to find records within N miles of a set of given lat/long coordinates, you only have to determine which X,Y square the lat/long coordinates fall into and query just those records that are within X+/-2 and Y+/-2. This will make use of the indexes on X and Y and greatly reduce the number of records that you need to perform the (CPU costly) distance calculation upon.

This method increases the efficiency of questions that require a join (e.g. "records within N miles of each other") because the X and Y coordinate join, although not an equi-join, can still use the indexes.

It also helps with figure-based queries (e.g. "records inside a circle of radius R miles" and "records inside complex polygon with vertices P1,P2,P3...") because the squares that the figure fully or partially encompasses can be pre-calculated and used in the WHERE clause of the query.

Price is really weird (0)

Anonymous Coward | more than 2 years ago | (#38720278)

How could it possibly cost 20k to do what you're talking about? I did the same thing last summer for a guy for about $200. And why would it cost $20k to move to MSQL. I suspect either there is a lot not being talked about in the summary for the prices are missing a decimal point.

Re:Price is really weird (0)

Anonymous Coward | more than 2 years ago | (#38720936)

You've never tried licensing MS development tools, have you?

MSDN subscription (annually): somewhere in the neighborhood of $1200 - $4000, depending on what features you want with it. Multiply that by the number of developers. Add in cost of MSSQL (guesstimate: $5k - $10k). You could push $20k easy with a 3-man dev team.

There's a reason large-scale enterprise development is still done in Java.

Re:Price is really weird (1)

viperidaenz (2515578) | more than 2 years ago | (#38720988)

Keep in mind that $20k is probably only 200-400 man-hours of work. I've just spent the last year as the only developer on a project that cost $1.8M. I only billed them $250k. The rest went to project managers, BA's, testers, SME's, HR, Managers, etc...

Postgresql GIS (0)

Anonymous Coward | more than 2 years ago | (#38720280)

Drop MySQL in favor of postgres.

PostgreSQL with PostGIS (5, Informative)

binarstu (720435) | more than 2 years ago | (#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.

Re:PostgreSQL with PostGIS (4, Informative)

binarstu (720435) | more than 2 years ago | (#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.

Re:PostgreSQL with PostGIS (5, Informative)

Korin43 (881732) | more than 2 years ago | (#38720516)

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 (3, Interesting)

SplashMyBandit (1543257) | more than 2 years ago | (#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!

Re:PostgreSQL with PostGIS (1)

Anonymous Coward | more than 2 years ago | (#38720568)

A nice series: http://www.daniel-azuma.com/blog/archives/category/tech/georails

Re:PostgreSQL with PostGIS (-1)

Anonymous Coward | more than 2 years ago | (#38720344)

+1

Re:PostgreSQL with PostGIS (2)

punker (320575) | more than 2 years ago | (#38720356)

+1

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

Re:PostgreSQL with PostGIS (-1)

Anonymous Coward | more than 2 years ago | (#38720446)

Two idiots post "+1" as if it helps move the conversation forward. GTFO.

Re:PostgreSQL with PostGIS (5, Interesting)

yeltski (1438587) | more than 2 years ago | (#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 :)

Why not use a Mixed-Solution. (5, Informative)

Anonymous Coward | more than 2 years ago | (#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.

Little cost today, huge cost tomorrow ? (1)

unity100 (970058) | more than 2 years ago | (#38720298)

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 custom front/application slowly, and at one point be vendor-free.

there's a lot of FOSS options (5, Informative)

jaymz2k4 (790806) | more than 2 years ago | (#38720306)

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.

What about Postgre? (0)

Anonymous Coward | more than 2 years ago | (#38720308)

SQL Server makes GIS a breeze, see here http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-2008-proximity-search-with-th
what about PostgreSQL...can you use that?

DotNetNuke is open source! (0)

Anonymous Coward | more than 2 years ago | (#38720312)

I think that the spatial geography data types that are native to MS SQL seem like they would be pretty key to what you're doing. Add to that DotNetNuke, which is free and open source, and that appears to be a pretty strong solution. DNN is also extremely extensible, very robust and it think would play well with MS SQL and your project.

PostGIS (5, Informative)

SSpade (549608) | more than 2 years ago | (#38720326)

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.

Re:PostGIS (1)

Anonymous Coward | more than 2 years ago | (#38720364)

I was once a research assistant on a project that relied on GIS. PostGIS is the OpenSource defacto in GIS. There's also a healthy community of other Open Source tools that can use PostGIS, such as GeoServer.

PostGIS and OSGeo.org (5, Informative)

daboochmeister (914039) | more than 2 years ago | (#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] .

Re:PostGIS and OSGeo.org (1)

cptdondo (59460) | more than 2 years ago | (#38720650)

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

I've written that kind of code using MySQL... (0)

Anonymous Coward | more than 2 years ago | (#38720338)

To make it easy, you store the lat/long of each item, and it is relatively easy to search quickly for a all of the matches within a rectangle, and then within that group you can use a little bit of geometry to narrow it down to an exact circle (if you wish). This might cost something like one man-day to implement. Are you sure that your developers are reasonably competent?

PS. I work in a mixed Linux/Mac/Windows environment, and we've found Windows based computers to be relatively expensive (in terms of our time) to maintain and administer.

Open Source (1)

Anonymous Coward | more than 2 years ago | (#38720346)

Either Postgresql or Mysql (w/ geo extensions) will work on the back end. Geoserver makes a good, if somewhat heavyweight, middleware solution—it is fast though. It'll serve out selected areas in a variety of formats, including JSON. Arcsoft can use some of the other formats. Google Maps or OpenLayers will work well on the front end.

PostgreSQL + PostGIS (5, Informative)

sgtstein (1219216) | more than 2 years ago | (#38720348)

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.

Unprofessionalism at its finest (-1)

Anonymous Coward | more than 2 years ago | (#38720386)

Are you telling us that you are putting your own personal bias for open source ahead of choosing the best solution for the problem? You ought to be fired just as much as the average "Nobody ever got fired for buying M$" drone. You're not being paid to be an open source evangelist; you're being paid to do your job!

Write down the pros and cons for each solution, pick the best one from an engineering perspective, and, if some closed source solution unfortunately has the advantage, grit your teeth and implement it.

Re:Unprofessionalism at its finest (1)

cptdondo (59460) | more than 2 years ago | (#38720682)

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.

Stay away from proprietary (-1)

Anonymous Coward | more than 2 years ago | (#38720388)

Don't lock yourself in, use a FOSS solution. If your developers don't like it, fire them. You are in control, don't let them strong-arm YOU.

PostGIS + GeoDjango, no contest (1)

yeltski (1438587) | more than 2 years ago | (#38720396)

These are obvious choices without better alternative even in the commercial world. All ESRI products, or other products included. ESRI doesn't advertise it, but they are also using GDAL and other open GIS standards.

https://docs.djangoproject.com/en/dev/ref/contrib/gis/tutorial/ [djangoproject.com]
I have 10 years of Web, and 5 of GIS experience, and doing consulting now. Let me know if you're interested, and we can have a private conversation.

MySQL and Drupal are fine (4, Interesting)

topham (32406) | more than 2 years ago | (#38720400)

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:MySQL and Drupal are fine (1)

ckaminski (82854) | more than 2 years ago | (#38720848)

Where does one get a database like that?

English? (-1)

Anonymous Coward | more than 2 years ago | (#38720404)

*than. For the love of God.

Only one true FOSS option: PostgreSQL + PostGIS (5, Informative)

Anonymous Coward | more than 2 years ago | (#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.

Re:Only one true FOSS option: PostgreSQL + PostGIS (1)

yeltski (1438587) | more than 2 years ago | (#38720466)

As an ESRI guy, how do you feel, or what do you think is the ESRI feeling about Google hiring the top GDAL guy? Any idea, what, if anything they are planning? :)

Re:Only one true FOSS option: PostgreSQL + PostGIS (1)

Anonymous Coward | more than 2 years ago | (#38720578)

Meh, who knows. By all accounts it doesn't sound like Google even realized who they were hiring.

The big G has been making inroads into the GIS realm for years now and brought a long needed kick to the industry (Esri very much included). They continue to attack the simple low-end solutions market with Google Maps, Google Earth, Fusion Tables, etc.

However they've also run into some recent challenges after starting to charge for Google Maps tiles. Even if most sites will never hit the usage limits and have to start paying the mere fact that it's not just 'free' seems to have pushed a lot of people to start investigating alternatives both commercial and FOSS. Something I personally consider a very good thing. If all you need to is serve up some map tiles with some points on them, with a few distance calculations thrown in, then using ArcGIS Server is a bit heavy-handed (although, of course, it can do that too).

Ultimately Google is nowhere close to having a 'GIS' in the classic sense and I doubt that's what they're going for. But if your needs aren't at the full-blown Enterprise GIS level you can go a long way with both Google provided solutions or homegrown solutions built on FOSS.

Re:Only one true FOSS option: PostgreSQL + PostGIS (1)

snsh (968808) | more than 2 years ago | (#38720886)

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 there's SQL2012 RC which you can use for free for maybe six months before it expires.

Work out what you need first. (2)

jdaragon (1636279) | more than 2 years ago | (#38720442)

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 set of points you have and extending that in each direction by an arbitrary amount. Lo - store grid references or Lat/Long, and you need no geospatial support in your database at all. If you *did* need geospatial support, and were persuaded that MS-SQL were the way to go, then check if you qualify for WebSiteSpark in Canada - that will get rid of most of the 20k you need to spend and get you Server 2008, SQL Sever 2008, Visual Studio 2010 &c for $100 over 3 years. How much data do you have? SQL Server 2008 Express has the spatial extensions.

How often does data change? (0)

Anonymous Coward | more than 2 years ago | (#38720464)

How often does the data change? I'm not talking about adding new records but changes to existing ones. If the answer is rarely then you should be putting your data in a SOLR search engine, not in a database of any sort. But if a database is called for, you really need to use PostgreSQL, either on its own or as part of a GIS system using POSTGIS. MySQL is just the wrong tool for this job.

Look at tools like TileMill that make mapping really easy.

Fork You (-1)

Anonymous Coward | more than 2 years ago | (#38720494)

If Oracle ownership of MySQL bothers you, then download MariaDB [mariadb.org] .

You don't have to use it right away if you don't want to, but it is a drop-in replacement for MySQL that you can substitute at any time.

CODE RED!
CODE RED!
DEPLOY MARIA!
GO!
GO!
GO!

$20,000 for Mathmatical Functions? (0)

Anonymous Coward | more than 2 years ago | (#38720504)

Please tell me you are not actually thinking that having geometric functions in SQL is worth $20,000. If so, I have a really nice hammer I can sell you, only $800.

PostGIS (0)

Anonymous Coward | more than 2 years ago | (#38720532)

Postgres + PostGIS is the way to go for you. Has any function you will find in Oracle Spatial or MSSQL. Just have a quick look at the documentation and you'll see for yourself.

GIS improvements already are in MariaDB (4, Interesting)

Lordrashmi (167121) | more than 2 years ago | (#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.

Re:GIS improvements already are in MariaDB (2)

ysth (1368415) | more than 2 years ago | (#38720772)

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 (1)

tomhath (637240) | more than 2 years ago | (#38720582)

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.

Re:Warm Fuzzy (0)

Anonymous Coward | more than 2 years ago | (#38720778)

The fact is as a non profit he has NO LICENSE FEES FOR MS PRODUCTS or many other vendors products.

Best Place to ask GIS questions (1)

Anonymous Coward | more than 2 years ago | (#38720640)

I know this post will get swamped, but slashdot isn't really the best place to get advice about that. You should ask your question over at http://gis.stackexchange.com

OSGeo, Worldwind, QGis and GeoServer. Specifically GeoServer which would allow you to server data from which ever database you decide directly to the users. If you aren't already aware, you should investigate Web Map Servers (WMS) as they would be a better delivery mechanism than CSV.

Your developers (1)

NewWorldDan (899800) | more than 2 years ago | (#38720660)

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:Your developers (1)

mjwx (966435) | more than 2 years ago | (#38720990)

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

Consider remedial training for your staff (0, Flamebait)

Arrogant-Bastard (141720) | more than 2 years ago | (#38720674)

If they really are "MS guys at heart", then they're not very well qualified. This may be due to intrinsic low intelligence, but presuming you've ruled that out, it's likely due to lack of significant experience with professional-quality software. (I consider MS software fine for children and amateurs, but utterly unsuitable for anyone who even pretends to be a professional.)

This exercise would thus make an excellent teaching moment for them: they should be tasked with investigating the many fine pieces of open-source software (and data) linked to in this thread, and educating themselves to the level necessary for them to understand not only how the solution to your current problem has very likely already been coded by someone else, but why it's very likely been coded by someone else. In other words, I'm suggesting that they come to an understanding of why common/ordinary/routing tasks should not be relegated to expensive, proprietary software, but are included in free, open-source software during the normal course of development.

Meanwhile, you should consider this question: is a relational database the best way for you to attack this problem? Or should you, instead, consider alternate ways of storing/searching the data? (I make no recommendation either way; without MUCH more detail on your precise requirements, I can't.)

Re:Consider remedial training for your staff (0)

Anonymous Coward | more than 2 years ago | (#38720922)

My advice is to not follow this guy's advice because he's obviously a dumbfuck.

After all, he's an 'open source guy at heart' so he's obviously not very well qualified. This may be due to intrinsic low intelligence, but presuming you've ruled that out, it's likely due to lack of significant experience with professional-quality software. (I consider FOSS software fine for children and amateurs, but utterly unsuitable for anyone who even pretends to be a professional.)

This exercise would thus make an excellent teaching moment for them: they should be tasked with investigating the many fine pieces of proprietary software linked to in this thread, and educating themselves to the level necessary for them to understand not only how the solution to your current problem has very likely already been coded by someone else, but why it's very likely been coded by someone else (and why they want to charge for it). In other words, I'm suggesting that they come to an understanding of why common/ordinary/routing tasks should not be cheap, unmaintained, buggy free software, but are included in quality, proprietary software during the normal course of development.

charity (1)

spongman (182339) | more than 2 years ago | (#38720676)

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.

Give PostGIS a try (1)

fuzzytv (2108482) | more than 2 years ago | (#38720704)

First of all, it's not your developers who should do this decision. Yes, you should ask for their opinion but if they're the "we know MSSQL and we're not willing to see other options therefore everything else is worse" type then they'll choose MSSQL no matter what. So do a cost-benefit analysis, as already recommended by wasabii.

Before giving any specific recommendations, I have to mention that I'm a PostgreSQL-guy. I do like how mature and reliable the database is, how the community works, how fast the occasional bugs are fixed, etc. Therefore I'd recommend you to give PostGIS a try - AFAIK the spatial functionality is infinitely better than in MySQL, quite comparable to MSSQL. There is a nice comparison matrix of features at http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008_postgis_mysql_compare [bostongis.com] but it's very old - it may give you an idea how the products compare. You can probably get more details on the PostGIS mailing list.

You should really decide whether you want to go with an open-source solution or not, how important the benefits are for you etc.

20k in the left hand, 20k in the right hand? (1)

Frosty-B-Bad (259317) | more than 2 years ago | (#38720706)

Things to consider:

1. How many "guys" (people) are you talking about when you say they are "MS guys at heart" == (amount)
2. How much will they have to learn to be as proficient at option X versus what they are good already (MSSQL) == (time)
3. How much will they be annoyed that option X "just doesn't do this the same way" == (more time wasted chatting about how dumb there boss is for making them change)

then run that through the ol' PERT analysis (shortest time + 4xlikely time + longest time) / 6, and see if that comes up to be more than your 20k initial expense..

so lets say 3 "guys"? and the project seems rather large and its doing something new, but lets guess 6 months (960hrs.. 40hrsx4x6),

lets say they on the conservative they waste 8 hrs a week learning/complaining/whatever, 10 likely and 12 if they are really mad at you for making them learn some new stuff.. ( pert says 10 after that )

so we'll say they'll blow 10 hrs a week learning the new SQL db, 240 total hrs a piece, and we'll guess there pay at $20 being non-profit and all, but probably paid more I would hope.. $4800 per person, times 3 is $14,400. so its getting in the warm zone of $20k.. would be interesting to see real numbers..

Comparison of PostGIS/mssql and other points (1)

Anonymous Coward | more than 2 years ago | (#38720722)

A feature comparison between Postgis and MSSQL server

http://bostongis.com/?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare#221

I've always used postgresql. Cross-platform, well documented, well supported, solid performance. What's not to like?

  Also ties in quite well with other open source geospatial projects like qgis, http://qgis.org , and geoserver, http://geoserver.org.

There is also a drupal module geospatial module that uses postgis, http://www.geops.de/blog/64-spatial-data-and-drupal-7

If you are looking cloud, it costs 41% less to run your linux instance on Amazon than a windows instance.

Proprietary, definitely. (-1)

Anonymous Coward | more than 2 years ago | (#38720734)

Open Source stuff tends to, quite frankly, suck ass. Whether you are talking about Linux, Firefox, Apache, etc etc etc, there doesn't exist a single open source code project that does anything better than its closed source competition, this is why I generally recommend avoiding open source whenever possible.

Re:Proprietary, definitely. (1)

Panaflex (13191) | more than 2 years ago | (#38721004)

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.

For Non-Profits (0)

Anonymous Coward | more than 2 years ago | (#38720782)

http://home.techsoup.org/ --> Great site for MS software cheap for non-profits. Instead of debating which software solution is best. If you have good guys giving their time, and they want a MS solution, then give it to 'em cheap!

Think this through (0)

Anonymous Coward | more than 2 years ago | (#38720792)

As someone has already pointed out, your developer is far more expensive than the software. So the more you can get done without custom code, the cheaper the solution.

Also, MS does give significant cost breaks for non-profits.

Finally. there is nothing to prevent you from opening up the source of the part your actually produce.

PostGIS (app server for PostgreSQL) (2)

Dreben (220413) | more than 2 years ago | (#38720810)

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 featured and the only open source implementation I know of that adheres to the Open Geospatial Consortium's 'Simple Features: Types and Functions 1.1' specification. It is very robust, scales well (in contrast to MS SQL) and you can't beat the price or ease-of-mind that comes with being licensed under v.2 of the GPL. Additionally, on comparable hardware, in terms of reliability, performance, and cost effectiveness, there is no comparison, regardless whether your using Oracle w/Spatial or MS SQL and ESRI's SDE.

Your developers, as good of guys at heart as they may be, should stay away from the kool aid for awhile

GIS Stackexchange (2)

spandex_panda (1168381) | more than 2 years ago | (#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.

One additional datum: is this the last question? (1)

rbrander (73222) | more than 2 years ago | (#38720832)

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 open source, fan, but working in a big bureaucracy, I've reluctantly ceded the wisdom of the Microsoft/ESRI "shop" as offering the best chance of quickly replacing people who move on.

Luciad (1)

Anonymous Coward | more than 2 years ago | (#38720836)

Someone else has probably already posted this, but ESRI is a MS-only solution too. They ditched Unix over a decade ago with ArcGIS 8.

I'm interested to learn of other, more portable, GIS Solutions as well. I've had the opportunity to play with LuciadMap in the past, but that runs in Java (a language I hate coding in). It is more portable than ESRI and the world has no real shortage of Java Developers. They're a proprietary solution based on the commonly accepted GIS standards. Their license isn't cheap, but still cheaper than ESRI from my understanding. They also have an Android version that was in the works last time I heard from them.

A bit sad. (4, Insightful)

tragedy (27079) | more than 2 years ago | (#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.

Although I use databases for much simpler things.. (1)

axlr8or (889713) | more than 2 years ago | (#38720866)

I'm afraid I'd have to put my eggs in the longevity basket. Databases come and go. Open source seems reasonable to me for that reason alone. Another thing, if your 'guys' are MS guys it still might be security in the bag to use something java related. I said MAYBE stone throwers be still. .NET was always annoying and now its interest is waining. Would it hurt to be able to platform hop? Nope, because even managing parts its been nice switch machines and locations. Furthermore doesn't PHP and something like MYSQL click?

GvSIG (1)

jcfandino (2196932) | more than 2 years ago | (#38720888)

I'm not into GIS but I once attended a presentation about this system.
Maybe it can help, meybe not.

http://www.gvsig.org/ [gvsig.org]

EVE ONLINE (1)

Jimekai (938123) | more than 2 years ago | (#38720966)

If you can stomach their dog-eat-dog world of AI agents long enough to discover that their data structures are based around the same ESRI core software and that the PCA routine VAR2VEC lies at the heart of their analysis, then you'll conclude that putting your loadings in XML makes a great interface.

The hard part of this ... (1)

Skapare (16644) | more than 2 years ago | (#38720994)

... 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 I might not trust SQL to do that kind of stuff as efficiently as it could).

Let my logic organize the data into grid squares in files and I could make this work very fast. But I need the data (or at least uniformly spread random sample of a few billion items) to test it.

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?