10 Replies Latest reply on Sep 19, 2013 11:48 AM by bgmartai

    Finding a percentage in a grid/polygon

    bgmartai
      Hello everyone,

      I'm working on a state level map and I have a year's worth of crashes. The crash data includes age.
      Younger drivers, by the standards I'm looking at, are between the ages of 16 and 19, inclusive. I have 1 square mile grids over the state (used Fishnet tool), and need to find the percentage of younger drivers out of all drivers in each grid.
      That is to say, if a grid has 100 drivers total, and 20 of them are younger, then it would equal 20% or 0.2. The point of this is to have these percentages get color-coded and look at where younger drivers cause more crashes than in other locations.

      I have no idea how to go about this, as I do not know how to VB Script (I looked at the Field Calculator in the Attribute Table, but wasn't sure what to do with it).

      I'm using Desktop 10.0, SP 5.
        • Re: Finding a percentage in a grid/polygon
          GeoffAppleby
          Sounds like an interesting analysis.

          What data format is your crash data in? Points at where the crash occurred or is it just non-spatial data as in a table?

          Should be an easy enough couple of steps, just curious to know the data a bit better.

          Thanks,
          • Re: Finding a percentage in a grid/polygon
            bgmartai
            Sounds like an interesting analysis.

            What data format is your crash data in? Points at where the crash occurred or is it just non-spatial data as in a table?

            Should be an easy enough couple of steps, just curious to know the data a bit better.

            Thanks,


            The data is pulled from a network database, and it has x- and y-coordinates, along with numerous other fields (weather, day, time, etc.) in an Excel workbook (Excel 97 format). The coordinates are used to map the points, but there are thousands of points so obviously I need an automated method for what I'm trying to do.
            I still haven't figured out a way to accomplish this.
            Do you have any insight as to how I should go about doing it? Seems like you have a general idea.

            I'm still learning GIS so if you could explain the steps relatively clearly, I would really appreciate it.
            • Re: Finding a percentage in a grid/polygon
              GeoffAppleby
              I feel like I have an idea, but others may have better ideas :P

              My instinct would be to create a point dataset (shapefile or feature class in a geodatabase) from the excel table using the X and Y coordinates. Hopefully you know the spatial reference of the points (I. e. latitude & longitude in degrees, or UTM coordinates in meters, etc...).

              Open ArcCatalog, navigate to the excel table, and open it one level (double click) to show the worksheets. Find out which sheet has the data, right click, "Create Feature Class" from XY Table.

              Choose the appropriate fields for the X and Y coordinates and the coordinate system, then save the feature class.

              I'll stop there for now just to make sure we're on the same page. I'll have to do a bit of thinking for the rest of the steps based on how the data is formatted.

              Let me know if that works.
              • Re: Finding a percentage in a grid/polygon
                bgmartai
                I already have the map all drawn up with the data points and the 1 square mile polygons from the fishnet tool.
                I just need to figure out how to do the calculations within each polygon.
                This is so that we can narrow down where police should patrol for certain age groups.
                • Re: Finding a percentage in a grid/polygon
                  GSCUser85
                  First step would be to spatially join the polygons to the points. Then you have a polygon identifier attached to each point. (So you know which points are in which polygon).
                  These can then be summarized in various ways using the poly ID as the grouping variable.
                  The details of how you would get the stats you need for young drivers would depend on the content of your data.
                  Cheers,
                  Neil
                  • Re: Finding a percentage in a grid/polygon
                    bgmartai
                    First step would be to spatially join the polygons to the points. Then you have a polygon identifier attached to each point. (So you know which points are in which polygon).
                    These can then be summarized in various ways using the poly ID as the grouping variable.
                    The details of how you would get the stats you need for young drivers would depend on the content of your data.
                    Cheers,
                    Neil


                    I'm assuming I can just google the different parts of these steps (like how to spatially join polygons to the points).
                    Once they are attached, how do I use the poly ID as a grouping variable? As in, what tool would allow me to actually do the calculation of the percentage?
                    • Re: Finding a percentage in a grid/polygon
                      dkwiens
                      Look into the Summary Statistics tool (case field = grouping field).
                      • Re: Finding a percentage in a grid/polygon
                        GSCUser85
                        The Arc help files are also rather useful....
                        http://resources.arcgis.com/en/help/main/10.2/index.html#//00080000000q000000
                        Also the link to the summary stats tool in the previous post.
                        • Re: Finding a percentage in a grid/polygon
                          bgmartai
                          Every time I run the spatial join tool, it gives me the check mark showing that it worked. However, the layer itself never has anything in it in the attribute table. I'm using the Fishnet as the Target Feature, and the layer of crash data as the Join Feature.
                          I also have a layer called COUNT in the crash data which I assume would be used to count the number of crashes in each polygon.
                          I'm just really lost and I literally don't know how to do this, even with the responses you guys have given me. :/
                          Hopefully this image will help clarify some things as far as what I'm trying to do.
                          [ATTACH=CONFIG]27595[/ATTACH]

                          The picture shows all of the different data in the crash layer, but the fishnet layer only has an FID, Shape, and Id (all equal to 0) columns. That is to say, they don't have any of the same columns as far as I can tell.
                          • Re: Finding a percentage in a grid/polygon
                            bgmartai
                            OK, so I'm trying a new way. I have the layers you can see in the picture. I'm trying to divide the sum of the Merged_Younger by the sum of the Merged_Polygon layer. However, since they are not rasters, Divide is not a working tool. Also, turning them into rasters and then using Divide does not give me a proper answer (in the picture it just gives me 0, and 1, I couldn't change the symbology).
                            I was wondering how I could divide the values in the Younger layer by the values in the Polygon layer, keeping the fishnet layer properties intact. That is, I want the new layer's values to be just the ratios of younger drivers over all drivers.
                            Any ideas?
                            [ATTACH=CONFIG]27604[/ATTACH]