Displaying Missouri sex offender/child day care facility proximity map using batchgeo.com


Thursday, July 28th, 2011

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.

File Preparation

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.

An R script Create-Map-Dataset.R shows details of how the file ProviderOffenderGoogleMaps.csv was created for batchgeo input to integrate offenders and providers into a single file.

The fields in this csv file are as follows:

  • Category:  Offender or Provider
  • Name
  • Address
  • City
  • State
  • Zip
  • County
  • Latitude
  • Longitude
  • 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.

Initial batchgeo.com screen

After pasting the data into the web page, press Validate and Set Options.  The data appears in columns:

Batchgeo.com after "Validate & Set Options"

For this dataset, batchgeo guesses all mappings correctly, including the "Group by" Category field.

Let’s also look at the Advanced Options:

Batchgeo guess all of the "advanced options" correctly for this file

At this point, we can press on the Make Google Map button.  Since geocoding is not needed, the map is displayed immediately:

Initial batchgeo map

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.

Give your map a title and description. Instructions will be sent to the specified E-mail address about how the map can be later modified.

Press Save Map and view your map.  Record the URL if desired.

Map with title at top and description showing at bottom

Check your E-mail for the notification about your map:

From: BatchGeo [mailto:[email protected]]
Sent: Thursday, July 28, 2011 2:44 AM
To: [email protected]
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>

BatchGeo Pro
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.

Some additional options to consider

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.

Final Map

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.

Input files:

Output file (load into Excel and cut and paste to batchgeo.com):


Earl F GlynnKansasWatchdog.org • Franklin Center for Government and Public Integrity

Tags: , , , ,

Leave a Reply