GIS Tips – Writing big selection queries

Don’t know about you but I get a lot of situations where someone will ask me to pick out a few features from some data….and then provide a table with 300+ features which need extracting. No small task and even worse they need it in the next hour or so…..

Fear not, you CAN do it and even better, you can put it back on their desk and still have time to stick some pins into that voodoo doll you have of them hidden in the desk (just me then?!) This assumes that you are using ArcGIS and UltraEdit…don’t worry if you don’t have UltraEdit (shame on you!) you can also do this in Notepad++

Step 1 – Filter the data for what you need

Let’s consider the email I got the other day, one of our guys needed me to select some environmental sites from the Natura 2000 designations (you can get this data here) and sent me the following spreadsheet (I have provided an extract and not the 40+ sites)

Example spreadsheet

So, how do you deal with this? How do you select out the designations above from the 177,000 in the World Database of Protected Areas?

Firstly, we need to extract only the data we are going to query from the database, so delete all the columns except for the “site name” column, note that the top row is merged, you don’t need it, delete that too!

Secondly, you may at this point realise that the ArcGIS “Search by attributes” isn’t that clever and may not pick up all the formatting or slight spelling differences, so to address this we need to have a quick scan of the column of the attribute table we will be selecting from:

WDPA

Export the table

Looking at this, I can see that the term(s) “SPA” and “SAC” aren’t used in the name field, therefore I need to remove everything but the raw name from the original spreadsheet. To do this I am going to first convert the spreadsheet to csv (comma delimited) format rather than edit this in Excel. So, simply export your table to csv….

Edit the text to build the query

Then open the csv file you created in your text editing software, for this I am using UltraEdit:

Now, hold down CTRL+R (this works in both UltraEdit AND Notepad++) – and use the find & replace to remove the unwanted text, for example here “SAC”, Ramsar Site” & “SPA” are removed (and also the title)

UE FIRST

You should end up with a list of text which is suitable for the selection process, this doesn’t have to be perfect but to work most efficiently you need to ensure ArcGIS will find the names you have asked it to (this is the hardest part of this!!)

Build the query

Okay….easy peasy?! Next we need to actually build the query, this is real easy as ArcGIS builds most of it for you, all you have to do is copy & place…I am assuming you can do that!!

Open ArcGIS, load the data you will be querying and then open it up in the “Select by attributes” tool:

Select By Attributes

Next is to use the select by attributes tool to build your query…..I know you can do this WITHOUT the use of the ArcGIS but trust me, I have built so many huge queries that have failed because I missed a space or comma that this is failsafe! So…you are going to build the first query and then we will apply this to the other entries.

Select By Attributes2

You can see that the query requires the searched text to have apostrophes around it, you can do this in UltraEdit by using the REPLACE ^P with ‘^P’ If you don’t have UltraEdit you may have to open the csv file in Excel and apply that way.

UE REPLACE

UE Apostrophe

Next to apply the query, to the first line add the first part of your query, the “[YOUR FIELDNAME]” LIKE¬† – REMEMBER COPY & PASTE THIS!

Then, copy and replace the ^P’ we put in earlier with ^P OR “[YOUR FIELDNAME]” LIKE’ (you may need to do this in Excel if you do not have UltraEdit:

UE NAME LIKE

It should come out like this:

UE FINAL

Couple of things to note before you copy and paste this into your query – DO NOT use duplicate search names (ie above Avon Valley is used twice, it will not run!) also DO NOT forget to put the apostrophe on the final term at the very bottom (if you look at the above example it is missing off Goelo)….also note the grave, apostrophe (d’Erquy) and foreign characters, the ArcGIS doesn’t like these, replace these before running any query, though note that you DO NOT need to remove the “next line” and can run it in a tabular manner:

Select By Attributes3

Voila!! I hope this helps, I have run 200+ queries in this manner before without any issue though I haven’t found an open source software that replaces on new lines quite the way that UltrEdit does.

Nick D

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s