innovate-inc / crs Goto Github PK

Create script to add to rice bowls feature class and automatically write the institution a rice bowl belongs to (grab the shape), the diocese/region/congressional district it is in.

Otherwise draw time is too slow in AGOL

Need to get access to data and perform and post-processing necessary to put in map

• Grassroots action takers – still need to identified?
• States (Us Senate) – found, not exported
• Names of Members of Congress – found, not exported
• Congressional Committee/Subcommittee – found, but not exported
• Diocese -Identified CBI vs PAC yet? PAC identified, not CBI
• Farmer to Farmer volunteers – still waiting for CRS to provide

Possible issues with Jitterbit agents (dev vs. prod). All other tables have already been pushed.

Still need access to prod KnowWho data

• Institutions
• Individuals
• Donations/RiceBowls
• Projects
• Congress

Once I have completed standing up services I will begin developing AGOL items. This process will include:

• Develope Map Image Layers from published services complete with descriptions and versioning.
• Develope Web Maps
• Add map image layers
• Order and rename layers
• Check pop-ups.
• Develope WAB viewer, Discussing with CRS team what layout they would like
• Configure widgets
• Build documentation of AGOL design process for Jenny to be able to do training with the CRS team.

Review data extracted from Saleforce and determine what will be needed to make it spatially aware. Might need to write some post-processing scripts in Python.

Nate and Janeen to meet with Ben Ace (CRS SF) to determine best way to accurately estimate % of funds allocated to each country, per FY the project is active.

• Need remote desktop access to the server where ArcGIS for Server is installed
• Need ArcGIS Server to be able to communicate with the SQL Server
• Need to have ArcGIS Desktop installed on ArcGIS Server

Many of the tables have repetitive address fields, i.e. "BillingAddress city" and "BillingCity" (appear to be duplicates). Do all of the fields need to be in the final feature class, or can the just the final concatenated "Address" field be included?

Plus, many of the address-related fields are empty. Do we need these?

Do we want every other non-addressed related field?

used datetime functionality of project start_date__c and end_date__c fields to create a list of all FY's that fall w/in the active range
Jan 1 - Sept 30: current year = FY
Oct 1 - Dec 31: current year + 1 = FY
calculate range (begYear, endYear)

• Projects - updated to include calculated FY, separated by country; add Awards donors instead of donors listed in projects table (more authoritative)
• Projects, BSDI/Research, DSPNs combined in series of views according to FY and Program Area
• Institutions will require additional fields than the existing US Ops exports.
• Opportunities can be adjusted based on existing Donations table

BSDI/Research, DSPNs, Awards may not require any geocoding of their own, just adding the related data in these tables to the projects info.

All scripts/services in the CRS dev are currently published using Jenny's CRS credentials.

Julia still needs to create:

• A permanent SQL Server user to connect scripts and ArcMap connection

Janeen still needs to create:

• A permanent Server Manager user to create secure map image services in AGOL

Test that we have access to CRS AGOL account.

• Finalize title/subtitle
• Determine links (if any)
• Finalize splash page/description/help/etc.
• Additional widgets?
• Pre-configure queries?

• Re-publish map
• Re-create map image layers
• Ensure pop-ups are configured

May be able to backup and restore the existing database in the dev environment, depends on quality of dev Salesforce data.

Otherwise, may have to set aside time to ensure all scripts rerun through the full dataset.

Create Python code needed to geocode flat SQL Tables into Feature classes. Might need to find a way to catch exceptions and allow CRS team to review. This will eventually be a scheduled task that fires after the data are loaded into SQL Server from Salesforce.

Set up a time to test and finishing configuring ArcGIS Server and SQL Server connectivity.

I will be the main GIS developer in this project. Once the Salesforce team has completed the extraction and provided me with SQL_Server feature classes I will begin the process of developing MXDs. This process includes:

• Prepare data
• Create and configure GDB
• Add feature classes to MXD
• Fill in Description fields for MXD and individual Feature Layers
• Develope cartography, discus with CRS team how they would like the features to look.

Once countries list to exclude is established, create total view and program area views for 2015 and 2014

Publish 2015 FY stats as map service
Publish 2014 FY stats as map service

Sent Nate list of objects/fields to review today (12/2/16) with his SF team.

• Opportunities (push separately from US Ops)
• Awards
• BSDI/Research
• Projects (just add fields to US Ops)
• DSPNS
• Institutions (add Partner Type to US Ops)

Still waiting for feedback from CRS team to add content to Service Ops Guide. Janeen is following up with CRS people. When she gets info, Jenny will combine and update the guide to pass to Steve.

Test access to SQL Server and ArcGIS Server box... VPN and Remote Desktop.

Get Julia to change views in prod vInstitutionsAddress and vIndividualsAddress
"where Address is not null" to prevent running through 100k+ extra records

• Add Award, DSPN, BSDI to data map spreadsheet

• Provide detail about years breakdown/countries discluded in documentation

Need access to full Salesforce data (as opposed to current Sandbox environment)

This is our test record. Only one point exists. Why are we getting a value of 5 in the aggregated data? Some field mappings are off by one.

To be included as part of geocoding script...

For now will just be a SQL table including the entry name, Id, address, source table, score, and geocoding address type from any address that doesn't match... See #11 to determine hard threshold.

Will likely need help of Julia - do not currently have permission to backup/restore

Also require registering the database with ArcServer in CRS environment

The number of parishes counted in institutions varies dramatically from the numbers listing in the parish field of the institutions table

Do all geocoding results need to be matched at 100%, as opposed to accepting 80-90%?
What is the acceptable threshold where addresses need to be thrown out?

Geocoding results will be included in the output feature class and an additional error report table.

This will include:

• Copy over map docs from dev to prod, fix any broken connections
• Republish and secure in Server Manager
• Recreate map image layers in AGOL
• Create prod map and configure all pop-ups accordingly

SF
-- Push Award, BSDI, DSPN, Opportunities from SF to Prod
-- Update Jitterbit project

Julia
-- Change ExecProjectsByCountry2 to ExecutiveProjectsFC, delete out everything
-- add Alter permission to ExecutiveProjectsFC, OpportunitiesFC (figure out ixx_getid stored proc)
-- delete out all old Exec views
-- build out all new Exec views
run crsGeocodeExecProjects.py

Janeen
-- Repair connections in Total, 2014, 2015, 2016 mxds
--Republish map services in prod

-- Add exec projects to crsTruncateTables.sql and job_GeocodeSalesforceData.bat
-- Make sure scheduled task is g2g

Missing what appears to be census data across many of the polygons in regions/dioceses/congress layers, including:

• Catholic population
• Hispanic Catholic population
• Total population

Talk to Stacy about future work -- could be getting census data/tigerline data to put in CRS AGOL

Innovate Salesforce team to test access to CRS Salesforce system

Obtain Saleforce Data for GIS Team To Review. This will be a key task for the team to come up with a plan.

Nate will provide list of countries not to include in Exec map -- use same in Tina's projects layer.

-- Update Nate's views, update Tina's views

The CRS team would like to observe what has to be done rolling the GIS data from our server to theirs. They requested we schedule a time with them when we are ready to deploy to their server. This will assist them in resolving issues that may come up during deployment.

Create a general support and maintenance contract. Look at 20-30% of the original contract.

He will set up time to QA extra Exec docs