Would the Google Maps API be helpful for that?

Well, duh, the obvious way to get the location of 12000 places is to *visit* them, GPS unit in hand...

:)

< homer >Mmmm, mileage allowance...< /homer >

(Go icon for obvious reasons - you've only got until MS ship SQL2008, oh wait, _plenty_ of time...)

how? very carefully, but also as quickly as possible. we have a deadline, you know.

are you done yet? never mind the testing, just put it in production, we'll fix the bugs in rollout...besides, you're working with a release-version MS enterprise-level database (or "beta release", as the rest of the world calls it), on a release-version MS enterprise-level OS (or "early beta release", to everyone else).

what could go wrong?

ok, i'm done channeling the IT Director/CEO/MD.

i'll be wearing the pinstriped jacket with armor plating, if you'd be so kind.

Yeah ranting about it is all very good and stuff, but you'd be well among the first to complain if the release had serious bugs in it!! Think about it...

Probably your project parameters are quite different then my average project. However, just the thought of the success of a project depending on some features of an unpublished version of some software, would definitely keep me from having a good night's rest. And that's with any vendor, no matter their track record. As you write, this particular company has quite a history of slipping schedules and evaporating feature sets when marketing meets reality.

Fool me once, shame on you. fool me twice, shame on me.

I'll be here all week,

Oracle

as for the conversion, ever heard of Shell Scripting, I mean you can even pick your language of choice, should not be longer than 10 lines to boot (if you do it on unix that is, go .Net and you are on your own)

Okay - so I've written my share of million row+ database applications, I've written my own GPS software, and I've co-written a front-end for a land title renderer (where the backend was a database with some many thousands x many thousands of square km's of geo data in it) - so I'm with you up to a point (and maybe that's to the point that I've never had a need to use SQL Server). However, when you say;

"The next question is how do we convert over 12 thousand location items - by hand?"

I've really got to ask, what's the SQL Server angle?

If I'm reading this right, other than complaining about Microsoft's delivery schedule (which is shooting fish in a barrel - go that market leading virtualisation product), you're saying that you've picked a data structure for your data, choosen an interpretation for that data, and are now considering manually converting all of your existing data into a new format so that you can query it in a new way. So how would this differ from an Oracle database, a MySQL database, a SQL Server database (of vintage prior to 2008), or any other RDBMS with a stored procedure coded to your radial search example?

If you said that SQL Server 2008 was going to auto-convert your data (which Microsoft should be able to do given toys like their MapPoint), then there would be some vague sense to what you're saying.

Meh. If this were mine:

1) Do a web search for "address validation" and pick a product/service that has data covering your region(s) of interest. Run your database through that and use the clean street, suburb and post/zip code data to pull geo values from your favourite map source automagically - if you picked an address validator that came without lat/long references. Update your DB appropriately.

1a) If, after 1 there are rejected addresses from the validator, then either: Give a shiny new Project Manager a database with the "rejected" addresses and let the PM run a room of monkeys making yes/no (did you mean street x in suburb a?) decisions with the help of the address validation program to re-jig the data set -- or -- flag rejected addresses against the customer database and send forth the memo to the CRMs to make it so and sure-up the data set via the customer that way. If rejects > acceptable_threshhold, goto 1.

2) Do a web search for the radial maths that you want to use (a fast algo could be a lat/long min/max range based on a search diameter-width diameter-height square, a slower algo could be a plan-view variation of the gaming technique called z-buffering / z-clipping, you could multi-stage the query using a temporary table populated with a fast pre-selector algo, etc - add to taste, curvature of the earth, pre-loaded sin tables, etc knock yourself out) and implement yourself a stored procedure or two, in your Oracle/MySQL/(SQLServer<2008) database.

3) Update pretty front-end software - try a 3 colour, 4 million-shade, CEO-quality, graphic.

4) Walk away richer (particularly if you ditched that expensive SQL Server license).

FYI - you'll find your radial queries will run faster if you drop those alpha fields to retain two numeric fields only - either offset the values for unsigned floats or use signed floats. This technique (signed floats) is one of the GPS interface options - see SiRF's $PSRF104 message.

Am I really missing something from your article, are you not just trolling? How could you possibly get to a new DB, new OS and new hardware over something so fundamental to any existing database ...?

And next week I gues we will notice that all those cool spartial data structures cannot be indexed in the version you are using as this would require the designers to open a book on algorithms and understand it.

Come one, experience should have told you to wait a few versions after a feature came out, as a) Microsoft tends to not get features right at the first version, and b) Microsoft tends to let many features rot to pieces.

I for one am happy that by them delaying a product release, it's code and testing levels would have had enough checks and time for MS to be happy to realease it and for us not to face the concequences of another rushed out, buggy release.

I couldn't give a monkeys if it was SQL Server 2010, as long as the features were implemented and tested properly releivig me of wading through knowledge base articles or finding only 1/2 a feature was implemented. As Chris mentioned above, how many hundred Mac/Linux/unix users would post here if they'd taken the opposite decision and rushed SQL Server 2008 out the door?? Seems MS can't win one way or the other!

If you were really anxious to start developing with this new data type, then I'd ask whether you'd looked at any of the beta cpoies & whether they'd got this feature in?

Expensive method: You buy the overlays for the areas you need from ESRI.com or another vendor. Highly accurate. Not cheap. They're raw text format. Includes elevation data. You will need a math whiz to convert points on the surface of an oblate speroid into an angle and determine the length of the curve. This will be more straightforward than you might think for distances less than 500km and accuracy on the order of three digits.

Cheap method: You use the Google Maps API and build your application around that. It will calculate distances for you. For most uses the data is good enough. Will calculate over-the-road distances. Will plot your datapoints on an arbitrary map. Includes satellite or terrain mapping features. Free. Does not include elevation data.

Free and open method: In the US we have a government Bureau - US Geographic Survey, that provides this data for free. In the UK this information is available at edina.ac.uk but it is not free. Bruce Gittings has some data at http://www.geo.ed.ac.uk/home/ded.html if the server is back up. GIS information in raw text format. Includes elevation data. Perhaps the government in the UK also keeps GIS data and makes it generally available. I should hope so -- it's pretty important. A cursory google search didn't find it though.

The best answer is probably a combination of these. You didn't say how many data points you needed.

Read the terms for your data provider to ensure your use is permitted of course.

Good luck

Yeah... whats the fuss about spatial data? Why even take the risk of using MSSQL that's not even realeased? I'm sure you have been questioned this lots of times... This is just one more :)

You should've gone the Oracle way, but thats just me...

If your business depends on Microsoft (or anyone else) releasing something on time, you shouldn't be in business.

Your problem is that you think because you throw money at Microsoft, they can magically change the nature of software development. Wise up - they don't need your money, and their stuff will be "ready when it's ready" just like everyone elses.

In the FOSS world, where no-one commits to dates (because they don't have to) if your chosen feature isn't there now, you either:

(a) write it yourself (and maybe submit as a patch to speed up the "official" version)

(b) propagate the "it'll be ready when it's ready" factor to your own project, and get on with something else

(c) use a different component

Apart from (a) being impossible with a closed-source platform, you basically have the same choices.

John

Actually its a Geoid ....

I used to be paid to write code which calculated the times of phenomena such as sunrise and sunset for given geographical locations, so I have some experience in handling latitudes and longitudes in software.

You'll save yourself a lot of trouble by ditching the text columns which store the E/W and N/S parts of the longitude and latitude, and use *signed* numbers instead. The standard (astronomical) convention is that south latitudes and west longitudes are negative. Then you only need two columns: one for latitude, and one for longitude.

You're going to use the latitudes and longitudes in calculations and in comparisons, so you're inevitably going to have to apply a sign to those floating-point numbers anyway. Let the database handle that.

For example, how would you sort your table by longitude, from east to west, using your current schema and in a single query? I don't think you can. But by dropping the E/W column and making longitude a signed value, it's easy to do.

Likewise, how do you select locations within a range of latitudes which includes the Prime Meridian, using a single query? It's possible, but kludgy, and you'll need separate queries depending on whether you expect your search range to include only east longitudes, only west longitudes, or some of both. But by making longitude a signed value and dropping the E/W column, it becomes a single, simple range query.

I hope that the rest of your schema isn't going to be as muddle-headed.

Why would anyone manage spatial data on MS SQL Server? Informix and one or two others do it well already. I guess to those that only have hammers every problem looks like a nail...

Post an article on a feisty, tech-savvy website, telling everyone how difficult a certain job is. Wait for the most convincing "Oh, that's easy" replies to come in, then call their bluffs. If you judge things right, you should be able to get at least one person with enough experience sufficiently riled that they'll show you exactly how to solve the problem, probably using open-source tools, just to prove you wrong...

Buy them a pint or two after the work's done, to show there's no hard feelings, and keep a note of their contact details for the next time you find yourself relying on dubious vapourware for the delivery of a project.

.. you're telling me, that in order to incorporate proper location handling in my data base I must

1) Buy a new server to run

2) A new OS

3) To run MSSQL2008?

Would it not be cheaper to just buy a good geo-calculation engine and use stored procedures to access it?

I did this years ago in order to analyse house sales and estate agent market shares. We used MySQL and a combination of Perl (for off-line) and PHP (for on-line). I think I still have the database we put together of all the town and postcode locations in the UK in National Grid format. There is a formual to convert to Lat/Long, although it is complex but then it would only need to be run once. Might take 3 seconds or so!

Query results were cached so that 1996 hardware didn't have to sweat too hard digging up answers to "How many estate agents are there within an x-mile radius of this postcode?" for popular areas, but the reality was that even ten years ago a £500 machine could answer such questions in real-time with a bit of careful programming.

But then, Micky-Mouse CS degrees and PHBs have pretty well killed off careful programming and now we're all expected to get excited about the possibility of the computing gods at MS (!) handing down something that was previously developed in a fairly lazy week by a single programmer using free tools.

Attributing locations to this type of data is fairly routine in the area I used to work. Almost address validation software should get you most of the way, the postal address file the rest. (And there are plenty of companies that will do the job for you at a reasonable price - you might also get the advice you appear to need on storing spatial co-ordinates.)

But your project depends on this, and you should have addressed it early as an obvious risk.

But, as you note, there are other significant risks to your plan - depending on an unreleased product arriving on time and behaving as you expect. As others have noted, there are other products that exist now. (Even if they don't do everything, you can deliverer something.)

Simply allowing extra time is not usually a good risk management strategy. At best there is an opportunity cost of not delivering early, at worst a competitor takes the market while you are waiting.

There are plenty of databases out there with spatial capabilities (Oracle and PostGIS, for example), gazetteers of geocoded place names, geocoded postcode databases, tools for converting between co-ordinate systems and projecting maps accordingly. If you have an urgent project, shouldn't you be using the tools that are presently available so that you can get to work? You can hardly blame Microsoft for your own unwillingness to choose a product that's actually available. Or is there another reason for your project's slipping from which attention can conveniently be diverted?

Geotrans

http://earth-info.nga.mil/GandG/geotrans/

can be scripted...

I'm surprised to read someone proposing to store longitude/latitude data as unsigned numbers and cute little N-S-E-W codes.

Far simpler to use signed numbers following the convention that north and east are positive, south and west are negative -- a well-established convention, btw.

Google Maps accepts such arguments, in fact. Try entering 25.345,97.4 and see where you end up. In fact, by using successive approximations of increasing precision it's possible to use Google maps to pinpoint a location within a few feet. "Where do you live?" "At 48.7732, -143.4775."

1. http://extranet.mapinfo.com/products/Overview.cfm?productid=1141

2. http://reference.mapinfo.com/#spatialware

Been around for many years, solid, robust, predates Oracle 8. Works on pretty muc all SQL Server versions providing a range of stored procedures and functions all accessible via SQL. Includes British National Grid coordinates, and transformation to geometric features.

Your problem would be solved by running a simple SQL function.

"For years now, in our application, we have been collecting data that already contains a spatial element"

I am quite frankly incredulous that you have to wait for a new OS and new database design to do this, why not just define a special data type: latitude, longitude, date. Any competent final year computing science graduate could do as much. I believe you are suffering from Microsoft-itise, after years of using Windows, you have lost the capacity for original thought, now where do I click .. :)

http://www.thescripts.com/forum/thread522414.html