SELECT distinct(b.rankyear) FROM magazine_details a left outer join ranking b on b.mag_id=a.sno where b.app_status != 0 and a.cat_id=149 and b.rnkid is not null group by a.year order by a.sno desc limit 1
SELECT distinct(a.year), count(*) as count_yr, a.image_name,a.url,a.label1,a.label2,a.date, b.rnkid,b.rnktitle,b.rankpicname,b.weburl,a.cat_id,b.app_status,b.mag_id,b.master_stat FROM magazine_details a left outer join ranking b on b.mag_id=a.sno where b.app_status != 0 and a.cat_id=149 and b.rnkid is not null and b.rankyear = '2019' group by a.year order by a.sno desc
Catherine Boush, GIS Analyst, City of Roanoke, Virginia
The City of Roanoke is participating in a regional GIS data-sharing project with neighboring localities Roanoke County and the City of Salem. Earlier this year, the municipalities applied for and received a grant from the Virginia 9-1-1 Services Board to help prepare for upcoming changes in Next Generation 911 (NG9-1-1) emergency response.
The purpose of the project was to create a common dataset and schema for integration into each locality’s emergency 911 (E911) computer-aided dispatching (CAD) system. Feature layers include address points, road centerlines, municipal boundaries, fire response zones, and law districts. Other layers were included for cartographic purposes. The top priority of the project was for each locality to have GIS data schema that was NG9-1-1 compliant and ingestible into each locality’s CAD system.
Through a series of workshops, a project scope was developed. These workshops looked at each localities CAD software and GIS Architecture to develop the most efficient methodologies to meet requirements for all stakeholders. Each locality uses a different CAD vendor but all use Esri GIS technology. A review of each localities GIS data was performed and data clean-up processes were recommended. Discussion included what reporting tools would be useful to stakeholders.
All three municipalities agreed to upgrade their GIS architecture to ArcGIS Enterprise 10.6 in order to leverage Portal for ArcGIS, which allows users to share data, maps and apps with those inside and outside of their organization.
Tracey Leet, GIS Manager, City of Roanoke, Virginia
It was the top choice for collaboration as it allows each locality to access their own site and download regional GIS data as needed without requesting from another locality.
Each locality created a file geodatabase to store the necessary feature classes for the project. The geodatabase was uploaded to Portal for ArcGIS for collaboration with other municipalities. A staging geo-database was populated to store the regional feature classes and an ETL tool was used to automate data migration from the locality’s source data to the staging database. The extract, transform and load (ETL) tool runs weekly and notifies managers via email if there is a problem running the tool. The ETL process begins with the creation of the target schema to be created. The existing data from each locality’s feature classes was cross-walked to the appropriate feature class and attributes in the staging geodatabase. When the ETL process is complete, a file geodatabase with all the regional feature classes is created in Portal and can be downloaded by each locality to be used in their CAD system.
Tools were also created in Portal to review data accuracy and assist with data clean up. A geo form was created for E911 staff to enter in any dispatch routing issues and a dashboard was created to view the results. A data reviewer dashboard tool was also created to show discrepancies in the data such as boundary gaps and overlaps. These tools allowed for GIS and E911 managers to visualize problem areas and develop resolutions for address data conflicts. Data accuracy was vital to the project. Throughout the project, conflict areas were identified and a plan developed for resolution.
Perhaps the greatest success of the regional GIS data-sharing project has been the increased communication between participating communities including GIS staff, public safety managers, and E911 staff. Bi-weekly conference calls were scheduled to complete the project, along with quarterly meetings to address data clean-up efforts. Next steps for the project include further testing the GIS data in each locality’s CAD system and continuing to address any data discrepancies that may arise.