Locating Incidents based on Map Coordinates

[socalvelo]

CR XI
Oracle db

Results: Locate clusters of geographic coordinates within a range.

I am analyzing 911 incidents for police calls. Each call has a geographic coordinate, X and Y. For the purposes of this question I will use these fields. CallNo, CallType, X, Y

X and Y are assigned by geocoding an address and are numeric. The X Y coordinates represent feet. So for example call #1 X= 6469774 Y=1785763. Call number 2 X= 64679974 and Y = 1785863. These two call incidents would be near each other, 200 ft, and 100ft respectively.

What I am trying to do with Crystal is to analyze hundreds of these X Y coordinates and locate high areas of calls based on how large of an area I want to search. I have already done this if I identify the first set of coordinates and look for calls within a range. I would like to have Crystal find clusters without first identifying a single XY point. This would be like finding more than 5 calls near one single call.

Can someone give some advise on how to start this.
I would probably develop a parameter field that asks how many feet the search should be from one single call.

I am thinking this will require a variable chosen from one set of coordinates that evaluated all the others. Then the second set of coordinates so it after, and so on.

 

[HowardHammerman]

Here is one way. Since your coordinate are feet, let's define a cluster as an area 100 feet by 100 feet.
Now if you divide both coordinates by 100 and truncate the result you get a group of points. Using your first example:
X=64697, Y=17857. Turn these two into strings and concatenate them. Then group on the result. Here is a formula to do that:

Totext(truncate({table.fieldx}))+Totext(truncate({table.fieldy}))

I love working on geographic reports. Let me know if I can help.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[socalvelo]

Howard,
Thanks for the reply (sorry for my delay responding back).
This is an excellent idea and it works well. Using 100 feet works well because it drops off the last few digits on the coordinates. However, if I want a to make a 300 ft search for example, I don't end up with clusters as there are no matching numbers.

 

[HowardHammerman]

Ok, now we have to do some math. If you remember your high school geometry, the sides of a right triangle relate to each other in the following way:

c squared = a squared + b squared.

thererfore the length of c =
square root of a squared + b squared.

In your case the right triangle will start from the zero, zero. point. So to find the distance between any two points you would calculate

square root of
abs(x1 -x2)squared minus
abs(y1- y2) squared

The "gottya" in your case is that you will have to do this for every pair of points. I can't think of a way to do this in Crystal. When I have this type of assignemnt I write a procedure in Foxpro to generate a temporary table with every pair of points and the distance between them.

Then it is easy to group by any distance you want:

truncate(@distance/300) for example.



Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lbass]

Just a little comment. Without understanding how mapping works, I wonder whether adding the same table a second time would allow you to compare each set of points with every other set of points.

-LB