This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
One of the great things about Alteryx is the ability to have multiple geography types (points, lines, regions) all contained within the same record.
However, when exporting map layers to MapInfo Tab file format, a decision has to be made of which spatial field to keep. What if you want all of them?!
Not to worry! The Transpose tool will save the day!
To configure the Transpose tool all fields will need to be selected under the Key Fields except for the spatial fields, they will be selected under the Data Fields.
Just note that there are now 2 fields for the radius. RadiusSize will correspond to the first trade area (row 2) and RadiusSize2 will correspond to the second trade area (row 3).
Now, more importantly all the spatial objects exist in one field (the Value field) and can now easily be exported to MapInfo.
Note: this methodology will also work for ESRI shapefiles, however, all the spatial objects must be of the same type. All points, all polygons...
(An example workflow is attached. This workflow was created in Alteryx 10.0)
Have you ever been frustrated because Alteryx and your database use different data types to store spatial data and they seem to have a hard time communicating?
If your answer is yes, then it is time to try the Spatial OleDB and ODBC connections! Below is information for Alteryx versions prior to V 11.
This article will show you what is unique about using spatial connections to connect to your database.
For general information on connecting to databases, please refer to the Database Connections page in the Alteryx Help files.
First, you want to select the correct provider. If you are connecting to SQL Server, the SQL Server Native Client as communicates the most seamlessly. This is true for both OleDB and ODBC (as well as non-spatial) connections.
For other databases, select the appropriate provider. If you already have a DSN set up for your ODBC connection, you may use that.
Even if the datatype in the Visual Query Builder window does not say “SpatialObj” it will still read correctly as a SpatialObj and be the correct data type in your SQL table.
This is what our example table looks like in SQL Server Management Studio:
In Visual Query Builder, the fields look like they might be binary:
The Select tool correctly shows them as SpatialObj:
And they return this map:
BLOB fields at end of SELECT
All of the things shown above will work better if you can use OleDB as it is faster and more reliable. However, if you do connect through ODBC, keep in mind that the spatial fields must be at the end of your SELECT statement or you will get this ugly looking error:
What to do if your spatial fields are not at the end of your table? No problem – they just need to be at the end of the select statement. Instead of SELECT * FROM SPATIAL_TEST, you will need to use something like SELECT Company, Address, City, State, Zip, Spatial_Field FROM SPATIAL_TEST.
Spatial Objects as Centroids
Another feature is to read in spatial objects as centroids:
If you check this option, Alteryx will read in centroids (points) instead of polygons. This does not affect the data stored in the table and you can easily switch back to reading in the polygons by unchecking the box.
There are a couple of options to keep in mind when using a spatial connection to write.
First, make sure you are selecting your spatial connection. You can recognize it because it starts with “sdbc”.
Another option to help you easily identify the connection string to use is to set up an Alias for your Spatial connection. You can refer to this article in the knowledge base on how to create an Alias.
The Output tool will have a few options not usually available:
Select your Spatial Object Field. NOTE: you can only load or update one spatial field at a time. If your table must contain more than one spatial field, the best way to proceed is to create the table separately (either directly in your SQL Server Manager or through a Pre-SQL Statement in Alteryx), making sure it has a Primary Key and then running updates for the fields through separate output tools.
If your field is a Geometry data type in SQL, you must check the Geometry Spatial Type checkbox, otherwise it will be treated as Geography.
For the difference between geometry and geography data types in SQL Server, see Microsoft’s help here.
For a description of spatial datatypes in postgreSQL, see here.
For Oracle spatial data types, see here.
You also have the option to select the projection to be used. If you click on the three little dots to the right of the field, you can select from a list of projections:
For more information on projections, THIS is a good point to start.
NOTE: When selecting a projection, make sure it is compatible with your database.
This should help you make the most of your spatial data types both in SQL and Alteryx!
Oftentimes in spatial analytics you’ll need to find the closest spatial object to another. The most intuitive way to do that that is through the Find Nearest Tool, which specifically captures the ability to find the shortest distance between spatial objects in one file (targets) and a user-specified number of objects in another file (universe objects). This tool does an amazing job of simplifying the process of finding the nearest object to another but it can also add significant time to your workflow.
I often elect for an alternative method that has trimmed significant run time off of many of my spatial workflows. That is, using the Append Fields Tool to duplicate your target spatial objects for each universe and using the Distance Tool to calculate DriveTime. After that’s done, simply add on a Summarize Tool, group by target and take the “Min” DriveTime for each. You could also sort ascending by DriveTime and sample for the first target by grouping with that field. There is a caveat, however, as the Append Fields Tool drastically increases the number of records in your input and will only speed up the process if there are significantly more targets than universes.
These methods are distinct in that the Find Nearest Tool must do a DriveTime run from each target spatial object to each universe spatial object (200 DriveTime passes in Example 1) whereas the Distance Tool approach already has all the points available to it and recognizes that there are many more targets than universes. As a result, it runs the reverse-direction DriveTime calculation starting from each universe to all target spatial objects at once (5 DriveTime passes in Example 1). If it is quicker for you to use the Find Nearest Tool, be sure to shed the spatial objects you no longer need in your workflow as soon as possible, even inside the Find Nearest Tool’s configuration if possible. That could also reduce your run time due to the sheer size of the spatial object datatype. Below are some examples of the methods. They can also be seen in the attached workflow, AppendAlternative.yxzp, which was made in Alteryx 10.0.
Universe Objects: 5
Attempt 1: Find Nearest Tool
Run Time: 8 minutes 13 seconds
Attempt 2: Append Fields Tool and Summarize
Run Time: 11.9 seconds
Universe Objects: 52
Attempt 1: Find Nearest Tool
Run Time: 49.7 seconds
Attempt 2: Append Fields Tool and Summarize
Run Time: 12.6 seconds
Client Services Representative
We recently had a user that was looking to distinguish polygons between each other using dashed lines, a style not currently available in the Report Map Tool. But that’s alright, we can use the opportunity to showcase how you can be creative in Alteryx by using a few tools.
In order to replicate this map style, you will need to separate out the polygons you want represented by the dotted lines. Add a Record ID tool, and either specifically filter out the record IDs you want to change or use the Sample tool to pull random records, or 1 of every N Records. (FYI – if you use the record ID, you will want to remove that column after you have split the records, due to the record ID used later in the mapping process.)
Once you have selected the polygons you want to use, you will need to break those polygons into individual points using the Poly-Split tool. Here, choose Polygon field and Split to Points. Splitting the polygon into points will allow you to adjust the polygon by each point.
Then, you'll want to remove some of the points to create the “dotted line” effect by using the Sample tool. This tool's settings will want 1 of every N Record selected (you can change the N=3 to any number you like that will have the spacing effect you want).
Now that you have removed some points, you'll want to do a few things to give the points a grouping effect. In order to do this, add another Record ID tool, then filter the record ID by odd and even numbers. You can do this using the Filter tool and using the expression mod([Record ID],2)>0. Then add Record ID tools to the T and F anchors to complete the grouping effect when you add them both to a Union tool.
Grouping the points allows you to build your Polylines. After the Union tool, add a Poly-Build tool. The Build method will be Sequence Polyline using the SpatialObj and the RecordID, as the Source and Group fields, respectively.
Your final step is to add a Map tool and pull in the data from your Poly-Build tool, as well as the original centroid points of the polygons with which you created the split lines, and finally the remaining polygons you want represented as full lines around the radius. When configuring the Map tool, remember that the points coming from the Poly-Build are actually lines and not Polygons. Your layering will need to have Points, Lines, and Polygons to complete the map.
The attached workflow, created in v. 10.0, demonstrates this functionality.
Those of you who have used the Report Map tool to create thematic maps have likely been unimpressed with the way Alteryx outputs the thematic legend text. Well, back in version 8.0, Alteryx added two little known/used tools: the Map Legend Splitter and Map Legend Builder. With a little finesse, you can get the legend to go from completely unformatted to fully customized.
Not only does this allow for an easier to read legend, it also can save valuable space on your map or document. The example above simply involves taking the default thematic output legend text and replacing it with user defined text for those layers.
Here's How You Do It
The entire workflow is illustrated after all of the steps below.
In the Report Map tool on the Legend tab, change Position to "Separate Field". This will output the map and legend as separate objects, allowing you to work with just the legend.
Add two Select tools after the map. In the first Select tool, select only the legend. In the second, only the map (and BoundingRect, if needed).
Add the Map Legend Splitter tool after the Select tool that selects the legend, and select "Legend".
Add a Record ID tool which will be used later to re-sort the legend back to its original order.
Add a Filter tool using the [ThemeName] field in order to extract just the records which make up the thematic part of the legend. For this example: [ThemeName] = “Block Groups”.
Create a Lookup table containing the Record IDs and the new text for the legend rows that you want to replace.
Join the lookup table to the legend stream using RecordID. Deselect the original “Text” field and rename the “NewText” field to “Text”. Deselect the second RecordID.
Union the new modified legend rows back with the non-modified legend rows using “Auto Config by Name”.
Sort the records back to their original position.
Use the Map Legend Builder to rebuild the new legend. The default configuration is all that is necessary.
From this point, you can choose to either overlay the legend on the map (using the Overlay tool), or join the legend back to the map (using “Join by Record Position” in the Join tool) and position the legend adjacent to the map as desired using the Layout tool.
Below is the entire workflow numbered by the steps above. Attached is a sample workflow created in 10.0.
I have configured my map to zoom to a specific layer, but the map is not zooming as expected. I have confirmed that the 'Zoom to Layer' checkbox in the Layers tab of the Report Map tool is checked only for the layer in question.
When it comes to spatial analyses, few tools come up more than the Trade Area Tool . Whether you’re looking to pad polygons around your spatial objects in distance or drive time, you won’t need to make a trade-off - just the Trade Area Tool.
Compariing spatial objects for equality is very subtle, because you have to define what is meant by equal. In the case of polygons, you can have 2 different polygons that describe the same shape, but have a different start and end point. Most people would say these are the same, but if you compare them exactly, they are in fact different. For instance:
[ -104, 39], [ -105, 39 ], [ -104, 38 ], [ -104, 39 ]
[ -105, 39 ], [ -104, 38 ], [ -104, 39], [ -105, 39 ]
These 2 sets of points describe the same shape just from different starting points. The same problem exists with lines - the same line specified backwards and forwards looks the same on a map, but would not be superficially equals.
The easiest way to test for equality is to test: does object A contain object B and also, does object B contain object A. If they both contain each other, they must cover the exact same area on a map.
In a formula or filter tool, you can say: ST_Contains([A],[B]) AND ST_Contains([B],[A)
In a SpatialMatch tool, you have to use a custom DE-9IM string to describe the match you want. In this case, use the string "T*F**FFF*". I won't try to document that string - see https://en.wikipedia.org/wiki/DE-9IM for a reference of how that works.
Attached is a module (for Alteryx 10) that demonstrates both techniques:
A common application in spatial analytics is to visualize and analyze data in multi-ring trade areas. This type of analysis is helpful for analyzing data in incrementally increasing distances from a spatial object. Such examples might include quantifying the number of customers with a 10, 20, and 30 minute drivetime, analyzing the demographics of a population within certain distances of a location, or visualizing the strength of a cell tower signal with increasing distance from the tower. Depending on the analysis to be done, the multiple Trade Areas may need to be configured as non-overlapping (separate rings) as opposed to nested as concentric circles. Using the Trade Area tool , both types of spatial objects can be created!
Creating Multiple Trade Areas (Overlapping)
To create multiple Trade Areas as nested concentric circles, select the option to specify the Trade Area radius as a 'Specific Value' and enter the radii for each Trade Area. A polygon output will be created for each specified Trade Area Radius. The sample configuration (Figure 1) creates three polygons (5 mile radius, 10 mile radius and 15 mile radius). Polygons are visualized in the order that they are listed. To create a "bullseye", list the radii for the Trade Areas in descending order. Note that the larger Trade Areas include the areas of the smaller Trade Areas (i.e., the 10 mile radius Trade Area includes the same area specified by the 5 mile Trade Area). To avoid "double counting" of area, consider creating non-overlapping trade areas (described below).
Figure 1: Create multiple Trade Areas around a point spatial object with the Trade Area tool. List the multiple radii (and specify the unit of miles, kilometers or DriveTime minutes), separating each with a comma.
Creating Non-Overlapping Trade Areas:
To create multiple non-overlapping Trade Areas ("doughnuts"), select the option to specify the distance ranges as 'Specific Values' and enter the distance ranges for each ring. Distance intervals should be specified as ranges (0-5 and 5-10), which each range separated by a comma (Figure 2). As a result, each ring is created as an individual polygon that begins at the distance specified by the minimum of the range and ends at the distance specified by the maximum of the range.
Figure 2: Each "doughnut" is specified as a distance range to create non-overlapping spatial objects. The highlighted "doughnut" below represents the second distance range (5-10 miles) from a point spatial object.
* For additional information on tool configuration, see the attached workflow (created in v10.6).
The Report Map Tool (master it here) allows to create thematic maps by selecting a "Theme" field on the Data tab and then further defining the theme on the Layers tab, for example:
The above example creates a map of Texas showing average annual rainfall totals where orange is the least rainfall and blue the most:
Pretty nice, right? But what if you want to change the map and instead of applying the theme to the fill color for the polygons, you want to apply the theme to the outline and just show that?
That is a little trickier because the Report Map Tool allows you to adjust the outline color and size of your polygons, it doesn't automatically apply the theme, so a workaround needs to be built.
You could feed in each polygon as an individual layer but that is difficult to manage - to keep the color gradient consistent, making sure they are in the right order. And what if a new rainfall range is introduced? You might have to adjust a couple of layers to account for it.
A better option would be to turn the polygon outlines into line polygons themselves. That would allow you to apply a theme right to the outline polygons.
In order to do this, we will use the following tools:
A RecordID is assigned so that we can pull the data apart and put it back together again.
The polygons are split into detailed regions using the Poly-Split tool and rows flagged as 'holes' are removed.
The polygons are split into points.
Those points are reassembled as a sequence polyline. The create the polyline, the data is grouped by the RecordID to keep each polyline separate. (A polyline contains multiple line segments, where a line has one start and one end point, but can have any number of points in between. A polyline can be quite complex as in the case of road systems, or rivers.)
The sequence polylines are joined back to the original data set.
Using the reporting tools to create the maps with rainfall range as the thematic field.
With that workaround you can create a map that looks like this:
For details on tool configurations, see the attached v10.5 workflow.
In a recent article (Create an Indexed Map), I mentioned the indexed maps found in the Rand McNally Road Atlas. Well, also found in the Road Atlas are mileage charts, or, distance matrices. These matrices can be easily created in Alteryx. The example below will provide the distance between every store in a dataset to every store in that same dataset. Here's how we did it.
Distance Matrix/Mileage Chart Example
Create a Cartesian Join of your data
Using the Append Tool, create a Cartesian Join of all of the records in the dataset.
This will give you a combination of every record to every record in your dataset.
Don't forget to "Allow All Appends" (learn more about creating Cartesian Joins here).
Measure the distances between all record combinations
Using the Distance Tool, measure the distances between the point combinations.
Flip the data into columns using the Cross Tab Tool
Use a Select Tool to change the Store Number column to a string
Doing this will prevent the Table Tool from adding commas to this field.
Use a Table Tool to create a formatted table
Add a Column Rule to the Store Number field to format the column as bold.
Create a Row Rule in order to force a one decimal place to the distance data.
Here's the workflow (created in 10.6), which you can also find attached:
That's it. Feel free to leave any comments or ask any questions.
If you are reading this, you are probably interested in maps. If you are interested in maps and of a certain age, then you have probably used a Rand McNally Road Atlas in the past. Waaay back in the past. In any case, the Road Atlas has a nice feature where you can look up a city within a sorted list, find it's assigned index value (for example B7), and then easily locate the city by using the x,y grid that was overlaid on the map. In Alteryx, it's possible to create a similar indexed map using several spatial and reporting tools. Below is one example of creating an Indexed Map.
In this example, we will be creating an index for a sample store file, which will allow the user to easily locate a specific store by narrowing the search down to a 10 x 10 mile grid section. Attached is the workflow (creating in 10.6).
Create the grids to encompass the stores
Using the Make Grid Tool, input '10' for Grid size and select the option 'Generate Single Grid for Entire Layer'.
Prep the grid values so they are in more of a user-friendly format
Using a Formula Tool and the SUBSTRING function, extract just the grid values.
Parse out the X and Y values.
Using another Formula Tool, add 1 to all grid values so that you don't have a grid of 0_0.
Using the Find Replace Tool, convert the Y values to letters so you get a label such as A1 as opposed to 1_1.
Finally, append the X and Y values together to get a single grid alphanumeric value.
Create the map and assign a grid section to each store
Using the Report Map Tool, create the map of your stores and overlay the grid.
Using the Spatial Match Tool, match the grid section to each store. In this example, I used the Store for the Target and the Grid for the Universe and matched where the Store touches or intersects with the Grid. Important: Without using the Touches option, the first store would be left out as it falls directly on the line of the grid.
I then sorted the stores by store number and created a table of the stores.
Create the Final Report
Join the table and map together into one record.
Using the Layout Tool, organize the table and map on the page.
Insert a Report Text Tool to add a title to the report.
Output the report using the Render Tool.
This particular workflow creates a report like this:
If you have a use case where you need multiple columns for your list of geographies, please see the Community article on Spanning Data Across Columns.
I hope you will find this useful. Please feel free to post any comments or questions.
You are in charge of marketing for Joe’s Bike Shop, a Colorado-based chain of bicycle shops. A new list of potential customers (DemandLoc.yxdb) has come into the system and you want to be sure that the closest location of a Joe’s Bike Shop (ExistingLoc.yxdb) within a reasonable driving distance (in this case, 20 miles) be advertised to the Potential Customer.
As always with Alteryx, there are many ways to approach this problem. Since our files of Potential Customers and existing locations of Joe's Bike Shop contain spatial objects (points), we can take advantage of the Find Nearest Tool. Though a number of other spatial processes could be used to handle this analysis, this scenario is a great opportunity to use the Find Nearest Tool because it:
includes two anchors for data input, which removes the obstacle of complex data arranging;
Accepts a distance criteria (Joe's Bike Shop must be within 20 miles of our potential customer), which eliminates the need to create Trade Areas around my existing location of Joe’s Bike Shops;
Calculates distance (or Drivetime) and direction, metrics I’d normally use the Distance tool to obtain and;
Allows a user to specify the number of closest spatial objects to identify, making the ol’ Sort and Sample trick unnecessary!
Let’s get started!
We have two input data files, the locations of Potential Customers (DemandLoc.yxdb, 3294 records) and the existing locations of Joe’s Bike Shops (ExistingLoc.yxdb, 6 records). Since we want to find the closest (within 20 miles) Joe’s Bike Shop location to a Potential Customer, we’ll set the Potential Customers file as the Target Input and the Existing Locations as the Universe input:
Then, we’ll configure the Find Nearest Tool’s parameters. Select the Spatial Object field for the Target and Universe inputs. Determine the number of points to find and specify the maximum distance* between a Target and Universe point. Note that the Find Nearest Tool also allows us the opportunity to re-name and re-order fields from incoming data, as well as select/de-select new fields generated in the Find Nearest Tool (FindNearestRank, DistanceMiles, and Direction).
*Users with the Spatial Data Bundle may also select Drive Time or Distance and the TomTom dataset to calculate.
The Find Nearest Tool has two output anchors, one for matched (M) Target inputs and another for unmatched (U) Target inputs. According to our analysis, 2341 of our 3294 potential customers have a Joe’s Bike Shop location within 20 miles of their location. The remaining 953 Potential Customers are outside the 20 mile distance.
In the event that two Universe objects are the same distance from a Target object, the Find Nearest Tool will output multiple matches for a Target Object. Since we specified that one Joe’s Bike Shop should be found per Potential Customer, we’ll verify that a Potential Customer has not matched to more than one shop location using a Unique Tool (see attached workflow).
Looking to optimize your Find Nearest Workflow? Check out this article for tips!
A truck is scheduled to deliver shipments to warehouses along a route spanning from Denver, CO to Abilene, TX. Deliveries are scheduled for certain days, so the route must be followed in the specified order (1 – 15). To spatially analyze the delivery truck’s route and area served by this truck’s shipments, we want to map:
1) the truck’s end-to-end route, and
2) the truck’s daily scheduled route.
To create the spatial objects (polylines) associated with the truck's routes, we'll use the Poly-Build tool. Located in the Spatial tool palette, the Poly-Build tool uses two or more point spatial objects and creates sequenced polyline or polygon to represent that data. For this particular use case, we’ll utilize the “Sequence Polyline” Build Method to vizualize and analyze our data.
1) Map the truck's end-to-end route
To map the truck’s entire delivery route, we’ll use the PolyBuild tool to generate a polyline that connects stops 1 to 15 in order. To control the order in which the spatial point objects are connected, we’ll use the field ‘Trip Sequence’ as the Sequence Field in the tool’s configuration:
The result is a single polyline that represents the 1,925 mile long route that the delivery truck will cover on its scheduled route:
2) Map each of the four daily delivery routes:
After some data preparation*, we’ll use a Multi-Row Formula tool to first group the truck’s stops into four segments, one for each day of the shipment schedule. Then, we’ll use the Poly-Build tool to create polylines for each of the day’s routes. Again, we'll select Sequence Polyline as our Build Method and 'Trip Sequence' as the Sequence Field. To create multiple polylines, one for each day, we’ll also select to group our data by the field 'Group'.
*See attached workflow for steps taken to prepare data for daily truck route creation.
The result is four polylines that represent each of the four daily segments of the truck's scheduled delivery route.
The Create Points Tool is the ‘Bonnie’ to your Clyde of any spatial analyses. The Create Points Tool allows you to take your latitude and longitude, often the spatial fields included in datasets, and convert them to a format that can play with Alteryx Spatial toolset. If your dataset lacks the latitude and longitude fields needed there are numerous free websites, geocoders, or geocoding APIs where this information is available.
One of the perennial challenges of creating high-quality maps is working with data sets where the spatial data is too spread out to make a useful map. The general solution for this challenge is to create a Map Book. A Map Book is a series of maps that show a subset of the data at a more detailed resolution. This article demonstrates methods for creating a Map Book in Alteryx.
Question What's the differnce between a Heat Map and a Thematic Map?
Answer It's probably easiest to show first and then explain:
Both maps were created in Alteryx using the same base data set of points.
A Heat map is generally thought of as showing off hot spots or concentrations of a value or spatial object. In this case we're showing areas where customers are located and their purchases in the same color ramp as the Thematic map, High = Light, Low = Dark. Generally speaking the heat is created by overlaying a grid across the map and adding values to the cells based on the presence of data within them. Cells that didn't contain something are given values usually based on the distance from a cell that did. There are many ways to do this and Alteryx provides a tool to help make it easier appropriately called Heat Map.
A Thematic map (or more specifically Choropleth map) is often made by shading standard geographical areas/units based on a value for that area. These can represent many various data types across the areas they cover. This is the standard shaded map that is used the most often. It's simple to understand, people generally know the geographic units being used (zip codes in my sample map), and values can be easily seen/compared. This is likely the better option I've found in most cases but not all. Since you're providing a single value for an area this can distort your data to look like it influences the whole area shaded but depending on what went into making that value this may not be accurate, even if correct.
If you are un lucky while rendering a map in Alteryx with a Carto base map, you may encounter the error message Error: AGG error loading font (C:\WINDOWS\fonts\C:\Program). This article explains the cause of this error, and how to resolve it.
Clustering analysis has a wide variety of use cases, including harnessing spatial data for grouping stores by location, performing customer segmentation or even insurance fraud detection. Clustering analysis groups individual observations in a way that each group (cluster) contains data that are more similar to one another than the data in other groups. Included with the Predictive Tools installation, the K-Centroids Cluster Analysis Tool allows you to perform cluster analysis on a data set with the option of using three different algorithms; K-Means , K-Medians , and Neural Gas . In this Tool Mastery, we will go through the configuration and outputs of the tool.