Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- using System.Windows.Forms;
- using Npgsql;
- //MULTIPLY BY 0.05534^-1 = 18.0701120347.
- namespace Project3Attempt2Visualization
- {
- public partial class Form1 : Form
- {
- public static double MULTCRIMES = 1;
- public static DataSet DSScholen = new DataSet();
- public static DataSet DSMisdaden = new DataSet();
- public static DataTable DTScholen = new DataTable();
- public static DataTable DTMisdaden = new DataTable();
- public static DataSet curDSScholen = new DataSet();
- public static DataSet curDSMisdaden = new DataSet();
- public static DataTable curDTScholen = new DataTable();
- public static DataTable curDTMisdaden = new DataTable();
- public static NpgsqlConnection Con = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Project3Attempt2");
- public static string extraBit = " (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) ";
- public Form1()
- {
- InitializeComponent();
- Func1();
- initializeChart();
- initializeComboBoxes();
- initializeCheckBoxes();
- updateChart(@"SELECT COUNT(*), wijk FROM scholen3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;",
- @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;");
- //findClosestSchoolsForAll();
- }
- private void Form1_Load(object sender, EventArgs e)
- {
- comboBox4.Visible = false;
- richTextBox1.Visible = false;
- }
- public static void Func1()
- {
- Con.Open();
- string command = @"SELECT COUNT(*), wijk FROM scholen3 WHERE wijk IN
- (
- SELECT wijk FROM misdaden3
- INTERSECT
- SELECT wijk FROM scholen3
- )
- GROUP BY wijk; ";
- NpgsqlDataAdapter DA = new NpgsqlDataAdapter(command, Con);
- DA.Fill(DSScholen);
- DTScholen = DSScholen.Tables[0];
- Con.Close();
- Con.Open();
- string command2 = @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN
- (
- SELECT wijk FROM misdaden3
- INTERSECT
- SELECT wijk FROM scholen3
- )
- GROUP BY wijk; ";
- NpgsqlDataAdapter DA2 = new NpgsqlDataAdapter(command2, Con);
- DA2.Fill(DSMisdaden);
- DTMisdaden = DSMisdaden.Tables[0];
- Con.Close();
- }
- public void updateChart(string SQLscholen, string SQLMisdaden)
- {
- chart1.Series.Clear();
- try
- {
- chart1.Series.Add("Scholen");
- chart1.Series.Add("Misdaden");
- }
- catch
- {
- }
- curDSMisdaden.Clear();
- curDSScholen.Clear();
- curDTMisdaden.Clear();
- curDTScholen.Clear();
- Con.Open();
- NpgsqlDataAdapter DAScholen = new NpgsqlDataAdapter(SQLscholen, Con);
- DAScholen.Fill(curDSScholen);
- curDTScholen = curDSScholen.Tables[0];
- for (int i = 0; i < curDTScholen.Rows.Count; i++)
- {
- for (int j = 0; j < curDTScholen.Columns.Count; j++)
- {
- richTextBox1.Text = richTextBox1.Text + curDTScholen.Rows[i][j].ToString() + "\n";
- }
- richTextBox1.Text = richTextBox1.Text + "\n";
- chart1.Series[0].Points.AddY(int.Parse(curDTScholen.Rows[i][0].ToString()));
- chart1.Series[0].Points[i].AxisLabel = curDTScholen.Rows[i][1].ToString();
- }
- NpgsqlDataAdapter DAMisdaden = new NpgsqlDataAdapter(SQLMisdaden, Con);
- DAMisdaden.Fill(curDSMisdaden);
- curDTMisdaden = curDSMisdaden.Tables[0];
- for (int i = 0; i < curDTMisdaden.Rows.Count; i++)
- {
- for (int j = 0; j < curDTMisdaden.Columns.Count; j++)
- {
- richTextBox1.Text = richTextBox1.Text + curDTMisdaden.Rows[i][j].ToString() + "\n";
- }
- richTextBox1.Text = richTextBox1.Text + "\n";
- chart1.Series[1].Points.AddY(int.Parse(curDTMisdaden.Rows[i][0].ToString()) * MULTCRIMES);
- }
- chart1.Series[0].YAxisType = System.Windows.Forms.DataVisualization.Charting.AxisType.Secondary;
- Con.Close();
- chart1.ChartAreas[0].AxisY.Title = "Aantal misdaden";
- //chart1.ChartAreas[0].AxisY.TextOrientation = System.Windows.Forms.DataVisualization.Charting.TextOrientation.Horizontal;
- chart1.ChartAreas[0].AxisY.Enabled = System.Windows.Forms.DataVisualization.Charting.AxisEnabled.True;
- chart1.ChartAreas[0].AxisY.LabelStyle.Enabled = true;
- chart1.ChartAreas[0].AxisY2.Title = "Aantal scholen";
- chart1.ChartAreas[0].AxisY2.Enabled = System.Windows.Forms.DataVisualization.Charting.AxisEnabled.True;
- //chart1.ChartAreas[0].AxisY2.TextOrientation = System.Windows.Forms.DataVisualization.Charting.TextOrientation.Horizontal;
- chart1.ChartAreas[0].AxisY2.LabelStyle.Enabled = true;
- chart1.ChartAreas[0].AxisX.Title = "Wijk";
- chart1.ChartAreas[0].AxisX.Enabled = System.Windows.Forms.DataVisualization.Charting.AxisEnabled.True;
- //chart1.ChartAreas[0].AxisY2.TextOrientation = System.Windows.Forms.DataVisualization.Charting.TextOrientation.Horizontal;
- chart1.ChartAreas[0].AxisX.LabelStyle.Enabled = true;
- }
- public void updateChartForDist(List<double> alldistances)
- {
- chart1.Series.Clear();
- float n = 0.5f;
- try
- {
- chart1.Series.RemoveAt(1);
- chart1.Series[0].Name = "Afstand vanaf de dichtbijzijnde school in kilometer";
- }
- catch
- {
- }
- chart1.Series.Add("Afstand vanaf de dichtbijzijnde school in kilometer");
- curDSMisdaden.Clear();
- curDSScholen.Clear();
- curDTMisdaden.Clear();
- curDTScholen.Clear();
- for (int i = 0; i < alldistances.Count; i++)
- {
- chart1.Series[0].Points.AddY((int)alldistances[i]);
- chart1.Series[0].Points[i].AxisLabel = ((i * n)/2.5).ToString() + "-" + (((i + 1) * n)/2.5).ToString();
- }
- chart1.ChartAreas[0].AxisY.Title = "Aantal misdaden";
- //chart1.ChartAreas[0].AxisY.TextOrientation = System.Windows.Forms.DataVisualization.Charting.TextOrientation.Horizontal;
- chart1.ChartAreas[0].AxisY.Enabled = System.Windows.Forms.DataVisualization.Charting.AxisEnabled.True;
- chart1.ChartAreas[0].AxisY.LabelStyle.Enabled = true;
- chart1.ChartAreas[0].AxisY2.Title = "Aantal misdaden";
- chart1.ChartAreas[0].AxisY2.Enabled = System.Windows.Forms.DataVisualization.Charting.AxisEnabled.True;
- //chart1.ChartAreas[0].AxisY2.TextOrientation = System.Windows.Forms.DataVisualization.Charting.TextOrientation.Horizontal;
- chart1.ChartAreas[0].AxisY2.LabelStyle.Enabled = true;
- chart1.ChartAreas[0].AxisX.Title = "Afstand";
- chart1.ChartAreas[0].AxisX.Enabled = System.Windows.Forms.DataVisualization.Charting.AxisEnabled.True;
- //chart1.ChartAreas[0].AxisY2.TextOrientation = System.Windows.Forms.DataVisualization.Charting.TextOrientation.Horizontal;
- chart1.ChartAreas[0].AxisX.LabelStyle.Enabled = true;
- }
- public void updateChartSeperate(List<string> SQLmisdaden, List<string> SQLscholen)
- {
- chart1.Series.Clear();
- Con.Open();
- chart1.Series.Add("Straafroven");
- chart1.Series.Add("Fietsendiefstallen");
- if (SQLscholen.Count == 1)
- {
- chart1.Series.Add("Scholen");
- }
- else
- {
- for (int i = 0; i < SQLscholen.Count; i++)
- {
- chart1.Series.Add("Scholen: " + i.ToString());
- }
- }
- for (int i = 0; i < SQLmisdaden.Count; i++)
- {
- NpgsqlDataAdapter TempDA = new NpgsqlDataAdapter(SQLmisdaden[i], Con);
- DataSet tempDS = new DataSet();
- DataTable tempDT = new DataTable();
- TempDA.Fill(tempDS);
- tempDT = tempDS.Tables[0];
- for (int j = 0; j < tempDT.Rows.Count; j++)
- {
- chart1.Series[i].Points.AddY(int.Parse(tempDT.Rows[j][0].ToString()));
- }
- }
- for (int i = 0; i < SQLscholen.Count; i++)
- {
- NpgsqlDataAdapter TempDA = new NpgsqlDataAdapter(SQLscholen[i], Con);
- DataSet tempDS = new DataSet();
- DataTable tempDT = new DataTable();
- TempDA.Fill(tempDS);
- tempDT = tempDS.Tables[0];
- chart1.Series[SQLscholen.Count + i + 1].YAxisType = System.Windows.Forms.DataVisualization.Charting.AxisType.Secondary;
- for (int j = 0; j < tempDT.Rows.Count; j++)
- {
- chart1.Series[i].Points[j].AxisLabel = tempDT.Rows[j][1].ToString();
- chart1.Series[SQLscholen.Count + i+1].Points.AddY(int.Parse(tempDT.Rows[j][0].ToString()));
- }
- }
- /*
- for (int i = 0; i < SQLscholen.Count; i++)
- {
- NpgsqlDataAdapter DAScholen = new NpgsqlDataAdapter(SQLscholen[i], Con);
- DAScholen.Fill(curDSScholen);
- curDTScholen = curDSScholen.Tables[0];
- for (int j = 0; j < curDTScholen.Rows.Count; j++)
- {
- for (int k = 0; k < curDTScholen.Columns.Count; k++)
- {
- richTextBox1.Text = richTextBox1.Text + curDTScholen.Rows[j][k].ToString() + "\n";
- }
- richTextBox1.Text = richTextBox1.Text + "\n";
- chart1.Series[i].Points.AddY(int.Parse(curDTScholen.Rows[j][0].ToString()));
- chart1.Series[i].Points[j].AxisLabel = curDTScholen.Rows[j][1].ToString();
- }
- }
- chart1.Series[0].YAxisType = System.Windows.Forms.DataVisualization.Charting.AxisType.Secondary;
- for (int i = 0; i < SQLmisdaden.Count; i++)
- {
- NpgsqlDataAdapter DAMisdaden = new NpgsqlDataAdapter(SQLmisdaden[i], Con);
- DAMisdaden.Fill(curDSMisdaden);
- curDTMisdaden = curDSMisdaden.Tables[0];
- for (int j = 0; j < curDTMisdaden.Rows.Count; j++)
- {
- for (int k = 0; k < curDTMisdaden.Columns.Count; k++)
- {
- richTextBox1.Text = richTextBox1.Text + curDTMisdaden.Rows[j][k].ToString() + "\n";
- }
- richTextBox1.Text = richTextBox1.Text + "\n";
- chart1.Series[0].Points.AddY(int.Parse(curDTMisdaden.Rows[j][0].ToString()) * MULTCRIMES);
- }
- }
- */
- Con.Close();
- }
- public void initializeChart()
- {
- chart1.Series.RemoveAt(0);
- chart1.Series.Add("Scholen");
- chart1.Series.Add("Misdaden");
- chart1.ChartAreas[0].AxisX.LabelAutoFitStyle = System.Windows.Forms.DataVisualization.Charting.LabelAutoFitStyles.None;
- chart1.ChartAreas[0].AxisX.LabelStyle.Font = new System.Drawing.Font("Trebuchet MS", 7.5F, System.Drawing.FontStyle.Bold);
- chart1.ChartAreas[0].AxisX.LabelStyle.Interval = 1;
- chart1.Series[1].AxisLabel = "Scholen";
- }
- public void initializeComboBoxes()
- {
- comboBox1.Items.Add("Alle wijken");
- for (int i = 0; i < DTScholen.Rows.Count; i++)
- {
- comboBox1.Items.Add(DTScholen.Rows[i][1].ToString());
- }
- comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
- comboBox1.SelectedIndex = 0;
- comboBox2.Items.Add("Alle tijden");
- comboBox2.Items.Add("00:00-06:00");
- comboBox2.Items.Add("06:00-12:00");
- comboBox2.Items.Add("12:00-18:00");
- comboBox2.Items.Add("18:00-00:00");
- comboBox2.DropDownStyle = ComboBoxStyle.DropDownList;
- comboBox2.SelectedIndex = 0;
- comboBox2.Visible = false;
- comboBox3.Items.Add("Straatroven en fietsendiefstallen gezamelijk");
- comboBox3.Items.Add("Straatroven en fietsendiefstallen apart");
- comboBox3.Items.Add("Alleen straatroven");
- comboBox3.Items.Add("Alleen fietsendiefstallen");
- comboBox3.DropDownStyle = ComboBoxStyle.DropDownList;
- comboBox3.SelectedIndex = 0;
- comboBox4.Items.Add("Gezamelijk");
- comboBox4.Items.Add("Apart");
- comboBox4.DropDownStyle = ComboBoxStyle.DropDownList;
- comboBox4.SelectedIndex = 0;
- }
- public void initializeCheckBoxes()
- {
- checkBox1.Checked = true;
- checkBox2.Checked = true;
- checkBox3.Checked = true;
- checkBox4.Checked = true;
- }
- private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
- {
- foreach (CheckBox CB in panel1.Controls)
- {
- CB.Visible = true;
- }
- if (comboBox1.SelectedIndex == 0)
- {
- updateChart(@"SELECT COUNT(*), wijk FROM scholen3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;",
- @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;");
- chart1.Series[0].YAxisType = System.Windows.Forms.DataVisualization.Charting.AxisType.Secondary;
- comboBox2.Enabled = true;
- comboBox3.Enabled = true;
- comboBox4.Enabled = true;
- checkBox1.Enabled = true;
- checkBox2.Enabled = true;
- checkBox3.Enabled = true;
- checkBox4.Enabled = true;
- }
- else
- {
- foreach (CheckBox CB in panel1.Controls)
- {
- CB.Visible = false;
- }
- testFunc(comboBox1.Items[comboBox1.SelectedIndex].ToString());
- comboBox2.Enabled = false;
- comboBox3.Enabled = false;
- comboBox4.Enabled = false;
- checkBox1.Enabled = false;
- checkBox2.Enabled = false;
- checkBox3.Enabled = false;
- checkBox4.Enabled = false;
- /*
- updateChart(@"SELECT COUNT(*), wijk FROM scholen3 WHERE wijk LIKE '" + comboBox1.Items[comboBox1.SelectedIndex].ToString() + "' GROUP BY wijk;",
- @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk LIKE '" + comboBox1.Items[comboBox1.SelectedIndex].ToString() + "' GROUP BY wijk;");
- chart1.Series[0].YAxisType = System.Windows.Forms.DataVisualization.Charting.AxisType.Primary;
- */
- }
- }
- private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
- {
- foreach (CheckBox CB in panel1.Controls)
- {
- CB.Enabled = true;
- }
- switch (comboBox3.SelectedIndex)
- {
- case 0:
- updateChart(@"SELECT COUNT(*), wijk FROM scholen3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;",
- @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;");
- break;
- case 1:
- foreach (CheckBox CB in panel1.Controls)
- {
- CB.Enabled = false;
- }
- List<string> tempstringScholen = new List<string>();
- List<string> tempstringMisdaden = new List<string>();
- tempstringMisdaden.Add(@"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) AND soort LIKE 'straatroof' GROUP BY wijk ORDER BY wijk;");
- tempstringMisdaden.Add(@"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) AND soort LIKE 'fietsendiefstal' GROUP BY wijk ORDER BY wijk;");
- tempstringScholen.Add(@"SELECT COUNT(*), wijk FROM scholen3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;");
- updateChartSeperate(tempstringMisdaden, tempstringScholen);
- break;
- case 2:
- updateChart(@"SELECT COUNT(*), wijk FROM scholen3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;",
- @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) AND soort LIKE 'straatroof' GROUP BY wijk ORDER BY wijk;");
- break;
- case 3:
- updateChart(@"SELECT COUNT(*), wijk FROM scholen3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;",
- @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) AND soort LIKE 'fietsendiefstal' GROUP BY wijk ORDER BY wijk;");
- break;
- }
- }
- public void updateGraphMultipleSchools()
- {
- List<string> activeScholen = new List<string>();
- List<bool> checkboxstates = new List<bool>();
- foreach (CheckBox CB in panel1.Controls)
- {
- checkboxstates.Add(CB.Checked);
- }
- if (checkboxstates.Any(a => a == true))
- {
- if (checkBox1.Checked == true)
- {
- activeScholen.Add("basis");
- }
- if (checkBox2.Checked == true)
- {
- activeScholen.Add("middelbaar");
- }
- if (checkBox3.Checked == true)
- {
- activeScholen.Add("MBO");
- }
- if (checkBox4.Checked == true)
- {
- activeScholen.Add("HBO");
- }
- string subcommand = "";
- int index = 0;
- foreach (string s in activeScholen)
- {
- index++;
- subcommand = subcommand + "niveau LIKE '" + s + "'";
- if (index != activeScholen.Count)
- {
- subcommand = subcommand + " OR ";
- }
- }
- string commandstring = "SELECT wijk, count(*) FROM scholen3 WHERE (" + subcommand + ") AND wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk ORDER BY wijk;";
- richTextBox1.Text = commandstring;
- DataTable DTWijken = insertSQLIntoDT("SELECT DISTINCT wijk FROM scholen3 WHERE (" + subcommand + ") AND wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk;");
- richTextBox1.Text = "";
- for (int i = 0; i < DTWijken.Rows.Count; i++)
- {
- richTextBox1.Text = richTextBox1.Text + DTWijken.Rows[i][0].ToString() + " ";
- }
- switch (comboBox3.SelectedIndex)
- {
- case 0:
- updateChart(commandstring, @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT DISTINCT wijk FROM scholen3 WHERE (" + subcommand + ") AND wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk) GROUP BY wijk ORDER BY wijk;");
- break;
- case 1:
- List<string> tempstringScholen = new List<string>();
- List<string> tempstringMisdaden = new List<string>();
- tempstringMisdaden.Add(@"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) AND soort LIKE 'straatroof' AND wijk IN (SELECT DISTINCT wijk FROM scholen3 WHERE (" + subcommand + ") AND wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk) GROUP BY wijk ORDER BY wijk;");
- tempstringMisdaden.Add(@"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) AND soort LIKE 'fietsendiefstal' AND wijk IN (SELECT DISTINCT wijk FROM scholen3 WHERE (" + subcommand + ") AND wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk) GROUP BY wijk ORDER BY wijk;");
- tempstringScholen.Add(commandstring);
- updateChartSeperate(tempstringMisdaden, tempstringScholen);
- break;
- case 2:
- updateChart(commandstring,
- @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) AND soort LIKE 'straatroof' AND wijk IN (SELECT DISTINCT wijk FROM scholen3 WHERE (" + subcommand + ") AND wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk) GROUP BY wijk ORDER BY wijk;");
- break;
- case 3:
- updateChart(commandstring,
- @"SELECT COUNT(*), wijk FROM misdaden3 WHERE wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) AND soort LIKE 'fietsendiefstal' AND wijk IN (SELECT DISTINCT wijk FROM scholen3 WHERE (" + subcommand + ") AND wijk IN (SELECT wijk FROM misdaden3 INTERSECT SELECT wijk FROM scholen3) GROUP BY wijk) GROUP BY wijk ORDER BY wijk;");
- break;
- }
- }
- }
- //Types
- //basis
- //middelbaar
- //MBO
- //HBO
- private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
- {
- if (comboBox4.SelectedIndex != 0)
- {
- foreach (CheckBox CB in panel1.Controls)
- {
- CB.Enabled = false;
- }
- }
- else
- {
- foreach (CheckBox CB in panel1.Controls)
- {
- CB.Enabled = true;
- }
- }
- }
- #region findAndCalculateDistance
- public void findClosestSchoolsForAll()
- {
- curDSMisdaden.Clear();
- curDSScholen.Clear();
- curDTMisdaden.Clear();
- curDTScholen.Clear();
- List<string> tempWijken = new List<string>();
- Con.Open();
- string command = @"SELECT lat, lng, wijk FROM misdaden3 WHERE wijk IN" + extraBit + ";";
- NpgsqlDataAdapter DA = new NpgsqlDataAdapter(command, Con);
- DA.Fill(curDSScholen);
- curDTScholen = curDSScholen.Tables[0];
- Con.Close();
- richTextBox1.Text = "";
- for (int i = 0; i < curDTScholen.Rows.Count; i++)
- {
- for (int j = 0; j < curDTScholen.Columns.Count; j++)
- {
- richTextBox1.Text = richTextBox1.Text + curDTScholen.Rows[i][j].ToString() + "\n";
- }
- richTextBox1.Text = richTextBox1.Text + "\n";
- }
- Con.Open();
- //Intialize wijken
- //string command1 = "SELECT DISTINCT wijk FROM scholen3 WHERE wijk IN " + extraBit + ";";
- string command1 = "SELECT DISTINCT wijk FROM scholen3;";
- NpgsqlDataAdapter DA1 = new NpgsqlDataAdapter(command1, Con);
- DataSet DSwijken = new DataSet();
- DataTable DTWijken = new DataTable();
- DA1.Fill(DSwijken);
- DTWijken = DSwijken.Tables[0];
- for (int i = 0; i < DTWijken.Rows.Count; i++)
- {
- tempWijken.Add(DTWijken.Rows[i][0].ToString());
- }
- richTextBox1.Text = "";
- for (int i = 0; i < 1; i++)
- {
- List<school> tempscholen = new List<school>();
- //string tempcommand = "SELECT lat, lng, casenummer FROM misdaden3 WHERE wijk LIKE '" + tempWijken[i] + "';";
- string tempcommand = "SELECT lat, lng, casenummer FROM misdaden3;";
- NpgsqlDataAdapter DAMisdadenTemp = new NpgsqlDataAdapter(tempcommand, Con);
- DataSet DSMisdadenTemp = new DataSet();
- DataTable DTMisdadenTemp = new DataTable();
- DAMisdadenTemp.Fill(DSMisdadenTemp);
- DTMisdadenTemp = DSMisdadenTemp.Tables[0];
- //string tempcommand2 = "SELECT naam, lat, lng FROM scholen3 WHERE WIJK LIKE '" + tempWijken[i] + "';";
- string tempcommand2 = "SELECT naam, lat, lng FROM scholen3;";
- NpgsqlDataAdapter DAScholenTemp = new NpgsqlDataAdapter(tempcommand2, Con);
- DataSet DSScholenTemp = new DataSet();
- DataTable DTScholenTemp = new DataTable();
- DAScholenTemp.Fill(DSScholenTemp);
- DTScholenTemp = DSScholenTemp.Tables[0];
- for (int j = 0; j < DTScholenTemp.Rows.Count; j++)
- {
- tempscholen.Add(new school(new latlng(double.Parse(DTScholenTemp.Rows[j][1].ToString().Replace(",",".")), double.Parse(DTScholenTemp.Rows[j][2].ToString().Replace(",","."))), DTScholenTemp.Rows[j][0].ToString()));
- }
- MessageBox.Show(tempscholen.Count.ToString());
- richTextBox1.Text = richTextBox1.Text + tempWijken[i].ToString() + "\n";
- for (int j = 0; j < tempscholen.Count; j++)
- {
- //richTextBox1.Text = richTextBox1.Text + tempscholen[j].name + "\n";
- //richTextBox1.Text = richTextBox1.Text + tempscholen[j].location.lat.ToString() + "\n";
- //richTextBox1.Text = richTextBox1.Text + tempscholen[j].location.lng.ToString() + "\n";
- //richTextBox1.Text = richTextBox1.Text + "\n";
- }
- richTextBox1.Text = "";
- for (int j = 0; j < DTMisdadenTemp.Rows.Count; j++)
- {
- double templat = double.Parse(DTMisdadenTemp.Rows[j][0].ToString().Replace(",", "."));
- double templng = double.Parse(DTMisdadenTemp.Rows[j][1].ToString().Replace(",", "."));
- //richTextBox1.Text = richTextBox1.Text + tempscholen.Count + "\n";
- //richTextBox1.Text = richTextBox1.Text + DTMisdadenTemp.Rows[j][2].ToString() + "\n";
- //richTextBox1.Text = richTextBox1.Text + DTMisdadenTemp.Rows[j][0].ToString() + "\n";
- //richTextBox1.Text = richTextBox1.Text + DTMisdadenTemp.Rows[j][1].ToString() + "\n";
- stringdouble temps = findClosestSchoolForOneCrime(tempscholen, new latlng(templat, templng));
- //richTextBox1.Text = richTextBox1.Text + temps.s + "\n";
- //richTextBox1.Text = richTextBox1.Text + temps.d.ToString() + "\n";
- //richTextBox1.Text = richTextBox1.Text + "\n";
- NpgsqlCommand commandaa = Con.CreateCommand();
- commandaa.CommandText = "UPDATE misdaden3 SET closestschool = '" + temps.s + "', closestdist = " + temps.d.ToString() + " WHERE casenummer LIKE '" + DTMisdadenTemp.Rows[j][2] + "';";
- try
- {
- richTextBox1.Text = richTextBox1.Text + commandaa.CommandText + "\n";
- commandaa.ExecuteNonQuery();
- }
- catch
- {
- }
- }
- }
- Con.Close();
- }
- public stringdouble findClosestSchoolForOneCrime(List<school> scholen, latlng misdaad)
- {
- double closestdist = 999999;
- int curI = 0;
- for (int i = 0; i < scholen.Count; i++)
- {
- double a = getDistBetweenLatLng(scholen[i].location, misdaad);
- if (a < closestdist)
- {
- closestdist = a;
- curI = i;
- //MessageBox.Show("CHANGE \n" + i.ToString() + "\n" + closestdist.ToString() + "\n\n" + misdaad.lat.ToString() + "\n" + misdaad.lng.ToString() + "\n\n" + scholen[i].location.lat.ToString() + "\n" + scholen[i].location.lng.ToString() + "\n");
- }
- }
- //MessageBox.Show(curI.ToString() + "\nYADUN\n" + closestdist.ToString() + "\n\n" + misdaad.lat.ToString() + "\n" + misdaad.lng.ToString() + "\n\n" + scholen[curI].location.lat.ToString() + "\n" + scholen[curI].location.lng.ToString() + "\n");
- return new stringdouble(scholen[curI].name, closestdist);
- }
- public double getDistBetweenLatLng(latlng A1, latlng A2)
- {
- double deltaX = 1*69 * (A1.lat - A2.lat);
- double deltaY = 1*111 * (A1.lng - A2.lng);
- return Math.Round(Math.Pow((Math.Pow(deltaX, 2) + Math.Pow(deltaY, 2)), 0.5), 3);
- }
- #endregion
- #region getDistanceFromSQL
- public void testFunc(string wijk)
- {
- float n = 1.0f;
- DataTable DT1 = insertSQLIntoDT("SELECT closestdist FROM misdaden3 WHERE wijk LIKE '" + wijk + "'ORDER BY closestdist;");
- richTextBox1.Text = DT1.Rows[0][0].ToString() + "-" + DT1.Rows[DT1.Rows.Count/2][0] + "-" + DT1.Rows[3 * DT1.Rows.Count / 4][0] + "-" + DT1.Rows[DT1.Rows.Count-1][0] + "\n";
- List<double> allDistancesTemp = new List<double>();
- for (int i = 0; i < DT1.Rows.Count; i++)
- {
- allDistancesTemp.Add(double.Parse(DT1.Rows[i][0].ToString()));
- }
- allDistancesTemp.Sort();
- double maxdist = allDistancesTemp[allDistancesTemp.Count - 1];
- maxdist = Math.Ceiling(maxdist * n)+1;
- List<double> totalCounterCrimes = new List<double>();
- for (int i = 0; i < maxdist; i++)
- {
- totalCounterCrimes.Add(0);
- }
- for (int i = 0; i < DT1.Rows.Count; i++)
- {
- double testdouble = double.Parse(DT1.Rows[i][0].ToString());
- testdouble = Math.Ceiling(testdouble*n)/n;
- //richTextBox1.Text = richTextBox1.Text + testdouble.ToString() + "\n";
- totalCounterCrimes[(int)testdouble * (int)n] = (totalCounterCrimes[(int)testdouble * (int)n] + MULTCRIMES);
- }
- Random R = new Random();
- for (int i = 0; i < totalCounterCrimes.Count; i++)
- {
- richTextBox1.Text = richTextBox1.Text + ((i * 0.5)/2.5).ToString() + "-" + (((i+1) * 0.5)/2.5).ToString() + ": " + totalCounterCrimes[i].ToString() + "\n";
- if (totalCounterCrimes[i] == 0)
- {
- //totalCounterCrimes[i] = R.Next(totalCounterCrimes.Max()/80)+10;
- }
- }
- updateChartForDist(totalCounterCrimes);
- }
- #endregion
- public static DataTable insertSQLIntoDT(string statement)
- {
- Con.Open();
- NpgsqlDataAdapter NDA = new NpgsqlDataAdapter(statement, Con);
- DataSet DS = new DataSet();
- NDA.Fill(DS);
- Con.Close();
- return DS.Tables[0];
- }
- private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
- {
- }
- private void checkBox1_CheckedChanged(object sender, EventArgs e)
- {
- updateGraphMultipleSchools();
- }
- private void checkBox2_CheckedChanged(object sender, EventArgs e)
- {
- updateGraphMultipleSchools();
- }
- private void checkBox3_CheckedChanged(object sender, EventArgs e)
- {
- updateGraphMultipleSchools();
- }
- private void checkBox4_CheckedChanged(object sender, EventArgs e)
- {
- updateGraphMultipleSchools();
- }
- private void panel1_Paint(object sender, PaintEventArgs e)
- {
- }
- }
- public class latlng
- {
- public double lat;
- public double lng;
- public latlng(double lat, double lng)
- {
- this.lat = lat;
- this.lng = lng;
- }
- }
- public class school
- {
- public latlng location;
- public string name;
- public school(latlng location, string name)
- {
- this.location = location;
- this.name = name;
- }
- }
- public class stringdouble
- {
- public string s;
- public double d;
- public stringdouble(string s, double d)
- {
- this.s = s;
- this.d = d;
- }
- }
- public class listTest
- {
- public List<string> test;
- public listTest(List<string> test)
- {
- this.test = test;
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement