 |
|
|
|
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 09/03/2011 05:11 PM, Darren New wrote:
> 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.
My satnav device cannot accurately determine which motorway I'm on.
Given that a six-lane motorway isn't exactly small, this implies a very
large inaccuracy in the GPS location.
(Of course, I suppose they may have turned off the encryption, but that
doesn't mean that a specific device knows about this fact...)
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Invisible wrote:
> On 09/03/2011 05:11 PM, Darren New wrote:
>
>> 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.
>
> My satnav device cannot accurately determine which motorway I'm on.
Yeah, and your calculator can't run DOOM. We already know you make bad
technology purchase choices. ;-)
> Given that a six-lane motorway isn't exactly small, this implies a very
> large inaccuracy in the GPS location.
No, it implies a crappy GPS receiver.
Plus, GPS while you're moving is somewhat harder to get accurate than GPS
while you're stationary. The way you get millimeter precision even when the
encryption is turned on is to leave the receiver in the same place for a
couple of days, integrating the position samples.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
>> My satnav device cannot accurately determine which motorway I'm on.
>
> Yeah, and your calculator can't run DOOM. We already know you make bad
> technology purchase choices. ;-)
Last time I checked, TomTom is the leading brand.
>> Given that a six-lane motorway isn't exactly small, this implies a
>> very large inaccuracy in the GPS location.
>
> No, it implies a crappy GPS receiver.
My dad has a crappy GPS receiver. It takes approximately 30 minutes to
locate a GPS satelite. Mine, on the other hand, requires nearer to 30
seconds...
> Plus, GPS while you're moving is somewhat harder to get accurate than
> GPS while you're stationary. The way you get millimeter precision even
> when the encryption is turned on is to leave the receiver in the same
> place for a couple of days, integrating the position samples.
Just because I'm on a motorway doesn't mean I'm *moving*! :-P
--
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Orchid XP v8 wrote:
>>> My satnav device cannot accurately determine which motorway I'm on.
>>
>> Yeah, and your calculator can't run DOOM. We already know you make bad
>> technology purchase choices. ;-)
>
> Last time I checked, TomTom is the leading brand.
Sometimes, for a few minutes, a GPS receiver will be wrong, I've noticed.
I'm assuming it happens when it picks up on a satellite for which it doesn't
have ephemeris or something. It usually corrects itself after a minute or less.
Of course, a GPS built into a car is going to assume the car is on one of
the roads on the map, which can also throw things off.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
> Of course, a GPS built into a car is going to assume the car is on one
> of the roads on the map, which can also throw things off.
More precisely, it assumes that you're on the road it's telling you to
drive down, until you get X number of yards away from that road, at
which point the location indicator suddenly snaps to the road that
you're *actually* on.
Some of this might be the inaccuracy of the road maps, but even at low
speed it seems to take a very long time to figure out where you actually
are, which leads me to think that most of the time it's guessing,
because the GPS fix isn't very accurate.
--
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Orchid XP v8 wrote:
> because the GPS fix isn't very accurate.
Yes, that's another behavior. I don't think it's because the GPS fix is
inherently inaccurate, but more that it's not especially reliable. You
wouldn't want your navigation to turn off every time you went through a
tunnel, either.
--
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/9/2011 2:57 PM, Darren New wrote:
> Tom Austin wrote:
>> I see - the primary key thing should prevent the 'error'.
>
> Yeah. OK, imagine a database of oh receipts, say.
>
> If the primary key is some arbitrary integer, it's possible to get the
> same receipt into the table more than once, which will screw up your
> accounting.
>
> If the primary key is the cash register number + timestamp to the
> second, the only way you get a collision is to make two sales on the
> same register within one second of each other, which probably *should*
> throw some sort of error. It also makes it easy to print on the paper
> receipt all you need to know to get the exact record.
>
> Whether you can do this sort of thing with any particular table of yours
> is another question.
>
This makes sense.
I think what still boggles my mind is how to do a double primary key /
double foreign key relationship.
In the case of the receipts, what relationships do you create to have a
table reference a particular receipt?
>
> Or not updating them at all, if you pass in the same data twice by mistake.
>
> Look at a CSV file full of receipts like above. Reading each row is
> going to clobber the record already there. If you use an auto-increment
> ID, you'll wind up with 200 reciepts if you read a 100-row CSV in twice.
>
So, the primary key relationship keeps the data from being inserted by
default.
> IME, a large part of database fuckage is either operator error (like
> loading stuff twice) or simple programming errors (like debiting
> everyone's account instead of just the account of the person you should
> have debited). The idea of making idempotent updates and keeping
> write-once historical records came about because that makes these things
> easy to fix.
>
And the trick is to have the database strong enough to prevent such
errors from causing trouble in the first place. You spend your time
setting it up right or making fixes for problems that crop up.
>
>> pretty much - the owner didn't want to invest the time to make it
>> better and gain the rewards. new owner - new ideas
>
> Cool. Let's hope it stays that way. :-)
>
I think it will - but the new push is to be very dynamic - so dynamic
that taking the time to set up a proper database is not possible. From
one extreme to another. I guess I rather take this extreme - it means
we are moving someplace.
>> I think I have run into this already
>> select A from b where A.ID in ( select ID from C)
>
> No, that's cool. Well, not that exact query, but that sort of thing. The
> trick is it's all in SQL. You aren't looping inside the PHP code or
> Javascript code or whatever you're using to submit that code to the
> database engine.
>
So what's not cool about the query - should something like that be avoided?
>> in its simplest form leaving out fluff like owners and such:
>>
>> table poles: (each pole)
>> ID, GPS Location
>> 1 entry for each pole
>>
>> table connections: (how poles are connected)
>> ID, PoleID1, PoleID2, Distance
>> 1 entry for each pole-pole joining (1 for each 'set' of cables)
>
> See, this is what I mean. There's no need for an ID on this table. You
> don't want two rows, one where it's
>
> (27, Pole 3, Pole 4, 500 feet)
> (29, Pole 3, Pole 4, 800 feet)
>
> You get that, your data is screwed, and you know it, but you can't fix it.
>
> A connection between two poles is defined by the two poles it's
> connecting. The distance and what types of cables go through are
> dependent data.
>
> table connections:
> PoleID1, PoleID2, Distance (pk=PoleID1+PokeID2)
>
> table cables:
> PoleID1, PoleID2, height, cable type. (pk=PoleID1+PoleID2+cable type)
>
Does this keep from having this case?
PoleID1 PoleID2
1 2
2 1
Does keying prevent this or does it have to be taken care of some other way?
>> table attachments: (cable attached to poles & height)
>> ID, PoleID, Height
>> multiple entries per pole
>> 1 for each attachment
>
> Is there a row in this table for a pole that has no cables attached?
> I.e., does this represent "Cables attached to this pole" or "places
> where it's posslbe to attach a cable"?
>
actual cables attached - so a pole can have 0 attachments.
>> table midspans: (lowest height between poles for each cable)
>> ID, AttachmentID1, AttachmentID2, Height, ConnectionID(redundant)
>> multiple midspans for each 'connection'
>> 1 midspan per cable over the connection
>> essentially 1 midspan for each connection on a pair of poles
>
>
> Consider a slightly different layout: a span.
>
> Cable type, poleID1, attachmenthieght1, poleID2, attachmentheight2,
> midspanheight.
>
> Then poleid1+poleid2+cabletype becomes your primary key.
>
Ok, I was confused but now I think I see clearly. Instead of
dereferencing to attachmentID like I did, one just uses the (poleID) and
(attachment height) as the key from the attachments table.
> That gives you poles, distances between poles, and spans of cable. If
> you need to know connectivity, you could have a table that lists the
> first pole and last pole along with a collection of spans.
>
> I'm not saying you should do it this way. I'm just pointing out there
> are lots of ways to organize the data, and you might want to think of a
> drastic simplification in the structure that will still give you
> everything you need with a bit of SQL processing time.
>
No, but it gives a lot of food for thought.
I don't think you have proposed anything much different than what we
have - it just rids the tables if ID's that can cause more trouble than
they are worth.
> What you want is a structure where it becomes impossible to have only
> some of the information you need. Just like you couldn't have a GPS
> location without a pole, you don't want to have (say) a connection
> height on a pole without a mispan, or a connection height on one pole
> but not on the other pole.
>
yes, and this is done in the schema without the need for any real code
at this point.
I like the concept - make it robust enough that it can't get fouled up -
no matter what you try to do.
I don't know if we will get there, but I know we will make a few steps
in that direction.
>> The rest is pretty much straight forward - objects and properties
>> associated with each of the above.
>>
>> As you can see, there are some circular references going on
>> (attachments and midspans connect poles
>
> Yeah. That means you're doing it wrong, if it's actually *circular*.
>
> It's often easier to draw stuff as pictures on paper when you're
> designing things.
>
> Draw a box for each table, and an arrow each time there's a foreign key.
>
> Any box with no outgoing arrows is a fundamental real-world entity. In
> this case, for example, the poles better be in the real world, because
> they're not attached to anything else.
>
Now that I think about it more, I don't think I have actual circular
going on.
I do have the case where I get connections from the connection table
(pole1, pole2, distance) and I can also get the same information from
the attachments table (poleID, height) and midspans( attachment1,
attachment2) table.
The touble arises if the attachments/midspan tables create a connection
that is not defined int he connections table.
>> You have presented some new ideas that 'extend' my thinking and
>> stretch it. I do not know how much will make it in to the system, but
>> it is good food for thought.
>
> It takes practice. I'm just trying to explain how I initially approach
> things, and why. Of course each situation varies.
>
I have the knowledge on how to make a database work - even properly in
the sense that it is clean. but the other 10% is the icing that makes
things really work well and keep working well with less effort.
>> I agree with it to a point - mainly for ease of working with the data,
>> but not at the sacrifice of usability of the data. The drive is so
>> hard that there is talk about flattening the database to make it
>> 'simpler' and that adding more tables makes it more complex.
>
> Add lots of tables. Create "simpler" tables by creating views. When you
> find in six months that you need new functionality, create other views
> that expose the data organized in the new way.
>
OK, a question
I have a table with a descriptor - let's say pole color.
Right now it is just that - a descriptor, but is to be presented to the
user as a list of distinct items to choose from.
I can vaguely see in the future that I will want to group the color into
families (maybe reds, blues, greens), but because of time I can't define
what they are so I will ignore them for now.
At some point in the future the need comes up to define the families and
add them to the DB.
How would one go about this kind of 'building' and how would keys play a
role.
Would I simply specify the column as 'text' and just put the color into
it for now. Then pull from a distinct query to get the list of
available colors. Then in the future make a new table that uses this
column as a foreign key that gives the families. but then the distinct
query is no longer needed as the new table has the distinct values.
Or should I go ahead and create the additional table so that it can
provide the colors with a 1 stop place to add a new one? Then as the
families are needed just add them to the table?
I know this might not be 100% clear, but how would you go about this?
>> Additionally some of our issue is that when we finish a job for a
>> client we do not need to reference the data any more. Rarely have we
>> found ourselves going back to old data. How much effort should be put
>> into a system that supports saving everything and making it accessible
>> when one does not refer back to it?
>
> Another good reason to avoid using auto-increment IDs. :-) You can have
> a "job" table that gives you top-level pointers for a job to each thing
> involved in a job. (I.e., if a Job consisted of a bunch of connections
> on a bunch of poles, you'd have a Job ID and a collection of poles. The
> collection of poles would give you the collection of cables. Etc.) You
> could then write out (as SQL even) all the rows associated with a given
> job, then delete those rows. If you ever needed them back, run that SQL
> back into the database. (Altho, honestly, most database engines with
> autoincrement IDs can handle actually inserting records with a given ID
> and not generating a new one.)
>
yes, I have seen this with Access - updates fail, but inserts work.
> But honestly, I don't know how big your database is, but a database can
> store millions of rows before you even start to see anything getting
> slow. It's not a case of "do we need to keep it around" as it is "do we
> need to throw it away?" But if you manage to build the data in a way
> that each chunk you might want to archive (i.e., a job) has a record
> that lets you get the whole collection of everything associated with
> that record, then you don't have to worry about the actual
> archive-and-delete part of the code until there's a reason to. Then you
> can write that code, confident that you know what you need to store out.
>
We won't get very big. We survey maybe 5000+- poles in a given year.
>> Thanks for your feedback - it is very helpful.
>
> I like helping,b ecause writing the thoughts out clearly enough to
> explain to someone else always clarifies things to me too.
>
Yes, I like helping others as well for much the same reason.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Tom Austin wrote:
>> If the primary key is the cash register number + timestamp to the
> In the case of the receipts, what relationships do you create to have a
> table reference a particular receipt?
You just store the same columns.
Let's say you had a table that recorded customer service records about a
particular receipt.
create table receipts (
register INT NOT NULL,
issued TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP, -- MySql sucks
amount decimal(10,2) NOT NULL,
clerk INT NOT NULL,
....
primary key (register, issued),
FOREIGN KEY (clerk) references clerks(ID),
...);
create table complaints (
receipt_register INT NOT NULL,
receipt_issued TIMESTAMP NOT NULL,
csr_talking INT not null,
complaint_answered TIMESTAMP NOT NULL,
resolution blob,
previous_call_csr INT,
previous_answered timestamp,
primary key (csr_talking, complaint_answered),
foreign key (receipt_register, receipt_issed)
references receipts(register, issued),
foreign key (previous_call_csr, previous_answered)
references complaints(csr_talking, complaint_answered)
);
So, modulo the fact that MySql dicks with timestamps in tables depending on
how many timestamps are in the table, and disregarding the fact that MySql
"NOT NULL" doesn't prevent you from inserting records with NULLs, we can
look at this structure:
A receipt is identified by the register number it was issued at and the time
it was issued. There's also other details, like the clerk that was logged
in, the amount of the sale, and so on.
When the phone rings and someone complains, the CSR creates a new complaint
record. This record is identified by the CSR taking the call and when they
took the call. The complaint is about a particular receipt, identified by
the register the receipt came from and the time that receipt was issued.
This may be a follow-up call, in which case the CSR has the previous calls
about this on the screen and may point out that this is a follow-up call
from a particular previous complaint, identified by *that* csr at *that* time.
Does that clarify?
>> Or not updating them at all, if you pass in the same data twice by
>> mistake.
>>
>> Look at a CSV file full of receipts like above. Reading each row is
>> going to clobber the record already there. If you use an auto-increment
>> ID, you'll wind up with 200 reciepts if you read a 100-row CSV in twice.
>>
>
> So, the primary key relationship keeps the data from being inserted by
> default.
Or at least you know when something goes wrong, instead of happily inserting
two copies of every credit card, or pole, or whatever.
>> IME, a large part of database fuckage is either operator error (like
>> loading stuff twice) or simple programming errors (like debiting
>> everyone's account instead of just the account of the person you should
>> have debited). The idea of making idempotent updates and keeping
>> write-once historical records came about because that makes these things
>> easy to fix.
>>
>
> And the trick is to have the database strong enough to prevent such
> errors from causing trouble in the first place. You spend your time
> setting it up right or making fixes for problems that crop up.
Precisely. That's exactly the "C" part of the database. But if you *do*
screw up, being able to go back and fix it and send out the next set of
bills with a credit for the screw-up is helpful compared to going "I don't
know, boss, all the balances are wrong and there's no way to know who we
billed twice for last week."
Your database sounds somewhat different, but to the extent you can actually
figure out by looking at the database what happened to change it, the easier
it's going to be to fix problems caused by going fast. Just something to
keep in mind.
> I think it will - but the new push is to be very dynamic - so dynamic
> that taking the time to set up a proper database is not possible. From
> one extreme to another. I guess I rather take this extreme - it means
> we are moving someplace.
Well, good luck with that. Some people can make that work, but I strongly
suspect your boss is just a jerk. :-)
>>> I think I have run into this already
>>> select A from b where A.ID in ( select ID from C)
>>
>> No, that's cool. Well, not that exact query, but that sort of thing. The
>> trick is it's all in SQL. You aren't looping inside the PHP code or
>> Javascript code or whatever you're using to submit that code to the
>> database engine.
>>
>
> So what's not cool about the query - should something like that be avoided?
No, the query is cool. It's fine. (I mean, other than being syntactically
wrong. :-) What you probably wanted to write is
select A from B where B.ID in (select ID from C)
but a better way of writing that is
select A from B, C where B.ID = C.ID
The nice thing about SQL is that both those statements will probably do
exactly the same thing inside the server.
The thing I'm trying to get you to avoid is putting a query inside a loop
that doesn't involve user interaction. I.e., you shouldn't have a for loop
or while loop in your code with a SQL select statement inside the body of
the for loop unless there's other I/O to read non-database files or take
clicks from the GUI or something. You should never need to do something like
$first_result = query_mysql("SELECT name from CUSTOMERS")
foreach ($name in $first_result)
$second_result = query_mysql(
"SELECT account from ACCOUNTS where ACCOUNTS.NAME=" + $name)
Instead, that should be one query:
select name, account from customers, accounts
where customer.name=accounts.name
> Does this keep from having this case?
> PoleID1 PoleID2
> 1 2
> 2 1
Well, technically not.
> Does keying prevent this or does it have to be taken care of some other
> way?
To prevent this, you'd probably have to use a trigger. The "consistency" in
a database is supposed to be done by having a trigger reject the insert if
the poles in a different order are already there.
That's why you have to take time to think about it: If you just type it in
as fast as you can think, you wind up with something like this and then
you're screwed. :-)
> Ok, I was confused but now I think I see clearly. Instead of
> dereferencing to attachmentID like I did, one just uses the (poleID) and
> (attachment height) as the key from the attachments table.
Yes, you can do it that way.
> I don't think you have proposed anything much different than what we
> have - it just rids the tables if ID's that can cause more trouble than
> they are worth.
If you can manage that, it makes things more clear.
For one thing, for example, if you identify cables by (for example) the
poles they're run between in the GUI, you don't have to then go look up the
ID from that information in order to use it elsewhere. If you find that
you're never actually referencing the ID in queries when you're not also
referencing the pole and height, chances are that ID can be replaced by the
pole and height.
> I like the concept - make it robust enough that it can't get fouled up -
> no matter what you try to do.
That's the goal, yes. That's the idea behind the "C" part of ACID. When you
have a monsterous database that has to live for decades, it's worth spending
weeks or months figuring that bit out. How much it's worth to *you* depends
on how easy it is to clean up mistakes. If each job essentially starts
over, it might not be worth putting too much time in up front, if you can
just (say) re-import the excel sheets for where the poles are on the third
job if you find the second job messed stuff up too bad.
> Now that I think about it more, I don't think I have actual circular
> going on.
It didn't sound to me like you did.
> The touble arises if the attachments/midspan tables create a connection
> that is not defined int he connections table.
Exactly. There really isn't an attachment without a midspan or a midspan
without an attachment, nor is there an attachment on just one pole.
> How would one go about this kind of 'building' and how would keys play a
> role.
I would say "color" is an attribute that deserves its own table. Assuming
that all colors are equal and don't need any calculations and such, I'd
store the colors in a table with
ID int, ColorName varchar(20)
or something like that.
This lets you change the color names (boss wants them all in caps) or later
add internationalized names (now you have ID, ColorName, Language in the
table), etc. Plus, it's more space efficient to store an ID number than a
color name everywhere. It also keeps people from misspelling a color and you
wind up having both Green and Gren poles.
When adding family support, I would then add a second table that holds the
allowable families. If each color was in only one family, I would put the ID
of the family in the colors table. Otherwise, I would have a third table
with Color.ID and Family.ID.
> Would I simply specify the column as 'text' and just put the color into
> it for now. Then pull from a distinct query to get the list of
> available colors. Then in the future make a new table that uses this
> column as a foreign key that gives the families. but then the distinct
> query is no longer needed as the new table has the distinct values.
> Or should I go ahead and create the additional table so that it can
> provide the colors with a 1 stop place to add a new one? Then as the
> families are needed just add them to the table?
>
>
> I know this might not be 100% clear, but how would you go about this?
Generally, if you have the same thing repeated many times in a table's
column from a small selection of values, but it's *not* a foreign key,
you've probably done something wrong. You probably want a table with that
set of values as its primary key.
You can certainly start out with the color in its own column, then move
things around later when you find it's problematic. You might have some
processing time, taking the system offline for half an hour while you
rearrange the tables and such, but I'm guessing (given you were using Access
before) that this wouldn't be a problem every month or two. :-)
You can even start out with the color as text in the column, later add
another table that has the colors as the primary key and add the FK
constraint, and after that start putting the different translations or
families or whatever on that second column. I.e., where I refered to the
"color.ID" above, you could just use the text of the color name as the ID,
then have the translation or the family or whatever keyed off of that. Only
after space or speed becomes a problem do you really need to worry about
making it into an integer ID or some such.
> We won't get very big. We survey maybe 5000+- poles in a given year.
You should have zero concern about performance. Even a pretty weak machine
should be able to do something with everything in your entire database
several times a minute, let alone individual small groups of stuff.
--
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
|
|
| |
| |
|
|
|
|
| |
| |
|
|
> More precisely, it assumes that you're on the road it's telling you to
> drive down, until you get X number of yards away from that road, at
> which point the location indicator suddenly snaps to the road that
> you're *actually* on.
Indeed, whilst moving the accuracy is not good enough to determine
whether you're on the motorway or on the slip-road 5 metres to the side
of it, so it assumes you're following the route (if any) or staying on
the road (if no route planned). Once the receiver knows, with a
specific degree of certainty, that you are on a different road, it will
snap you there on the map.
> Some of this might be the inaccuracy of the road maps, but even at low
> speed it seems to take a very long time to figure out where you actually
> are, which leads me to think that most of the time it's guessing,
> because the GPS fix isn't very accurate.
Once you're more than 10 metres or so away from the route it should snap
instantly, if it doesn't then you've either got a low signal (GPS
antenna positioning?) or a rubbish GPS receiver.
BTW the ones built into the car are far superior as the GPS antenna is
placed in the best spot and it gets input from the steering angle and
wheel speed (so it still works underground and from the second you turn
on the car). If you've used one you'll never go back to the portable
add-on ones.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
On 11/03/2011 08:43 AM, scott wrote:
>> More precisely, it assumes that you're on the road it's telling you to
>> drive down, until you get X number of yards away from that road, at
>> which point the location indicator suddenly snaps to the road that
>> you're *actually* on.
>
> Indeed, whilst moving the accuracy is not good enough to determine
> whether you're on the motorway or on the slip-road 5 metres to the side
> of it
Which is what I said. GPS isn't very accurate.
> Once you're more than 10 metres or so away from the route it should snap
> instantly
I haven't measured whether it's 10 meters, but yeah, that's
approximately what it does.
> BTW the ones built into the car are far superior
And outside of everybody's price range.
> If you've used one you'll never go back to the portable add-on ones.
Well, you will, because nobody has £80,000 for a car that has one. :-P
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
|
|
