Chris, The postcode is unique in this data collection. I *should* have stated that as it is important. My apologies.
The UK postcode is defined to cover several houses, in London my postcode covers around 20 houses, all of them on my street. This means the area it covers is pretty small. Outside London, a postcode can cover a far, far wider area specially in rural or sparsely populated areas. I would imagine Australian postcodes to be similar but thats based on a guess rather than actually any knowledge. What we wanted was a postcode to GPS lookup, it was not to pinpoint a house, you would need a little more information to be that specific, e.,g. house number or a name, but to get to a general area that has a central GPS point. For our use case, what we have is sufficient as all the postcodes are unique in our system. If the user has more information such as a full address and postcode, there are other techniques we can use to be more accurate but thats a different use case. Rob. > On 18 Sep 2015, at 01:44, Chris Waters <cwaters at watershed.com.au> wrote: > > >> On Thu, 17 Sep 2015 13:55:51 +0100 Simon Slavin wrote >> >>> At the end of the CREATE TABLE statement add keywords: "WITHOUT >>> ROWID". Like this: >>> >>> CREATE TABLE postcode( >>> postcode TEXT, >>> long TEXT, >>> lat TEXT >>> ) WITHOUT ROWID; >> >> The documentation suggests that you need to explicitly declare a PRIMARY >> KEY, so you would need something more like >> >> CREATE TABLE postcode( >> postcode TEXT PRIMARY KEY, >> long TEXT, >> lat TEXT >> ) WITHOUT ROWID; >> >> but perhaps I misunderstood. > > > > Elsewhere in that email (on another topic) a correspondent urged that the > outcome should be stated, the requirements understood so the proposed > solution could address that, not how to improve code snippets. I think that > applies here. In this particular thread I think an important element is that > postcode will not be unique, if Australia Post's version of this is typical. > One postcode covers several suburbs. I guess this will fundamentally alter > the approach. A blisteringly fast lookup retrieving the wrong result may not > be all that useful. > > Chris Waters > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
