Le 09/03/2011 10:29, Invisible a écrit :
> 
>>> GPS is only accurate to a few hundred yards of course,
>>
>> Uh, no. What are you smoking?
> 
> Hey, the satellites are 20,000 km up in the sky. It's astonishing that
> GPS can even figure out which city your in, never mind fixing your
> location to a few hundred yards.

GPS computation is accurate, with military access, to less than a yard.
One of its military purpose is guiding rocket/missile between buildings,
with a flight near the ground as close as possible to avoid radar detection.

Civil usage of GPS is accurate to 30 yards/meters.

Some area are covered with DGPS, an additional signal on radio which
provided the offsets measured between the GPS signal and the actual
location of reference points (so that a civil application can compensate
the delta...)

Remember that GPS is operated by the army of the USA (or whatever you
name it), and they can switch it down whenever they want. (or rather,
keeping it on only for the happy fews with the unscrambling devices...)

-- 
Software is like dirt - it costs time and money to change it and move it
around.

Just because you can't see it, it doesn't weigh anything,
and you can't drill a hole in it and stick a rivet into it doesn't mean
it's free.

Post a reply to this message

> GPS computation is accurate, with military access, to less than a yard.

So I've heard. This is only useful if you're in the military. ;-)

> Civil usage of GPS is accurate to 30 yards/meters.

That sounds more like it.

> Remember that GPS is operated by the army of the USA (or whatever you
> name it), and they can switch it down whenever they want. (or rather,
> keeping it on only for the happy fews with the unscrambling devices...)

*Other global positioning systems are available. ;-)

Post a reply to this message

> GPS computation is accurate, with military access, to less than a yard.
> One of its military purpose is guiding rocket/missile between buildings,
> with a flight near the ground as close as possible to avoid radar detection.
>
> Civil usage of GPS is accurate to 30 yards/meters.

I thought they removed the civil "randomness" in the GPS signal ages ago?

Post a reply to this message

On 3/8/2011 4:24 PM, Darren New wrote:
> Orchid XP v8 wrote:
>> Pro-tip: Don't use real-world things as primary keys. Because you know
>> what? Real-world things have this nasty habit of *changing*. You don't
>> ever want your primary key to change. This is why databases up and
>> down the country refer to Mr Smith not as Mr Smith but as customer
>> #238523.
>
> I disagree. The only time you should use automatically-generated primary
> keys is when there is no attribute of the item in the table that is
> guaranteed not to change.
>
> A sales receipt whose primary key is the cash register identifier and
> the time to the second when the receipt was printed is an excellent PK
> for a sales receipt. A record recording what song was playing on the
> radio has the obvious PK of location+timestamp+frequency. A road
> intersection has a fine PK of the GPS coordinates of the intersection.
>
> The problem is that people are changing all the time, so it's very hard
> to have a customer record that persists with a PK that makes sense.
>

I have to agree with Darren - A primary key is not necessarily an 
auto-generated value (random or auto-increment).  It only has to be 
unique and not change.

For me, the *easiest* way to guarantee no change is to have an 
auto-generated integer.  Can it be done better, yes.  But without the 
experience of how to look at data, I run too high of a chance that I 
chose the wrong key and it needs changed.

>> Use randomly-generated integers with no real-world meaning as PKs and
>> you'll have fewer problems if/when the thing the key refers to changes
>> in some way.
>
> The drawback of this is if you get a reference to the wrong value,
> you'll never notice.
>

I see the point in having the key have more meaning.  It reduces the 
mental referencing that must go on to understand the data without a 
proper interface.


>> I would suggest not setting up an Oracle database, for example. It
>> sounds like it would be utter overkill here.
>
> Yes. One of the free databases (Postgresql or mysql) or (on windows) SQL
> Server (Express, perhaps) would do fine. You'd probably find the most
> free help with MySql, but it's kind of messy to run on Windows, or at
> least was last I checked. SQL Server Express would *probably* have
> enough performance for you, given you were using Access.
>


I'm on this page fully.  I prefer to stay away from MS database 
solutions.  Not that they are not good, but they are just too ... well 
just 'too'.

Post a reply to this message

On 3/8/2011 5:03 PM, Orchid XP v8 wrote:
>
> Until the GPS coordinates turn out to have been inaccurate. GPS is only
> accurate to a few hundred yards of course, so different people will
> probably get different measurements depending on where they're standing.
> That's probably not a great idea. (Unless you explicitly round the
> coordinates up to some number that makes the size of the actual
> intersection negligable.)
>

I just ran across this where the GPS coordinate was wrong in a 
government data set - likely due to a typo.

Post a reply to this message

Invisible wrote:
> The point is *totally* to have a PK that never changes,

Well, yes, that's the desire. But you can only achieve that by having 
meaningless PKs on everything, which I find to be undesirable if a 
meaningful PK can be found.

If your database is likely to be big enough that an incorrect initial PK 
is 
going to be catastrophic, then you can go for meaningless PKs.

However, in Tom's case, the GPS coordinates being wrong means *everything
* 
is wrong for that pole, so changing the GPS coordinates might as well be 

treated as deleting the pole in the wrong place and creating the pole in 
the 
right place.

> OK, well I'm gonna have to say they must be doing something more precis
e 
> than "stand in the general vicinity of the intersection, take down the 

> GPS coordinates, and we'll just use those".

I don't know.

>>> GPS is only accurate to a few hundred yards of course,
>>
>> Uh, no. What are you smoking?
> 
> Hey, the satellites are 20,000 km up in the sky. It's astonishing that 

> GPS can even figure out which city your in, never mind fixing your 
> location to a few hundred yards.

"""
The phase difference error in the normal GPS amounts to 2–3 metre
s (6.6–9.8 
ft) of ambiguity. CPGPS working to within 1% of perfect transition reduce
s 
this error to 3 centimeters (1.2 in) of ambiguity. By eliminating this er
ror 
source, CPGPS coupled with DGPS normally realizes between 20–30 c
entimetres 
(7.9–12 in) of absolute accuracy.
[...]
However, 2 millimeter accuracy requires measuring the total phase—
the number 
of waves times the wavelength plus the fractional wavelength, which requi
res 
specially equipped receivers. This method has many surveying applications
.
"""

> So, what, you pick a GPS coordinate at random that's approximately in 
> the right area? (So long as its unambiguously not near any *other* 
> intersection.)

There's a certain level of error of a couple of meters. You pick a standa
rd 
place to measure from (such as the center of the manhole that you're 
interested in) and call that your location.

When you go back to find the intersection, are the GPS coordinates given 

good enough that someone else can tell what intersection you're talking a
bout?

It should be noted, btw, that they're not really interested in the 
intersections, but in the wires running under the roads. The same PK can 
be 
used to track the wires running under or over not-roads as well.

>> The point of using GPS coordinates is that they are guaranteed not to
>> change.
> 
> Sure. Unless the GPS coordinate system moves, which can't possibly 
> happen. Oh, wait...

Have latitude and longitude changed since we invented RDBMs?  I don't thi
nk so.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message

Le_Forgeron wrote:
> Civil usage of GPS is accurate to 30 yards/meters.

I think they turned off the encryption of the low bits back in Clinton's 
timeframe, when other countries started threatening to launch their own 
satellites. I think civilian GPS is quite accurate now.

Especially if you're measuring something that isn't moving, and you can let 
the device sit for a day, you can get down in the milimeter range.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message

On 3/8/2011 5:34 PM, Darren New wrote:
> Is it the same telephone pole if I dig it up and plant it somewhere
> else? Sure, maybe. But am I ever going to need to know that if my job
> involves routing cables? Probably not. Am I going to need to know that
> if my job involves replacing them when they wear out? Most definitely.
>
> How often do you replace the little tags on the side of the pole that
> give the pole number? Not very often. Is there any reason if that ID tag
> gets replaced that you can't just update in the database everyplace that
> appears? Probably not.
>

Poles move all of the time, but they are considered 'new' poles and not 
related to the old poles :-)  pole tags usually do not change, but if 
the tag is missing on one day, it could have a new one the next.  In 
fact, the power companies here tag their poles according to geographical 
coordinates - likely they use it as their primary key.  But I have seen 
some wrong tags that will likely get changed.

>> But sure, especially if you're recording historical data (which is of
>> course especially unlikely to change), there's no real need for a
>> synthetic key.
>
> I contend that most primary data in the database should be historical.
>
>> Until the GPS coordinates turn out to have been inaccurate.
>
> Then you fix it. There never *was* an intersection with those GPS
> coordinates. So you fix it. The point is to avoid collisions, not to
> necessarily have a PK that never changes.
>
> Given that the BIGGEST TRANSACTIONAL DATABASE IN THE WORLD (*)
> identifies street intersections with GPS coordinates, I'm gonna have to
> go with GPS coordinates on this one. ;-)
>
>
> Assuming those don't change, they're probably based on geometry also.
> The point of using GPS coordinates is that they are guaranteed not to
> change. If you enter them in the database wrong, then you have bad data,
> but that's not because the intersection moved.
>

I will stay out of this one....  I think I will stick with auto number 
fields for now - just to keep my head on straight.

Post a reply to this message

On 3/8/2011 6:22 PM, Darren New wrote:
> Tom Austin wrote:
>> I think most of our slow downs is currently in the applications that
>> access it. Basically inefficient routines and use of objects. A
>> required approach when coding time as of the essence.
>
> Yeah, but now you hve some luxury to do it right, I'd wager, since you
> already have a working system.
>

Exactly - but just how much time we can allocate to it is yet to be 
seen.  But I think I can push for more time now because it will cost 
much more time later.  But this only works to a point.

>> Does it make sense to set up a transaction wrapper on even simple
>> single updates?
>
> If you're modifying more than one row at a time, yes. In most database
> engines, each statement you submit becomes its own transaction. This
> isn't true on all types of MySql tables. For example, if you have a
> MyISAM table and you say "update blah set x = 50 where y = 3" and it
> gets interrupted half way, you might update only half the rows where y = 3.
>
> A single append of a single row is atomic. You're not going to corrupt
> the database per se. Just the consistency of the data inside the database.
>
> So, yes, there's really so little overhead in using a transaction around
> anything that affects more than one row that it makes no sense not to do
> so.
>

Makes sense - on anything that affects more than 1 row in 1 table is OK. 
  Transact wrapper everything else :-)

>>> That said, it's surprisingly common that if you structure your queries
>>> right, you can make every operation essentially an "append to this
>>> table", followed by an eventual "update that table in a way that if it
>>> fails, we can reconstruct that table entirely from scratch". Think
>>> Journal -> General Ledger.
>>>
>>
>> OK, I've been dealing with databases for 15 years now - on mostly a
>> low level. that just went right over my head. Makes sense, but I have
>> no concept of how it is implemented.
>
> I've usually done financial databases. So, basically, all the history of
> everything in and out of the database is recorded in one set of tables,
> while the current state is in a second set of tables.
>
> Like your checkbook: Every check you write gets a row appended, and you
> never change that row except to set a flag that you never unset (the
> "this check cleared" flag). But there's another table that lists each
> account and says how much money is in it. In theory, you can construct
> the latter from the former, if you keep all your old checkbook registers
> around.
>

Ok, makes sense - if you keep a 'transaction' history.  In the financial 
world - highly recommended.

I don't think we will have to go that far.

Our bigger problem is getting the data that is entered to be correct. 
Not just structurally correct, but actually correct.  This has to do 
with the interfaces that were assembled with too much haste.

>> for some reason I am hesitant to present the key as something that the
>> end user interacts with. Maybe I'm a control freak :-&
>
> No, there's almost never a reason to present the key that way. That
> doesn't make it less useful as a key. :-)
>

True.  I believe that if the system is built right the user will NEVER 
have to look at the raw data.  The interfaces should give the user all 
of the information that they need.  And if they need to much with raw 
data for some kind of reporting, the system can output some form of csv 
that the user can manipulate at will.

The keys can be almost anything and it not change the functionality of 
the database or how the user interacts with.  it does make it somewhat 
more readable for the person trying to figure out a problems that 
somehow got into the data.

>>>> OK, you want a job surveying utility poles :-) You have good insight.
>>> I've just done this dozens of times in the last couple decades.
>> what - survey telephone poles?
>
> No, I only did that once. :-) Design databases from scratch.
>
>> that is very correct. Our first stab was fast and mostly dirty. We
>> spent a lot of time to try to get the data right, but we missed some
>> pieces of data.
>
> That's not unusual. The trick is to have the database structured in a
> way you can add the missed data later without *changing* what's already
> there. The best way to do that is to primarily record into the database
> exactly what's outside the database, reflecting reality as closely as
> possible.
>

Yes, agreed.  Depending on how quickly the thing was built, it can be 
very difficult if a complete aspect was missed.  Also, one has to define 
*what* and *how* base rules before adding the data or you get a mess - 
which we are approaching.

>
>>> What about your current system is so broken that you can't afford the
>>> time to fix it?
>>>
>>
>> It has layers upon layers of fixes. The end users had to fight with it
>> to get it to put out data correctly. it was an ad-hoc rich system to
>> the point where I refused to make any more changes because of the risk
>> of data corruption/loss and our client getting crap for a deliverable.
>
> Been there done that. Along with "before I start, has this ever been
> backed up?" Answered by "No, it has never been reliable enough we could
> get a backup of the data." Followed by "Thanks, but I like my current
> job enough."
>

We are not quite that far :-)  But it is nice to be starting with trying 
to make a working system instead of forcing a broken one to continue to 
work.


>>> The other possibility is to hire someone who actually does this sort of
>>> stuff for a living for long enough to show you how to set up the basics
>>> of the database.
>>>
>>
>> It's a balance. Depending on our requirements it could take just as
>> much of our time working with the guy and doing it ourselves.
>
> This is true. I don't think it would take any less time, but you'd
> probably have a more robust system when you were done.
>

very true - worth considering

>> yes, one just has to pay attention and not set up things that break.
>> It could be that applications do not rely on the views (or other
>> non-common features) But then one could be crippling themselves.
>
> Yes. It's better to just say "if we need multiple archives of this
> database, we'll use the same engine."
>

I just have to get this through to the rest of the guys.

>> yes, I don't think it will take much of a machine. We have an old
>> server box (PIII 600 I think) that would likely do just that. Or I
>> might incorporate MySQL on our current file server (Linux) to simplify
>> things and use the old box for development.
>
> Yeah. And you'll likely want a replicated server as well.
>

Not a bad idea - but that can get a bit more complicated.  But I will 
take it as a strong suggestion.

>> Luckily none of them were damaging. Just annoying and cost a couple of
>> hours of everyone's time.
>
> Yeah, that's much better than the place catching on fire. :-)
>

We do have off-site backups :-)   Just 500GB in one big tarball tho :-(

Post a reply to this message

On 3/8/2011 4:31 PM, Darren New wrote:
> The absolute best way to make this work out, then, is to record
> "reality" in the database. Ignore how you're going to use the data while
> you're designing what's in the database (to the extent possible).
> Instead, simply record facts.
>
> What I'm trying to say is don't say "our calculations would be easier if
> our data was arranged like *this*." If you stick with the reality of
> what's out there, you will never run into the situation where you have
> to *change* data you already have in the database.
>
> I know this sounds kind of vague, and it can be hard to get away from it
> if you already have a model of something up and running and you know how
> that works.
>

I am realizing this - I believe that our current model is not yet too 
bad in this regard.  It basically reflects reality.  We just need to 
lock it down a bit more.

Your tips here will help us move forward more quickly.  We do get mired 
down in how to make the data usable.

I do need to consider usability somewhat to make sure that the data that 
is needed makes it into the system.

Post a reply to this message