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'

GeoMobile Innovations offer updated ArcPad and Studio classes in Portland and Seattle

GeoMobile Innovations announces updated ArcPad Bootcamp and ArcPad Studio training for ArcPad 10. The next ArcPad 10 Bootcamp will be held in Seattle, WA on October 12th/13th, 2010. The next ArcPad Studio with Enterprise Management Tricks & Tips class follows on October 14th/15th.

More training is scheduled for 2010 and 2011. Send in your registration form today to secure a seat for this great investment in ArcPad/GIS training!

Download a compete PDF registration packet including class descriptions, course schedule and training locations at http://www.geomobileinnovations.com/file_download/12/GeoMobile_ArcPad10_Training_2010-2011.pdf

Create Local Map Tiles

Users often have lots of data that take out to the field purely for visual representation, however with that can come performance issues on mobile devices – as lots of features all on top of each other with labeling and complex symbology can take along time to draw (even on a pc). People do this though because: If you can’t see where you are how can you believe the GPS is in the correct location and if you have no GPS signal how else do you navigate around your project area without the functional base map?

It has often been advertised that you need your data to be as accurate as possible to determine where you are, but all data needs to be (and is) generalized based on the scale that you are viewing so that your PC can draw as fast as possible. As the diagram shows, when looking at a project working on Main land Australia, why should I even bother drawing the islands that are around it – sorry Tasmania (we still love you)! What you’ll also notice is that the boundary is a lot smoother as well. At this scale the map doesn’t need to draw 15,000,000 + vertices why not just a couple of thousand. These techniques are not new but I want to highlight them along with considering data as “project data”. It is of course a great idea to have a central repository for storage and management of data, but why not take a copy/version and process it so that it works the best it can for you!



Another method of generalizing vector data is building image tiles (caches) of them – sound familiar? This is the basic theory of ArcGIS Online, Bing and Google. They all edit vector data but they are now publishing image (rasters) tiles that draw much fasterer. It has always been faster to draw rasters in ArcPad but has seldom been taken up. Sound like a good idea? Now if you have ArcGIS Server you can take care of this out of the box with the geoprocessing tools for Server – see our other blog entry Basemap Image Tiles for ArcPad 10. Although if you don’t have ArcGIS Server but you do have ArcGIS 10 for the desktop then perhaps I can help with another approach.



As I mentioned before we believe in using project based data – where all data is manipulated whether it be labeling, scale references, generalization, query definitions, symbology and even Areas of interest. Please remember, your maps may look great in the office but are they functional in the field In the ArcPad Team R&D department (we’re allowed to say it’s an official department because we have a lab coat in the office) we used the new ArcGIS 10 ArcPy module to develop a Python script sample that exports the data frame you have set up in ArcMap to a series of TIFF tiles rendered at the scale you nominate using the tool. Phew, that was a mouthful! So in summary, it squishes all your vector layers together and cuts the map up in to little pieces so they’re nice and fast to use in ArcPad.

You could actually perform this function without the tool. In your map, simply set the scale to your desired working scale and then use the Export Map function under File > Export Map. Give the image a File name and in the options set the to write GeoTIFF tags and write the world file. Then loop through this until you have exported your map. I did this for a city area of about 8 km2 and it took about an hour to create 56 tiles. Even If you did it this way you have a great set of base tiles, and if you have any updates to your base vectors, you would only have to export the area that was affected.

For those that don’t want to do this approach manually I have scripted, basically exactly those steps into a geoprocessing tool. The tool will prompt you with a dialog box for your working scale and the folder location that you want to store the images. The script is only a SAMPLE of what you can do, if you are up for a challenge take the code further fix my mistakes! To get the download – CLICK HERE. Please read the instructions!!!



A couple of hints:

• Being a sample this only work for data frames in ArcMap that are set to Meters (there’s the first challenge for you to fix).
• If you hard code scale and folder location and the Map Document name into a script you could run this as a scheduled task so every day you could have updated tiles.
• You may have to edit the Tools source and point it to the location where you extracted the files from the zip file.
• You can use this on any data in your data frame, even Cloud services such as ArcGIS Online, Bing or OpenStreetMap! Whatever data you have sitting on top of these base maps will be combined.
• TIFF is the best version for the ArcPad to render but you can edit the script to change the format that you want to use (remember, we suggest TIFF)
• You could reload the tiles into ArcMap, set reference scales and use the data manager to send them to ArcPad then you have scale dependent rendering (Hey, maybe you can add that to the script as well!)

Once the images are created you will be able to load them straight into ArcPad. So get mapping and have fun!