Your state is pondering a new law: No convicted sex offenders should live within a mile of a day-care center. But what's the context? To find out, you need to answer two questions: How many sex offenders actually live near each day-care center in your neighborhood and, for each one, exactly how far away is the nearest offender?
You know how to start answering these questions using your geographic information system (GIS) program - I used ESRI ArcView 3.2. You've already used geocoding to turn a data table of day-care center street addresses in your community into a point map, and did the same for a table of convicted sex offenders. So now all you have to do is count up which ones are near each other.
How?
That's where it gets interesting.
ArcView will need to go through and check each day-care center for the closest sex offender. And it will have to count how many offender points are within a mile of each center. That's simple to do by hand if you're only looking at one or two facilities. But what if you're looking at dozens, or thousands?
ArcView doesn't come with that ability. You have to build it yourself using ArcView's scripting language, called Avenue. Once you learn Avenue you can make ArcView do pretty much anything.
In this case, it's using coordinate geometry. To answer the questions, you'll need to fill out both themes with accurate X and y coordinates. Then you can use scripts or queries to search through the data to figure out which sex offenders are closest to the day-care centers.
Step one is creating the coordinates. If you have a point theme in ArcView, odds are you're dealing in latitude and longitude - decimal degrees. While these are flexible, they aren't the best for measuring distance, so you need to put some coordinates in your data.
Start by changing the projection of your view. Switch to the NAD 1983 projection for your state plane. (These projections are built in to ArcView 3.x.) Also, change the distance and map units to feet. Then open the attributes table for the theme you want to work on first. Start editing it and add two fields: "ProjX" and "ProjY". Set them up as numeric and be sure to allow for several decimal places. Close the table but don't hit the "Stop Editing" button yet.
Now you're ready to add some coordinates. To do it, you'll need an Avenue script that cycles through each record and retrieves the X and y coordinates that correspond to the map projection you've chosen. The code looks like this:
curView = av.GetProject. FindDoc("mymap" thmToUpdate = curView.FindTheme("shp") tabToUpdate = thmToUpdate.GetFTab prjView = curView.GetProjection
fldShape = tabToUpdate.FindField("Shape") fldProjX = tabToUpdate.FindField("projx") fldProjY = FindField
for each rec in tabToUpdate
curRect = tabToUpdate.ReturnValue(fldShape, rec)
ProjX = curRec.ReturnProjected(prjView).GetX ProjY = curRec.ReturnProjected(prjView).GetY
tabToUpdate.SetValue(fldProjX, rec, ProjX) tabToUpdate.SetValue(fldProjY, rec, ProjY)
end
After you enter the code, make sure you update it with the name of your view and theme. Then compile it with the checkmark icon and run it.
Now back to your table. Look at the ProjX and ProjY fields and they should be filled in with numbers - the eastings and northings of your point. Instead of using latitude and longitude, these coordinates identify a point based on how many feet north and east it is from a predefined point on the map. Click "Stop Editing" and save the results. Then export the table you just finished and repeat the process for your second table. Import both into Access.
This leaves you with a database, two tables that lack a common field for joining. But Microsoft Access and other database managers will let you use more sophisticated relationships.
Instead of using a join, you can link the points in the two tables using the Pythagorean distance formula, which can calculate the distance between any two points on a plane using their coordinates.
Here's how you'd use Access SQL to link the tables based upon points that are at most a mile (5,280 feet) apart:
WHERE sqr((((daycare.ProjX - offenders.ProjX)^2)+(daycare.ProjY- offenders.ProjY)^2)) <= 5280
Now you can insert that into a SQL statement to answer the two questions.
First: How many sex offenders live within a mile of each day-care center? You'll want a query that will check the data and count every point where the distance is less than 5,280 feet. Here's the SQL:
SELECT daycare.Name, Count(offenders.Name) AS offenders_within_mile FROM daycare, offenders WHERE (((Sqr((((offenders.projx-daycare.projx)^2)+((offenders.projy-daycare.projy)^2))))<5280) AND ((daycare.projX) Is Not Null) AND ((offenders.projX) Is Not Null)) GROUP BY daycare.Name;
It's important that your query filter out null values, which will appear in any records that you were unable to geocode. Even if only one record wasn't coded to a spot on the map, the null value will cause Access to choke.
Second: How far from each day-care center is the nearest sex offender? You'll want a query that, for each day-care center, chooses the minimum result from the distance calculation. Here's the SQL:
SELECT daycare.Name, Min(Sqr((((offenders.projx-daycare.projx)^2)+(offenders.projy-daycare.projy)^2))) AS distance FROM daycare, offenders WHERE (((daycare.projx) Is Not Null) AND ((offenders.projx) Is Not Null)) GROUP BY daycare.Name;
It's possible to handle these calculations with Avenue (and you don't even have to get Pythagorean to do it, because the language has a built-in distance function). But bringing it back into a database is easier, not to mention much faster. And once you've figured out how to do spatial math in Access, it's easy to change your analysis, ask different questions of your data or bring in other data, maybe the location of sex crimes or neighborhood police stations.
What else can you do?
Why not find out how many sewer pipes dump out near your community's beaches? Or look at whether fires are happening close to the fire departments. You could add the distance calculations to an analysis of ambulance response times. And you could incorporate it into your Web site by creating searches that let users find the day-care centers closest to their homes.
Contact Brad Heath by e-mail at bheath@detnews.com.