Computer Assisted Reporting
This is the last of four articles about analyzing distances between sex offenders and church child daycare centers in Missouri as part of a joint project with KSHB NBC Action News in Kansas City.
The previous two articles gave details about how to use Google Geocoding to convert an address to geolocation coordinates, and then how to use those coordinates to compute a distance matrix between sex offenders and child care facilities.
Batchgeo.com Mapping Tool
The web site batchgeo.com provides an easy-to-use web interface for creating interactive Google maps. If you have names and addresses and other information, you can quickly create on online Google map with up to 2500 points.
Normally batchgeo takes addresses and geocodes them, much like described in the earlier article in this series using R. However, with geocoded data with longitude and latitude values already computed, the batchgeo process is even faster since the geocoding has already been done.
To show Missouri sex offenders and child care facilities on the same map, we need to integrate two datasets together since there is only one input file to batchgeo.
We want to map any sex offender within 1000 feet of any child care provider, and likewise, any child care provider within a 100 feet of a sex offender. This information can be deduced from the distance matrix explained in the earlier article.
The fields in this csv file are as follows:
- Category: Offender or Provider
- DistanceFeet: Distance to closest offender/provider
- CountTooClose: How many are too close to this offender/provider
- Comments: Geocoding results. If offender: compliance and offenses.
To distinguish between an “offender” and a “provider” a Category field was introduced.
The Name, Address (City, State, Zip, County) and geolocation coordinates (Latitude, Longitude) matched between offender and provider datasets.
The DistanceFeet field for an offender tells the distance to the closest church child daycare facility. For a child care facility, this distance is how close an offender is.
An offender can be less than 1000 feet to more than once daycare facility. Likewise, a childcare facility can be closer than 1000 feet to more than one offender. CountTooClose tells how many are within 1000 feet including the closest one.
The Comments field has several subparts. For both offenders and providers, the first part of the field indicates how good the geocoding results were. “ROOFTOP” is the best geocoding match.
For offenders only, the remaining part of the comments show whether they are compliant in reporting their address to authorities, and what offenses they committed.
The biggest part of the Create-Map-Dataset.R script dealt with combining multiple offenses into a single string for an offender since the original data represented this data with multiple records.
Once this R script created the ProviderOffenderGoogleMaps.csv file, we were ready to create our interactive Google map.
Batchgeo Map Definition
We opened he ProviderOffenderGoogleMaps.csv file in Excel, selected the whole worksheet, and placed its contents on the clipboard.
Next we went to the batchgeo.com web site.
After pasting the data into the web page, press Validate and Set Options. The data appears in columns:
Let’s also look at the Advanced Options:
At this point, we can press on the Make Google Map button. Since geocoding is not needed, the map is displayed immediately:
Press Save & Continue
Give information about the map. Specify the Email address that will be used to claim the map to make changes.
You can choose to have a map unlisted. You will be sent a URL that would be unlikely to be guessed that can be shared with others to view the map before you decide to make it public.
Press Save Map and view your map. Record the URL if desired.
Check your E-mail for the notification about your map:
From: BatchGeo [mailto:firstname.lastname@example.org]
Sent: Thursday, July 28, 2011 2:44 AM
Subject: Missouri Sex Offender/CurcChild Day Care Provider
Your map is available at:
Edit your map:
To EDIT your map options & data, click the URL below:
Embed your map:
To embed this map on your web site, use the following HTML code:
<p><iframe src=”http://batchgeo.com/map/7447d73c388ebb0ca6e692169bea5178″ frameborder=”0″ width=”100%” height=”550″ style=”border:1px solid #aaa;border-radius:10px;”></iframe></p><p><small>View <a href=”http://batchgeo.com/map/7447d73c388ebb0ca6e692169bea5178″>Missouri Sex Offender/CurcChild Day Care Provider</a> in a full screen map</small></p>
To create a locator box, use the following HTML code:
<form action=”http://batchgeo.com/map/”> <input type=”hidden” name=”i” value=”7447d73c388ebb0ca6e692169bea5178″> <label>Search for nearest location <input type=”text” name=”q” value=””></label> <input type=”submit” value=”Search”></form>
We now offer extended functionality for professional BatchGeo users.
10x Faster Geocoding – make maps in seconds
Enhanced security – full time SSL and password protection
Export to PDF – get PDF copies of map for extra portability
30 Days no questions asked full refund policy
At this point I recommend using the edit link from the E-mail to change a few options not shown the first time.
You can also make any other changes to your map, including replacing the data behind it.
By default “Advertisements” are enabled and your map will expire two months after no pageviews. For published articles I change both of these selections to “Disabled”.
After making changes, press the Update Map button at the bottom of the page.
If you no longer want your map, there is a Delete option at the bottom of the page.
Here’s the final map for the KSHB study of Missouri sex offenders living withing 1000 feet of church daycare centers:
View Provider-Offender Map in a full screen map
I’m not sure why, but WordPress destroys the HTML code for the embedded map in “visual” editing mode. I have to add the HTML to embed the map only in HTML editing mode. This is a pain to remember during subsequent edits of an article containing a batchgeo.com map.
Program and Files
Create-Map-Dataset.R reads “min-distance” files derived from distance matrix and selects offenders and providers 1000 feet apart or closer. Uses offender master file to extract offenses to be used as comments on batchgeo.com Google map.
- msor-offender-master-file.csv (used to get compliance, offenses for offender)
- ChurchDaycare-min-distance-from-offender-to-any-provider.txt (extract from zip file)
Output file (load into Excel and cut and paste to batchgeo.com):
- Loophole in law allows hundreds of Missouri sex offenders to live near church daycares, KSHB NBC Action News, Kansas City, July 18, 2011.
- Analysis of Missouri Sex Offender Registry Data, Franklin Center CAR Blog, July 18, 2011.
- Geocoding addresses from Missouri Sex Offender Registry, Franklin Center CAR Blog, July 19, 2011.
- Computing distance matrix between Missouri sex offenders and child daycare facilities, Franklin Center CAR Blog, July 28, 2011.
- Sex offender rules strengthened, Independence/Blue Springs/Grain Valley Examiner, June 25, 2011.
- Lande rape case may alter law: Child rape forces lawmakers to look at state sex offender statute, Independence/Blue Springs/Grain Valley Examiner, June 25, 2011.
Earl F Glynn • KansasWatchdog.org • Franklin Center for Government and Public Integrity