-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathStbl_Ice Crm_Shop_Lctn_SQL_Code.txt
87 lines (75 loc) · 3.64 KB
/
Stbl_Ice Crm_Shop_Lctn_SQL_Code.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
The overall criteria for this projects are met by the following SQL quesries are:
#Greater than 500 farms for milk production
#A labor pool of at least 25,000 individuals between the ages of 18 and 64 years
#A low crime index (less than or equal to 0.02)
#A population of less than 150 individuals per square mile
#Located near a university or college
#At least one recreation area within 10 miles
#Interstate within 20 miles
############### County Level Codes ###################
SELECT counties.gid,
counties.area,
counties.perimeter,
counties.name,
counties.pop1990,
counties.age_18_64,
counties.no_farms87,
counties.pop_sqmile,
counties.sq_miles,
counties.gavprimary,
counties.x,
counties.y,
counties.geom
FROM ice_cream.counties
WHERE counties.age_18_64 >= 25000::numeric AND counties.no_farms87 > 500::numeric AND counties.pop_sqmile < 150::double precision;
############### City Level Codes ###################
SELECT cities.gid,
cities.id,
cities.name,
cities.population,
cities.total_crim,
cities.crime_inde,
cities.university,
cities.geom
FROM ice_cream.counties_view
CROSS JOIN ice_cream.cities
WHERE st_within(cities.geom, counties_view.geom) AND cities.crime_inde <= 0.02 AND cities.university = 1::numeric;
############### Intersates Level Codes ###################
SELECT cities_view.gid,
cities_view.id,
cities_view.name,
cities_view.population,
cities_view.total_crim,
cities_view.crime_inde,
cities_view.university,
cities_view.geom,
st_distance(st_transform(cities_view.geom, 2271), st_transform(interstates.geom, 2271)) / 5280::double precision AS miles_to_interstates,
interstates.name AS interstate_name
FROM ice_cream.cities_view
CROSS JOIN ice_cream.interstates
WHERE interstates.type::text = 'Interstate'::text AND st_dwithin(st_transform(cities_view.geom, 2271), st_transform(interstates.geom, 2271), (5280 * 20)::double precision);
####################### Final Codes for suitable ice cream shop location #############################
SELECT intersates_view_and_recarea.gid,
intersates_view_and_recarea.name,
intersates_view_and_recarea.population,
intersates_view_and_recarea.total_crim,
intersates_view_and_recarea.crime_inde,
intersates_view_and_recarea.university,
intersates_view_and_recarea.geom,
min(intersates_view_and_recarea.miles_to_interstates) AS miles_to_interstates,
min(intersates_view_and_recarea.miles_to_recarea) AS miles_to_recarea
FROM ( SELECT intersates_view.gid,
intersates_view.name,
intersates_view.population,
intersates_view.total_crim,
intersates_view.crime_inde,
intersates_view.university,
intersates_view.geom,
intersates_view.miles_to_interstates,
intersates_view.interstate_name,
st_distance(st_transform(intersates_view.geom, 2271), st_transform(recareas.geom, 2271)) / 5280::double precision AS miles_to_recarea
FROM ice_cream.intersates_view
CROSS JOIN ice_cream.recareas
WHERE st_dwithin(st_transform(intersates_view.geom, 2271), st_transform(recareas.geom, 2271), (5280 * 10)::double precision)) intersates_view_and_recarea
GROUP BY intersates_view_and_recarea.name, intersates_view_and_recarea.gid, intersates_view_and_recarea.population, intersates_view_and_recarea.total_crim, intersates_view_and_recarea.crime_inde, intersates_view_and_recarea.university, intersates_view_and_recarea.geom
ORDER BY intersates_view_and_recarea.name;