prn_

1
Enterprise ETL the Home Again Web Mapping
Application Columbus, Ohio
Erick Lobao GIS Project Manager Stantec
Sonia Krammes GIS Analyst City of Columbus, Dept.
of Technology
Robert Parsons Software Engineer City of
Columbus, Dept. of Technology
Nick Soltes GIS Analyst Stantec
2

• Overview
• Home Again
• Methodology
• Technical Issues
• Web Application
• Return On Investment

3

• Home Again
• Home Again is a Public Private partnership put
  forth by the Mayor to revitalize areas of
  Columbus by purchasing, renovating, selling
  properties, and demolishing vacant homes.
• The Department of Development maintains data
  using several applications and creates reports
  from these data sources to manage the Home Again
  initiative.

Columbus
4

• Project Goals
• Combine data stored in different file formats
  with data models / table structures.
• Recreate and automate reports that were manually
  produced by a former employee.
• Display Home Again point information on a web map
  and provide a basic level of spatial analysis.

5
Workflow Diagram
6
Data Sources
7

• This is a Job for Model Builder!
• Model Builder allows you to string together
  ArcGIS processes to automate multi-step
  operations.
• Models can be exported to a script and scheduled
  to run as a scheduled task.

Model Builder scripts can be extended with Java,
Python or VBA code if needed.
8
GIS Workflow
Model goes here
And here
9

• Why Model Builder?
• Easy to use.
• Basic understanding of available geoprocessing
  tools.
• Programming language skills not required.
• Future updates, revisions, changes can be made by
  GIS staff.
• Models are re-usable / speeds up repetitive
  tasks.
• Execute tasks in a defined order.
• Open a single file to perform a total of
  31 geoprocessing operations.

10

• Under The Hood
• Models developed with one or two processes at a
  time.
• Allows you to debug / tune model properly
• Export, format, combine data from multiple
  sources.
• Join tables based on Parcel ID
• Assign spatial information to non-spatial data
• Export to python script for scheduling processes
  using MS Windows Task Scheduler

11

• Adding Location to Table

• Issue Data stored in a table without spatial
  reference.
• Address information and Home Again attributes
  stored in a table.
• Solution 1 Create an Oracle table view to join
  staging table to Citys Master Address Point
  Layer.
• Provides a Live-View of final point layer.
• Significant amount of lag time when viewing this
  layer.
• Solution 2 Use model builder to extract Home
  Again Points utilizing the coordinates of the
  Address Point Layer.
• Produces a static file each time model is
  executed.

12

• Adding Location to Table

• Join staging table to address points based on
  Parcel ID.
• Update staging table with (X,Y) from address
  points.
• Create event feature from staging table.

13

• Adding Location to Table

• Join stage table to address points where Parcel
  ID Parcel ID

14

• Adding Location to Table

• Calculate X, Y coordinates within stage table.

15

• Adding Location to Table

• Create event feature class from table.
• Remove any ltNULLgt X,Y points

16

• Good But Not Perfect
• Adds Complexity.
• Model requires ArcMap to call ArcGIS functions.
• Limits use to machines with ArcMap installed.
• ArcGIS file locking issues (Not an issue in
  Python)
• Some operations in Model Builder were buggy.
• Model Builder can do a lot, but at what point do
  you make the jump to something else?

17

• Web Application

18

• Monthly Reports

19

• ROI Process
• Whos available to maintain and update ETL
  procedures and whats their skill level?
• Leverage existing staff, software, and
  experience.
• Leverage existing software licenses (ESRI,
  Crystal Reports).
• Modular approach spreads responsibility.
• Time
• Repeatable and Automated ETL

20

• ROI Home Again
• Web Application provides GIS capabilities to non
  GIS savvy users.
• Allows GIS staff to work on higher level tasks.
• Provides spatial analysis capabilities to City
  leadership.
• Spatially enabling data.
• Getting more use / functionality from existing
  data.
• Development staff can examine relationships
  between multiple data sources.
• Informs City leadership and Home Again program
  staff.

21

• A More Permanent Solution
• Streamline ETL procedures by improving data
  capture and storage.
• Ex. use common field names data types
• Helps with report generation and data
  aggregation.
• Web site with forms to enforce data standards.
• Provide flexible reporting.

22

• Questions Comments

Erick Lobao GIS Project Manager Stantec Erick.Loba
o_at_stantec.com [phone]
Sonia Krammes GIS Analyst City of Columbus, Dept.
of Technology SRKrammes_at_columbus.gov (614)
645-5392
Robert Parsons Software Engineer City of
Columbus, Dept. of Technology RGParsons_at_columbus.g
ov [phone]
Nick Soltes GIS Analyst Stantec NSoltes_at_stantec.co
m [phone]