topic Help building a database with street segments; weekly crime/police data in Mapping Questions

Help building a database with street segments; weekly crime/police data

MikeDeckard — Thu, 17 Jan 2013 16:56:06 GMT

I REALLY need help trying to figure out how to build this dataset! I'm tasked with constructing a database containing three core elements:

1) crime data for a mid-sized city from 2012
2) police dispatch data from 2012
3) census data from the 2010 decenniel census

My advisor wants each row (we'll do data analysis in Stata) to correspond to a "segement-week".

I.e.:
Row 1 = Segment 1, Week1
Row 2 = Segment 2, Week2
....
Row 9288 = Segment 258, Week36

I'm struggling with finding the quickest and easiest way to accomplish this task. I pull the data in Excel '.CSV' format and Geocode it in ArcMap10.1. The only thing I have come up with is building a giant model to put each segment-week in a new feature class, then merging all 9,000+ together into the database.

Can anyone help me wrap my head around this? Can it be done without a team of programmers with unlimited time and resources? I.e., can a graduate research assistant do it in a couple of days? :confused:

Thanks!

MD

Re: Help building a database with street segments; weekly crime/police data

TrishRice — Tue, 05 Feb 2013 21:15:03 GMT

Sounds like this might be too late to help with your project but here's an idea for posterity.

If you want the output as a spatial file this is what comes to mind. I'd add a "Week" field to your street segments. Number them all 1 initially. Open an edit session. Copy/paste all the segments over themselves, and number the duplicates Week 2. Repeat for all the weeks you need. There is probably a way to model this but I know zilch about modeling. Similarly you could make duplicate feature classes of the segments, one for each week, assign the week values, and merge the feature classes together.

If you want the output as an Excel table, copy your attribute table into it. Assign all existing rows to Week 1. Use an equation to calculate the first new value of Week 2 and drag that equation down for all the weeks you need. It will repeat your segment IDs and increment the weeks. For three segments it'd look like so:
FID Segment Week
1.....1.............1
2.....1.2..........1
3.....1.3..........1
4.....=B2.........=C2+1
5.....=B4.........=C3+1
6.....=B5.........=C4+1