1. OGR 라이브러리

• OGR의 3가지 Component
  • OGR 배치 명령 : 벡터 데이터 정보 요약 및 변환
  • 파이썬 스크립트 : 미리 개발해 놓은 GIS 분석 기능
  • OGR 라이브러리 : 파이썬에서 사용할 수 있는 OGR 라이브러리

2. OGR 배치 명령

• OGR은 벡터데이터 요약 정보 제공, 변환 등을 위한 배치 명령을 제공
• 대표적으로 아래와 같은 배치 명령 존재
  • ogrinfo : 벡터 데이터 요약정보 확인
  • ogr2ogr : 벡터 파일을 다른 포맷으로 변경
  • ogrtindex : 벡터 타일 생성
  • ogr2vrt : 벡터 타일 생성(orgtindex 보다 광범위하게 사용됨)
• 터미널에서 명령을 수행함
  • Jupyter notebook에서 명령어 앞에 !를 붙이면 터미널 명령 수행 가능

2.1 ogrinfo

• 파일 입출력 가능 포맷 조회

!ogrinfo --formats

Supported Formats:
...
  ESRI Shapefile -vector- (rw+v): ESRI Shapefile
  KML -vector- (rw+v): Keyhole Markup Language (KML)
  GeoJSON -vector- (rw+v): GeoJSON
...

• 파일의 요약 정보 보기
  • data 폴더의 link 파일 요약 정보 보기
  • -so : 요약 정보만 보겠다는 옵션
  • -so가 없으면 shp의 모든 정보를 표시함

!ogrinfo -so "data" MOCT_LINK

INFO: Open of `data'
      using driver `ESRI Shapefile' successful.

Layer name: MOCT_LINK
Metadata:
  DBF_DATE_LAST_UPDATE=2021-03-30
Geometry: Line String
Feature Count: 528232
Extent: (101766.537600, 67516.493200) - (546275.776100, 665746.517600)
Layer SRS WKT:
PROJCS["ITRF2000_Central_Belt_60",
    GEOGCS["GCS_ITRF_2000",
        DATUM["International_Terrestrial_Reference_Frame_2000",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            AUTHORITY["EPSG","6656"]],
        PRIMEM["Greenwich",0],
        UNIT["Degree",0.0174532925199433]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",38],
    PARAMETER["central_meridian",127],
    PARAMETER["scale_factor",1],
    PARAMETER["false_easting",200000],
    PARAMETER["false_northing",600000],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["Easting",EAST],
    AXIS["Northing",NORTH]]
Data axis to CRS axis mapping: 1,2
LINK_ID: String (10.0)
F_NODE: String (10.0)
T_NODE: String (10.0)
LANES: Integer (4.0)
ROAD_RANK: String (3.0)
ROAD_TYPE: String (3.0)
ROAD_NO: String (5.0)
ROAD_NAME: String (30.0)
ROAD_USE: String (1.0)
MULTI_LINK: String (1.0)
CONNECT: String (3.0)
MAX_SPD: Integer (4.0)
REST_VEH: String (3.0)
REST_W: Integer (4.0)
REST_H: Integer (4.0)
LENGTH: Real (18.12)
REMARK: String (30.0)

• KML 파일 읽기
  • 샘플 데이터 : 다운로드

!ogrinfo "KML_Sample.kml" -summary

INFO: Open of `KML_Sample.kml'
      using driver `KML' successful.
1: Placemarks (3D Point)
2: Highlighted Icon (3D Point)
3: Paths (3D Line String)
4: Google Campus (3D Polygon)
5: Extruded Polygon (3D Polygon)
6: Absolute and Relative (3D Polygon)

2.2 ogr2ogr

• shp 파일을 GeoJson파일로 변환

!ogr2ogr -f "GeoJson" "output.json" "data/MOCT_LINK.shp"

3. 파이썬 스크립트

• GDAL 설치 경로에 GIS 분석에 사용할 수 있는 스크립트 제공
  • 경로 예시(anaconda일 때) : C:\Users\82103\Anaconda3\envs\geospatial\Lib\site-packages\GDAL-3.0.2-py3.6-win-amd64.egg-info\scripts
• scripts 예시
  • ogrmerge.py : shp파일을 merge 하여 다른 파일로 변환함
• 사용 방법
  • 터미널 또는 Jupyter notebook에서 사용 방식에 따라 명령 수행

#경로 이동
cd "C:\Users\82103\Anaconda3\envs\geospatial\Lib\site-packages\GDAL-3.0.2-py3.6-win-amd64.egg-info\scripts"

#터미널에서 수행 할 때
ogrmerge.py -f GPKG -o merged.gpkg "MOCT_LINK.shp"

# Jupyter Note Book에서 수행할 때
%run ogrmerge.py -f GPKG -o merged.gpkg "MOCT_LINK.shp"

• 참고 : %run
  • Jupyter notebook의 매직명령
  • 외부 스크립트를 Jupyter notebook에서 실행 할 때 사용

4. OGR 라이브러리

• OGR 라이브러리는 2개의 주요 모듈로 구성
  • ogr : 벡터 지오메트리를 주로 다룸
  • osr : 투영에 관해 다룸
• OGR 이 제공하는 7개의 클래스
  • Geometry : 기하학 좌표 연산/변환 제공, Spatial Reference System을 포함
  • Spatial Reference : 투영과 좌표계 관련 기능 정의
  • Feature : Geometry와 속성정보를 가진 Feature를 정의(Polygon 등의 객체)
  • Feature Class Definition : Feature의 스키마 정보를 정의
  • Layer : Feature들을 모아놓은 Layer 정의
  • Dataset : OGRLayer 객체를 하나 이상 포함하는 File 또는 Database을 표현
  • Drivers : 포맷 변환을 정의하며, 모든 Drivers는 GDALDriverManager에서 관리함

4.1 OGR로 객체 생성하기

• OGR로 Point, Line, Polygon과 같은 벡터 Geometry 생성 가능
• Polygon 생성 예제

from osgeo import ogr
r = ogr.Geometry(ogr.wkbLinearRing)
r.AddPoint(1,1)
r.AddPoint(5,1)
r.AddPoint(5,5)
r.AddPoint(1,5)
r.AddPoint(1,1)
poly = ogr.Geometry(ogr.wkbPolygon)
poly.AddGeometry(r)
print(poly.ExportToWkt())

POLYGON ((1 1 0,5 1 0,5 5 0,1 5 0,1 1 0))

4.2 GeoJSON으로 부터 객체 생성하기

• GeoJSON 구조로 부터 객체 생성 가능
• GeoJSON으로 부터 Polygon 생성 예제

from osgeo import ogr
geojson = """{"type":"Polygon","coordinates":[[[1,1],[5,1],[5,5],[1,5],[1,1]]]}"""
polygon = ogr.CreateGeometryFromJson(geojson)
print(polygon)

POLYGON ((1 1,5 1,5 5,1 5,1 1))

4.3 Geometry 연산 수행

• Polygon Geometry 연산 수행

# 1. 면적 구하기
print("폴리곤 면적은 ", polygon.Area(), "입니다.")

# 2. 중심점 구하기
cen = polygon.Centroid()
print(cen)

# 3. 경계 구하기
boundary = polygon.GetBoundary()
print(boundary)

# 4. Point 가 Polygon 내부에 존재하는지 확인
point = ogr.Geometry(ogr.wkbPoint)
point.AddPoint(10, 10)
polygon.Contains(point)

# 5.  Buffer 구하기
buffer = polygon.Buffer(2)

폴리곤 면적은  16.0 입니다.
POINT (3 3)
LINESTRING (1 1,5 1,5 5,1 5,1 1)
False

4.4 Shape 파일에 Polygon Data 쓰기

• 아래 순서로 진행
  • 좌표계 설정
  • Shape file 생성
  • Layer 생성
  • feature 생성 및 Geometry 입력
  • Layer에 Feature 입력

import osgeo.ogr, osgeo.osr

# 1. 좌표계 설정
spatialReference = osgeo.osr.SpatialReference()
spatialReference.ImportFromProj4('+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs')

# 2. ShapeFile 생성
driver = osgeo.ogr.GetDriverByName('ESRI ShapeFile')
shapeData = driver.CreateDataSource('polygon.shp')

# 3. layer 생성
layer = shapeData.CreateLayer('polygon_layer', spatialReference, osgeo.ogr.wkbPolygon)
layerDefinition = layer.GetLayerDefn()

# 4. Feature 생성 및 Geometry 입력
featureIndex = 0
feature = osgeo.ogr.Feature(layerDefinition)
feature.SetGeometry(polygon)
feature.SetFID(featureIndex)

# 5. Layer에 Feature 입력
layer.CreateFeature(feature)

# 6. 생성 확인
!ogrinfo polygon.shp

INFO: Open of `polygon.shp'
      using driver `ESRI Shapefile' successful.
1: polygon (Polygon)

4.5 feature 다루기

• Shape 파일을 읽어 특정 필드명 출력

from osgeo import ogr
import os
shapefile = r'data/node.shp'
driver = ogr.GetDriverByName("ESRI Shapefile")
dataSource = driver.Open(shapefile,0)
layer = dataSource.GetLayer()

# 데이터가 많아서 Sample로 5개 필드만 출력
idx = 0
for feature in layer:
    print(idx, ", ", feature.GetField("NODE_NAME"))
    idx += 1
    if(idx == 5):
        break

0 ,  하계5,6단지앞교차로
1 ,  공릉터널남측
2 ,  미성아파트5동앞
3 ,  유토피아빌딩
4 ,  청구빌라4단지401동

• Shape 파일의 모든 필드 이름 나열하기

from osgeo import ogr
source = ogr.Open(r'data/MOCT_LINK.shp')
layer = source.GetLayer()
schema = []
ldefn = layer.GetLayerDefn()
for n in range(ldefn.GetFieldCount()):
    fdefn = ldefn.GetFieldDefn(n)
    schema.append(fdefn.name)
print(schema)

['LINK_ID', 'F_NODE', 'T_NODE', 'LANES', 'ROAD_RANK', 'ROAD_TYPE', 'ROAD_NO', 'ROAD_NAME', 'ROAD_USE', 'MULTI_LINK', 'CONNECT', 'MAX_SPD', 'REST_VEH', 'REST_W', 'REST_H', 'LENGTH', 'REMARK']

• Feature 개수 구하기

from osgeo import ogr
import os
shapefile = r'data/MOCT_LINK.shp'
driver = ogr.GetDriverByName("ESRI Shapefile")
dataSource = driver.Open(shapefile, 0)
layer = dataSource.GetLayer()
featureCount = layer.GetFeatureCount()
print("count = ", featureCount)

count =  528232

4.6 좌표계 다루기

• CRS 정보 조회하기
  • Layer로부터 구하기
  • Geometry로부터 구하기

# 1) Layer로부터 구하기
spatialRef = layer.GetSpatialRef()
print(spatialRef)

PROJCS["ITRF2000_Central_Belt_60",
    GEOGCS["GCS_ITRF_2000",
        DATUM["International_Terrestrial_Reference_Frame_2000",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            AUTHORITY["EPSG","6656"]],
        PRIMEM["Greenwich",0],
        UNIT["Degree",0.0174532925199433]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",38],
    PARAMETER["central_meridian",127],
    PARAMETER["scale_factor",1],
    PARAMETER["false_easting",200000],
    PARAMETER["false_northing",600000],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["Easting",EAST],
    AXIS["Northing",NORTH]]

# 2) Geometry로부터 구하기
feature = layer.GetNextFeature()
geom = feature.GetGeometryRef()
spatialRef2 = geom.GetSpatialReference()
print(spatialRef2)

PROJCS["ITRF2000_Central_Belt_60",
    GEOGCS["GCS_ITRF_2000",
        DATUM["International_Terrestrial_Reference_Frame_2000",
            SPHEROID["GRS 1980",6378137,298.257222101,
                AUTHORITY["EPSG","7019"]],
            AUTHORITY["EPSG","6656"]],
        PRIMEM["Greenwich",0],
        UNIT["Degree",0.0174532925199433]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",38],
    PARAMETER["central_meridian",127],
    PARAMETER["scale_factor",1],
    PARAMETER["false_easting",200000],
    PARAMETER["false_northing",600000],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["Easting",EAST],
    AXIS["Northing",NORTH]]

참고

• 파이썬을 활용한 지리공간 분석 마스터하기
• GDAL Vector Data Model
• 예제는 국가표준노드링크로 진행

1. 분석 함수(Analytic Function)

• 분석 함수란?

  • 테이블의 행을 특정 그룹별로 그루핑하여 편리하게 함수를 적용하는 것

• Sample 데이터 생성 SQL

-- 1. PRODUCT_GROUP 테이블 생성
CREATE TABLE PRODUCT_GROUP (
GROUP_ID SERIAL PRIMARY KEY,
GROUP_NAME VARCHAR (255) NOT NULL
);

-- 1.1 PRODUCT_GROUP 데이터 삽입
INSERT INTO PRODUCT_GROUP (GROUP_NAME)
VALUES
('Smartphone')
, ('Laptop')
, ('Tablet');

SELECT * FROM PRODUCT_GROUP

-- 2. PRODUCT 테이블 생성
CREATE TABLE PRODUCT (
PRODUCT_ID SERIAL PRIMARY KEY
, PRODUCT_NAME VARCHAR (255) NOT NULL
, PRICE DECIMAL (11, 2)
, GROUP_ID INT NOT NULL
, FOREIGN KEY (GROUP_ID)
REFERENCES PRODUCT_GROUP (GROUP_ID)
);

-- 2.1 PRODUCT 데이터 삽입
INSERT INTO PRODUCT (PRODUCT_NAME,
GROUP_ID,PRICE)
VALUES
('Microsoft Lumia', 1, 200)
, ('HTC One', 1, 400)
, ('Nexus', 1, 500)
, ('iPhone', 1, 900)
, ('HP Elite', 2, 1200)
, ('Lenovo Thinkpad', 2, 700)
, ('Sony VAIO', 2, 700)
, ('Dell Vostro', 2, 800)
, ('iPad', 3, 700)
, ('Kindle Fire', 3, 150)
, ('Samsung Galaxy Tab', 3, 200);

SELECT * FROM PRODUCT

2. OVER절

• OVER절?
  • GROUP BY의 한계를 개선하기 위해 나온 함수
  • 집계 함수의 결과를 테이블에 바로 보여줌

SELECT *
      , COUNT(*) OVER()
FROM PRODUCT

3. OVER ... PARTITION BY 절

• PARTITION BY 절?
  • GROUP BY와 비슷한 기능으로 OVER절, 분석함수와 같이 사용
  • 특정 칼럼으로 그룹핑 하여 앞의 집계 함수를 그룹별로 적용

SELECT
    A.PRODUCT_NAME
    , A.PRICE
    , B.GROUP_NAME
    , AVG (A.PRICE) OVER (PARTITION BY B.GROUP_NAME)
FROM
PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

4. ROW _NUMBER, RANK, DENSE_RANK 함수

• 그룹에서 특정 컬럼의 순위를 구하는 함수

  함수명	내용
  ROW_NUMBER	- 같은 순위면 중복 상관 없이 순서대로 순위 구함 - EX) 10,20,20,30 일 때 -> 순위 : 1,2,3,4
  RANK	- 같은 순위면 다음 순위로 건너뜀 - EX) 10,20,20,30 일 때 -> 순위 : 1,2,2,4
  DENSE_RANK	- 같은 순위면 다음 순위로 건너뛰지 않음 -EX) 10,20,20,30 일 때 -> 순위 : 1,1,2,3

• ROW_NUMBER

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , ROW_NUMBER () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• RANK

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , RANK () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• DENSE_RANK

SELECT
    A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
    , DENSE_RANK () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

5. FIRST _VALUE , LAST_VALUE 함수

• 그룹안에서 특정컬럼의 첫 번째 값 또는 마지막 값을 구하는 함수

• FIRST_VALUE(컬럼명)

  • 그룹안에서 컬럼명의 첫 번째 값을 구함

SELECT
  A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
  , FIRST_VALUE (A.PRICE) over (
      PARTITION BY B.GROUP_NAME ORDER BY A.PRICE
      ) AS LOWEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• LAST_VALUE(컬럼명)

  • 그룹안에서 컬럼명의 마지막 값을 구함

  • 범위 지정 옵션 : 옵션 범위 기준 마지막값을 구함

    옵션	내용
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	첫 번째 로우부터 마지막 로우
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	첫 번째 로우부터 현재 로우

SELECT
  A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
  , LAST_VALUE (A.PRICE) OVER(
    PARTITION BY B.GROUP_NAME ORDER BY A.PRICE
    RANGE BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING
  ) AS HIGHEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

6. LAG, LEAD 함수

• 그룹안에서 특정 컬럼의 이전 행 또는 다음 행 값을 구함

• LAG

SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
,             LAG (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS PREV_PRICE
, A.PRICE - LAG (A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS CUR_PREV_DIFF
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

• LEAD

SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
,             LEAD (A.PRICE, 1) OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ) AS NEXT_PRICE
, A.PRICE - LEAD (A.PRICE, 1) OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE )AS CUR_NEXT_DIFF
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
ON (A.GROUP_ID = B.GROUP_ID);

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. Group by절

• Group by 란?
  • 특정 컬럼값을 기준으로 그룹화함
  • 각 그룹에 대한 합계, 평균, 개수 등의 함수 적용 가능
  • 예) 이름으로 그룹핑 및 각 그룹의 평균 계산 후 평균으로 내림차순 정렬

SELECT NAME, AVG(SCORE) AS AVG_SCORE
FROM SCORELIST GROUP BY NAME
ORDER BY AVG_SCORE DESC

2. Having절

• Having 절이란?
  • Group by의 결과를 특정조건으로 필터링하는 기능
  • 예시) 평균이 94점 이상인 이름(그룹)만 출력

SELECT NAME, AVG(SCORE) AS AVG_SCORE
FROM SCORELIST GROUP BY NAME
having AVG(SCORE)  >= 94

3. GROUPING SETS절

• Grouping sets절이란?

  • 여러 칼럼에 대한 여러 개의 집계를 한번에 출력할 때 사용

• Grouping sets의 필요성

  • 만약 과목별 평균과 이름별 평균, 전체 평균 정보를 한번에 보고 싶다면...
  • 아래와 같이 하나씩 Group by 한 결과를 Union All 해서 출력 가능

-- 1. 전체 평균 계산
SELECT NULL,   NULL, AVG(SCORE) FROM SCORELIST
-- 2. 과목, 이름별 평균 계산
UNION ALL
SELECT SUBJECT, NAME, AVG(SCORE) FROM SCORELIST GROUP BY SUBJECT, NAME
-- 3. 과목별 평균 계산
UNION ALL
SELECT SUBJECT, NULL, AVG(SCORE) FROM SCORELIST GROUP BY SUBJECT
-- 4. 이름별 평균 계산
UNION ALL
SELECT NULL,    NAME, AVG(SCORE) FROM SCORELIST GROUP BY NAME

• 상기 예제를 Grouping sets 절로 구현
  • 수행 속도가 더 빠르고 간단함

SELECT SUBJECT, NAME, AVG(SCORE) FROM SCORELIST
GROUP BY
GROUPING SETS(
    (SUBJECT, NAME),
    SUBJECT,
    NAME,
    ()
)

4. ROLL UP 절

• ROLL UP 이란?
  • 지정된 GROUPING 컬럼의 소계를 생성함
• ROLL UP의 원리
  • 인자로 들어온 컬럼을 오른쪽부터 하나씩 빼면서 Group 생성
• 예제 1, 2, 3 비교(아래 그림 참고)
  • SUBJECT, NAME 으로 그룹핑 하여 평균 출력하는 부분은 동일함
    

    

• 예제 1) GROUP BY ROLLUP( SUBJECT, NAME )
  • SUBJECT, NAME에서 오른쪽인 NAME이 제외된 SUBJECT 그룹으로 평균 출력
  • 그 다음으로 SUBJECT가 제외되어 아무것도 없고 GROUP BY절에도 아무것도 없으므로 전체 평균 출력
• 예제 2) GROUP BY SUBJECT, ROLLUP( NAME )
  • ROLLUP에 NAME뿐이어서 NAME이 제외되면 아무것도 없으므로 GROUP BY에 있는 SUBJECT 그룹으로 평균 출력
• 예제 3) GROUP BY ROLLUP((SUBJECT, NAME))
  • ROLLUP에 SUBJECT, NAME이 괄호안에 하나의 그룹이고, 제외 되면 아무 것도 없으므로 전체 평균 출력

5. CUBE 절

• CUBE란?

  • 지정된 GROUPING 컬럼의 다차원 소계를 생성함

• ROLL UP과의 차이

  • ROLL UP은 오른쪽부터 순서대로 하나씩 뺌

  • CUBE는 방향 상관없이 하나씩 빼며 모든 가능한 조합의 Group 생성

  • CUBE와 ROLLUP의 그룹 조합 차이

    CUBE(C1, C2)	ROLLUP(C1,C2)
    C1, C2	C1, C2
    C1	C1
    C2	X
    ()	()

• 예제 1, 2, 3 비교(아래 그림 참고)

  • SUBJECT, NAME 으로 그룹핑 하여 평균 출력하는 부분은 동일함
    

    

• 예제 1) GROUP BY CUBE( SUBJECT, NAME )

  • SUBJECT, NAME에서 NAME이 제외된 SUBJECT 그룹으로 평균 출력
  • SUBJECT, NAME에서 SUBJECT가 제외된 NAME 그룹으로 평균 출력
  • 그 다음으로 모두 제외되어 아무것도 없고 GROUP BY절에도 아무것도 없으므로 전체 평균 출력

• 예제 2) GROUP BY SUBJECT, CUBE( NAME )

  • CUBE에 NAME뿐이어서 NAME이 제외되면 아무것도 없으므로 GROUP BY에 있는 SUBJECT 그룹으로 평균 출력

• 예제 3) GROUP BY CUBE((SUBJECT, NAME))

  • CUBE에 SUBJECT, NAME이 괄호안에 하나의 그룹이고, 제외 되면 아무 것도 없으므로 전체 평균 출력

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. Join의 종류

• Join이란?
  • 두 테이블에 공통으로 있는 칼럼을 이용해 연결 시키는 것

2. Join의 문법

3. Join 예시

• INNER JOIN

SELECT * FROM BASKET_A BA
INNER JOIN BASKET_B BB
ON BA.FRUIT = BB.FRUIT

• LEFT OUTER JOIN

SELECT * FROM BASKET_A BA
LEFT OUTER JOIN BASKET_B BB
ON BA.FRUIT = BB.FRUIT

• RIGHT OUTER JOIN

SELECT * FROM BASKET_A BA
RIGHT OUTER JOIN BASKET_B BB
ON BA.FRUIT = BB.FRUIT

• FULL OUTER JOIN

SELECT * FROM BASKET_A BA
FULL OUTER JOIN BASKET_B BB
ON BA.FRUIT = BB.FRUIT

• SELF JOIN

SELECT A.EMPLOYEE_ID AS ID, A.MANAGER_ID AS M_ID
    ,B.EMPLOYEE_ID AS ID,  B.MANAGER_ID AS M_ID
FROM EMPLOYEE A
INNER JOIN EMPLOYEE B
ON A.EMPLOYEE_ID = B.MANAGER_ID

• CROSS JOIN

SELECT * FROM CROSS_T1 A
CROSS JOIN CROSS_T2 B

• NATURAL JOIN

SELECT CATEGORY_ID AS C_ID, PRODUCT_ID AS P_ID
      , CATEGORY_NAME AS C_NAME ,PRODUCT_NAME AS P_NAME
FROM PRODUCTS NATURAL JOIN CATEGORIES

# 참고

​

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. 데이터 조회

• 데이터 조회
  • SELECT 컬럼명 FROM 테이블명

SELECT * FROM CUSTOMER;          -- 모든 컬럼 조회
SELECT FIRST_NAME FROM CUSTOMER; -- 특정 컬럼 조회

• 데이터 정렬
  • ORDER BY 칼럼명 ASC => 정렬하기
    • ASC : 오름차순 정렬(Default)
    • DESC : 내림차순 정렬

SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER
ORDER BY FIRST_NAME ASC,
    LAST_NAME DESC

• 중복 제외값 조회
  • SELECT DISTINCT 칼럼명 FROM 테이블명
    • 테이블 내에 존재하는 칼럼명을 중복 제거 후 1개씩만 출력
  • SELECT DISTINCT ON(칼럼명1) 칼럼명1, 칼럼명2 FROM 테이블명 ORDER BY 칼럼명1, 칼럼명2 DESC
    • 칼럼명1 기준으로 중복 제고 후 칼럼명2는 DESC 정렬된 맨 위 1개의 값만 보여줌

2. 데이터 필터링

• 조건으로 필터링
  • SELECT 칼럼명 FROM 테이블명 WHERE 조건
    • WHERE절 이하 조건에 따라 테이블 조회

SELECT FIRST_NNAME FROM CUSTOMER
WHERE FIRST_NAME='Jamie'

• 출력 개수로 필터링
  • LIMIT
    • SELECT * FROM 테이블명 LIMIT N
      • N개행만 출력
    • SELECT * FROM 테이블명 LIMIT N OFFSET M
      • M+1번째 행부터 N개 출력
  • FETCH
    • SELECT * FROM 테이블명 FETCH FIRST N ROW ONLY
      • N개행만 출력
      • N을 입력하지 않으면 1개 행만 출력
    • SELECT * FROM 테이블명 OFFSET M ROWS FETCH FIRST N ROW ONLY
      • M+1번째 행부터 N개 출력

-- LIMIT
SELECT * FROM CUSTOMER LIMIT 5          -- 5개 행만 출력
SELECT * FROM CUSTOMER LIMIT 4 OFFSET 3 -- 3+1번째 행부터 4개행 출력

-- FETCH
SELECT * FROM CUSTOMER FETCH FIRST 5 ROW ONLY               -- 5개 행만 출력
SELECT * FROM CUSTOMER OFFSET 3 ROWS FETCH FIRST 4 ROW ONLY -- 3+1번째 행부터 4개행 출력

• IN 연산자
  • SELECT * FROM 테이블명 WHERE 칼럼명 IN (V1, V2)
    • 칼럼명의 값이 V1, V2인 것만 조회
  • SELECT * FROM 테이블명 WHERE CUSTOMER_ID NOT IN (V1, V2)
    • 칼럼명의 값이 V1, V2가 아닌 것만 조회

SELECT * FROM CUSTOMER WHERE CUSTOMER_ID IN (1,2) -- ID가 1,2인 대상 추출
SELECT * FROM CUSTOMER WHERE CUSTOMER_ID NOT IN (1,2) -- ID가 1,2가 아닌 대상 추출

• BETWEEN 연산자
  • SELECT * FROM 테이블명 WHERE 칼럼명 BETWEEN V1 AND V2
    • 칼럼명의 값이 V1 이상이면서 V2 이하인 것만 조회
  • SELECT * FROM 테이블명 WHERE 칼럼명 NOT BETWEEN V1 AND V2
    • 칼럼명의 값이 V1 미만이면서 V2 초과인 것만 조회
• LIKE 연산자
  • SELECT * FROM 테이블명 WHERE 칼럼명 LIKE 특정패턴
    • 칼럼명이 특정 패턴을 갖는 것만 조회
      • 특정패턴이 % : 길이와 상관없이 아무 문자가 있는 패턴
      • 특정패턴이 _ : 한 개의 문자가 아무 문자가 있는 패턴

-- 이름이 KIM으로 시작하는 고객 추출
SELECT * FROM CUSTOMER
WHERE NAME LIKE 'KIM%';

-- 이름이 3자리이고 가운에 문자가 'A' 인 고객 추출
SELECT * FROM CUSTOMER
WHERE NAME LIKE '_A_';

• IS NULL 연산자
  • SELECT * FROM 테이블명 WHERE 칼럼명 IS NULL
    • 칼럼명의 값이 NULL 인 것만 조회
  • SELECT * FROM 테이블명 WHERE 칼럼명 IS NOT NULL
    • 칼럼명의 값이 NULL이 아닌 것만 조회

# 참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)

1. 벡터 데이터 타입

2.래스터 데이터 타입

1. postgresql 설치(postGIS 포함)

• programmerpsy.tistory.com/94

2. 파이썬에서 PostgreSQL 연결하기

• psycopg2 설치하기

conda install -c anaconda psycopg2

3. 데이터 베이스 연결하기

• geospatial DB에 연결 후 points 테이블 생성하는 예제

import psycopg2, pprint

connection = psycopg2.connect(database="geospatial", user="postgres", password="******")
cursor = connection.cursor()

cursor.execute("CREATE TABLE points (id SERIAL PRIMARY KEY, name VARCHAR(255), location GEOMETRY)")
connection.commit()

• psycopg2.connect
  • 데이터베이스명, 사용자ID, PW를 입력하여 DB에 연결
• connection.cursor
  • 데이터베이스와 통신할 수 있는 cursor 생성
• cursor.execute
  • 인자로 입력된 SQL문을 실행
• connection.commit
  • 변경 내용을 DB에 저장

4. 테이블에 데이터 추가

• WKT형태의 좌표를 Geometry로 저장

cursor.execute("INSERT INTO points (name, location) VALUES ('p1',ST_GeomFromText('POINT(100 200)'))")             
cursor.execute("INSERT INTO points (name, location) VALUES ('p1',ST_GeomFromText('POINT(200 400)'))")
cursor.execute("INSERT INTO points (name, location) VALUES ('p1',ST_GeomFromText('POINT(700 300)'))")
cursor.execute("INSERT INTO points (name, location) VALUES ('p1',ST_GeomFromText('POINT(400 300)'))")
cursor.execute("INSERT INTO points (name, location) VALUES ('p1',ST_GeomFromText('POINT(100 400)'))")
cursor.execute("INSERT INTO points (name, location) VALUES ('p1',ST_GeomFromText('POINT(100 300)'))")
connection.commit() 

cursor.execute("SELECT * from points") 
cursor.fetchall() 

• WKT 대신 shapely를 이용해 geometry를 컨트롤 하는 방법
  • shapely의 Point 객체이용
  • Point 객체는 wkt 함수를 이용해 WKT로 변환 가능

from shapely.geometry import Point, MultiPoint
p = [Point(100,200), Point(100,100)]
for pp in p:
    s = ("INSERT INTO points (name, location) VALUES ('p1',ST_GeomFromText('{}'))").format(pp.wkt)
    cursor.execute(s)
connection.commit() 

cursor.execute("SELECT * from points") 
cursor.fetchall() 

• 주피터 노트북에서 이미지 띄우기
  • 좌표 리스트들이 표시됨

MultiPoint(p)

5. 테이블 조회

• points를 조회 하는 예제
  • fetchall() 을 이용하면 select 된 모든 데이터 가져옴
  • fetchone을 이용하면 1개의 데이터만 가져옴

cursor.execute("SELECT * from points") 
data=cursor.fetchall() 
data

[(1, 'p1', '010100000000000000000059400000000000006940'),
 (2, 'p1', '010100000000000000000069400000000000007940'),
 (3, 'p1', '01010000000000000000E085400000000000C07240'),
 (4, 'p1', '010100000000000000000079400000000000C07240'),
 (5, 'p1', '010100000000000000000059400000000000007940'),
 (6, 'p1', '010100000000000000000059400000000000C07240'),
 (8, 'p1', '010100000000000000000059400000000000006940'),
 (9, 'p1', '010100000000000000000059400000000000005940')]

• geomtery가 WKB 형태로 출력
  • shapely를 이용해 WKB-> Point -> WKT 변환 하기

from shapely.wkb import loads
aPoint=loads(data[0][2],hex=True) 
aPoint.wkt

'POINT (100 200)'

6. 좌표계 설정/변경하기

cursor.execute("SELECT UpdateGeometrySRID('points','location',4326)") 
cursor.execute("SELECT Find_SRID('public','points','location')") 
cursor.fetchall()

cursor.execute("SELECT code, ST_AsTexT(ST_Transform(location,3857)) from points") 
cursor.fetchone()

• updateGeomterySRID
  • Geometry에 좌표계 설정함
• Find_SRID
  • Geomtery가 어떤 좌표계 인지 보여줌
• ST_Transform
  • 좌표계를 변경함

7. KNN을 이용한 지정한 점에 가장 가까운 3개 점 얻기

cursor.execute("SELECT name, ST_Distance(location::geography, ST_GeometryFromText('POINT(-106.591838300225 35.1555000000615)')::geography) as d from points ORDER BY location<->ST_GeometryFromText('POINT(-106.591838300225 35.1555000000615)') LIMIT 3") 
cursor.fetchall() 

[('p1', 7120405.75751067),
 ('p1', 16921336.66007583),
 ('p1', 16921336.66007583)]

8. line 데이터 다루기

• line 생성 예제

from shapely.geometry import LineString 
from shapely.geometry import MultiLineString
connection = psycopg2.connect(database="geospatial",user="postgres",password="****")
cursor = connection.cursor() 
cursor.execute("CREATE TABLE lines (id SERIAL PRIMARY KEY, location GEOMETRY)") 
thelines=[] 
thelines.append(LineString([(-106.635585,35.086972),(-106.621294,35.124997)])) 
thelines.append(LineString([(-106.498309,35.140108),(-106.497010,35.069488)])) 
thelines.append(LineString([(-106.663878,35.106459),(-106.586506,35.103979)]))
mls=MultiLineString([((-106.635585,35.086972),(-106.621294,35.124997)),((-106.498309,35.140108),(-106.497010,35.069488)),((-106.663878 ,35.106459),(-106.586506,35.103979))])
for a in thelines:    
    cursor.execute("INSERT INTO lines (location) VALUES (ST_GeomFromText('{}'))".format(a.wkt)) 
    connection.commit() 
cursor.execute("SELECT id, ST_AsTexT(location) from lines") 
data=cursor.fetchall() 
data
connection.commit()

• 두 선 교차 여부 확인 하기
  • ST_Intersects 함수 사용

cursor.execute("SELECT ST_Intersects(l.location::geography,ll.location::geometry) FROM lines l, lines ll WHERE l.id=1 AND ll.id=3") 
cursor.fetchall()

• 두 선을 교차하는 점 확인 하기

cursor.execute("SELECT ST_AsText(ST_Intersection(l.location::geography, ll.location::geometry)) FROM lines l, lines ll WHERE l.id=1 AND ll.id=3") 
cursor.fetchall()

9.Polygon 데이터 다루기

• polygon 생성 예제

  from shapely.geometry import Polygon
  connection = psycopg2.connect(database="pythonspatial",user="postgres", password="postgres") 
  cursor = conectionn.cursor() 
  cursor.execute("CREATE TABLE poly (id SERIAL PRIMARY KEY, location GEOMETRY)") 
  a=Polygon([(-106.936763,35.958191),(-106.944385,35.239293),(-106.452396,35.281908),(-106.407844,35.948708)]) 
  cursor.execute("INSERT INTO poly (location) VALUES (ST_GeomFromText('{}'))".format(a.wkt)) 
  connection.commit()

• 포인트가 폴리곤 내부에 있는지 확인

  • ST_Intersects 또는 ST_Contains 사용

# 참고

• 파이썬을 활용한 지리공간 분석 마스터하기
• WKB, WKT의 이해 및 변환

1. GDAL/OGR

• GDAL 과 OGR 이란?
  • GDAL(Geospatial Data Abstraction Library : 래스터 데이터 처리에 많이 사용하는 라이브러리
  • OGR : 벡터 포맷 데이터 처리에 많이 사용하는 라이브러리
• GDAL 설치 하기
  • 아나콘다 내비게이터를 이용해 설치
    • 가상 환경 생성
    • 생성된 가상환경에 Not installed 설정 -> gdal 검색하여 체크 -> Apply
    • 
  • 아나콘다 프롬프트를 이용해 설치
    • 가상 환경 생성

conda create -n geospatial
activate geospatial
conda install gdal

2. GEOS(Geometry Engine Open Source)

• GEOS란?
  • JTS(Java Topology Suite)의 서브셋과 선택된 기능의 C/C++ 포트
  • JTS의 완전한 기능을 C++로 포함하는 것을 목표
  • QGIS, PostGIS 등 여러 어플리케이션에서 사용됨
  • GDAL과 함께 컴파일해서 OGR의 모든 기능을 제공
• JTS란?
  • Java로 작성된 지리공간 지오메트리 라이브러리
• GEOS 설치하기

conda install -c conda-forge geos

3. Shapely

• Shapely란?
  • 평면 형상의 처리와 분석을 위한 파이썬 패키지
  • GEOS 라이브러리 및 JTS 포트의 기능을 사용함
  • 지오메트리 분석만 다룸(공간 파일을 읽고 쓸 수 있는 기능 없음)
  • 8가지 기본 지오메트리 타입 지원
    • 포인트, 멀티 포인트, 멀티 라인 스트링, 멀티 폴리곤, 폴리곤, 다원형, 지오메트리 컬렉션
  • OGR보다 더 파이썬에 가깝고 직관적인 인터페이스, 최적화가 잘 되어 있음
• Shapely 설치하기

conda install -c scitools shapely

4. Fiona

• Fiona란?
  • OGR API로 데이터 포맷을 읽고 쓰는데 사용
  • OGR 보다 파이썬에 가깝고 신뢰성이 높고 오류 발생률이 낮아 OGR대신 사용
  • 벡터 데이터에 관한 공간 정보를 표현하기 위해 WKT와 WKB라는 두 개의 마크업 언어 사용
  • Shapely와 같은 다른 파이썬 라이브러리와 잘 결합됨
  • 벡터 데이터 복사 시 메모리를 많이 사용(C의 포인터가 아니라 파이썬의 오브젝트 사용하기 때문)
• Fiona 설치

conda install -c conda-forge fiona

5. pyshp

• pyshp(Python Shapefile Library) 란?
  • Shape File을 다루기 위한 파이썬 라이브러리
  • Shape File만 다룬다면 GDAL을 쓰는 것 보다 pyshp을 사용하는 것을 추천
• pyshp 설치

conda install pyshp

6. pyproj

• pyproj 란?
  • 도표 변환과 측지 연산을 수행하는 파이썬 패키지
  • PROJ.4 함수에 파이썬 인터페이스를 제공하기 위한 Cython 래퍼로 파이썬에서 기존 C코드 라이브러리에 직접 접근 가능
• PROJ.4 란?
  • 여러 좌표계 중에서 데이터를 변환하는 투영 라이브러리
  • GDAL과 OGR에서도 이용 가능
  • 인기 있는 이유
    • 다양한 좌표계를 지원
    • Rasterio, GeoPandas 라이브러리 모두 pyproj를 사용하고, 그에 따라 PROJ.4 기능을 사용함
• pyproj 설치

conda install -c conda-forge pyproj

7. Rasterio

• Rasterio 란?
  • 래스터 데이터를 위한 GDAL 및 Numpy 기반 파이썬 라이브러리
  • Web과 App을 위한 온라인 지도 제공업체 맵박스 위성팀에서 제공하는 오픈소스
• Rasterio 설치

conda config --add channels conda-forge
conda install rasterio

8. GeoPandas

• GeoPandas 란?
  • 벡터 데이터 작업을 위한 파이썬 라이브러리
  • pandas와 유사하고 공간 데이터 처리에 유용하게 사용됨
• GeoPandas 설치

conda install -c conda-forge geopandas

# 참고

• 파이썬을 활용한 지리 공간 분석 마스터하기

blog.naver.com/PostView.nhn?blogId=ilsan_ilsan&logNo=221483634939&parentCategoryNo=&categoryNo=63&viewDate=&isShowPopularPosts=true&from=search

0. 문제

어떤 수를 소인수분해 했을 때 그 소인수가 2 또는 3 또는 5로만 이루어진 수를 Ugly Number라고 부릅니다. Ugly Number를 차례대로 적어보면
1, 2, 3, 4, 5, 6, 8, 9, 10, 12, 15, .......입니다. 숫자 1은 Ugly Number의 첫 번째 수로 합 니다. 자연수 N이 주어지면 Ugly Number를 차례로 적을 때 N번째 Ugly Number를 구하는 프로그램을 작성하세요.

1. 입력설명

   • 첫 줄에 자연수 N(3<=N<=1500)이 주어집니다.

2. 출력 설명

   • 첫 줄에 N번째 Ugly Number를 출력하세요.

3. 입력 예제1

   • 10

4. 출력 예제1

   • 12

5. 입력 예제2

   • 1500

6. 출력 예제2

   • 859963392

7. 문제 풀이 핵심

   • vector(또는 배열)에 ugly number를 순서대로 입력 한다.
   • ugly number는 2,3,5를 이용해 작은 값 순서대로 입력한다.

1. 풀이1 : vector에 ugly number를 순서대로 입력한다.

1. p2,p3,p5 변수를 생성하여 vector의 제일 앞을 가리킨다.
   • p2,p3,p5 는 각각 vector를 가리키는 인덱스 값
2. p2,p3,p5 가 각각 가리키는 값을 곱하여 최소값을 구한다.
   • p2V = vector[p2] * 2 : p2가 가리키는 uglynumber에 2를 곱함
   • p3V = vector[p3] * 3 : p3가 가리키는 uglynumber에 3를 곱함
   • p5V = vector[p5] * 2 : p5가 가리키는 uglynumber에 5를 곱함
3. p2V, p3V, p5V의 최소값을 vector에 넣는다.
4. 최소값을 가리키는 인덱스는 1증가한다.
   • 만약 p2V가 가장 작으면, p2 에 +1을 한다.
   • 만약 여러 개(p2V,p3V 등) 이 동시에 가장 작으면 , p2,p3에 +1을 한다.
5. 최종적으로 입력받은 number에 해당하는 vector값을 출력한다.

#include<iostream>
#include<vector>
int p2 = 0, p3 = 0, p5 = 0;
int p2V, p3V, p5V;
std::vector<int> vecData;

// 최소값 구하기
int findMin() {
    int min = 0;
    p2V = vecData[p2] * 2;
    p3V = vecData[p3] * 3;
    p5V = vecData[p5] * 5;

    if (p2V <= p3V) {
        if (p2V <= p5V) {
            min = p2V;
        }
        else {
            min = p5V;
        }
    }
    else {
        if (p3V <= p5V) {
            min = p3V;
        }
        else {
            min = p5V;
        }
    }
    return min;
}

int main() {
    int n;
    scanf_s("%d", &n);
    int data;
    vecData.push_back(1);
    for (int i = 1; i < n; i++) {
        int min = findMin();
        if (min >= p2V) {
            p2++;
            data = p2V;
        }
        if (min >= p3V) {
            p3++;
            data = p3V;
        }
        if (min >= p5V) {
            p5++;
            data = p5V;

        }
        vecData.push_back(data);
    }
    std::cout << vecData.back() << std::endl;
    return 0;
}

6. 예시
   

   

참고

1. it 취업을 위한 알고리즘 문제풀이 (with C/C++) : 코딩테스트 대비