User talk:Ram-Man/sandbox

/** Generate wikified city names **/ select distinct states.name as state, cities.county as county, concat("*[[",if(cities.city_type = "township", concat(cities.name," Township, ",cities.county),cities.name),", ",states.name,"|",if(cities.city_type = "township", concat(cities.name," Township"),cities.name),"]]") as city, cities.city_type as type from states inner join cities on states.state_id = cities.state_id where type != 3 && cities.city_type != "County" && cities.city_type != "157-30" && cities.city_type != "158-30" && cities.city_type != "Bow" && cities.sumlevel = 160 order by states.name, cities.county, cities.name limit 1; /** Generate wikified city names in lists **/ select @output:="", @current_state:="", @current_county :="", @old_county :=""; create table output1 select distinct states.state_id, @current_state := states.name as state, IF( @old_county != cities.county, 1, 0 ) as c, IF(@old_county != cities.county, @output:=concat("\n",trim(concat("*[[",if(cities.city_type = "township", concat(cities.name," Township, ",cities.county),cities.name),", ",states.name,"|",if(cities.city_type = "township", concat(cities.name," Township"),cities.name),"]]")),"\n"), @output:=concat(trim(@output),trim(concat("*[[",if(cities.city_type = "township", concat(cities.name," Township, ",cities.county),cities.name),", ",states.name,"|",if(cities.city_type = "township", concat(cities.name," Township"),cities.name),"]]")),"\n")) as output, IF( @old_county != cities.county, @old_county := cities.county, cities.county ) AS county, cities.name from cities inner join states on cities.state_id = states.state_id where cities.type != 3 && cities.sumlevel = 160; alter table output1 order by state_id, county, name desc; create table output2 select distinct * from output1 group by county, state order by state, county, output; drop table output1; /** New county information w/ city lists **/ SELECT concat("\n\n'''", TRIM(counties.name), "''' is a [[county]] located in the [[U.S. State]] of [[", TRIM(counties.state), "]]. As of [[2000]], the population is ", FORMAT(counties.population,0), ".\n\n== Geography ==\nThe county has a total area of ", FORMAT(counties.total_area_km,0), " [[square kilometer|km²]] (", FORMAT(counties.total_area,0), " [[square mile|mi²]]). ", FORMAT(counties.land_area_km,0), " km² (", FORMAT(counties.land_area,0), " mi²) of it is land and ", FORMAT(counties.water_area_km,0), " km² (", FORMAT(counties.water_area,0), " mi²) of it is water. The total area is ", FORMAT(counties.percent_water,2), "% water.\n\n== Demographics ==\nAs of [[2000]], there are ", FORMAT(counties.population,0), " people, ", FORMAT(counties.households,0), " households, and ", FORMAT(FLOOR(counties.households * counties.percent_families / 100),0), " families residing in the county. The population density is ", FORMAT(counties.pop_density_km,0), "/km² (", FORMAT(counties.pop_density,0), "/mi²). There are ", FORMAT(counties.housing_units,0), " housing units at an average density of ", FORMAT(counties.hu_density_km,0), " persons/km² (", FORMAT(counties.hu_density,0), " persons/mi²). The racial makeup of the county is ", FORMAT(counties.white * 100 / counties.population,2), "% White, ", FORMAT(counties.black * 100 / counties.population,2), "% [[African American]], ", FORMAT(counties.native * 100 / counties.population,2), "% [[Native American]], ", FORMAT(counties.asian * 100 / counties.population,2), "% [[Asia|Asian]], ", FORMAT(counties.pacific * 100 / counties.population,2), "% Pacific Islander, ", FORMAT(counties.other * 100 / counties.population,2), "% from other races, and ", FORMAT(counties.two_or_more * 100 / counties.population,2), "% from two or more races. ", FORMAT(counties.hispanic * 100 / counties.population,2), "% of the population are [[Hispanic]] or [[Latino]] of any race.\n\nThere are ", FORMAT(counties.households,0), " households out of which ", FORMAT(counties.percent_with_children,2), "% have children under the age of 18 living with them, ", FORMAT(counties.percent_married,2), "% are married couples living together, ", FORMAT(counties.percent_no_husband,2), "% have a woman whose husband does not live with her, and ", FORMAT(counties.percent_non_families,2), "% are non-families. ", FORMAT(counties.percent_alone,2), "% of all households are made up of individuals and ", FORMAT(counties.percent_seniors,2), "% have someone living alone who is 65 years of age or older. The average household size is ", FORMAT(counties.average_household_size,2), " and the average family size is ", FORMAT(counties.average_family_size,2), ".\n\nIn the county the population is spread out with ", FORMAT(counties.percent_under_18,2), "% under the age of 18, ", FORMAT(counties.percent_18_to_24,2), "% from 18 to 24, ", FORMAT(counties.percent_25_to_44,2), "% from 25 to 44, ", FORMAT(counties.percent_45_to_64,2), "% from 45 to 64, and ", FORMAT(counties.percent_65_up,2), "% who are 65 years of age or older. The median age is ", FORMAT(counties.median_age,0), " years. For every 100 females there are ", FORMAT(counties.males_per_100_females,2), " males. For every 100 females age 18 and over, there are ", FORMAT(counties.males_per_100_females_18_up,2), " males.", "\n\nThe median income for a household in the county is $", FORMAT(counties.median_income_households,0), ", and the median income for a family is $", FORMAT(counties.median_income_families,0), ". Males have a median income of $", FORMAT(counties.median_income_male,0), " versus $", FORMAT(counties.median_income_female,0), " for females. The per capita income for the county is $", FORMAT(counties.per_capita_income,0), ". ", FORMAT(counties.percent_poverty,2), "% of the population and ", FORMAT(counties.percent_poverty_families,2), "% of families are below the poverty line. Out of the total people living in poverty, ", FORMAT(counties.percent_poverty_under_18,2), "% are under the age of 18 and ", FORMAT(counties.percent_poverty_65_up,2), "% are 65 or older.\n\n== Cities and Towns ==", output2.output) FROM counties LEFT JOIN output2 ON output2.state_id = counties.state_id && output2.county = counties.name WHERE counties.state != "Alaska" and counties.state != "Louisiana" INTO OUTFILE 'output.txt'; LIMIT 3; /** Generate city lists **/ /** Generate information on Alaskan Boroughs **/ /** Generate information on Alaskan Census Areas **/ /** Generate information on Louisiana **/ SELECT concat("\n\n[[", TRIM(counties.name), ", ", TRIM(counties.state), "]]\n\n== Cities and Towns ==", output2.output) FROM counties LEFT JOIN output2 ON output2.state_id = counties.state_id && output2.county = counties.name INTO OUTFILE 'output1.txt'; SELECT concat("\n\n'''", TRIM(counties.name), "''' is a [[borough]] located in the [[U.S. State]] of [[", TRIM(counties.state), "]]. As of [[2000]], the population is ", FORMAT(counties.population,0), ".\n\n== Geography ==\nThe borough has a total area of ", FORMAT(counties.total_area_km,0), " [[square kilometer|km²]] (", FORMAT(counties.total_area,0), " [[square mile|mi²]]). ", FORMAT(counties.land_area_km,0), " km² (", FORMAT(counties.land_area,0), " mi²) of it is land and ", FORMAT(counties.water_area_km,0), " km² (", FORMAT(counties.water_area,0), " mi²) of it is water. The total area is ", FORMAT(counties.percent_water,2), "% water.\n\n== Demographics ==\nAs of [[2000]], there are ", FORMAT(counties.population,0), " people, ", FORMAT(counties.households,0), " households, and ", FORMAT(FLOOR(counties.households * counties.percent_families / 100),0), " families residing in the borough. The population density is ", FORMAT(counties.pop_density_km,0), "/km² (", FORMAT(counties.pop_density,0), "/mi²). There are ", FORMAT(counties.housing_units,0), " housing units at an average density of ", FORMAT(counties.hu_density_km,0), " persons/km² (", FORMAT(counties.hu_density,0), " persons/mi²). The racial makeup of the borough is ", FORMAT(counties.white * 100 / counties.population,2), "% White, ", FORMAT(counties.black * 100 / counties.population,2), "% [[African American]], ", FORMAT(counties.native * 100 / counties.population,2), "% [[Native American]], ", FORMAT(counties.asian * 100 / counties.population,2), "% [[Asia|Asian]], ", FORMAT(counties.pacific * 100 / counties.population,2), "% Pacific Islander, ", FORMAT(counties.other * 100 / counties.population,2), "% from other races, and ", FORMAT(counties.two_or_more * 100 / counties.population,2), "% from two or more races. ", FORMAT(counties.hispanic * 100 / counties.population,2), "% of the population are [[Hispanic]] or [[Latino]] of any race.\n\nThere are ", FORMAT(counties.households,0), " households out of which ", FORMAT(counties.percent_with_children,2), "% have children under the age of 18 living with them, ", FORMAT(counties.percent_married,2), "% are married couples living together, ", FORMAT(counties.percent_no_husband,2), "% have a woman whose husband does not live with her, and ", FORMAT(counties.percent_non_families,2), "% are non-families. ", FORMAT(counties.percent_alone,2), "% of all households are made up of individuals and ", FORMAT(counties.percent_seniors,2), "% have someone living alone who is 65 years of age or older. The average household size is ", FORMAT(counties.average_household_size,2), " and the average family size is ", FORMAT(counties.average_family_size,2), ".\n\nIn the borough the population is spread out with ", FORMAT(counties.percent_under_18,2), "% under the age of 18, ", FORMAT(counties.percent_18_to_24,2), "% from 18 to 24, ", FORMAT(counties.percent_25_to_44,2), "% from 25 to 44, ", FORMAT(counties.percent_45_to_64,2), "% from 45 to 64, and ", FORMAT(counties.percent_65_up,2), "% who are 65 years of age or older. The median age is ", FORMAT(counties.median_age,0), " years. For every 100 females there are ", FORMAT(counties.males_per_100_females,2), " males. For every 100 females age 18 and over, there are ", FORMAT(counties.males_per_100_females_18_up,2), " males.", "\n\nThe median income for a household in the borough is $", FORMAT(counties.median_income_households,0), ", and the median income for a family is $", FORMAT(counties.median_income_families,0), ". Males have a median income of $", FORMAT(counties.median_income_male,0), " versus $", FORMAT(counties.median_income_female,0), " for females. The per capita income for the borough is $", FORMAT(counties.per_capita_income,0), ". ", FORMAT(counties.percent_poverty,2), "% of the population and ", FORMAT(counties.percent_poverty_families,2), "% of families are below the poverty line. Out of the total people living in poverty, ", FORMAT(counties.percent_poverty_under_18,2), "% are under the age of 18 and ", FORMAT(counties.percent_poverty_65_up,2), "% are 65 or older.\n\n== Cities and Towns ==", output2.output) FROM counties LEFT JOIN output2 ON output2.state_id = counties.state_id && output2.county = counties.name WHERE counties.state = "Alaska" && LOCATE("Borough",counties.name) != 0 INTO OUTFILE 'output2.txt'; SELECT concat("\n\n'''", TRIM(counties.name), "''' is a [[census area]] located in the [[U.S. State]] of [[", TRIM(counties.state), "]]. As of [[2000]], the population is ", FORMAT(counties.population,0), ".\n\n== Geography ==\nThe census area has a total area of ", FORMAT(counties.total_area_km,0), " [[square kilometer|km²]] (", FORMAT(counties.total_area,0), " [[square mile|mi²]]). ", FORMAT(counties.land_area_km,0), " km² (", FORMAT(counties.land_area,0), " mi²) of it is land and ", FORMAT(counties.water_area_km,0), " km² (", FORMAT(counties.water_area,0), " mi²) of it is water. The total area is ", FORMAT(counties.percent_water,2), "% water.\n\n== Demographics ==\nAs of [[2000]], there are ", FORMAT(counties.population,0), " people, ", FORMAT(counties.households,0), " households, and ", FORMAT(FLOOR(counties.households * counties.percent_families / 100),0), " families residing in the census area. The population density is ", FORMAT(counties.pop_density_km,0), "/km² (", FORMAT(counties.pop_density,0), "/mi²). There are ", FORMAT(counties.housing_units,0), " housing units at an average density of ", FORMAT(counties.hu_density_km,0), " persons/km² (", FORMAT(counties.hu_density,0), " persons/mi²). The racial makeup of the census area is ", FORMAT(counties.white * 100 / counties.population,2), "% White, ", FORMAT(counties.black * 100 / counties.population,2), "% [[African American]], ", FORMAT(counties.native * 100 / counties.population,2), "% [[Native American]], ", FORMAT(counties.asian * 100 / counties.population,2), "% [[Asia|Asian]], ", FORMAT(counties.pacific * 100 / counties.population,2), "% Pacific Islander, ", FORMAT(counties.other * 100 / counties.population,2), "% from other races, and ", FORMAT(counties.two_or_more * 100 / counties.population,2), "% from two or more races. ", FORMAT(counties.hispanic * 100 / counties.population,2), "% of the population are [[Hispanic]] or [[Latino]] of any race.\n\nThere are ", FORMAT(counties.households,0), " households out of which ", FORMAT(counties.percent_with_children,2), "% have children under the age of 18 living with them, ", FORMAT(counties.percent_married,2), "% are married couples living together, ", FORMAT(counties.percent_no_husband,2), "% have a woman whose husband does not live with her, and ", FORMAT(counties.percent_non_families,2), "% are non-families. ", FORMAT(counties.percent_alone,2), "% of all households are made up of individuals and ", FORMAT(counties.percent_seniors,2), "% have someone living alone who is 65 years of age or older. The average household size is ", FORMAT(counties.average_household_size,2), " and the average family size is ", FORMAT(counties.average_family_size,2), ".\n\nIn the census area the population is spread out with ", FORMAT(counties.percent_under_18,2), "% under the age of 18, ", FORMAT(counties.percent_18_to_24,2), "% from 18 to 24, ", FORMAT(counties.percent_25_to_44,2), "% from 25 to 44, ", FORMAT(counties.percent_45_to_64,2), "% from 45 to 64, and ", FORMAT(counties.percent_65_up,2), "% who are 65 years of age or older. The median age is ", FORMAT(counties.median_age,0), " years. For every 100 females there are ", FORMAT(counties.males_per_100_females,2), " males. For every 100 females age 18 and over, there are ", FORMAT(counties.males_per_100_females_18_up,2), " males.", "\n\nThe median income for a household in the census area is $", FORMAT(counties.median_income_households,0), ", and the median income for a family is $", FORMAT(counties.median_income_families,0), ". Males have a median income of $", FORMAT(counties.median_income_male,0), " versus $", FORMAT(counties.median_income_female,0), " for females. The per capita income for the census area is $", FORMAT(counties.per_capita_income,0), ". ", FORMAT(counties.percent_poverty,2), "% of the population and ", FORMAT(counties.percent_poverty_families,2), "% of families are below the poverty line. Out of the total people living in poverty, ", FORMAT(counties.percent_poverty_under_18,2), "% are under the age of 18 and ", FORMAT(counties.percent_poverty_65_up,2), "% are 65 or older.\n\n== Cities and Towns ==", output2.output) FROM counties LEFT JOIN output2 ON output2.state_id = counties.state_id && output2.county = counties.name WHERE counties.state = "Alaska" && LOCATE("Borough",counties.name) = 0 INTO OUTFILE 'output3.txt'; SELECT concat("\n\n'''", TRIM(counties.name), "''' is a [[parish]] located in the [[U.S. State]] of [[", TRIM(counties.state), "]]. As of [[2000]], the population is ", FORMAT(counties.population,0), ".\n\n== Geography ==\nThe parish has a total area of ", FORMAT(counties.total_area_km,0), " [[square kilometer|km²]] (", FORMAT(counties.total_area,0), " [[square mile|mi²]]). ", FORMAT(counties.land_area_km,0), " km² (", FORMAT(counties.land_area,0), " mi²) of it is land and ", FORMAT(counties.water_area_km,0), " km² (", FORMAT(counties.water_area,0), " mi²) of it is water. The total area is ", FORMAT(counties.percent_water,2), "% water.\n\n== Demographics ==\nAs of [[2000]], there are ", FORMAT(counties.population,0), " people, ", FORMAT(counties.households,0), " households, and ", FORMAT(FLOOR(counties.households * counties.percent_families / 100),0), " families residing in the parish. The population density is ", FORMAT(counties.pop_density_km,0), "/km² (", FORMAT(counties.pop_density,0), "/mi²). There are ", FORMAT(counties.housing_units,0), " housing units at an average density of ", FORMAT(counties.hu_density_km,0), " persons/km² (", FORMAT(counties.hu_density,0), " persons/mi²). The racial makeup of the parish is ", FORMAT(counties.white * 100 / counties.population,2), "% White, ", FORMAT(counties.black * 100 / counties.population,2), "% [[African American]], ", FORMAT(counties.native * 100 / counties.population,2), "% [[Native American]], ", FORMAT(counties.asian * 100 / counties.population,2), "% [[Asia|Asian]], ", FORMAT(counties.pacific * 100 / counties.population,2), "% Pacific Islander, ", FORMAT(counties.other * 100 / counties.population,2), "% from other races, and ", FORMAT(counties.two_or_more * 100 / counties.population,2), "% from two or more races. ", FORMAT(counties.hispanic * 100 / counties.population,2), "% of the population are [[Hispanic]] or [[Latino]] of any race.\n\nThere are ", FORMAT(counties.households,0), " households out of which ", FORMAT(counties.percent_with_children,2), "% have children under the age of 18 living with them, ", FORMAT(counties.percent_married,2), "% are married couples living together, ", FORMAT(counties.percent_no_husband,2), "% have a woman whose husband does not live with her, and ", FORMAT(counties.percent_non_families,2), "% are non-families. ", FORMAT(counties.percent_alone,2), "% of all households are made up of individuals and ", FORMAT(counties.percent_seniors,2), "% have someone living alone who is 65 years of age or older. The average household size is ", FORMAT(counties.average_household_size,2), " and the average family size is ", FORMAT(counties.average_family_size,2), ".\n\nIn the parish the population is spread out with ", FORMAT(counties.percent_under_18,2), "% under the age of 18, ", FORMAT(counties.percent_18_to_24,2), "% from 18 to 24, ", FORMAT(counties.percent_25_to_44,2), "% from 25 to 44, ", FORMAT(counties.percent_45_to_64,2), "% from 45 to 64, and ", FORMAT(counties.percent_65_up,2), "% who are 65 years of age or older. The median age is ", FORMAT(counties.median_age,0), " years. For every 100 females there are ", FORMAT(counties.males_per_100_females,2), " males. For every 100 females age 18 and over, there are ", FORMAT(counties.males_per_100_females_18_up,2), " males.", "\n\nThe median income for a household in the parish is $", FORMAT(counties.median_income_households,0), ", and the median income for a family is $", FORMAT(counties.median_income_families,0), ". Males have a median income of $", FORMAT(counties.median_income_male,0), " versus $", FORMAT(counties.median_income_female,0), " for females. The per capita income for the parish is $", FORMAT(counties.per_capita_income,0), ". ", FORMAT(counties.percent_poverty,2), "% of the population and ", FORMAT(counties.percent_poverty_families,2), "% of families are below the poverty line. Out of the total people living in poverty, ", FORMAT(counties.percent_poverty_under_18,2), "% are under the age of 18 and ", FORMAT(counties.percent_poverty_65_up,2), "% are 65 or older.\n\n== Cities and Towns ==", output2.output) FROM counties LEFT JOIN output2 ON output2.state_id = counties.state_id && output2.county = counties.name WHERE counties.state = "Louisiana" INTO OUTFILE 'output4.txt'; /************************************************/ /************************************************/ /************************************************/ /************************************************/ /******* The Above is Obsolete ******************/ /************************************************/ /************************************************/ /************************************************/ /************************************************/ /***** Table Formats for FIPS data *****/ CREATE TABLE fips ( state_code TINYINT DEFAULT -1 NOT NULL, place_code MEDIUMINT DEFAULT -1 NOT NULL, alpha_code CHAR(2) DEFAULT NULL, num_counties TINYINT DEFAULT -1 NOT NULL, num_sequence TINYINT DEFAULT -1 NOT NULL, class_code CHAR(2) DEFAULT NULL, place_name CHAR(52) DEFAULT NULL, county_code SMALLINT DEFAULT -1 NOT NULL, county_name CHAR(22) DEFAULT NULL, part_of_code MEDIUMINT DEFAULT -1 NOT NULL, other_name_code MEDIUMINT DEFAULT -1 NOT NULL, zip_code MEDIUMINT DEFAULT -1 NOT NULL, postal_match CHAR(2) DEFAULT NULL, zip_range TINYINT DEFAULT -1 NOT NULL, gsa_code SMALLINT DEFAULT -1 NOT NULL, mrf_code SMALLINT DEFAULT -1 NOT NULL, msa_code SMALLINT DEFAULT -1 NOT NULL, cd_codes CHAR(28) DEFAULT NULL ); LOAD DATA INFILE '/D/usgs/fips/output.txt' INTO TABLE fips; /***************************************************** * Generate wikified city names in lists (Counties) *****************************************************/ SELECT @output:="", @current_state:="", @current_county :="", @old_county :=""; CREATE TABLE temp SELECT DISTINCT @current_state := state AS state, IF( @old_county != county, IF( repeat = 0, @output := concat( "\n", CONCAT("*[[", city, ", ", state, "|", city, "]]"), "\n" ), @output := concat( "\n", CONCAT("*[[", city, ", ", county, ", ", state, "|", city, "]]"), "\n" ) ), IF( repeat = 0, @output := concat( trim(@output), trim(concat("*[[", city, ", ", state, "|", city, "]]")), "\n" ), @output := concat( trim(@output), trim(concat("*[[", city, ", ", county, ", ", state, "|", city, "]]")), "\n" ) ) ) AS output, IF( @old_county != county, @old_county := county, county ) AS county, city AS name FROM articles5 WHERE valid; ALTER TABLE temp ORDER BY state, county, name desc; CREATE TABLE lists SELECT DISTINCT CONCAT(county, ", ", state) AS title, output AS list FROM temp GROUP BY county, state ORDER BY state, county, output; DROP TABLE temp; /*** Generate Entries for Cities #2 ***/ CREATE TABLE articles1 SELECT DISTINCT state, id, type, name, IF( population = 0, "location", IF( city_type = "(balance)", "balance", IF( name LIKE BINARY "% town", "town and county", IF( name LIKE BINARY "% city", "city and county", IF( name LIKE BINARY "% AFB", "U.S. Air Force base", IF( city_type = "Reservation", "Indian reservation", IF( city_type = "UT", "unorganized territory", IF( city_type = "Bow", "town", IF( city_type = "157-30", "township", IF( city_type = "158-30", "township", IF( city_type = "CDP", "CDP", city_type))))))))))) AS city_type, IF(county = "none" || county LIKE BINARY "% town" || county LIKE BINARY "% city", "", county ) AS county, sumlevel, state_id, place_id, population, total_area, total_area_km, land_area, land_area_km, water_area, water_area_km, percent_water, pop_density, pop_density_km, housing_units, hu_density, hu_density_km, total_one_race, white, black, native, asian, pacific, other, two_or_more, hispanic, households, percent_families, percent_with_children, percent_married, percent_no_husband, percent_non_families, percent_alone, percent_seniors, average_household_size, average_family_size, percent_under_18, percent_18_to_24, percent_25_to_44, percent_45_to_64, percent_65_up, median_age, males_per_100_females, males_per_100_females_18_up, median_income_households, median_income_families, per_capita_income, median_income_male, median_income_female, percent_poverty, percent_poverty_under_18, percent_poverty_65_up, percent_poverty_families, source, IF( name LIKE BINARY "% town", SUBSTRING(name,1,LOCATE(" town",name)), IF( name LIKE BINARY "% city", SUBSTRING(name,1,LOCATE(" city",name)), IF( city_type = "Reservation", CONCAT(name, " Reservation"), IF( city_type = "township", CONCAT(name, " Township"), IF( city_type = "Bow", CONCAT(name, " Bow"), IF( city_type = "157-30", CONCAT(name, " 157-30"), IF( city_type = "158-30", CONCAT(name, " 158-30"), IF( ((city_type = "County") and (type != 3)), CONCAT(name, " County"), IF( city_type = "UT", CONCAT( name, ""), name))))))))) AS city FROM cities WHERE type != 3 ORDER BY state_id, county, name; create table duplicates1 select state, city, count(city) AS count from articles1 group by state, city; create table duplicates2 select state, city, county, count(city) as count from articles1 group by state, county, city; CREATE TABLE articles2 SELECT articles1.*, IF( duplicates2.count > 1, "0", "1" ) AS valid FROM articles1 LEFT JOIN duplicates2 ON articles1.state = duplicates2.state && articles1.county = duplicates2.county && articles1.city = duplicates2.city; DROP TABLE articles1; DROP TABLE duplicates2; CREATE TABLE articles3 SELECT articles2.*, IF( duplicates1.count > 1, "1", "0" ) AS repeat FROM articles2 LEFT JOIN duplicates1 ON articles2.state = duplicates1.state && articles2.city = duplicates1.city; DROP TABLE articles2; DROP TABLE duplicates1; CREATE TABLE articles4 SELECT articles3.*, IF( repeat = 0 || county = "", CONCAT( "[[", city, ", ", state, "]]" ), CONCAT( "[[", city, ", ", county, ", ", state, "]]" ) ) AS link, IF( repeat = 0 || county = "", CONCAT( city, ", ", state ), CONCAT( city, ", ", county, ", ", state ) ) AS title, CONCAT( "\n\n'''", TRIM(city), "''' is ", IF(county = "" && city_type = "balance", "a partially independent city that is", IF(city_type = "balance", "a city that is partially", CONCAT( IF(city_type LIKE "u%" || city_type LIKE "i%", "an ", "a "), IF(city_type="CDP","town",city_type) ) ) ), " located in [[", IF((county = "") || (type = 2), TRIM(state), CONCAT(TRIM(county), ", ", TRIM(state)) ), "]]. As of the [[2000]] census, the ", IF(city_type = "CDP", "town", city_type), " had a total population of ", FORMAT(population, 0), ".\n\n== Geography ==\nAccording to the [[United States Census Bureau]], the ", IF(city_type = "CDP", "town", city_type), " has a total area of ", FORMAT(total_area_km, 1), " [[square kilometer|km²]] (", FORMAT(total_area, 1), " [[square mile|mi²]]). ", IF( water_area = 0, "None of the area is covered with water.", CONCAT( FORMAT(land_area_km, 1), " km² (", FORMAT(land_area, 1), " mi²) of it is land and ", FORMAT(water_area_km, 1), " km² (", FORMAT(water_area, 1), " mi²) of it is water. The total area is ", FORMAT(percent_water, 2), "% water." ) ), "\n\n== Demographics ==\nAs of the [[census]] of [[2000]], there are ", IF( population = 0, CONCAT( "no people living in the ", IF(city_type="CDP", "town", city_type) ), CONCAT( FORMAT(population,0), " people, ", FORMAT(households,0), " households, and ", FORMAT(FLOOR(households * percent_families / 100),0), " families residing in the ", if(city_type="CDP","town",city_type), ". The [[population density]] is ", FORMAT(pop_density_km,1), "/km² (", FORMAT(pop_density,1), "/mi²). There are ", FORMAT(housing_units,0), " housing units at an average density of ", FORMAT(hu_density_km,1), " persons/km² (", FORMAT(hu_density,1), " persons/mi²). The racial makeup of the ", IF(city_type="CDP","town",city_type), " is ", IF(population = 0, "0.00", FORMAT(white * 100 / population,2)), "% White, ", IF(population = 0, "0.00", FORMAT(black * 100 / population,2)), "% [[African American]], ", IF(population = 0, "0.00", FORMAT(native * 100 / population,2)), "% [[Native American]], ", IF(population = 0, "0.00", FORMAT(asian * 100 / population,2)), "% [[Asia|Asian]], ", IF(population = 0, "0.00", FORMAT(pacific * 100 / population,2)), "% Pacific Islander, ", IF(population = 0, "0.00", FORMAT(other * 100 / population,2)), "% from other races, and ", IF(population = 0, "0.00", FORMAT(two_or_more * 100 / population,2)), "% from two or more races. ", IF(population = 0, "0.00", FORMAT(hispanic * 100 / population,2)), "% of the population are [[Hispanic]] or [[Latino]] of any race.\n\nThere are ", FORMAT(households,0), " households out of which ", FORMAT(percent_with_children,1), "% have children under the age of 18 living with them, ", FORMAT(percent_married,1), "% are married couples living together, ", FORMAT(percent_no_husband,1), "% have a female householder with no husband present, and ", FORMAT(percent_non_families,1), "% are non-families. ", FORMAT(percent_alone,1), "% of all households are made up of individuals and ", FORMAT(percent_seniors,1), "% have someone living alone who is 65 years of age or older. The average household size is ", FORMAT(average_household_size,2), " and the average family size is ", FORMAT(average_family_size,2), ".\n\nIn the ", IF(city_type="CDP","town",city_type), " the population is spread out with ", FORMAT(percent_under_18,1), "% under the age of 18, ", FORMAT(percent_18_to_24,1), "% from 18 to 24, ", FORMAT(percent_25_to_44,1), "% from 25 to 44, ", FORMAT(percent_45_to_64,1), "% from 45 to 64, and ", FORMAT(percent_65_up,1), "% who are 65 years of age or older. The median age is ", FORMAT(median_age,0), " years. For every 100 females there are ", FORMAT(males_per_100_females,1), " males. For every 100 females age 18 and over, there are ", FORMAT(males_per_100_females_18_up,1), " males.", IF( median_income_households != 0 || median_income_families != 0 || median_income_male != 0 || median_income_female != 0 || per_capita_income != 0 || percent_poverty != 0.0 || percent_poverty_families != 0.0, CONCAT( "\n\nThe median income for a household in the ", IF(city_type="CDP","town",city_type), " is $", FORMAT(median_income_households,0), ", and the median income for a family is $", FORMAT(median_income_families,0), ". Males have a median income of $", FORMAT(median_income_male,0), " versus $", FORMAT(median_income_female,0), " for females. The per capita income for the ", IF(city_type="CDP","town",city_type), " is $", FORMAT(per_capita_income,0), ". ", FORMAT(percent_poverty,1), "% of the population and ", FORMAT(percent_poverty_families,1), "% of families are below the poverty line.", IF( percent_poverty != 0.0 || percent_poverty_families != 0.0, CONCAT( " Out of the total people living in poverty, ", FORMAT(percent_poverty_under_18,1), "% are under the age of 18 and ", FORMAT(percent_poverty_65_up,1), "% are 65 or older." ), "" ) ), "" ) ) ) ) AS article FROM articles3; DROP TABLE articles3; CREATE TABLE articles SELECT * FROM articles4; DROP TABLE articles4; SELECT CONCAT( "\n***", "\n*1*", title, "*1*", "\n*2*", state, "*2*", "\n*3*", county, "*3*", "\n*4*", city, "*4*", "\n*5*", city_type, "*5*", "\n*6*", title, "*6*", "\n*7*", article, "*7*", "\n***\n" ) AS data FROM articles WHERE !valid && type = 2 INTO OUTFILE 'articles.txt'; /*** Article Rules: ***/ type valid repeat other format ---- ----- ------ -------------- --------------------------- 1) both yes no [[City, State]] 2) both yes yes [[City, County, State]] 3) both no yes [[City (type), County, State]] or [[City, County, State]] or [[City, State]] or [[City (type), State]] CREATE TABLE articles5 SELECT state, county, city, CONCAT( "\n\n'''", TRIM(city), "''' is ", IF(county = "" && city_type = "balance", "a partially independent city that is", IF(city_type = "balance", "a city that is partially", CONCAT( IF(city_type LIKE "u%" || city_type LIKE "i%", "an ", "a "), IF(city_type="CDP","town",city_type) ) ) ), " located in [[", IF((county = "") || (type = 2), TRIM(state), CONCAT(TRIM(county), ", ", TRIM(state)) ), "]]. As of the [[2000]] census, the ", IF(city_type = "CDP", "town", city_type), " had a total population of ", FORMAT(population, 0), ".\n\n== Geography ==\nAccording to the [[United States Census Bureau]], the ", IF(city_type = "CDP", "town", city_type), " has a total area of ", FORMAT(total_area_km, 1), " [[square kilometer|km²]] (", FORMAT(total_area, 1), " [[square mile|mi²]]). ", IF( water_area = 0, "None of the area is covered with water.", CONCAT( FORMAT(land_area_km, 1), " km² (", FORMAT(land_area, 1), " mi²) of it is land and ", FORMAT(water_area_km, 1), " km² (", FORMAT(water_area, 1), " mi²) of it is water. The total area is ", FORMAT(percent_water, 2), "% water." ) ), "\n\n== Demographics ==\nAs of the [[census]] of [[2000]], there are ", IF( population = 0, CONCAT( "no people living in the ", IF(city_type="CDP", "town", city_type), "." ), CONCAT( FORMAT(population,0), " people, ", FORMAT(households,0), " households, and ", FORMAT(FLOOR(households * percent_families / 100),0), " families residing in the ", if(city_type="CDP","town",city_type), ". The [[population density]] is ", FORMAT(pop_density_km,1), "/km² (", FORMAT(pop_density,1), "/mi²). There are ", FORMAT(housing_units,0), " housing units at an average density of ", FORMAT(hu_density_km,1), "/km² (", FORMAT(hu_density,1), "/mi²). The racial makeup of the ", IF(city_type="CDP","town",city_type), " is ", IF(population = 0, "0.00", FORMAT(white * 100 / population,2)), "% White, ", IF(population = 0, "0.00", FORMAT(black * 100 / population,2)), "% [[African American]], ", IF(population = 0, "0.00", FORMAT(native * 100 / population,2)), "% [[Native American]], ", IF(population = 0, "0.00", FORMAT(asian * 100 / population,2)), "% [[Asia|Asian]], ", IF(population = 0, "0.00", FORMAT(pacific * 100 / population,2)), "% Pacific Islander, ", IF(population = 0, "0.00", FORMAT(other * 100 / population,2)), "% from other races, and ", IF(population = 0, "0.00", FORMAT(two_or_more * 100 / population,2)), "% from two or more races. ", IF(population = 0, "0.00", FORMAT(hispanic * 100 / population,2)), "% of the population are [[Hispanic]] or [[Latino]] of any race.\n\nThere are ", FORMAT(households,0), " households out of which ", FORMAT(percent_with_children,1), "% have children under the age of 18 living with them, ", FORMAT(percent_married,1), "% are married couples living together, ", FORMAT(percent_no_husband,1), "% have a female householder with no husband present, and ", FORMAT(percent_non_families,1), "% are non-families. ", FORMAT(percent_alone,1), "% of all households are made up of individuals and ", FORMAT(percent_seniors,1), "% have someone living alone who is 65 years of age or older. The average household size is ", FORMAT(average_household_size,2), " and the average family size is ", FORMAT(average_family_size,2), ".\n\nIn the ", IF(city_type="CDP","town",city_type), " the population is spread out with ", FORMAT(percent_under_18,1), "% under the age of 18, ", FORMAT(percent_18_to_24,1), "% from 18 to 24, ", FORMAT(percent_25_to_44,1), "% from 25 to 44, ", FORMAT(percent_45_to_64,1), "% from 45 to 64, and ", FORMAT(percent_65_up,1), "% who are 65 years of age or older. The median age is ", FORMAT(median_age,0), " years. For every 100 females there are ", FORMAT(males_per_100_females,1), " males. For every 100 females age 18 and over, there are ", FORMAT(males_per_100_females_18_up,1), " males.", IF( median_income_households != 0 || median_income_families != 0 || median_income_male != 0 || median_income_female != 0 || per_capita_income != 0 || percent_poverty != 0.0 || percent_poverty_families != 0.0, CONCAT( "\n\nThe median income for a household in the ", IF(city_type="CDP","town",city_type), " is $", FORMAT(median_income_households,0), ", and the median income for a family is $", FORMAT(median_income_families,0), ". Males have a median income of $", FORMAT(median_income_male,0), " versus $", FORMAT(median_income_female,0), " for females. The per capita income for the ", IF(city_type="CDP","town",city_type), " is $", FORMAT(per_capita_income,0), ". ", FORMAT(percent_poverty,1), "% of the population and ", FORMAT(percent_poverty_families,1), "% of families are below the poverty line.", IF( percent_poverty != 0.0 || percent_poverty_families != 0.0, CONCAT( " Out of the total people living in poverty, ", FORMAT(percent_poverty_under_18,1), "% are under the age of 18 and ", FORMAT(percent_poverty_65_up,1), "% are 65 or older." ), "" ) ), "" ) ) ) ) AS article FROM articles4; create table duplicates4 select state, county, city, population, land_area, white, median_age, count(CONCAT(population,",",land_area,",",white,",",median_age)) as counted from articles group by state, county, city, population, land_area, white, median_age; CREATE TABLE articles6 SELECT articles.*, IF( valid || duplicates4.counted > 1, "1", "0" ) AS unique_data FROM articles RIGHT JOIN duplicates4 ON articles.state = duplicates4.state && articles.county = duplicates4.county && articles.city = duplicates4.city && articles.population = duplicates4.population && articles.land_area = duplicates4.land_area && articles.white = duplicates4.white && articles.median_age = duplicates4.median_age; CREATE TABLE articles6 SELECT articles.*, IF( duplicates4.count > 1, "1", "0" ) AS unique_data FROM articles RIGHT JOIN duplicates4 ON articles.state = duplicates4.state && articles.county = duplicates4.county && articles.city = duplicates4.city && articles.population = duplicates4.population && articles.land_area = duplicates4.land_area && articles.white = duplicates4.white && articles.median_age = duplicates4.median_age; create table articles7 select articles6.*, CONCAT(city, IF(unique_data > 0, ", ", CONCAT(" (", city_type, "), ")), county, ", ", state) AS newtitle from articles6 where !valid; select CONCAT("*1*\n", city, " (", city_type, "), ", state, "\n#REDIRECT [[", newtitle, "]]") from articles6 where !valid && unique_data limit 30; +-----------------------------+-----------------------+------+-----+---------+-------+ | state | varchar(64) | | | | | | id | int(10) unsigned | | | 0 | | | type | tinyint(3) unsigned | | | 0 | | | name | varchar(64) | | | | | | city_type | varchar(32) | | | | | | county | varchar(64) | | | | | | sumlevel | tinyint(3) unsigned | | | 0 | | | state_id | smallint(5) unsigned | | | 0 | | | place_id | mediumint(8) unsigned | | | 0 | | | population | int(10) unsigned | | | 0 | | | total_area | float | | | 0 | | | total_area_km | float | | | 0 | | | land_area | float | | | 0 | | | land_area_km | float | | | 0 | | | water_area | float | | | 0 | | | water_area_km | float | | | 0 | | | percent_water | float | | | 0 | | | pop_density | float | | | 0 | | | pop_density_km | float | | | 0 | | | housing_units | int(10) unsigned | | | 0 | | | hu_density | float | | | 0 | | | hu_density_km | float | | | 0 | | | total_one_race | int(10) unsigned | | | 0 | | | white | int(10) unsigned | | | 0 | | | black | int(10) unsigned | | | 0 | | | native | int(10) unsigned | | | 0 | | | asian | int(10) unsigned | | | 0 | | | pacific | int(10) unsigned | | | 0 | | | other | int(10) unsigned | | | 0 | | | two_or_more | int(10) unsigned | | | 0 | | | hispanic | int(10) unsigned | | | 0 | | | households | int(10) unsigned | | | 0 | | | percent_families | float | | | 0 | | | percent_with_children | float | | | 0 | | | percent_married | float | | | 0 | | | percent_no_husband | float | | | 0 | | | percent_non_families | float | | | 0 | | | percent_alone | float | | | 0 | | | percent_seniors | float | | | 0 | | | average_household_size | float | | | 0 | | | average_family_size | float | | | 0 | | | percent_under_18 | float | | | 0 | | | percent_18_to_24 | float | | | 0 | | | percent_25_to_44 | float | | | 0 | | | percent_45_to_64 | float | | | 0 | | | percent_65_up | float | | | 0 | | | median_age | float | | | 0 | | | males_per_100_females | float | | | 0 | | | males_per_100_females_18_up | float | | | 0 | | | median_income_households | mediumint(8) unsigned | | | 0 | | | median_income_families | mediumint(8) unsigned | | | 0 | | | per_capita_income | mediumint(8) unsigned | | | 0 | | | median_income_male | mediumint(8) unsigned | | | 0 | | | median_income_female | mediumint(8) unsigned | | | 0 | | | percent_poverty | float | | | 0 | | | percent_poverty_under_18 | float | | | 0 | | | percent_poverty_65_up | float | | | 0 | | | percent_poverty_families | float | | | 0 | | | source | varchar(128) | | | | | | city | varchar(76) | | | | | | valid | char(1) | | | | | | repeat | char(1) | | | | | | link | varchar(212) | | | | | | title | varchar(208) | | | | | | article | mediumblob | YES | | NULL | | +-----------------------------+-----------------------+------+-----+---------+-------+ CONCAT(city, state) ----- Query2.txt ---- !!!! NOTES !!!! * "charter Township" needs to be updated to be "Charter Township" # # create table articles_temp select articles6.*, CONCAT(city, IF(unique_data > 0, ", ", # CONCAT(" (", city_type, "), ")), county, ", ", state) AS newtitle from articles6; # select IF(valid, title, newtitle) AS title from articles_temp into outfile '/G/mysql/cities.txt' # select CONCAT(IF(valid, title, newtitle)," | ", county, ", ", state) from articles_temp into outfile '/G/mysql/cities.txt' # # SELECT CONCAT(title, " | ", latitude, " | ", longitude) FROM census_data WHERE valid; # # SELECT CONCAT(IF(valid, census_data.title, newtitle), " | ", latitude, " | ", longitude) AS line from census_data inner join # articles_temp where census_data.sumlevel=articles_temp.sumlevel && census_data.state_id=articles_temp.place_id && # census_data.place_id=articles_temp.place_id; # create table temp1 SELECT CONCAT(IF(articles_temp.valid, articles_temp.title, newtitle), " | ", latitude, " | ", longitude) AS line, census_data.valid, census_data.title, longitude, latitude, articles_temp.valid AS valid2, articles_temp.title AS title2, articles_temp.newtitle AS newtitle from census_data inner join articles_temp where census_data.sumlevel=articles_temp.sumlevel && census_data.state_id=articles_temp.state_id && census_data.place_id=articles_temp.place_id && census_data.title=articles_temp.title; SELECT CONCAT( "\n***", "\n*1*\n", title, "\n*2*\n", TRUNCATE(ABS(latitude),0), "°", TRUNCATE((ABS(latitude) - TRUNCATE(ABS(latitude),0)) * 60,0), "'", ROUND((((ABS(latitude) - TRUNCATE(ABS(latitude),0)) * 60) - TRUNCATE((ABS(latitude) - TRUNCATE(ABS(latitude),0)) * 60,0)) * 60,0), "\" ", IF(latitude >= 0, "North", "South"), ", ", TRUNCATE(ABS(longitude),0), "°", TRUNCATE((ABS(longitude) - TRUNCATE(ABS(longitude),0)) * 60,0), "'", ROUND((((ABS(longitude) - TRUNCATE(ABS(longitude),0)) * 60) - TRUNCATE((ABS(longitude) - TRUNCATE(ABS(longitude),0)) * 60,0)) * 60,0), "\" ", IF(longitude >= 0, "East", "West"), " (", ROUND(latitude,6), ", ", ROUND(longitude,6), ")", "\n*3*\n", name, "\n*4*\n", FORMAT(population,0), "\n", FORMAT(households,0), "\n", FORMAT(FLOOR(households * percent_families / 100),0), "\n", FORMAT(pop_density_km,1), "\n", FORMAT(pop_density,1), "\n", IF(population = 0, "0.00", FORMAT(white * 100 / population,2)), "\n", FORMAT(percent_25_to_44,1), "\n", FORMAT(median_income_male,0), "\n", FORMAT(median_income_female,0), "\n", "\n***\n" ) AS coordinates FROM census_data WHERE valid INTO OUTFILE 'coordinates.txt'; SELECT CONCAT( "\n***", "\n*1*\n", title, "\n*2*\n\n", "\n*3*\n", name, "\n*4*\n", FORMAT(population,0), "\n", FORMAT(households,0), "\n", FORMAT(FLOOR(households * percent_families / 100),0), "\n", FORMAT(pop_density_km,1), "\n", FORMAT(pop_density,1), "\n", IF(population = 0, "0.00", FORMAT(white * 100 / population,2)), "\n", FORMAT(percent_25_to_44,1), "\n", FORMAT(median_income_male,0), "\n", FORMAT(median_income_female,0), "\n", "\n***\n" ) AS data FROM articles WHERE valid INTO OUTFILE 'output.txt'; | articles | | articles4 | | articles5 | | articles6 | | articles7 | | articles_cdp | | articles_temp | | census_cosub | | census_counties | | census_data | | census_places | | census_zipcodes | | cities | | classcodes | | counties | | cur | | duplicates4 | | fips | | gis | | gnis_deci | | lists | | old2 | | output2 | | states | | temp1 | | unlocode | | unlocode2 |