Results 1 to 8 of 8

  1. Post
    #1

    mySQL increasing accuracy of data?

    So, I've grabbed a CSV of postcode data from GeoNames.

    Reformatted it into a query and dumping it into a table on mySQL with a float(10,6).

    Click image for larger version. 

Name:	locations.PNG 
Views:	63 
Size:	30.0 KB 
ID:	224910

    Looks like this. Note the highlighted value.

    Truncated the table and done this twice with the same result:
    Click image for larger version. 

Name:	whatthe.PNG 
Views:	60 
Size:	22.7 KB 
ID:	224911

    Where it the great googley-moogley is it getting that value from?

    This is the original text source:
    countryCode postCode placeName region regionCode adminName2 adminCode2 adminName3 adminCode3 latitude longitude accuracy
    fieldtype varchar(2) varchar(20) varchar(180) varchar(100) varchar(20) varchar(100) varchar(20) varchar(100) varchar(20) (wgs84) (wgs84) int(1)
    NZ 600 Blockhouse Bay Auckland E7 -36.9182 174.7002 4
    NZ 602 Kelston Auckland E7 -36.8982 174.6602 4
    NZ 604 Huia Auckland E7 -36.9983 174.5667 4
    NZ 610 Te Atatu South Auckland E7 -36.8647 174.6477 4

  2. Post
    #2
    Nevermind, found it. It's a base2 translation error:
    https://docs.python.org/release/2.5.1/tut/node16.html

    I'll have to switch to a decimal datatype.

  3. Post
    #3
    Unless you need to do math on those coordinates at the query level or are short on storage space just convert them to strings, imo.

  4. Post
    #4
    teelo7 wrote:
    Unless you need to do math on those coordinates at the query level or are short on storage space just convert them to strings, imo.
    Why, though?

    A 9,6 decimal based on my understanding will use around 6 bytes and a VARCHAR(11) (9+decimal point and negative sign) will use double that (12).

    And if something is going in that column that wouldn't work I'd want to know about it.

  5. Post
    #5
    Because in this day and age storage space doesn't matter anymore, especially if accuracy does.

    And if something is going in that column that wouldn't work I'd want to know about it.
    Not sure about mySQL - surely it would support that type of constraint?

  6. Post
    #6
    teelo7 wrote:
    Because in this day and age storage space doesn't matter anymore, especially if accuracy does.

    Not sure about mySQL - surely it would support that type of constraint?
    Storage space, no, but memory and data processing performance, yes.

    Unsure what you mean for the second part. What i mean is if I write a query that doesn't specify column names on an insert, and miss or add a comma, it will still do the insert if it's a varchar. Whereas it may not if it's a numeric only field. It's just an extra layer of notification if I screw up my code.

    If using generic containers were always the answer because data storage wasn't a problem, strongly typed programming languages wouldn't exist.

    Not that I like strongly typed languages, but people will regularly argue in favour of them.

  7. Post
    #7
    Bad practice not to specify the column names, imo. If the table structure changes you want to get the "no default value" errors rather than data into the wrong columns.

  8. Post
    #8
    suntoucher wrote:

    If using generic containers were always the answer because data storage wasn't a problem, strongly typed programming languages wouldn't exist.

    Not that I like strongly typed languages, but people will regularly argue in favour of them.
    You're right about storage space - string representations of a number use more space than a numeric representation of a number, bytes and all that.

    But statically typed programming languages (Python is strongly typed, JavaScript is weakly typed, both are dynamic type languages) exist because they remove an entire category of bugs at compile time. Obvious example being the runtime errors you encounter with duck typing wherein you've passed a value in, but it's not the type that was expected. In Python (being strongly typed) it'll throw a runtime exception, in JS (or Perl or PHP) it'll coerce a type to make it work and then you have way more fun subtle bugs.

    Thorough unit testing can catch most of that, but using a statically typed language removes the need for so much unit testing because the compiler prevents errors that dynamically typed languages need to test for.

    Other advantages of static typing is that it faciliates compile time optimisations and JIT optimisations.