이것이 MySQL이다 - part04_MySQL 응용 프로그램 및 공간 데이터

Chapter 12 PHP 기본 프로그래밍

Apache, MySQL, PHP 는 회사/기관이 각각 달라 별도로 설치하는것이 원칙이나 종종 호환성 문제를 일으켜 XAMPP로 미리 해결해서 배포하는 소프트웨어를 사용한다. 

XAMPP  = Apache + MySQL(혹은 mariadv) + PHP + Perl

설치시 select components에서 apache, php 2개만 남기고 체크를 끄고, 

bitnami for zampp에서 learn more about bitnami for xampp의 체크를 꺼도 좋다 .

컴퓨터 재부팅 후 xampp-controel.exe 실행

apache 가 활성화되있다면 start를 클릭해 활성화한다. 

php파일을 xampp/htdocs에 utf-8로 저장해 localhost/파일명.php로 접속할 수 있다. 

https://sourceforge.net/projects/xampp/

XAMPP

ALTER USER 'root'@'localhost' IDENTIFIED BY '5678';
FLUSH PRIVILEGES;

<?php
$host = "localhost";
$user = "root";
$password = "1234";
$database = "test";

$conn = new mysqli($host, $user, $password, $database);

if ($conn->connect_error) {
    die("접속 실패: " . $conn->connect_error);
}

echo "MySQL 접속 성공";

$conn->close();
?>

 

 

 

html로도 간단한 웹페이지를 만들수 있다.

html 문법으로만 구성된 파일을 확장명을 .php로 저장해서 사용해도 상관없다. 혹은 섞어서 사용할 수도 있다. 

서버스크립트 언어로는 php jsp asp.net이 있다. 

 

php 기본문법은 아래와같다. 

<?php ?> 기본틀

//한줄주석

/* 여러줄 주석 *// 

$var = 100; 변수 초기화, 언더바 사용가능, 숫자로 시작할 수 없으며 대소문자를 구별한다.  정수, 실수, 문자열, 불형, 객체, 배열..가능

print var, echo var 출력

if() elseif() else(), switch(){ case A: a; case B: b;}, for(){} while(){}조건문, 반복문

 

php 내장함수

date() max() min() pi(),strlen() 

round() ceil() 반올림, 올림

trim() 양쪽공백 제거

str_repeat(문자열, 횟수) 문자열을 횟수만큼 반복

str_replace(old, new, target) target문자열에서 old를 new로 바꿈

str_split(문자열, 길이) 문자열을 길이만큼 잘라 배열로 분리. 길이 기본값 1

explode(구분자, 문자열) 문자열을 구분자로 분리해서 배열로 저장한다. 

implode(배열, 구분자) 배열 중간에 구분자를 넣어서 하나의 문자열로 이어준다. 

htmlspecialchars(html코드)  html코드를 해석하지 않고 그대로 표시한다.

 

 

php mysql 내장함수

mysqli_connect() 서버연결

mysqli_connect_error() 오류발생시 원인을 알려줌

mysqli_close() 서버종료

mysqli_select_db() 사용할 데이터베이스 지정

mysqli_query() sql실행

mysqli_error() sql실패시 원인을 알려줌

mysqli_num_rows() select이 몇개의 행인지 알려줌

mysqli_fetch_array() select 결과행을 추출함

 

Chapter 13 php와 mysql의 연동

 

mysql db접속

<?php
$conn = new mysqli("localhost", "root", "1234", "test");

if ($conn->connect_error) {
    die("DB 접속 실패: " . $conn->connect_error);
}

echo "DB 접속 성공";

$conn->close();
?>

 

 

내장함수활용해보기

<?php
// DB 접속
$conn = mysqli_connect("localhost", "root", "1234", "test");

if (!$conn) {
    die("접속 실패: " . mysqli_connect_error());
}

// SQL 실행
$sql = "SELECT id, name FROM users";
$result = mysqli_query($conn, $sql);

// SQL 오류 확인
if (!$result) {
    die("SQL 오류: " . mysqli_error($conn));
}

// 조회된 행 수 출력
echo "조회 건수: " . mysqli_num_rows($result) . "<br><br>";

// 결과 출력
while ($row = mysqli_fetch_array($result)) {
    echo "ID: " . $row["id"] . " / 이름: " . $row["name"] . "<br>";
}

// 연결 종료
mysqli_close($conn);
?>

 

 

간단한 회원관리 페이지를 경험해보자.

<?php

$conn = mysqli_connect("localhost", "root", "1234", "test");
if (!$conn) {
    die("DB접속실패 : " . mysqli_connect_error());
}

// 회원등록
if (isset($_POST['join'])) {

    $name = $_POST['name'];
    $age = $_POST['age'];

    $sql = "INSERT INTO member(name, age)
            VALUES('$name', '$age')";

    if (mysqli_query($conn, $sql)) {
        echo "회원등록 성공<br><br>";
    } else {
        echo mysqli_error($conn);
    }
}
?>





<form method="post">
    이름 : <input type="text" name="name">
    나이 : <input type="number" name="age">
    <input type="submit" name="join" value="회원등록">
</form>
<hr>
<h3>회원목록</h3>




<?php

$sql = "SELECT * FROM member";
$result = mysqli_query($conn, $sql);

// 데이터가 있으면 출력
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_array($result)) {
        echo "번호 : " . $row['id'];
        echo " / 이름 : " . $row['name'];
        echo " / 나이 : " . $row['age'];
        echo " <a href='?del=".$row['id']."'>삭제</a>";
        echo "<br>";
    }
} else {
    echo "회원이 없습니다.";

}

// 회원삭제
if (isset($_GET['del'])) {
    $id = $_GET['del'];
    $sql = "DELETE FROM member
            WHERE id = $id";
    if (mysqli_query($conn, $sql)) {
        echo "<script>
                location.href='?';
              </script>";
    } else {
        echo mysqli_error($conn);
    }
}

mysqli_close($conn);

?>

 

 

 

Chapter 14 MySQL과 공간 데이터

 

GIS 지리정보시스템: 각종 자연물에 대한 위치속성 정보를 컴퓨터에 입력해 사용할 수 있게 만든 정보 시스템

GIS를 위해서는 속성데이터 dbms와 공간데이터 file이 필요하다.

MySQL 5.0이전에는 수치지도상 표현되는 개체를 저장 할 수 없었다.  이후로는 지도 map파일(ngi, shp, dxf...) 뿐 아니라 이미지/동영상등 모든 파일이 저장가능해지면서 longblob, geometry 지원데이터 타입이늘어나 가능해졌다. 

이 geometry는 하천테이블, 건물 테이블 등 개체 단위로 각 행에 저장되어 개체 하나하나를 행 데이터로 취급해 저장한다. 

 

 

 

테이블 생성 및 조회

ST_GeomFromText로 공간데이터를 입력할 수 있고 이 개체를 활용해 하천길이 > 10km등의 조건식도 가능하게 되었다. 

-- 테이블 생성
CREATE TABLE river (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    geom LINESTRING NOT NULL,
    SPATIAL INDEX(geom)
);

-- 데이터 입력
INSERT INTO river (name, geom)
VALUES
(
    '한강',
    ST_GeomFromText('LINESTRING(0 0, 3 4, 6 8)')
),
(
    '낙동강',
    ST_GeomFromText('LINESTRING(0 0, 6 8, 12 16)')
),
(
    '금강',
    ST_GeomFromText('LINESTRING(0 0, 2 2)')
);

-- 전체 하천 조회
SELECT
    id,
    name,
    ST_AsText(geom) AS geometry,
    ST_Length(geom) AS length
FROM river;

-- 길이가 10 이상인 하천 조회
SELECT
    id,
    name,
    ROUND(ST_Length(geom), 2) AS length
FROM river
WHERE ST_Length(geom) > 10;

-- 공간 객체 타입 확인
SELECT
    name,
    ST_GeometryType(geom) AS geom_type
FROM river;

 

 

 

Workbench에서 이것을 시각화해 확인 할수 있다. 

select * from 테이블; 했을때 이진데이터 형식으로 들어가기에잘 보이지않는데,

원하는 gemonetry에서 마우스 오른쪽 - open value in viewer하면 입력한 형태로 볼 수 있고

erd 를 봤던것처럼 result메뉴에서 스크롤해 spatial view를 선택해 projection을 equirectangular로 선택해 확인 할 수있다. 

* layer에서 grid - 마우슨 오른쪽 - fill polygons 해제시 격자를 안보이게 할 수 있다. 

 

다른 result메뉴로는 form editor가 있는데 지정한 하천, 빌딩등의 하나하나 그림과 내용이 표현된다. 

 

하천과 빌딩 등을 모두 표현하고싶다면 union all

SELECT
    id,
    name,
    'RIVER' AS feature_type,
    ST_GeometryType(geom) AS geom_type,
    ST_AsText(geom) AS geometry
FROM river

UNION ALL

SELECT
    id,
    name,
    'BUILDING' AS feature_type,
    ST_GeometryType(geom) AS geom_type,
    ST_AsText(geom) AS geometry
FROM building;

 

 

Geometry 데이터 형식 함수 정리.

-- WKT 문자열을 Geometry 객체로 생성
SELECT ST_GeomFromText('POINT(127 37)');

-- Geometry를 WKT 문자열로 출력
SELECT ST_AsText(ST_GeomFromText('POINT(127 37)'));

-- 선(LineString)의 길이 계산
SELECT ST_Length(ST_GeomFromText('LINESTRING(0 0,3 4)'));

-- 폴리곤의 면적 계산
SELECT ST_Area(ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'));

-- 두 객체가 교차하는지 확인 (1=true, 0=false)
SELECT ST_Intersects(ST_GeomFromText('LINESTRING(0 0,10 10)'), ST_GeomFromText('LINESTRING(0 10,10 0)'));

-- 점 주변 버퍼(완충영역) 생성
SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT(0 0)'), 10));

-- 폴리곤이 점을 포함하는지 확인
SELECT ST_Contains(ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'), ST_GeomFromText('POINT(5 5)'));

-- 두 점 사이 거리 계산
SELECT ST_Distance(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('POINT(3 4)'));

-- 두 Geometry를 합집합으로 결합
SELECT ST_AsText(ST_Union(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('POINT(1 1)')));

-- 두 Geometry의 교집합 계산
SELECT ST_AsText(ST_Intersection(ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'), ST_GeomFromText('POLYGON((5 5,5 15,15 15,15 5,5 5))')));

 

 

 

 

Chapter 15 파이썬과 MySQL 응용 프로그래밍

 

파이썬 설치 및 기본문법 요약

https://standout.tistory.com/1683

혼자 공부하는 파이썬 - Chapter 01 파이썬 시작하기

https://standout.tistory.com/1682

혼자 공부하는 파이썬 - Chapter 02 자료형

https://standout.tistory.com/1701

혼자 공부하는 파이썬 - Chapter 03 조건문

https://standout.tistory.com/1702

혼자 공부하는 파이썬 - Chapter 04 반복문

https://standout.tistory.com/1725

혼자 공부하는 파이썬 - Chapter 05 함수

 

 

 

 

window 프로그래밍

tkinter 파이썬에서 GUI 관련 모듈을 제공해주는 표준 윈도 라이브러리 윈도창이 필요할때 쓴다. 

TK()는 기본이 되는 윈도를 반환해 루트윈도, 베이스 윈도라 부른다. 

window.mainloop() 이벤트 루프로 보통 마지막에 써줌. 호출부분

window, window.title, window.geometry, window.resizable() 창설정

label 문자를 표현할 수 있는 위젯, font 폰트, fg 글자색, bg 배경색 widht height 지원. anchor 위젯이 어디위치에 자리잡을 것인가. n, ne, e, se, s, sw, w, nw, center 등이 가능. 기본값 center.

button(부모윈도, 옵션) command 함수명을 지정해 클릭했을때 원하는 함수를 티리거한다.

side 는 위젯의 정렬, padx, pady는 여백

frame은 화면의 구역을 나눌떄, entry는 입력상자, listbox는 목록을 표현한다.

messagebox.showinfo() 기본적인 메세지창, tkinter.simpledialog 임포트후 askstring, askinteger 등의 숫자나 문자를 입력받을 수도 있다. 

Tk()는 그림을 그릴 수 없어 Canvas()를 활용할 수 있다.

통합예시코드로 이해해보자. 

from tkinter import *
from tkinter import messagebox, simpledialog

# 창 생성
window = Tk()
window.title("Tkinter 예제")
window.geometry("400x350")
window.resizable(False, False)

# 프레임
frame = Frame(window)
frame.pack(pady=10)

# 라벨
label = Label(frame, text="이름 입력")
label.pack(side=LEFT)

# 엔트리
entry = Entry(frame, width=15)
entry.pack(side=LEFT)

# 리스트박스
listbox = Listbox(window, width=30, height=5)
listbox.pack(pady=10)

# 캔버스
canvas = Canvas(window, width=150, height=80, bg="lightyellow")
canvas.pack(pady=10)
canvas.create_rectangle(20, 20, 100, 60, fill="skyblue")
canvas.create_text(60, 40, text="Canvas")

# 버튼 함수
def add_name():
    name = entry.get()

    if name:
        listbox.insert(END, name)
        messagebox.showinfo("알림", f"{name} 추가됨")

def ask_info():
    age = simpledialog.askinteger("나이", "나이를 입력하세요")
    hobby = simpledialog.askstring("취미", "취미를 입력하세요")

    if age and hobby:
        messagebox.showinfo(
            "결과",
            f"나이: {age}\n취미: {hobby}"
        )

# 버튼
btn1 = Button(window, text="추가", command=add_name)
btn1.pack(pady=5)

btn2 = Button(window, text="정보입력", command=ask_info)
btn2.pack(pady=5)

# 이벤트 루프
window.mainloop()

 

 

 

파이썬과 MySQL의 활용

pymysql.connect() 데이터베이스연결

cursor sql문을 실행하거나 결과를 돌려받는 통로.

만일 파이썬과 함께한다면 앞서 살펴본 GIS를 더 예쁘게, 풍성하게 웹에서 표현할수있을것이다. 

import sqlite3
from tkinter import *

# -----------------------------
# DB 연결 + 커서 생성
# -----------------------------
conn = sqlite3.connect("gis.db")
cur = conn.cursor()

# -----------------------------
# 테이블 생성 (POINT 저장)
# -----------------------------
cur.execute("""
CREATE TABLE IF NOT EXISTS place (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    x INTEGER,
    y INTEGER
)
""")

# -----------------------------
# 데이터 입력
# -----------------------------
cur.execute("INSERT INTO place (name, x, y) VALUES (?, ?, ?)", ("학교", 50, 60))
cur.execute("INSERT INTO place (name, x, y) VALUES (?, ?, ?)", ("공원", 120, 80))

# commit (저장)
conn.commit()

# -----------------------------
# 데이터 조회 (fetchone 사용)
# -----------------------------
cur.execute("SELECT name, x, y FROM place LIMIT 1")
row = cur.fetchone()

# -----------------------------
# GUI (Canvas GIS 표현)
# -----------------------------
window = Tk()
window.title("GIS Canvas 예제")
window.geometry("300x300")

canvas = Canvas(window, width=250, height=200, bg="white")
canvas.pack()

# 전체 데이터 다시 읽기
cur.execute("SELECT name, x, y FROM place")
rows = cur.fetchall()

# 점 그리기 (GIS 포인트)
for r in rows:
    name, x, y = r
    canvas.create_oval(x, y, x+5, y+5, fill="red")
    canvas.create_text(x+20, y, text=name)

# fetchone 결과 출력
Label(window, text=f"첫 데이터: {row}").pack()

# -----------------------------
# DB 종료
# -----------------------------
conn.close()

window.mainloop()

 

 

 

 

Chapter 1