Latest Contributions by BruceHarold

This article is another spinoff from the recent (at writing) 2022 Esri Partner and Developer conferences in Palm Springs, California, where an attendee asked if we could see where his Android phone goes.  Well we can't, but the phone owner sure can, and as usual with ArcGIS Data Interoperability, it's no-code easy! If you enable location history on your Android device you can download periodic snapshots of the location data, along with many other variables, from Google's Takeout site.  There are a lot of things you can package up in any download, if you're only interested in location data deselect all options and scroll about 2/3 the way through the options to find Location History, and select it: Location History  While setting up your export there are some options to send the data to a cloud store, these are accessible to Data Interoperability too: Export Options   When you get some data you'll see it is in month chunks as JSON documents, for example 2022_MARCH.json.  There are a number of object types in the data; places visited, activities that describe movement (like driving and walking, but many more possibilities), places parked at and child places, where visiting a place was judged to result in secondary place visits - like shopping.  Everything has coordinates and UTC time stamps and places have street addresses and business names. The data is probabilistic, meaning Google's guess at how you moved and where you visited is given a rank amongst a number of possibilities.  You'll see in the tools in the post download I only preserved the highest ranking option.  I rate the accuracy as very high. So how did I ingest this data and what does it look like? I created a custom format for the JSON (details below) and some raw data looks like this when it lands in a geodatabase: Raw Location History   The linework isn't very inspiring at first glance (but see below); while start and end points are usually accurate, waypoints are only captured at sparse intervals, something like every 20 minutes (don't quote me on that, I haven't done a rigorous analysis).  At small scales the 'routes' are way off: Activity Extent    However, we can uplift the data by replacing the routes with those solved by the ArcGIS Online Routing Service. Here is how the data looks after that treatment: Uplifted Routes   Much better!  The routes are not perfectly accurate but do use the start, waypoint and stop coordinates, plus traffic conditions if the data isn't too old. If we go back to the full extent we can see some activities in mid extent (shopping?) and in Palm Springs (working?). Destination Activities   First the supposed shopping: Desert Hills at Cabazon   I can recommend the date shake at the place famous for them :-). How about the final destination? Palm Springs Convention Centre   The Esri conference! I will not pick through the tools in detail, but in the download you will find: File Description GoogleLocationHistory.fmw Workspace to create the custom format RouteActivitySegments.fmw Workspace to route the custom format GOOGLE_LOCATION_HISTORY_JSON.fds Custom format definition   Copy the fds file into your default custom format folder (you can set up other shares in FME options too).  That is a path like this:  C:\Users\<username>\Documents\FME\Formats If you experience a crash of Pro when using the custom format you have hit a known bug which will be fixed in Pro 3.0, in the meantime just reboot your machine and the crash will not reoccur. I used Data Interoperability for Pro 2.9 so you'll need that release or later.  The routing functionality requires an ArcGIS Online account with routing permissions and will consume credits.  I used the default travel modes for my organization, if you want to tune yours see this help topic. If you are new to Data Interoperability there is an Easy Button.  Install and license the extension, this delivers a toolset named Data Interoperability Tools, in there is a tool Quick Import.  Open that tool, search for the input custom format Google Location History JSON choose a destination file geodatabase name and run the tool - it will deliver the data (unrouted) into the new file geodatabase.  If you want the data routed you can edit and run RouteActivitySegments.fmw with the Workbench app or create a new ETL tool in a toolbox with RouteActivitySegments.fmw as its source and run it interactively. Now you are all set, go get that location history into your GIS! ... View more

Very often making information products means implementing a feature classification system using business rules of a user community.  Examples abound: administrative divisions of land, classes of asset, agricultural production, forms of land cover, quality metrics, ownership hierarchies, and the example I'm running with in this post - NAICS industrial classifications. There are more than a thousand industry classes when you go to the most granular level.  You will appreciate that querying them can be complex and error prone.  This post is about you as a data curator making complex queries simple for your colleagues by using ArcGIS Data Interoperability. With NAICS data, queries can look like this, needing just a few clicks: Selecting Data Classes And not like this, scrolling though 1000+ rows and guessing search terms: Industry Classes by SQL Query Best of all - you don't have to write a line of code. As a bonus extra I'm going to include some tips on precision geocoding of this industry data.  Let's get going! My subject industry data is from an Open Data site for the City of Seattle, specifically their Active Business License Tax Certificate dataset.  Seattle use Socrata technology, which is directly readable by Data Interoperability once you download and install the relevant Hub package.  Socrata is a popular choice for open data sites, typically being used for purely tabular data, as is the case here.  My hypothetical use case is I run a business specializing in pest control and I want to map potential customers in the food business for target marketing.  I'm going to build a Spatial ETL tool that lets me use smart pickers for this complex data and appropriate geocoding options to make the data spatial, then I can share the tool with anyone interested in the same business data but perhaps for different industry sectors. The North American Industrial Classification System is used in the United States, Canada and Mexico and is broadly compatible with the UN's International Standard Industrial Classification System, there are analogs worldwide.  The business license dataset is not spatial but is very well maintained and contains good address data.  I'm going to geocode it with tight quality control; I'll do this by confining geocode matches to high precision point of interest (POI, meaning matches include the business name) categories available in the ArcGIS Online World Service - the ones that match my industry classes of interest.  Successful POI geocodes validate business names and can return things like website and phone number details.  I will also allow the high-precision cases of normal address geocoding, if a POI match cannot be found we use the street address.  No matching to any sort of zone or area will be allowed.  I will geocode to match the industry classifications of interest, with a fallback to precise types of street address. Here is the tree-pick experience for geocode category, the categories themselves are built into the geocode service but the picker is enabled by Data Interoperability, just like the industry class one: Geocode Categories There are several categories I picked that are out of sight in the screen capture above, but navigating to them was very easy given the tree structure and hierarchy of available choices. So I can easily choose my Socrata-hosted industries of interest to extract that data, and I can pick how I want the data geocoded by category.  Is there anything else I need to control?  Yes, when inspecting the data I could see a few food business licensees located across the border in Canada, so to support specifying two allowed countries I built a picker for the country code parameter: Country Code Country code is a hard filter, no matches can stray into another country no matter how much an address is like one in another country. For completeness I also built a picker for the language code parameter which you can read about at the same link as the one above for country code: Language Code For my data, specifying language code wasn't useful, but if you need to work multilingually you can. Now let's get to the real value proposition for this post, showing you how to build smart ETL tool parameters.  The 'trick' is using advanced options when building Choice parameters. In my tool that extracts and geocodes business data I have a number of user parameters.  To make each tree picker I used the Import option (see to the right in the Choice Configuration area) to read a CSV file supplying the tree definition.  The CSV file has Display and Value columns that the importer is looking for: Choice Tree Parameter Configuration The CSV files are in the post download, the one for industry classes looks like this: Industry Parameter Import CSV The only remarkable detail for this file is I used a pipe delimiter and not a comma as the display values contain commas. How did I make the CSV files?  They are all built with one Data Interoperability workspace - MakeParameterCSVs.fmw, in the post download.  Start Workbench from the Analysis ribbon in Pro, open the file and we can walk through it. MakeParameterCSVs To make Categories.csv and Countries.csv for geocoding parameter purposes the geocode service definition is queried and the JSON response picked apart; this is a simple example of handling JSON from the web.  LangCodes.csv is made by querying the web help.  In the post download is a Python script GeocodeServerProperties.py that will give you an idea of all available properties in the service.  As a side note, if you are geocoding against a StreetMap geocode service on-premise the same properties are available. To make NAICSCodes.csv the appropriate NAICS spreadsheet download URL is read by an Excel reader and the desired hierarchy built.  The Socrata dataset is also read to make sure only industry codes that exist in Seattle will be available in the destination tool parameter. With the desired CSV files in place my ETL tool - BusinessExtractor - can be configured.  Let's walk through that: BusinessExtractor Workspace Basically Socrata is read and geocoded with the findAddressCandidates REST endpoint.  If the data was bigger in row count I would have used geocodeAddresses.  I use 4 concurrent threads in the HTTPCaller and performance was excellent.  Note that you will need to edit the ArcGISOnlineTokenGetter to use your credentials, and you will need geocoding privilege in Online.  Seasoned users will know there is a Geocoder transformer  I could have used, but it doesn't support category filtering or language code. The Geocoder transformer is scheduled for a rewrite so stay tuned for a post on that! Geocoding parameters are taken from the input pickers.  The most challenging part was parsing the category parameter to make it correctly formed for the REST API. Here is how categories arrive from the user interface (order does not matter)... "African Food" "American Food" "Argentinean Food" "Australian Food" "Austrian Food" Intersection "Point Address" "Street Address" Subaddress "BBQ and Southern Food" Bakery "Balkan Food" "Belgian Food" Bistro "Brazilian Food" Breakfast Brewpub "British Isles Food" Burgers "Cajun and Creole Food" "Californian Food" "Caribbean Food" "Chicken Restaurant" "Chilean Food" "Chinese Food" "Coffee Shop" "Continental Food" Creperie "East European Food" "Fast Food" "Filipino Food" Fondue "French Food" "Fusion Food" "German Food" "Greek Food" Grill "Hawaiian Food" "Ice Cream Shop" "Indian Food" "Indonesian Food" "International Food" "Irish Food" "Italian Food" "Japanese Food" "Korean Food" "Kosher Food" "Latin American Food" "Malaysian Food" "Mexican Food" "Middle Eastern Food" "Moroccan Food" "Other Restaurant" Pastries Pizza "Polish Food" "Portuguese Food" Restaurant "Russian Food" "Sandwich Shop" "Scandinavian Food" Seafood Snacks "South American Food" "Southeast Asian Food" "Southwestern Food" "Spanish Food" "Steak House" Sushi "Swiss Food" Tapas "Thai Food" "Turkish Food" "Vegetarian Food" "Vietnamese Food" Winery Butcher "Candy Store" Grocery Market "Wine and Liquor" "Bar or Pub" ...and here is how they go to the category parameter in the API (again, order does not matter): Intersection,Subaddress,Bakery,Bistro,Breakfast,Brewpub,Burgers,Creperie,Fondue,Grill,Pastries,Pizza,Restaurant,Seafood,Snacks,Sushi,Tapas,Winery,Butcher,Grocery,Market,African Food,American Food,Argentinean Food,Australian Food,Austrian Food,Point Address,Street Address,BBQ and Southern Food,Balkan Food,Belgian Food,Brazilian Food,British Isles Food,Cajun and Creole Food,Californian Food,Caribbean Food,Chicken Restaurant,Chilean Food,Chinese Food,Coffee Shop,Continental Food,East European Food,Fast Food,Filipino Food,French Food,Fusion Food,German Food,Greek Food,Hawaiian Food,Ice Cream Shop,Indian Food,Indonesian Food,International Food,Irish Food,Italian Food,Japanese Food,Korean Food,Kosher Food,Latin American Food,Malaysian Food,Mexican Food,Middle Eastern Food,Moroccan Food,Other Restaurant,Polish Food,Portuguese Food,Russian Food,Sandwich Shop,Scandinavian Food,South American Food,Southeast Asian Food,Southwestern Food,Spanish Food,Steak House,Swiss Food,Thai Food,Turkish Food,Vegetarian Food,Vietnamese Food,Candy Store,Wine and Liquor,Bar or Pub Here is the end result, a high proportion of point of interest geocodes with the vast majority of the remainder geocoded to rooftop locations (PointAddress and SubAddress).  Exactly what I was looking for to use in my pest control business! Seattle Food Businesses The workspaces, CSV files and toolbox are in the post download.  Now you can build smart input parameters for your ETL tools! ... View more

If your team's job requires repeated custom data ingest then you'll be interested in making it a one-step process - the proverbial Easy Button. The best way to share an ETL integration is to make the destination dataset a feature service, they are ideal for sharing, and of course you can make web tools in Enterprise.  However, sometimes you cannot use those options; you need a simple way to ingest a dataset just for you or your colleagues, or like-minded people who are not in your organization or even not known to you.  You just want anyone doing the same job to make data the same way.  Your data may be sensitive or time bound, or you may be off any network.  You just want data loaded into a geodatabase in one step. A couple of teams here at Esri came to me recently with this problem and in both cases the solution was the same - ArcGIS Data Interoperability custom formats.  Data Interoperability comes with an 'easy button' called the Quick Import geoprocessing tool.  This tool lets you pick any non-raster format from its gallery of 500 or so and convert the data to file geodatabase.  To add new formats all you need to do is create them! Relax, its a no-code experience.  Then you can share them with anyone.  In my colleagues' cases they were dealing with special 'dialects' of XML and CSV that needed specific logic to create features correctly.  Custom formats are built on top of existing formats (e.g. XML, CSV and hundreds more) and encapsulate all the logic to create feature classes or tables however you need. Data like the current US weather can be queried at a location: National Weather Service Report   Anyone can do this with Quick Import if the weather site response is made a custom format.  I did this, so the Quick Import input dataset format gallery lets me pick US Point Location Weather Forecast format and set its parameters (any address, location or POI in the US will work, under the covers it uses the World Geocode Service): US Weather Format   Weather Format Properties   Then a Forecast feature is available to map and query - the green hexagon is a weather feature: Boston Weather Feature This is not relevant to the story of custom formats, but the feature is a hexagon because I used an Uber H3 encoding of the XY value geocoded from the input.  This complies with terms for non-storage use of the geocode service - all I know is the geocode XY is somewhere inside the hexagon.  The hexagon size (i.e. H3 index scale) also agrees with the coverage NOAA's NWS service returns for a point forecast.  So how do you make a custom format?  All that is required is just a normal standalone Workbench document (.fmw) that satisfies two conditions: The workspace must have an input dataset The workspace must write to a file geodatabase Once you have configured your workspace and verified it creates data correctly, the File menu in Workbench has a choice Export as Custom Format.  This saves the workspace as a format definition to a default location in your profile directory with a short name and long description you can see and search on in the formats gallery.  The format definition has the file extension '.fds' but it remains editable with Workbench.  These fds files can be shared with anyone either by copying into their profile directory or putting on a file share they can see - the Tools>FME Options>Default Paths dialog allows you to specify Shared FME Folders where your team can share fds files and other resources like credentials. Here is my profile directory with a few custom formats in it: C:\Users\<username>\Documents\FME\Formats Two of these files are in the blog download, NWSFORECAST.fds and CASOLAR.fds - the file names come from the short name you give the format at export time.  If you copy them into your C:\Users\<username>\Documents\FME\Formats folder (make it if it isn't there already) you'll have two new formats! NWSFORECAST is the weather forecast format and CASOLAR is California Distributed Generation Interconnected Project Sites data. This is a tabular monthly digest of electrical generation installations (that are not off grid) in California, it has a lot of fields describing generation projects (mostly solar). Here is the data at writing summarized as total system kilowatts per ZIP code and extruded as 1Kw = 1m: California Generation Kw November 2021 If you want your own copy look for this format: California Generation Format I chose to make these example custom formats from underlying XML for the weather format and CSV for the power generation format, partly because XML and CSV (along with JSON) are frequently encountered but also because in this case they are delivered via HTTP, not a dataset reader, and I wanted to show you how to do that.  To meet the condition that a custom format workspace must have an input dataset I used a special format called NULL, which as the name suggests does nothing. If you edit the fds files you'll see nothing special. Weather format workbench   Solar projects workbench If you want to test drive the custom formats you will require ArcGIS Data Interoperability extension installed for Pro.  I used Pro 2.9 to create the formats and have not tested earlier releases.  Be aware the power generation data will take about 40 minutes to download and process (nearly 1.3M rows), and you will need to find California ZIP code boundaries to map the data.  The weather report format takes a few seconds. My real message here is that one person with Data Interoperability (or FME) skills can create custom formats for big button ingest and share the functionality with anyone using the extension.  The person using the format only needs to know about the Quick Import tool and does not need Data Interoperability training. ... View more

No, it's just a slogan, a bit like "The Geographic Approach" you'll hear from Esri, where the definite article doesn't literally mean there is a single path to nirvana. "Building a Data Driven Organization" is just a set of ideas and approaches you can look at and judge for yourself whether each is relevant to your organization. ... View more

Let's expand the scope of the 'data driven organization' theme just a little to include every organization in the world, or at least all those you care about in their B2B interactions.  If you're an agency responsible for promoting business efficiency you might like to curate data like in the below map in your SDI, it is all businesses in a country with all their functional locations uniquely keyed, the map view is centered in a city industrial zone (there is an estuary in the middle - it's a very scenic industrial zone): Company Locations The dots on the map are coming from an ArcGIS Knowledge graph service, I'll get to the details in a bit, but more importantly what is the big picture?  It is standardized, authoritative, shared access to entity location in a way you can build into your B2B processes and trust its validity and persistence.  Using a standard in other words.  The dots are GS1 Global Location Number (GLN) locations.  Any legal, physical, functional or even digital location can get a GLN, and where they have a location, be used for logistics - i.e. supply chain analysis.  When combined with other operational data like purchase orders, inventory value, goods in transit between GLN points etc., a graph analysis opens up many more analytic opportunities to improve near real time business decision making.  Even just adopting GLN as a shared foreign key in an economy will enable efficiencies and reduce errors. The dots on the map above are where registered businesses identify their primary location to be.  Well, more correctly I geocoded the locations from addresses contained in a bulk download of a day's snapshot of GLN data.  I used the ArcGIS Online World Geocode Service and I worked in ArcGIS Pro, using the ArcGIS Data Interoperability extension to handle the JSON data in the download.  I didn't write any code. If you read up on the GLN, you'll see its a 13-digit number and does not in itself encode location.  To create geometry from GLN data you will need supporting address or coordinate data. Lets discuss GLN a little before having some fun with the data.  There are many approaches to encode 2D geometry into a single value, such as geohash, Google's S2 library, Uber's H3 library, several distributed global grid (DGGS) hashing schemes and at first I wondered why GS1 didn't use one.  On reflection businesses can relocate, or even be mobile, and locations may be 3D.  Digital GLN numbers (web domains etc.) aren't even physically located, so a number was chosen as the 'encoding'.  13 digits is a little awkward, it overflows a long integer type and using a double precision type runs the risk of some storage or transport formats creating a decimal from the number (adding '.0') so for my purposes I handle GLN as char(13) and generate geometry as 3D WGS84 points, with 0 as the default elevation above the ellipsoid.  I suppose if I was doing this for real I would store the epoch too so tectonically fast-moving places like Australia could maintain 2D accuracy over long time periods.  Some businesses are hundreds of years old!  Anyway, on to a scenario! The simplest use case for keying B2B transactions with GLN is logistics - shipping goods.  Things like sale and purchase agreements would come for the ride, recording any transaction's GLN details.  My (fake) scenario is the International Space Station has a need for a new dishwasher, your transactions may be more mundane.  The purchasing folks for the ISS are very smart (after all, they are rocket scientists) and looked for an innovative appliance manufacturer closely located to a launch provider.  After a little research they found a good pair of suppliers and organized delivery of a custom zero-gravity dishwasher.  The agency where the companies are located made an enlightened decision to base their corporate registration system on GLN, so this high value (or any) transaction can be driven by GLN! In the blog download is a locator, unzip it into an ArcGIS Pro project folder and add it to your project.  Make two maps in Web Mercator coordinate system, make the basemap Imagery Hybrid and arrange them side by side.  In the left map use the Locate tool to go to the location 9429040747378 and in the right map go to 9429034019108.  I built the locator to return a company name in a field EntityName.  When you geocode to the GLN locations the candidate popup will reveal the suppliers I'm talking about.  They are real companies that really operate in the industries I mention.  A human interest factoid, the CEO of the rocket company used to work at the appliance manufacturer, and so did I. Here is how things should look with the maps at 1:2000 (you will not have the blue dots), obviously the companies would use their GLN locations for shipping arrangements in their contract. Appliance manufacturer and launch provider locations via GLN locator So that's a shout out to GLN as a hook to hang B2B processes on.  It is simple, official, and extensible - organizations can have any number of GLN numbers for delivery points, including ones they might keep private for internal purposes.  All that remains is for you good folks to build it out.  Ask your local Esri representative for help building out GLN as part of your SDI. I said I created a Knowledge graph with the data.  The source dataset was ~6GB of zipped JSON, I got it into the graph in three steps (without unzipping):   Extract Company Data   Load Entities   Load Relationships  In hindsight I could have done less flattening of the JSON and retained more expansion tables but it doesn't matter for this exercise.  The graph has these object counts: Graph Investigation Contents   That's the whole database, including historic records.  I might not retain everything if I was doing this for real. I mentioned I flattened everything in the JSON, so I ended up with very much a star schema, not a snowflake one.  The Company entity is the sole fact table, everything else is a dimension with a relationship starting at Company, hence the names I gave them beginning "co".  You can go either way with a graph. Lets see a couple of things the graph can tell us. In the map of GLN locations I noticed a lot of business locations in amongst residential housing, to the east of the map.  What's going on there? Businesses in a residential area I sent in this query to the graph: match (ra:RegisteredAddress {postCode:'2016'})<-[:coHasRegisteredAddress]-(co:Company {entityStatusDescription:'Registered'}) with co match (co)-[:coHasIndustryClassification]->(ic:IndustryClassification) return ic.classificationDescription, count(*) as icCount order by icCount desc Here is what is returned: Business Classifications in Postcode 2016   They are a bunch of property developers!  Well, the data has a long tail, there are 689 business classifications represented, making fascinating reading, right down to motor wreckers, milliners and milkers of cows. Back to the graph theme.  I can see 10 construction carpentry businesses and can get them into a link chart: match (ra:RegisteredAddress {postCode:'2016'})<-[:coHasRegisteredAddress]-(co:Company {entityStatusDescription:'Registered'}) with co match (co)-[:coHasIndustryClassification]->(ic:IndustryClassification {classificationCode:'E324220'}) return co Construction Carpenters in Postcode 2016   At this point I run out of steam with my analytical abilities with graphs so I'll stop while I'm ahead.  That's a personal limitation, not the technology, I just haven't spent time refining my skills. The point I really wanted to make is the world's businesses and transactions can be modelled in ArcGIS and there is a handy linking key available to you in the GS1 GLN.  Talk to the responsible people in your organization, province, state or country and help get everyone on the map! ... View more