Tag: DB

PostGIS 기초 사용법

PostGIS의 사용법은 그리 어려울게 없다.

그냥 데이터를 사용하기 위한 기반 지식이 많이 필요하다.

기술적으로 알아야 할 것은 geometry 인덱싱, st_프로시저 정도로 많지 않고 지도 데이터, 좌표계 변환 등의 지식을 많이 알아야한다.

설치

http://postgis.net/install/

-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D 
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

 

인덱싱

 

ST_프로시저

많이 사용되는 것만 대강 기억하고 PostGIS doc를 찾아서 쓰면 된다.

https://postgis.net/docs/reference.html

폴리곤 문자열 좌표계로 변환
select st_geomfromtext('POLYGON ((206150.488 464470.391000001,206220.011 464532.584000001,206341.442 464351.384, 206150.488 464470.391000001))', 5174)
좌표계와 겹치는 좌표계 검색
select st_astext(geom) from t_mapdata from st_intersects(st_geomfromtext('POLYGON ((206150.488 464470.391000001,206220.011 464532.584000001,206341.442 464351.384, 206150.488 464470.391000001))', 5174));
지도의 좌표계를 4243좌표로 변환
select st_transform(geom, 4243) from t_mapdata;

 

ST_Intersects – 겹침

ST_Within – 완전포함

ST_Contains – 포함 within하고 비슷한데 확인필요

ST_Area – 넓이 구하기

ST_Buffer – 좀 넓게 잡기

초기데이터 설정

insert into public.spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values(
105188,'USER',105188,'PROJCS["Korea 2000 / East Sea Belt 2010",GEOGCS["Korea 2000",DATUM["Geocentric_datum_of_Korea",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6737"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4737"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",38],PARAMETER["central_meridian",131],PARAMETER["scale_factor",1],PARAMETER["false_easting",200000],PARAMETER["false_northing",600000],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AUTHORITY["EPSG","5188"]]','+proj=tmerc +lat_0=38 +lon_0=131 +k=1 +x_0=200000 +y_0=600000 +ellps=GRS80 +units=m +no_defs'
);

insert into public.spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values(
105187,'USER',105187,'PROJCS["Korea 2000 / East Belt 2010",GEOGCS["Korea 2000",DATUM["Geocentric_datum_of_Korea",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6737"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4737"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",38],PARAMETER["central_meridian",129],PARAMETER["scale_factor",1],PARAMETER["false_easting",200000],PARAMETER["false_northing",600000],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AUTHORITY["EPSG","5187"]]','+proj=tmerc +lat_0=38 +lon_0=129 +k=1 +x_0=200000 +y_0=600000 +ellps=GRS80 +units=m +no_defs'
);

insert into public.spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values(
105186, 'USER', 105186 ,'PROJCS["Korea 2000 / Central Belt 2010",GEOGCS["Korea 2000",DATUM["Geocentric_datum_of_Korea",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6737"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4737"]],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"]],AUTHORITY["EPSG","5186"]]','+proj=tmerc +lat_0=38 +lon_0=127 +k=1 +x_0=200000 +y_0=600000 +ellps=GRS80 +units=m +no_defs'
);

insert into public.spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values(
105185, 'USER', 105185, 'PROJCS["Korea 2000 / West Belt 2010",GEOGCS["Korea 2000",DATUM["Geocentric_datum_of_Korea",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6737"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4737"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",38],PARAMETER["central_meridian",125],PARAMETER["scale_factor",1],PARAMETER["false_easting",200000],PARAMETER["false_northing",600000],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AUTHORITY["EPSG","5185"]]',
'+proj=tmerc +lat_0=38 +lon_0=125 +k=1 +x_0=200000 +y_0=600000 +ellps=GRS80 +units=m +no_defs'
);

 

 

PostgreSQL 9.5 초기설정

기본설정은 버전이 변경되도 크게 바뀌지 않는다

설정파일

postgresql.conf

https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html

사용자가 늘어서 튜닝을 해야한다 하는상황이 아니라면 
이거말구 크게 바꿀게 없다.
listen_addresses = 'localhost'
listen_addresses = '*'
listen_addresses = '192.168.0.2,localhost'
listen_addresses = '127.0.0.1'

pg_hba.conf

  • https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
개발용이면 보통은 이거한줄 추가하지 않을까
host    all             all             127.0.0.1/0             md5
운영서버라면 서버 ip를 명시하면 되고

서버 범위 지정하는거면 ip주소 쓰는칸에
192.168.0.0/24

터미널접속

sudo -u postgres psql -U postgres -w

사용자 관리

사용자 목록보기
postgres=# \deu
postgres=# \du

create user ${my-user} with password '${my-password}';
create database ${my-database};
grant all privileges on database ${my-database} to ${my-user};

권한문제 나올 때
create user --superuser ${my-user}
alter role ${my-user} superuser;
관리자권한 줘버리는거나 껄쩍지근하기도 한데...
완료후 권한축소
alter role ${my-user} nosuperuser;

Hibernate 쿼리 날리기 – hql, criteria

Criteria 쿼리

Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.le("playTime",length));
criteria.add(Restrictions.like("title","%A%");
//criteria.add(Property.forName("palyTime").le(ength));//위와 동일한 문장
criteria.Order(Order.asc("title");
criteria.ignoreCase();
criteria.list();

 

HQL 쿼리

Users 테이블이 있는 경우

Select * from Users;

from com.beansugar.templates.dto.Users 와 같은 형태의 쿼리

 

ERwin7 네이밍 룰 만들기

Tools -> Names -> Model Naming Options

Name Mapping탭 선택

오라클하고 메서드명이 비슷한것같다.

 

Entity to Table : tbl_%EntityName()
이렇게 하면 테이블명 앞에 tbl이 자동으로 붙는다.
prefix에다 써줘도 된다.

Relationships : R_%Substr(%Parent,5)_to_%Substr(%Child,5)
tbl을 제외한 나머지 글자들이 R_tbl1name_to_tbl2name 이런식으로 연결된다.