Category Archives: data

Deleting corrupt rasters from ArcSDE

Sometimes data gets corrupted when trying to load data into ArcSDE.  This can be problematic as standard and recommended processes to remove data from ArcSDE cannot be used (i.e. ArcCatalog).

Manual deletion must be undertaken by going to the database directly and deleting all record of the data in the SDE system tables and the actual business table itself.  Yes, this is scary!, but if you do some defensive SQL to create “backups” of the data you are about to delete then you should be OK.  Also check that you have made a backup of your database too for “belt and braces”.

Featured image

In SDE Schema:

SELECT rastercolumn_id FROM raster_columns WHERE table_name = <table name>
SELECT layer_id FROM layers WHERE table_name = <table name>

The results from above will be used in the schema where the faulty raster is stored so make a note of the rastercolumn_id and the layer_id  !!

CREATE TABLE table_registry_bak AS SELECT * FROM table_registry WHERE table_name = <table name>;
DELETE FROM table_registry WHERE table_name = <table name>;

CREATE TABLE layers_bak AS SELECT * FROM layers WHERE table_name = <table name>;
DELETE FROM layers WHERE table_name = <table name>;

CREATE TABLE raster_columns_bak AS SELECT * FROM raster_columns WHERE table_name = <table name>;
DELETE FROM raster_columns WHERE table_name = <table name>;

CREATE TABLE gdb_objectclasses_bak AS SELECT * FROM gdb_objectclasses WHERE NAME = <table name>;
DELETE FROM gdb_objectclasses WHERE NAME = <table name>;

CREATE TABLE geometry_columns_bak AS SELECT * FROM geometry_columns WHERE f_table_name = <table name>;
DELETE FROM geometry_columns WHERE f_table_name = <table name>;

CREATE TABLE column_registry_bak AS SELECT * FROM column_registry WHERE table_name = <table name>;
DELETE FROM column_registry WHERE table_name = <table name>;

In the faulty data schema:

CREATE TABLE DEM_20M_0042_LUTE_ON_SP_HS_bak AS SELECT * FROM <table name>;
DROP TABLE <table name>;

CREATE TABLE sde_aux_<rastercolumn_id>_bak AS SELECT * FROM sde_aux_<rastercolumn_id>;
DROP TABLE sde_aux_<rastercolumn_id>;

CREATE TABLE sde_bnd_<rastercolumn_id>_bak AS SELECT * FROM sde_bnd_<rastercolumn_id>;
DROP TABLE sde_bnd_<rastercolumn_id>;

CREATE TABLE sde_blk_<rastercolumn_id>_bak AS SELECT * FROM sde_blk_<rastercolumn_id>;
DROP TABLE sde_blk_<rastercolumn_id>;

CREATE TABLE sde_ras_<rastercolumn_id>_bak AS SELECT * FROM sde_ras_<rastercolumn_id>;
DROP TABLE sde_ras_<rastercolumn_id>;

CREATE TABLE f4707_bak AS SELECT * FROM f<layer_id> ;
DROP TABLE f<layer_id>;

CREATE TABLE s<layer_id>_bak AS SELECT * FROM s<layer_id>;
DROP TABLE s<layer_id>;

Once you have run these sql commands, check everything is ok in ArcCatalog, then delete all tables with “_bak” to clean everything up.


Data Normalisation – Flash Card

Quick reminder on Codd’s laws – the first 3 anyway:


First Normal Form

Any repeating groups must be but into a separate table


Second Normal Form

All nonkey columns must depend on the primary key.  This only applies to composite primary keys.


Third Normal Form

Every nonkey column must be about the primary key.

Get your XML right – Elements vs Attributes

I have seen from time to time XML from various examples/tutorials/forums – explanations that are confusing and others that are just plain WRONG!  Most of it comes down to the inappropriate use of XML elements and attributes.  I also happen to think this is one of the most confusing and ambiguous asepects of XML – but I think if you retain the following knowledge you should have little problems in forming your own correct XML documents.

Always bear in mind that “attribute” is essentially metadata i.e. something that describes something about the data.  I think a lot of people get confused with the actual terminology – “attribute”, thinking that the main element should have attribute data e.g.:

<part description="some part" price="£12.34" location="A1B2">

Don’t fall into that trap, it will cause you no end of problems, as W3School explains:

  • attributes cannot contain multiple values (child elements can)
  • attributes are not easily expandable (for future changes)
  • attributes cannot describe structures (child elements can)
  • attributes are more difficult to manipulate by program code
  • attribute values are not easy to test against a DTD

I can’t think of a reason why you would want to store your data in such a way.  The correct way would be to do the following:

    <description>some part</description>

Each peice of data is represented in it’s own element tag and not stored as an attribute to some arbitary first element.

The rule to remember is – if it looks like data  then store as an element.  The next question you are probably thinking is – when should I use attributes then?  There are no hard and fast rules – annoying I know, but one possible scenario for using an attribute could be as an ID attribute – something that is not intrinsically part of the data but can be used to help identify or tell you something extra about the data.

Conclusion: Use elements to store data, use attributes to describe something about the data.

Ripping DVDs is a consumers right

On the topic of encoding DVDs there is so much ambiguity about what you can legally do, but basically if you live in the UK or US, it’s illegal – nice! But people still rip DVDs and CDs regardless, you can’t throw us all in jail! The way I see it, I bought it, I own it, it’s for my own use, as long as I don’t file share it, what’s the problem? What possible threat am I doing to the poor, strapped for cash, beleaguered film industry? It physically doesn’t affect them in any monetary way, there is no financial hit to them, and that’s what it all boils down to anyway – money. I think the law should be officially changed to stop all this ambiguity as to whether it’s legal or not to encode your own DVDs for private use, especially concerning UK and US residents.

The Tide is Turning!

It’s inevitable, it will only be a matter of time and the laws will be changed to allow you to legally ‘format-shift’ your DVDs to hard disk or whatever format you like. What has started the ball rolling is a landmark case in 2007 – where the DVD Copy Control Association (a body you must get a license from if you intend to decode DVDs and play them in your technology, say if you were a manufacturer of DVD players) lost its breach-of-contract case from Kaleidoscope Systems, a maker of media servers.

Of course, it seems you can’t have a court case ruling without an appeal, and so DVD CCA are trying to overturn the ruling, BUT if they fail it will have huge ramifications – basically, it will mean that consumers will be able to format-shift DVDs – hooray!! Well thats for those in the US, but I expect that will influence legislation in other countries, especially the UK, who seem to follow closely in the steps of US. However format-shifting is happening in countries like Sweden, Germany and New Zealand despite the US stance and in Spain its legal to distribute your own media as well as pirated media, it only gets illegal if you try and do it for profit. Anyway I just hope, from a British standpoint, we don’t have to wait eons before format-shifting is legal here in the UK.

As an aside – why am I so keen on ripping DVDs….

Ripping DVDs is becoming more common because technology has advanced – its dictating how we, the consumer want our media. For the first time we have the power and technology and most importantly the storage capacity to rip DVDs. A couple of years ago this simply wasn’t the case, hard drives were too small and computers were too slow. Today you can have all your films streamed to Windows Media Centre or a PS3 etc, via your home network, and enjoy any of the movies you own, at the click of the button. This takes out the hassle of finding storage for a sizable collection, trying to locate a particular DVD, avoiding damage to the original discs, and lastly an overwhelming smug/warm feeling inside that you have all your film collection neatly stored on disk to access on a moments whim!

Software recommendations for format-shifting

  • DVD Shrink – encodes your DVD to VOB format.
  • Handbrake – converts VOB files to MP4 and loads of other stuff too.
  • PS3 Video 9 – more emphasis on PS3 but can be used for other devices

Also look at Lifehacker blog for some good info on DVD ripping software.