Latest Contributions by BruceHarold

In the Extract Transform and Load (ETL) world you can make the mistake of focusing on data movement at the expense of data usability.   As data moves and evolves one crucial aspect that often gets overlooked is metadata. Metadata, the information about your data, plays a vital role in ensuring the context, accuracy, and integrity. It also embraces the FAIR data principles (Findable, Accessible, Interoperable, and Reusable). @BruceHarold and @JordanDuft team up in this post to show how to create and maintain metadata as data moves through ArcGIS.  As the use cases involve ETL, ArcGIS Data Interoperability will be used. We'll show three use cases for metadata management: Populating feature class and table metadata in ArcGIS Pro Populating metadata for a hosted feature layer in ArcGIS Online Updating metadata for multiple portal items in ArcGIS Online in bulk Not shown, but discussed near the end of this post: Deep copying data and metadata between environments using metadata.xml Let's start with an example of Case #1 - feature class & table metadata written at ETL time. To set us up Bruce imported data from Vancouver's open data web site. The data was brought into an ArcGIS Pro project geodatabase by a spatial ETL tool (ImportBuildingPermits, in the blog download) that reads from Vancouver's open data web site.  The dots on the map are today's permit locations in the feature class BuildingPermits.  Not only was the building permit data written to a feature class it also had metadata generated by an ETL process. In the screenshot below, we are viewing the metadata for the BuildingPermits feature class in ArcGIS Pro. In the metadata we can see the item information (title, tags, summary, description, credits, use limitations). Building permits metadata If you're familiar with writers in spatial ETL tools you'll be aware they don't have much in the way of metadata support.  However, as Data Interoperability is an ArcGIS Pro extension it knows about everything ArcGIS Pro does - including ArcPy's metadata module, and that is where we go for this support! To back up a little bit, the Workbench app delivered by Data Interoperability has a feature called a shutdown script (there is also a startup script) which lets you run Python code with any installed interpreter on completion of any tool run.  For the subject matter data, we leverage this feature to do two things the built-in writers don't know about: Creating relationship classes between output feature types Creating metadata for each output (yay!) The only trick to using this feature is knowing that the shutdown script has a built-in dictionary object (fme.macroValues) that has keys for all tool parameters, this includes input and output data paths.  The rest, as they say, is easy!  We built the script starting from snippets from the Copy Python Command option in ArcGIS Pro's History pane.  Here is the shutdown script:   # Create relationship classes between BuildingPermits and child tables PropertyUse and SpecificUseCategory import arcpy from datetime import datetime import fme import os import pytz arcpy.env.overwriteOutput = True gdb = fme.macroValues['OutputGDB'] arcpy.env.workspace = gdb origin = "BuildingPermits" for destination in ["PropertyUse","SpecificUseCategory"]: arcpy.management.CreateRelationshipClass( origin_table=origin, destination_table=destination, out_relationship_class=origin + '_' + destination, relationship_type="SIMPLE", forward_label=destination, backward_label=origin, message_direction="NONE", cardinality="ONE_TO_MANY", attributed="NONE", origin_primary_key="PermitNumber", origin_foreign_key="PermitNumber") # Create layer level metadata current_time_utc = datetime.now(pytz.utc) pst = pytz.timezone('Canada/Pacific') current_time_pst = current_time_utc.astimezone(pst) current_time_pst_formatted = current_time_pst.strftime('%Y-%m-%d %H:%M:%S') descriptions = {"BuildingPermits":"Construction projects and any change of land use or occupancy on private property requiring a building permit", "PropertyUse":"General use of property; multiple uses will be accessible in a 1:M lookup", "SpecificUseCategory":"Category of property use; multiple categories will be accessible in a 1:M lookup"} for obj in ["BuildingPermits","PropertyUse","SpecificUseCategory"]: new_md = arcpy.metadata.Metadata() name = obj.replace("gP","g P").replace("yU","y U").replace("cU","c U").replace("eC","e C") new_md.title = f"{name} of Vancouver, Canada 2024." new_md.tags = f"Demo,Esri,City of Vancouver,Canada,applications,{obj},{name}" new_md.summary = f"Layer includes information of all {name} issued to date by the City of Vancouver in 2024" new_md.description = descriptions[obj] new_md.credits = f"City of Vancouver, {current_time_pst_formatted} Pacific." new_md.accessConstraints = "https://opendata.vancouver.ca/pages/licence/" tgt_item_md = arcpy.metadata.Metadata(obj) tgt_item_md.copy(new_md) tgt_item_md.save()   Taking the time to embed metadata automation at ETL time at minimum ensures you don't forget it after the fact and is a best practice.  Why?  To: Ensure that essential information is documented for your data. Help consumers of your data understand the context of your data and how it can be used in future mapping and analysis. Enhance search and discovery of your data- making it easier for people to find. You'll have noticed in the map and metadata screen grab above that Bruce used the unique value renderer to display the data in categories (type of permitted work) and configured pop-ups.  He then published the layer and related tables to ArcGIS Online - the target information product.  At publication time the metadata for each layer flows to the sublayers of the hosted feature layer, like this: ..and here it is in the BuildingPermits layer: Sublayer metadata flows through Because metadata is flowing from the data to the published sublayers of the hosted feature layer this saves Bruce the trouble of recreating it.  So that's how to get metadata into your information product at ETL time and why it's important to do so. However, there is another level of metadata we need - for the hosted feature layer itself, so we'll move on to Case #2 - populating metadata for a hosted feature layer using ETL tools. Below is the details page of the hosted feature layer in ArcGIS Online: Item home page showing metadata Note, the metadata for the hosted feature layer looks amazing!! However, Bruce must confess something… he doesn’t enjoy creating metadata. Therefore, he didn't write a line of the summary, description, tags or terms of use metadata on the item details page. So where did he get metadata? How did he populate the item information on the item details page?  Using magic? (…maybe, depending on how you define magic!) This metadata was harvested straight from the source - Vancouver's portal. Below is the landing page, with datasets sorted on popularity (which we may be skewing 😉).  We highlight the dataset we are harvesting and if you look near the bottom we also highlight a link to an Excel spreadsheet with all dataset metadata. Open Data Landing Page Opening the spreadsheet we can see the row for the subject matter dataset - Issued building permits. Dataset metadata spreadsheet It is just a matter of getting the cell values for metadata elements from the Excel spreadsheet to the hosted feature layer - no problem - that's the sort of thing this technology was built for.  Here is where that happens, the RefreshBuildingPermits ETL tool (in the blog download) - this is it. RefreshBuildingPermits ETL tool Without making this a tutorial on ETL tool design, the steps we're using for our information product are to: Make an initial file geodatabase incarnation of the data. ...using the ETL tool ImportBuildingPermits that has the shutdown script shown above. In ArcGIS Pro create the layer with symbology and pop-up behavior we want. Publish the feature class and related tables as a hosted feature layer to ArcGIS Online. Maintain the hosted feature layer using the RefreshBuildingPermits ETL tool. ...which means we put the hosted feature layer's metadata update functions in this second ETL tool.  If you look bottom right you'll see a bookmark Update Metadata.  The FeatureWriter transformer handling actual permit data has a summary port, if any data is written a hidden connector (for aesthetic reasons) triggers a FeatureReader which accesses the Excel file on the open data site and gets the right cell contents to an ArcGISOnlineConnector (also works with ArcGIS Enterprise) with the following settings: Set metadata details with ArcGISOnlineConnector So it's as easy as filling in the form!  If there are no data changes there are no metadata changes, otherwise there may be.  How easy is that?  You now know how to harvest and write metadata using ETL tools! Note:  The ArcGISOnlineConnector is downloadable manually or will automatically download if you type the transformer name into your Workbench canvas and are connected to the internet. Lastly, the third leg to the stool.  Case #3, making bulk metadata changes to any number of portal items. Let's say that we manage an ArcGIS Online or ArcGIS Enterprise organization with hundreds of items and we need to change our terms of use for all hosted feature layers in a content folder. This is what we have now: This is what we want: This is how to effect the bulk change, it is crazy simple with another ETL tool: It is just an ArcGISOnlineConnector used to list the candidate portal items, a Tester to filter for hosted feature layers, then another ArcGISOnlineConnector to update the target element: If no value is supplied for an item it is left unchanged, to zero it out you would supply a value of NULL. Now, we mentioned above another ETL pattern of interest is deep copying of data and metadata between ArcGIS environments, for example between ArcGIS Enterprise and ArcGIS Online.  There are core approaches for this use case in which metadata travels with the data, as you would expect, but there are also use cases where you wish to perform edits on the data and/or metadata during the ETL process.  In this situation you might work with the metadata.xml file for each dataset.  This can be accomplished with an option in the Get Details and Set Details actions in the the ArcGISOnlineConnector to get and set the value of metadata.xml. Move metadata between portal environments So that's it, managing metadata end to end using ArcGIS Data Interoperability!         ... View more

At the simple end of the ETL Patterns continuum is the most common format of them all - CSV, or comma separated values, and its close cousin, the Excel spreadsheet.  Vast troves of data move around as these formats - easily shared, human readable - what's not to like in ArcGIS?  Well, these things! No proper schema built into the format Column names often not valid in ArcGIS Text fields with numeric values are treated as numeric Text field widths are assumed to be 8000 (CSV) or 255 (Excel) bytes Integer fields may be treated as double precision float Null values may be encoded with zero or other unlikely value Geometry is often encoded in an unsupported format for feature class creation Take this popular dataset from data.gov, mirrored from Washington's open data portal: First, the data as a map: Washington EV Population Now the source CSV file: CSV or Excel Data Issues In red I'm picking out some of these usual suspect violations.  Column names have spaces or metacharacters, USPS postcodes can have leading zeros so should be text, zeroes have been used to encode nulls in Electric Range and Base MSRP columns, Vehicle Location is in WKT format and 2020 Census Tract has big integers and a name beginning with a number. The subject dataset has velocity - it is regularly updated - so you if you're interested in using it, or any of the thousands like it - you'll want to automate handling these issues with geoprocessing. First, some research!  In the blog download is a script tool I used to scan the CSV file for maximum data widths, here is the code:   tbl = arcpy.GetParameterAsText(0) d = arcpy.da.Describe(tbl) flds = [f.name for f in d['fields'] if f.type == 'String'] mDict = {f:0 for f in flds} with arcpy.da.SearchCursor(tbl,flds) as cursor: for row in cursor: for f in flds: if row[flds.index(f)]: mDict[f] = max(mDict[f],len(row[flds.index(f)])) for k in mDict.keys(): arcpy.AddMessage(f"""Input table '{tbl}' field '{k}' has maximum data width '{mDict[k]}'""")   This yields: Maximum Text Field Widths  Ignoring the Postal Code column, which local knowledge tells me is 5 characters wide, I can now see the widths required per text field. I'm now in a position to design a schema that correctly handles the subject matter file.  I chose ModelBuilder to persist my processing as it can wrap core geoprocessing and Python functions I plan to use.  The finished model is this, which we'll walk through. EVPopulation Model The first order of business is to impose a designed schema.  This is done using the field map control in Export Table, basically copying the data into an in-memory table in the schema you want.  The Field Map control lets you rename, cast and even construct fields very flexibly. In this case there is only one source field for each output field, so the Action is always First, and only the field properties are manipulated. Field Map Downstream of Export Table the output feature class is created using the in-memory table as a template, rows (still with no geometry) are appended to it, then geometry is created from the WKT column and the two fields with zero-encoded nulls are fixed with Calculate Field tools (note the conditional processing).  Lastly, the Vehicle Location field is dropped as surplus to requirements. Are we done?  No!  Best practice is full automation, and this data is coming from the web. The URL used for file download isn't valid as a geoprocessing Text File input like the model wants. There is also a risk that a user might use an input CSV file of a different name than Electric_Vehicle_Population_Data.csv, which would trigger the field map in Export Table to revert to default field handling, undoing all our schema design work. To handle both web data input and consistent file name input the model EVPopulation is wrapped in a parent model, URL2EVPopulation: URL2EVPopulation Web Download First a Calculate Value model tool downloads the current data to a consistently named file of Text File parameter type, then the EVPopulation model is called to process the data.  See how a very simple Python snippet takes care of automation? 😉 Now we have automated CSV file handling!  I stretched the concept of "local well-known files" a little by reaching out to the web for data, but these days files across the internet are local.  I can schedule or manually run URL2EVPopulation any time I like. The models and script tool are in the blog download.   ... View more

SQL powered ETL.  How hard can it be, you know COPY in PostgreSQL and how to stage data in cloud warehouses, right? How about downloading 51 million road segments from a cloud object store into your Pro project? Road segments in the USA Or the same data for France? Parisian transport segments from Overture via SQLIn the world of data engineering, the process of Extract, Transform, Load (ETL) can often be a daunting task, especially when dealing with complex columnar data from cloud-native sources. However, with the right tools and techniques, it can be simplified significantly. This blog post delves into using SQL to streamline ETL processes, specifically focusing on handling complex georelational data types. By leveraging the power of SQL, along with tools like DuckDB and ArcGIS, we can efficiently manage and transform large datasets, such as those provided by the Overture Maps Foundation. Whether you're dealing with GeoParquet, JSON, or CSV files, this guide will show you how to unpack and normalize complex data structures, making your ETL tasks more manageable and less time-consuming. You're probably aware of GeoParquet as an upcoming format, and this post uses the format as subject matter.  If it's possible to inject complex objects into columnar data, someone will do it. I suppose the rationale is to supply a rich data model in a single row and a complete dataset in a single file or set of files of the same schema. However, it puts pressure on us ETL folks to unpack into a normalized schema. Now to throw a little light on the subject matter, look closely at the map above (click to enlarge). The linear features are transport segments of many types (road, rail, and water types with many subtypes of road) from the Overture Maps Foundation transportation theme.  Overture delivers global scale data as GeoParquet files in S3 and Azure blob storage with anonymous access. The parquet files are partitioned into chunks of about 1GB using a common schema per theme.  The segment features for an area of interest can be split across any number of parquet files, random spatial access is facilitated by a bounding box property on the data. This is big data, you don't want to download it all to get going. In the lower right-hand area, you'll see a yellow feature heading southeast out of Paris; it's the E54 route that makes its way to Mulhouse on the German border. Here is a smaller-scale view: E54 from Paris to Mulhouse Now you can see I have lots of data – millions and millions of transport segments in France. While the source data is of a global scale, I just extracted features in France. Not shown are many more millions of various event types - road surface, condition flags, access restrictions, speed zones, turn restrictions, routes, and so on. All are imported to ArcGIS from GeoParquet in an S3 bucket using SQL. Full disclosure, the ETL is given a leg up by ArcPy, but nothing that will surprise you. Processing uses notebooks in ArcGIS Pro. If you wanted, these could be hosted notebooks in ArcGIS Online or ArcGIS Enterprise. My invaluable helper in all this is the DuckDB package, supplying connectivity to web data sources, remote query capability for S3-API-compliant object stores, a performant local database, and above all, SQL support for files, including GeoParquet, and additionally supporting spatial SQL!  Who knew files could behave like DBMS objects?! Before we go further, what is one of these complex objects I'm talking about, I hear you say? Here is one:   { "speed_limits": [ { "is_max_speed_variable": true, "between": [ 0, 0.025818175 ] }, { "max_speed": { "value": 120, "unit": "km/h" }, "is_max_speed_variable": true, "between": [ 0.025818175, 0.125278098 ] }, { "max_speed": { "value": 100, "unit": "km/h" }, "is_max_speed_variable": true, "between": [ 0.125278098, 0.219762685 ] }, { "max_speed": { "value": 80, "unit": "km/h" }, "is_max_speed_variable": true, "between": [ 0.219762685, 0.831591278 ] }, { "is_max_speed_variable": true, "between": [ 0.831591278, 1 ] } ] }   The above is a struct column value from a single transport segment that defines speed zones along the segment. There are five zones for the segment and four have a maximum speed property. They could also have a minimum speed property or conditional properties pertaining to heavy vehicles, axle count, time of day, and so on. The 'between' array is a pair of linear event coordinates that ArcGIS can display given a suitable route system (which we'll build). You're going to see how to unravel all this with SQL. My data is Overture Maps Foundation transportation theme at the 2024-07-22.0 release. Much of Overture's data is out of beta, but the transportation theme at writing remains in beta, so while you may edit the notebooks to use a later release, there may be schema changes. Where do you start? You start by seeing what you've got in the data source. Here, I am doing that in a utility notebook: Inspecting the segments theme The important things in the upper cell are importing duckdb (you'll need to clone your environment and install it from Conda Forge or PyPi) and creating a connection (which could be just to memory - no database path). The lower cell describes my subject matter data by reading the S3 hive. By inspection, I can see ordinary columns that are VARCHAR, but one, 'connector_ids', is an array signified by the [] brackets at the end. There are a bunch of these structs, which I think of as JSON dictionaries. Frustratingly, the column_type details do not line-wrap, so a little Python to the rescue, and we can see struct columns can also be arrays, for example, as we see in speed_limits above. Showing the full schema So, having inspected the segment schema documentation and sample data, we can see the basic shape of it is segment linestring features (with unique key 'id'), WKB geometry, a bounding box struct, some descriptive fields, plus a set of array & struct fields we'll have to unpack into linear events. In classic divide and conquer fashion, I split the job into tractable chunks with a view to making it easy for you to modify for your area of interest. To get started, add the blog download notebooks to a Pro 3.3+ project; I created a new project.  I'm guessing most people will have an area of interest, not the whole planet, so I based my processing around this. You are free to extract the whole planet but it may take some time.😉 Overture includes a political divisions dataset named division_area. The notebook that extracts this data is DivisionArea. Open and run DivisionArea, and inspect the output. DivisonArea features Identify some features in your area of interest.  Determine a layer definition query ('where' clause) for the data that defines the total set of features within which you want to extract transportation theme data from Overture. For example, my area of interest is the country of France:     country = 'FR' and subtype in ('country') and names.primary in ('France')     You don't need to apply a layer definition query, it's the SQL we want. You can select any number or combination of DivisionArea features, disjoint or not. The next step is to extract the segments data from S3 to a local project DuckDB database. That will save going back to the S3 bucket again for data. Open the Segments notebook. Edit the line for wktRelation to use your where clause.  You'll see a few samples I left behind. Save and run the notebook. Depending on the feature count in your area of interest it may take some time to extract the data. The notebook also makes a route system.  In my case it took about 50 minutes for all metropolitan France, including making a Connectors feature class, which you may consider optional. Now you have your transport segments table in DuckDB you can run the rest of the notebooks, in any order, to make your transport information product - the various events on the segments. Here is a simple one for road condition flags in the Flags notebook: Flags notebook If you do a little reading on DuckDB you'll see it includes the GDAL library which has the OpenFileGDB driver so it's possible to use a simpler approach to write out the data. However, while this automatically derives a schema, it defaults text fields to 64K width and appears not to offer the OPENFILEGDB_DEFAULT_STRING_WIDTH configuration parameter, so I defined the schema manually with ArcPy. To find suitable text field widths I used a two-stage process, first writing with very large widths, then checking the actual data size using a simple script tool (code below, atbx included in the blog download) to refine the text widths:     tbl = arcpy.GetParameterAsText(0) d = arcpy.da.Describe(tbl) flds = [f.name for f in d['fields'] if f.type == 'String'] mDict = {f:0 for f in flds} with arcpy.da.SearchCursor(tbl,flds) as cursor: for row in cursor: for f in flds: if row[flds.index(f)]: mDict[f] = max(mDict[f],len(row[flds.index(f)])) for k in mDict.keys(): arcpy.AddMessage(f"""Input table '{tbl}' field '{k}' has maximum data width '{mDict[k]}'""")     You'll see in the notebooks I may use multiple UNNEST steps on each array and struct. This powerful function does what it suggests and flattens the data - arrays into new rows and structs into new columns. However, if a row has a null value for an array or struct then the unnest function does not emit the row so you'll see UNION clauses to bring all rows through, for example in the AccessRestrictions notebook.     # when_using conn.sql("""create or replace temp view restrictions_view3 as select id, access_type, when_during, when_heading, unnest(when_using) as when_using, when_recognized, when_mode, vehicle, begins, ends from restrictions_view2 where when_using is not null union all by name select * exclude (when_using) from restrictions_view2 where when_using is null;""")     If 'when_using' is null we still want the row because we have yet to unpack 'when_recognized' and 'when_mode'. The exclude term prevents a column type error between VARCHAR values unnested from 'when_using' and STRUCT values in 'when_using' before unnesting. All the notebooks except ProhibitedTransitions create event tables you get on the map as linear events along the  SegmentsM route system, using the begins and ends fields as from-measure and to-measure values.  See the Make Route Event Layer geoprocessing tool. ProhibitedTransitions creates line features with a transition_order sequence flag. That's it, bulk data delivered from a cloud object store with the help of SQL!  If you look back at the problem before starting, it looked very daunting, something for some serious developer time.  Remote GeoParquet files in an S3 hive, complex column types with nested data, geometric scoping using linear referencing – all these problems go away when you throw the right tools at them.  The star of the show though is SQL, making complex ETL simpler. Notes:  I had some feedback that prompts me to add this clarification: The order in which to implement this sample is: Download the blog attachment to a project home folder, preferably a new project Install the DuckDB package if you have not already, and activate the environment This is called python-duckdb in Conda Forge, duckdb in PyPi Run the DivisionArea notebook and inspect the output feature layer Figure out the where clause you need to select DivisionArea features for your area of interest Plug the where clause into the Segments notebook Run the Segments notebook This will create a segments.duckdb local database Run the other notebooks These depend on reading segments.duckdb ... View more

In an earlier post I enumerated common data source types that you will encounter when bringing data into ArcGIS.  This document discusses one of these data patterns - common file types available on your computer or by simple manual download. The particular scenario discussed in the attached document is a CSV file with commonly found ETL requirements:  Controlling the output geodatabase feature class schema and creating geometry from a column.  ModelBuilder in ArcGIS Pro 3.3 is the automation environment - it has everything you need, and preserves work for on-demand re-use. The post attachment includes the discussion document and Model, but not the subject data, which you can download yourself. Please use this post to ask questions or comment. ... View more

In an earlier post I enumerated common data source types that you will encounter when bringing data into ArcGIS.  This document discusses one of these data patterns - calling an API that requires pagination to retrieve a complete dataset.  Pagination is a technique used by data publishers to manage load on their servers. Response payloads are typically JSON or CSV. Please read the attached document and use this space to ask questions. The post attachment also includes the ArcGIS Pro 3.3 toolbox discussed, but not the subject data. ... View more

One-page "cheat sheet" documents are always popular, for example in the context of ArcGIS a perennial favorite has always been the functionality matrix for ArcGIS Enterprise. When considering your inbound ETL options there are high level decision criteria, this document (available as PDF in the post download) is a brief summary of two of them, namely where your source data comes from and how often you need to bring it in. This is only light guidance, in many cases you will have multiple implementation options, so treat this as an invitation to have a discussion, including in this community space. ETL Patterns   ... View more