Translating text for ArcGIS – Google Translate + Excel – A quick guide

This is a useful tip that has been pointed out to me by a colleague, so useful in fact I have to make sure I note it down somewhere.   Until my idea is incorporated into ArcGIS this is a quick and dirty workaround for translating Arabic labels in ArcGIS.

Yes, but what does it mean?

We have received some GIS data from a client, it’s landuse in a geodatabase with Arabic labels.  Trouble is our maps are needed in English!   In the past we’ve tested the Microsoft translation tools (for office) against Google’s online translate tool and found that Google does a much better translation.

So how do we translate this large landuse table quickly and easily?   Use Excel of course!   Please note that this bullet point list assumes you know ArcGIS and Excel quite well, to instruct from a beginners point of view would be a bit to long winded for me.

  1. First create a field name for the English Translation in ArcGIS
  2. Open a new Excel document
  3. Copy (using this method) the table from ArcGIS into Excel.
  4. Create a PivotTable that lists the row labels (in this case Arabic Landuse)
  5. Copy and paste this list out (so the text is static, you probably don’t have to)
  6. Copy the Arabic text into the text box at

    Google Translate
  7. Now Copy that English translation text list back adjacent to your Arabic landuse list into Excel

    Pasted back into Excel
  8. Now you have to use some Excel magic, select the English and Arabic text and under the Formulas tab (in Excel 2007) define a name ( in this case I called it English Translate)
  9. Once you have done this go to the English Landuse field name column and type in code like this “=VLOOKUP(E7,EnglishTranslate,2)”.   E7 is the Arabic landuse in your original table in Excel, EnglishTranslate is that Name you defined above and the number 2 is the column number of the EnglishTranslate you need to use if matched.
  10. Then click and drag copy this down your English translation field to check it works.
  11. Now copy back this data into ArcGIS or Join/Link it.

Well that’s it if you have any improvements/questions/suggestions please add them in the comments section below!

ArcGIS Toolbox – CityEngine Raster and Vector Clipper

The blog post on the ESRI site “Easily clip an entire workspace for a specific study area” alerted me to the toolbox “Clip Workspace“, which I thought would be useful for ArcGIS to CityEngine exports.   What I’ve done is tidy up the original rule tool and added a raster clipper tool as well.

It’s a beta release (is there any other release nowadays?!) so use at your own risk, modify if you like and perhaps you can share it back?

Anyway here is the download:

CityEngine Toolbox
CityEngine Toolbox
221.5 KiB

Review: DropMyEmail, backup for those without adequate broadband?

First up full disclosure: One of my old friends from secondary school has recently started working here. He did not ask me to write this review but I did start using it because I know and trust him.

You probably should backup your stuff somewhere….

Backups, I think everyone reading this will agree are essential if you want to ensure your stuff isn’t lost or damaged by some event, virus or sheer stupidity….   At work we have a system for backing up our data, emails are important but being in control of our data word documents, ArcGIS maps etc.. is essential.  At home it’s a different story it’s not word documents that are important it’s photos and emails.

So I have a solution for photo backups, basically a combination of external hard drive, main PC and Dropbox.   My broadband although not quick can cope with the odd photo or two that I want to share and keep.   It also feels natural to go somewhere take pictures and when home back them up.

Emails are another story, I get a lot, not all are important but it is my life blood at the moment all my life, from receiving that first email from a lady who ended up being my wife to photos of friends, announcements of births, deaths as well as work related emails with important attachments.   Then there are all those online purchases, receipts, serial codes etc!!    Now I really should have a backup of these, Gmail is good but it isn’t perfect and you do hear stories of people who have lost all their emails.

So I have been using an old version of outlook to periodically download all my emails and attachments off gmail and others… trouble is  I less and less use this PC and whole months go by without me using it.   That’s down to me using a work PC during the day and an iPad at home nowadays.

A while back I noticed that one of my old friends had jumped from his then employer Google to a startup called DropMyEmail.  Wow, I thought what the hell is dropmyemail?

Basically is a service that allows you to backup multiple email accounts in one place from a single dashboard. It’s really easy to use and despite a Web Of Trust rating issue (someone seems to have rated it as untrustworthy but is talking about another site), I can assure you it is safe.

It’s quick and easy to setup and can be logged into using twitter, facebook or google accounts, once in you get a free amount of space that you can boost with referrals and the like but 5GB starts at $10 a year which ain’t too bad at all in my book.   You can manage the backups in one place which is great and if you do have multiple accounts the backups can all be searched from here.   Attachments to emails also get their own attachment manager which is very useful and can be used to share them on a number of services.

So why use this service over other methods?   Well for starters if you live in a rural location in the UK and your broadband is poor this could be very useful reassurance. Since it doesn’t rely on you downloading emails to your PC all you have to worry about backing up is your photos, you did do that right? 🙂

Not for everyone I admit, esecpially if you have good broadband speeds.    Also if you don’t trust companies to keep your data safe well I’m not going to convince you!   If you’re interested head over to their site and take a look, it’s free for a set amount of data and if you’ve got multiple email accounts like me you’ll be wanting to upgrade pretty soon.

Summary: Email backup service for multiple accounts
Why? If you’ve got poor internet connections but still need backups of your webmail this is for you.
Rating: For me personally I give it a 4/5 it looses points because their sister site for backing up websites (dropmysite) is not integrated with dropmyemail

ArcGIS – CityEngine – SketchUp – Lumion3D – A workflow

I’m quite partial to a good workflow, so here’s the result of one I’ve posted on GeoPlanIT’s YouTube channel:

Lumion3D is a great rendering/visualisation tool I hope to be using much more in the future.   It might not give you full control over everything but if you want quick and easy renderings of your models it’s brilliant.   A word of caution though, you might want to upgrade your graphics card… (or like me your entire PC).   You can download a free (limited feature) trial now from here.

ArcGIS: Dynamic Charting: What’s it good for?

Don't worry Sedbergh, it's only a test!

In my line of work sometimes I need an overview of what I’m plotting in ArcGIS.   For instance, how much land area is being covered by the feature I’ve just plotted?  Or am I getting the expected proportions of land-uses?   This can help ‘GeoDesigners‘!   We use ArcGIS for City Masterplanning so the tool has a definite use.

Dynamic Charting is a tool that does just that, install the add in for ArcGIS10 and add the button.   Once you’ve clicked on the Dyanmic Charting button you can drag the layer you’re editing over to the dynamic charting box and watch as you add features and the chart (pie or bar) is updated ‘on-the-fly’.


You can read more about the tool here, as well as download it here.

Digital Elevation Models – OS OpenData to SketchUp : A workflow

Update: Geolocating the DEM in SketchUp has been solved see at bottom of page!

So you have SketchUp and you’ve heard wondrous things about the UK’s Ordnance Survey OpenData?!    In particular you hear there maybe some contour/elevation models out there for free as well!   This quick workflow guide shows you how get that elevation model into SketchUp so you can plan horrible developments in undeserving places (I’m a planner so I know…).


  1. Windows Vista (I’m sure XP, Windows 7 etc.. all work as well)
  2. SketchUp Pro (although you can import DEMs with the free version)
  3. MicroDEM – follow the install guide to get yourself up and running
  4. OS OpenData, in particular the Land-Form Panorama dataset, select the download option enter your details (it only requires your email address) and then wait for  523MB zip file to download
  5. Google Earth (to check your DEM is georeferenced properly)
  6. You need a basic working knowledge of MS Windows, SketchUp and some file management skills. 

Once downloaded you need to know what OS Grid square you want to import.   You can read the wiki article or I have used StreetMap and once you’ve searched for your location you can look just below the map and it says “Click here to convert coordinates” on this page LR seems to relate to the OS grid.

The Walkthrough

Double click on the ‘’ file and navigate to this directory ‘\DTM\ASCII\data’.   Yes there are other types of data (contours as DXF and DEM as NTF) but this is what worked for me.

1 – Extract a tile from the directory name corresponding to your chosen OS tile.  In this case we’re going to use Sedbergh, Cumbria tile which is under ‘\DYM\ASCII\data\sd\sd68.asc’.  You can copy it to any directory but in this instance I tend to copy it to the ‘mapdata\DEMs’ directory created by your installation of MicroDEM (you did install it right?)

2 – Load up MicroDEM and then click File –> Open DEM now navigate to the ‘mapdata\DEMs’ directory and select the ‘sd68.asc’ file.

3 – MicroDEM will ask you to pick its projection parameters as its OS OpenData my guess is that these settings are okay and then click ‘Mercator’ instead of ‘OK’

4 – The DEM should load up and look something like this :

Nice Colours?

5 – If you want to get rid of the legend and scale bar and any grid that may appear right click on the image and select ‘Legend/marginalia’.  Uncheck the boxes and click the ‘Grid’ button and select the option ‘neither’

6 – Click ‘OK(Close’ and say ‘yes to redraw… I often get errors and warnings at this point which I ignore….
Now click on the menu heading file again and save this as a DEM and in particular a USGS ASCII one:

7 – Once it is saved close MicroDEM and open up SketchUp…  I’m assuming you will import into a fresh new SketchUp Model, so click on the File menu and select import.

8 – Choose the file type DEM (*.dem, *.ddf) and find that file you saved in MicroDEM, before you click open click on Options:

9 – Here you can see I’ve entered 20000 points to import the lower the number the less detail for this tile 20000 as suggested by Chris Fullmer’s tutorial seems good.  I suggest you experiment with this to get what you want though!  Also I’ve check ‘Generate gradient texture’ this is entirely up to you, I suggest you first try with and then without.

The DEM should be imported and the axes, click the zoom extents button to check it’s all there:

10 – Now to get rid of all those lines, double click the DEM (to edit component) and select all of the DEM (keyboard shortcut : ‘ctrl-a’).   Now right click the selected DEM and  click on ‘Soften/Smooth Edges’:

11 – As per Chris Fullmer’s suggestion slide to around 90 degrees and check both boxes (Smooth normals and Soften coplanar)

12 – Et voila! You now have a terrain model for placing your models on!

mmm smooth elevation model!

13 – One important thing to note this is not GeoReferenced.  I haven’t figured out why SketchUp doesn’t load the DEM in the correct place.  If anyone has any suggestions please tell me (via Twitter or otherwise) and I’ll add it to this tutorial.

Geo-Reference (or Geo-Locate) your DEM

14 – First you need to know where your DEM is in Latitude and Longitude you can do this by going to Coordinate Convertor and putting in your OS tile number (in this case SD68), I suggest you select output as Coordinate Conversion only:

15 – You are now going to copy the Lat and Long coordinates into SketchUp so leave this webpage open and….

16 – In SketchUp click on the menu ‘Window’ then ‘Model Info’ and select ‘Geo-Location’

17 – Give the Country name and location something meaningful…. and copy and paste your latitude and longitude’s full number (and letter after) in the appropriate places.
Now to test it press the Preview in Google Earth button:

18 – If you’re computer is up to it you should see the DEM appear in the correct location in Google Earth, it may take a while to load though so be patient!

DEM Placed in GE

Top Tip: Creating a folder contents list in a simple text file

I have to use the keyboard?!

Updated 28/02/2012 to amend the syntax and remove instructions on making an xls file (Excel opens plain text files anyway)

Coming to the end of a project I realised I needed to create a table listing all of the files and folders within for that project.

This was a GIS (using ArcGIS) with many MXDs and associated data files, so the prospect of manually creating a list was a non-starter for me.

As with most discoveries laziness has been the driving factor in today’s “top tip”!

So I did a little research and with the helpful post located at found the perfect solution that has been around forever.   To be honest I don’t know why I haven’t known about this as it seems quite a simple request…

These instructions have been tried on Windows Vista only but I’m pretty sure Windows XP and 7 (works on Windows 7) will work just as well.   If not let me know in the comments section below!   The usual disclaimers about don’t try this if you’re not sure apply.

Okay so here is how to do it (step 1 for a list with all the heading and summary info and step 2 to just have a list):

Step 1 Create a simple text or Excel file listing files and folder in a particular folder:

  • Click start and in the start search text box type “cmd” and hit ‘enter’.
  • Next type in the Command Prompt box to change your location to the desire folder you wanted to make a list from e.g.


cd d:\test\list

  • Now to create a text file (which you can open in Excel) with the contents of the folder you have navigated to type this in:

dir > filelist.txt

Now you can use file explorer to open your file and folder list, with either Excel or Notepad!

Step 2 Create a simple text listing all sub-files and folders and without the header information or summary:
Following the first 2 steps above type this instead:

dir  /s/b > filelist.txt

Not rocket science I know but I have found it very useful.