Spatial Query I

1. Spatial Query I Exercise 6 GIS in Planning and Health Fall, 2006 Wansoo Im, Ph.D.

2. Go to Google.com and type “Acute Care Hospital New Jersey Shapefile” • You should be able to find a link which shows the following • www.state.nj.us/health/chs/gis/nj_hospitals.htm • After you click the link, you should be able to find the link • http://www.state.nj.us/health/chs/gis/nj_hospitals.zip • click and extract the file to c:\gis directory • The new shape file has a name, “nj_hospitals.shp” • You may notice that the file contains *.prj file. • Download “Counties of New Jersey”-stco.zip file from • http://www.state.nj.us/dep/gis directory • There is no *.prj file for stco.shp file. We need to define a projection • Run ArcMap and add stco.shp (after you define a projection) and nj_hospitals

3. After we add county and NJ Hospital shape files, we need to identify which one is RWJ-New Brunswick hospital, so we can identify which Counties are within a 5 mile radius. We can select RWJ-New Brunswick from the attribute table of nj_hospitals.shp. To open attribute table, right-click nj_hospitals. And click “Open Attribute Table”.

4. Once attribute table is open, we need to find RWJ-New Brunswick hospital. We can sort hospital name variable (HNAME) by name, and find The hospital by alphabetical order. To do, you can left-click on the “HNAME” variable. It will highlight the variable. And then you can Right-click your mouse on the variable, you can see short cut key box. Choose “Sort Ascending” button.

5. Once you sorted the HNAME, you can scroll down and find “Robert Wood Johnson University Hospital” in Middlesex county (be aware that there Are many Robert Wood Johnson Hospitals in New Jersey. To select the hospital, you need to select the corresponding small box on the left side (then it will select entire row). When you select cases, you can see on the map the RWJ hospital is highlighted with a big blue dot. Next step is create a new shape file with RWJ hospital-New Brunswick only. We will export the point layer (only from selected point).

6. To create another shape file based on your selection, you can Click right-button of your mouse to see the menu and select Export menu. In Export: choose “Selected features” since we are interested in Selecting selected hospital. Use the same coordinate system: we choose “this layer’s source data” In fact, you can choose “the data frame”, too. It will create “prj” file for the newly created shape file. Output shape file name: Again you can name any name you want, be sure that you write down somewhere all the file name you create When the application ask “Do you want to add….?” click “Yes’

7. You can see “RWJ” shape file on the map. Now we have to identify where is 5 mile radius buffer. To to this, we need to use “Buffer” function based on Proximity. Click Toolbox button, and choose “Proximity” – “Buffer”, you can see Buffer Window box. Input Features: which one do you want to buffer Out feature: outcome layer of your buffered result Distance: make sure we choose “Miles” as a unit for this analysis, and set “5”. Instead of linear unit, we can choose “Field” which means we can buffer based on the field value not based on the fixed value. When you see the 5-mile buffered area, we can find out that the buffered area covered Middlesex and Somerset counties. We will download census tract boundary (2000) from geographynetwork.com

8. We assume, by this time, you know how to download census tract (2000) layers from www.geographynetwork.com site. We need to download Middlesex and Somerset counties. Once you download you need to “define projection” for those shape files, since they don’t have prj files.

9. Here is the map view of those two counties census tract boundary with 5 mile buffered area. To be able to do the analysis, we need to combine (merge) two census tract data layers into one.

10. To do the “Merge Function”, click ArcToolbox, and choose “Data management Tools” – “General”, and choose “Merge”. You need to add layers that you are going to merge. In this case, those two census tract layers. Name output feature (MIDSOMCT), and click OK button

11. Once we merged two shape files, it will create a one shape file which combines both two layers. We can see the attribute tables, and census tract data from Middlesex (FIPSSTCO = 34023) and Somerset (FIPSSTCO = 34035) counties.

12. Now we need to download and process population data for the census tract layer. Go to www.geographynetwork.com and download “Census Tract Demographics (SF1)” data – which is based US 2000 Census. We can see the content of the file by using Excel. The file contains many basic demographic data at the census tract level.

13. Once we downloaded census tract level demographic data, we need to join the file to our merged census tract layer. By looking at both data files we can tell “STFID” is the key column which can be used for join data.

14. This screenshot shows how to join, which we already know.

15. This is the outcome of the joined result, we can view demographic data from the attribute of the MIDSOMCT file.

16. By using joined table result, a thematic map based on HISPANIC variable (quantile method) was created.

17. Now we have to create Percent Hispanic based on the variables that we have. To calculate Percent Hispanic variable, we need to have “Total Population” and “Hispanic Population”. Before we calculate, we need to create a new field (Add Field). Click “Options” button and choose “Add Field”. Name: “PHISP” – or you can name your own Type: Basically “Integer”: number without decimal, “Float”: number with decimal Precision is “How many columns we want to use for the variable” Scale is “How many columns we want to use as a decimal points” In this case, we are creating a variable with format of “xxxx.xx , I.e., 9876.87 “.

18. We can see that PHISP variable is created without any data. Now we have to calculate the value. We can choose the variable column (click the variable name) and entire column will be highlighted. Now click right button of your mouse to bring menu, and we can choose “Calculate Values” and we can see a new window box, called “Field Calculator” In the Field Calculator, you can calculate the value of PHISP.

19. This is the newly calculated result and a thematic map

20. Since we are interested in a area within 5 miles from the RWJ hospital, we need to identify census tract area only within 5 miles. We have to perform “Clip” function, which is a cookie cutting process. Run ArcToolbox, and “Analysis Tools”-”Extract” – “Clip”. Input Features: shape file which needed to be clipped Clip features: shape file which will set clip boundary (in this case, 5 mile radius) Output Feature Class: output name Click “OK”

21. Once you clipped the shape file, you can create a thematic map based on PHISP variable again. Now we need to identify area which as PHISP more than or equal to 10. We can choose “Selection” from the menu, and “Select by Attributes”-where we can choose it based on the attribute value. From the newly open window, you can create “PHISP” >= 10. And click “OK”

22. Now you can see the selected polygon based on the criteria. Let’s open the attribute table. Those cases highlighted with color are selected polygons. On the table, by clicking “Selected” button, we can only view selected variables.

23. Now, we want to export the selected cases, and import in Excel format to make a decent table. To export the table result, click “Options” and click “Export”.

24. This is the outcome of the selected polygon in excel format. You can put title, neatline and others to make a presentable format. Deliverables: - Map - Table with Selected Polygons