Import old data into new Admin Interface

[arouault]

Import all old survey data and reports

[allthesignals]

There are some discrepancies in the current import process:

District name	SurveyResponseCountDiff
Abington	-16
Acton	-4
Arlington	-3
Auburn	-1
Bedford	-4
Beverly	-3
Boston	-28
Bourne	-24
Brockton	-3
Carlisle	-10
Chelsea	-1
Dennis-Yarmouth	-1
Dracut	-5
East Longmeadow	-2
Easton	-2
Fall River	-2
Falmouth	-3
Greenfield	-1
Hudson	-1
Lawrence	-1
Lexington	-1
Lynn	-272
Malden	-3
Medford	-4
Melrose	-34
Milton	-1
Nantucket	-8
Natick	-1
Needham	-2
New Bedford	-3
Newburyport	-1
Northborough	-1
Pembroke	-54
Pittsfield	-95
Plymouth	-4
Revere	-75
Salem	-100
Sharon	-2
Somerville	-3
Stoughton	-2
Wakefield	-50
Waltham	-1
Watertown	-1
Woburn	-41
Worcester	-2

[allthesignals]

Abington

For Abington, only Abington High School has any survey responses. It also has only one survey set, however, the site says there are 22 responses. Using that survey set's begin and end date and filtering the exported responses reveals this: survey_responses.abington_high[survey_responses.abington_high$created >= as.Date("2013-06-14") & survey_responses.abington_high$created <= as.Date("2013-06-25"),]

id	grade	to_school	dropoff	from_school	pickup	school_id	street	cross_st	nr_vehicles	nr_licenses	distance	created	modified	st_astext	schid	survey_id	shed	current_time
1	367	1	cp	yes	b	NA	3	NA	NA	NA	NA	0.4169399	6/14/13	6/14/13 23:12	POINT(-70.9491331641643 42.1183611253733)	10505	307	2	6/6/17 21:35
2	370	4	fv	no	fv	yes	3	NA	NA	2	569	6.8574504	6/14/13	6/14/13 23:12	POINT(-71.0032653808594 42.0574502202468)	10505	319	0	6/6/17 21:35
3	371	1	w	NA	b	NA	3	Stewart Dr	NA	10	0	1.0737291	6/14/13	6/14/13 23:12	POINT(-70.9507196151247 42.1101713913868)	10505	321	0	6/6/17 21:35
4	376	11	fv	no	o	NA	3	NA	NA	1	1	0.1547214	6/25/13	6/25/13 20:47	POINT(-70.9536552429199 42.1168160205243)	10505	326	2	6/6/17 21:35
5	377	4	sb	NA	w	NA	3	Main St	Michele Dr	1	1	2.5501066	6/25/13	8/7/13 21:11	POINT(-70.9600925445557 42.114014592749)	10505	327	0	6/6/17 21:35
6	378	1	sb	NA	fv	yes	3	Main St	Pond St	1	1	2.8061114	6/25/13	8/7/13 21:11	POINT(-70.9551396113043 42.1522913832089)	10505	328	0	6/6/17 21:35
7	379	k	b	NA	sb	NA	3	NA	NA	NA	NA	1.1284097	6/25/13	6/25/13 21:11	POINT(-70.9517669677734 42.1100032419403)	10505	329	0	6/6/17 21:35

[allthesignals]

@TimReardon noted that some surveys will NOT have a survey set.

The NEW data structure is like this: Surveys have many Survey Responses

So there will be Survey Responses that are "orphaned" - they may need to be rounded up into a new foster Survey Set so that it has meaning in the new data structure.

[allthesignals]

Re-running the migration task to include survey response orphans yields much better migration differential:

District	SurveyCountDiff
Abington	-11
Boston	-1
East Longmeadow	-2
Easton	-1
Lawrence	-1
Melrose	-11
New Bedford	-3
Revere	-1
Somerville	-1
Wakefield	-1

Question/Action Items:

• Do we move forward with this data loss or investigate further? (For context, as the comments above show, the source used in the data migration is whatever SQL view is used in the old site to provide a dump of the data). We could say there was a data loss of less than 0.01%. @TimReardon.
• How should we round up survey response orphans? We could compute their min and max date ranges and use that as the survey begin & end dates. If we were really fancy, we could dynamically generate the intervals along a timeline based on "density" of survey response observations. It depends on how this might impact the data. I think the former option is the best balance of preserving data integrity vs being sane.

[allthesignals]

After running the new rake task rake import:cleanup, the differentials remain the same as last. Moving on to other tasks! This is needed-by #24