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’ );