Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm planning to load a number of OS datasets into Postgres/PostGIS.

In order to save space and speed up queries, I'd like to strip the 'osgb' off the front of the TOID strings and then store the remaining number as a bigint.

Are there risks (or outright problems) with this?

I'm guessing that the numeric part of the TOID string is itself unique, but I know that some TOIDs have 13 numeric digits and some 16, and I'd like to be sure that any leading zeroes that follow 'osgb' aren't material to the number-part. This would be an issue if, e.g., there were two TOIDs in the same dataset as follows, where stripping off the 'osgb' would result in the same number:

osgb0000123412341234
osgb0123412341234 

Thank you.

share|improve this question
    
Postgis will create its own unique GID for indexing. postgis.refractions.net/documentation/manual-1.3/… (GIST is on the actual geometry) so TOID can be left as a string and trimmed to what you want - still saving space. –  Mapperz Jan 22 '13 at 14:43
add comment

1 Answer 1

up vote 2 down vote accepted

The Ordnance Survey TOIDS (Topological ID) are indeed unique IDs for OS features. The reason there are sometimes 13 and sometimes 16 digits after the 'osgb' bit is because they changed the specification but some data had already been released with the early 13 digit TOIDS so they were stuck with it.

Many systems designed to work with OS data prepend the leading zeros for consistence and ease of parsing later, but they are not part of the original spec and can be deleted with impunity. In fact, OS' own TOID validation service requests that you omit the leading zeros.

The 'osgb' part is common to all OS TOIDs and really just identifies a TOID as an ID belonging to and/or originating from OS data to enable a quick distinction between data from other sources. If you can guarantee that your databases will never acquire data from elsewhere it might be safe enough to lose the 'osgb'.

However, having said all that, the TOID is the basis for receiving change-only updates from OS, so you would have to reverse engineer all your space saving for each update. Also, having headed up a team that was developing automated processing methods working on the entire OS MasterMap dataset plus data derived from other sources, I can say from experience that having the full TOID complete with 'osgb' was very useful for tracking the provenance of a problematic features in some combined and derived datasets when you are a few steps from the 'raw' data. Given that disk-space is cheap these days, I'd rather buy a slightly larger drive than save the space if required. Even if you keep your 'raw' data pure (recommended!), GIS data gets used and abused, so having an idea of the provenance after OS data has been combined or analysed with other non-OS data is very useful. Knowing the full TOID in such circumstances also means you know whether you have to re-compute your derived data following an update from OS. Without the full TOID, you cannot guarantee you know where a feature came from after processing.

In addition to the above, the OS data also contains a number of attributes of the form 'ReferenceTo'. These are references between data-sets that provide additional information on how they interrelate (e.g. how lines might relate to polygons - i.e. a road casing line relates to the matching road topological-area polygon). Any stripping exercise you perform on the TOID itself must be matched on these fields as well and that increases the risk of error during your stripping process.

share|improve this answer
    
I agree. If you're worrying about space or performance of the extra 4 bytes, you're doing something wrong. If you do get data from several different sources, you could preprocess the string to strip the 'osgb' part (AKA the authority in WKT terms) and place it in a column of its own. If you're really picky about space, you could use a byte to use as a look-up into an authority string table. –  MerseyViking Jan 22 '13 at 11:08
    
Sylvester Sneekly -- cheers for that. I am aware that removing the string would require us to take a few extra steps pre-processing any updates, etc. We won't be mixing data with different provenance in this project (I think!), so less need for it, although adding a provenance column would be useful. –  saint_utz Jan 22 '13 at 14:55
    
I should have been more clear about saving 'space': I was mainly thinking about memory rather than storage. I have run a quick experiment on two tables, one with a single text column populated by 7.5m TOID records and the other with TOIDs converted to bigint. With an index on each, the bigint table was 55% the size of the text table (390Mb vs 707Mb) -- so a significant if not huge difference. –  saint_utz Jan 22 '13 at 15:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.