To Get The Job Done Scott Snider GISP GIS Coordinator City of Lancaster ITTelecom Ohio GIS Conference Hyatt Regency Columbus September 24 26 2018 Columbus Ohio Backstory Started using Cartegraph in 2014 for Asset Management ID: 727384
Download Presentation The PPT/PDF document "Advancing Your Skillset" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Advancing Your Skillset To Get The Job Done
Scott Snider, GISPGIS CoordinatorCity of Lancaster IT/Telecom
Ohio GIS Conference
Hyatt Regency Columbus
September 24 – 26, 2018
Columbus, OhioSlide2
BackstoryStarted using Cartegraph in 2014 for Asset ManagementCartegraph and GIS communication is 2-way
Utility Customer Data lives in inHance Utility Billing DatabaseGas Dept. Needed to see this data in Cartegraph with locationsOriginal Integration used GIS as the Intermediary Slide3
BackstoryOriginal IntegrationUsed GIS to grab SQL views written by a colleague
Processed all pertinent data in one modelIssues exporting this to Python to run on a scheduleSlide4
ChallengeGas Department wanted to start collecting service valves with GPSBetter field access to shutoffs in the event of an emergency
No lateral data recorded in GIS, only tap cardsOld process deleted/appended all 15K points when ranDropped points on center of parcelSlide5
GoalsUpdating points without deleting locations
Display owner/resident/meter information from UB in Cartegraph and GISEnable use of Collector to make updates in the fieldNOT make any changes or modifications in UBGrab new points added on a regular basis via scheduled taskNightly update of owner/resident/meter infoSlide6
SolutionGoogle-Fu!All research pointed to SQL to handle this data
With retirement of CIO our departmental SQL knowledge dropped to basically zeroDo I go outside the City to find someone to help?Do I buckle down and learn a little about SQL?Drop, Insert, Truncate, etc. What does it all mean?Left join, right join, inner join what’s the difference?Stored Procedure vs. JobThe learning curve was going to be steep on this project.Slide7
SolutionFirst Step: Create SQL Views to get data out of Utility Billing (without manipulating any data)Calls for data only when needed
Views might reduce performance, but it works in my caseSlide8
SolutionSecond Step: Establish points & update process
Initial Creation
(in case I need it ever again)
Regular update catching new locationsSlide9
SolutionFinal step: Series of scheduled tasks to create .csv files and run embedded import tools in CartegraphSlide10
SolutionData used in GIS through related tablesData used in Cartegraph matchesSlide11
ConclusionDon’t be afraid to take on a huge challenge and even rewrite old processes.
*Expect
setbacks along the path though*