How to use SQL for working with data in ArcPad – Part One, Select

People often ask how to manipulate lots of features at once, or how can ArcPad give me a summary of the work I have done today? Well, if you use your editable data inside an AXF then you can leverage the power of SQL. For those of you who don’t know what SQL is, it is a database language designed to allow data insert, query, update and delete, schema creation and modification, and data access control. There are lots of books and web pages on what you can do with SQL and the best part is that the language hasn’t changed for decades. I’m still using a book that has been floating around the office for years (and published in 1987). You may not even be aware that you have used SQL before as well, however ArcMap takes advantage of this in Select by Attributes or a layers’ Definition Query.

A quick example of SQL.

From your feature class, for this example we shall talk about Roads, you want to select all of the Roads with a Name Type of Avenue. So let’s have a look at the query:

Select * ‘This line selects the columns you want to return in your query. The * means you will see every column

From Roads ‘Here we are selecting the feature class (or as SQL thinks the table)

Where Name_Type = ‘Avenue’ ‘The criteria is set using a where statement

Ok, so now we are all experts in SQL from that comprehensive demonstration, let’s talk about how you can use SQL with ArcPad. The AXF is a SQL Server Compact database (also known as an SDF), with the special ArcPad schema built into it. As I previously mentioned having the data in the AXF allows us to select, query, edit and delete data so if you open ArcPad Studio we shall start there. For this blog I am going to use the much loved sample dataset that comes with ArcPad – Riverside. Here you can see the table of contents showing the feature layers available in the dataset. Once you show the data a new SQL Query Window is opened and it shows a SELECT statement to show all the records in the Poles table (I hear you saying “That’s even easier than the example you showed above”).

Now in the Query Window, let’s run a couple of queries:

1. Find all the records for the Manufacturer ‘ACME’

2. Find all the records for the Manufacturer ‘ACME’ and that have a PoleID Greater than 90000

3. Find the count of poles that have a Material of ‘S’ or ‘W’– Hint there are two potential ways of doing this!

Seeing as we are all GEO-Nerds let’s do a spatial query!

4. Find all the records that fall within the extent of x 6213310, y 2291175 and x 6214523, y 2291759

Finally let’s edit some data

5. Update the Comment to ‘The pole requires maintenance’ for all poles that have a Material = ‘S’

Where else can I use SQL in ArcPad?

Now that you have seen the power of SQL and the benefits of what it might offer you in the field, let’s explore some options of how you can use it practically.

Not only can you use SQL in ArcPad Studio but you can filter layers based on attributes in ArcPad. If you edit the filter tab in the layers’ properties then you can adjust what is seen on the screen. Below you can see the example of filtering based on a material type. Therefore you can filter your data based on any attribute in your dataset. What if the layer was a list of locations a field user has to inspect/maintain mapped by their status of complete/under way/incomplete. You could use the filter to only show records that ARE NOT complete, and everytime a job /task is finished, the location disappears off the map. Another major benefit of filtering is “THE LESS YOU WANT ON THE SCREEN, THE FASTER ARCPAD WILL DRAW”. And finally you can also use multiple criteria for the filters as well such as Material =’s’ and Manufacturer = ‘ACME’. Or you could use do: @##MAPSCALE## < material =""> The second example shows how you can set a visibility scale as well as filtering by attributes.

Now that sounds really cool! But now you’re thinking that you want to flick between different filters without having to go through those manual steps. SQL can be constructed in a script so you can pass dynamic values into the filter. Open the Riverside sample APM in ArcPad, and open the script window (hint: press Ctrl + Enter). I am showing you this purely as an example, generally you would put these types of scripts on a tool button or embedded somewhere in ArcPad. Making toolbar buttons are well documented in the Customizing ArcPad Help, so read up if you are unfamiliar to them. The script window is a good place to check scripts are working in ArcPad before you put them into Studio as a specific customization. Ok, Copy the sample below and paste it into the script window.

dim myValue, myFilter

myValue = Application.InputBox("Type a material type: al, s or w", "ArcPadTeam Blog: SQL Filter")

myFilter = "Material = '" & myValue & "'"

Layers("Poles").Filter = myFilter

Application.ExecuteCommand("zoomfullextent")

Press, the red exclamation mark! You should see an Input box and type one of the values al, s or w (Aluminum, Steel and Wood respectively) and then press ok. You should notice that map changes based on the value you selected. Why not try and edit the script now, based on some of the questions. You don’t have to have the Input box at all, you can always hard code it so the filter is always the same e.g. Example 2: Find all the records for the Manufacturer = ‘ACME’ and that have a PoleID Greater than 90000.

dim myFilter

myFilter = "MANUFACTURER ='ACME' and POLEID > 90000"

Layers("Poles").Filter = myFilter

Application.ExecuteCommand("zoomfullextent")

That’s enough script code for one blog Especially as this is supposed to about SQL!

Hopefully you can see there are many benefits to filters, primarily it allows the map to focus on specific data and therefore makes your map faster to draw. This entry has been about selecting data and upcoming blogs will talk about Inserting new data and updating existing records.

Answers to Questions

1. SELECT * FROM [POLES] where MANUFACTURER ='ACME'

2. SELECT * FROM [POLES] where MANUFACTURER ='ACME' and POLEID > 90000

3. SELECT count(MATERIAL) FROM [POLES] where MATERIAL = 'S' or MATERIAL ='W' / Option 2 / SELECT count(MATERIAL) FROM [POLES] where MATERIAL <> 'Al'

4. SELECT * FROM [POLES] where SHAPE_X > 6213310 and SHAPE_Y > 2291175 and SHAPE_X < 6214523 and SHAPE_Y < 2291759 or you could use SELECT * FROM [POLES] where SHAPE_X between 6213310 and 6214523 and SHAPE_Y between 2291175 and 2291759

5. UPDATE Poles SET COMMENT = 'The pole requires maintenance' WHERE MATERIAL = 'S'