도로명주소 안내시스템 개발자센터

Tech & Tips 상세보기 | 도로명주소 개발자센터

Tech & Tips

API, DB, 전자지도등 도로명주소를 이용한 기술개발 및 활용팁을 올려주세요

[주소DB구축] 주소 검색 시 검색 결과 정렬 예시 (MSSQL SERVER)

3656님의 제안

  • 댓글 : 0
  • 조회수 : 791
  • 추천수 22 +1

2017-09-14

MSSQL을 이용하여 도로명주소DB 구축 후 주소 검색 시 주소정렬 예시입니다.
도로명에 숫자가 포함되면 도로명, 건물본번, 건물부번 순으로 정렬 시
OO로10길이  OO로2길 보다 먼저 조회되므로 숫자 순서대로 정렬하는 SQL QUERY SAMPLE 입니다.

주소DB로 구축하였으며, MSSQL 버전은 Microsoft Sql Server 16 입니다.

 

 

1. TRANSLATE 함수 생성
  ORACLE의 TRANSLATE()와 같은 기능을 하는 함수가 MSSQL에는 없어 유사한 함수를 생성합니다.
  ------------------------------------------------------------------------------------------------------
  ** 출처 : Microsoft 스크립트 센터 (https://gallery.technet.microsoft.com/scriptcenter/Translate-Function-00f04329)
  ------------------------------------------------------------------------------------------------------
  GO
  IF OBJECT_ID (N'dbo.Translate', N'FN') IS NOT NULL
      DROP FUNCTION dbo.Translate;
  GO
  CREATE FUNCTION dbo.Translate (@Data VARCHAR(MAX), @DataToReplace VARCHAR(100), @ReplacedWithData VARCHAR(100))
  RETURNS VARCHAR(MAX)
 
  BEGIN
   
   DECLARE @TranslaedData VARCHAR(MAX)
   
   ;WITH CTE(PosToReplace,Data,DataToReplace,ReplacedWithData) AS
   (
   SELECT 1,CAST(@Data AS VARCHAR(MAX)) AS Data,CAST(SUBSTRING(@DataToReplace,1,1) AS VARCHAR(MAX)) AS DataToReplace,CAST(SUBSTRING(@ReplacedWithData,1,1) AS VARCHAR(MAX)) AS ReplacedWithData
   UNION ALL
   SELECT C.PosToReplace+1 AS PosToReplace , CAST(REPLACE(C.Data,C.DataToReplace,C.ReplacedWithData) AS VARCHAR(MAX)) AS Data,CAST(SUBSTRING(@DataToReplace,PosToReplace+1,1) AS VARCHAR(MAX)) AS DataToReplace,CAST(SUBSTRING(@ReplacedWithData,PosToReplace+1,1) AS VARCHAR(MAX)) AS ReplacedWithData
   FROM CTE C
   WHERE C.PosToReplace <= LEN(@DataToReplace)
   )
   SELECT  @TranslaedData = C.Data FROM CTE C WHERE C.PosToReplace = LEN(@DataToReplace)+1
    
   RETURN @TranslaedData       
  END
  ------------------------------------------------------------------------------------------------------

 

 

2. 데이터 중간의 숫자 정보만 가져오는 함수 생성
   오라클의 TRANSLATE와 달리 위 TRANSLATE 함수는 변환대상문자를 일일이 지정하여야 하므로
   도로명에서 숫자만 추출하고자 할때 한글조합을 모두 지정할 수 없어 아래와 같은 함수 생성함
  ------------------------------------------------------------------------------------------------------
  /* 인수로 받은 넘겨받은 데이터에서 숫자에 해당하는 값만 추출하여 리턴
     문자뒤에 오는 숫자의 위치를 잡아 양 옆의 문자를 제외하여 number를 추출
  */
  IF OBJECT_ID (N'dbo.fn_getNumData', N'FN') IS NOT NULL
      DROP FUNCTION dbo.fn_getNumData;
  GO
  CREATE FUNCTION dbo.fn_getNumData (@Data VARCHAR(MAX))
  RETURNS DECIMAL(10)
  WITH EXECUTE AS CALLER
  AS
  BEGIN
   DECLARE @getNum DECIMAL(10);
   DECLARE @data_nonum VARCHAR(MAX);
   DECLARE @data_ind DECIMAL(10);
 
   SET @data_nonum = RTRIM(LTRIM(dbo.translate(@Data , '01234567890','          ')));
   SET @data_ind = charindex(' ', RTRIM(LTRIM(dbo.translate(@Data, '01234567890','          '))));
   SET @getNum = CASE @data_ind WHEN 0 THEN 0
       ELSE CAST(dbo.translate(dbo.translate(@Data, RTRIM(LEFT(@data_nonum, @data_ind - 1)),''),LTRIM(RIGHT(@data_nonum,LEN(@data_nonum) - @data_ind)),'') AS DECIMAL(10)) END ;
    
   RETURN(@getNum); 
  END;
  GO   
  ------------------------------------------------------------------------------------------------------
  
 
3. 조회쿼리 SAMPLE
  dbo.translate(B.도로명, '01234567890',''), dbo.fn_getNumData(B.도로명) 항목을 조회해 보면
   대학로     => 대학로, 0
   대학로10길 => 대학로길, 10
   대학로2길  => 대학로길, 2
   등으로 조회되므로 정렬에 활용함
  ------------------------------------------------------------------------------------------------------
 SELECT A.기초구역번호 AS 새우편번호,
        B.시도명 +' '+B.시군구명+' '+(CASE B.읍면동구분 WHEN '0' THEN '' + B.읍면동명 ELSE '' END)+' '+
        B.도로명 + (CASE A.지하여부 WHEN '0' THEN ' '
                                    WHEN '1' THEN ' 지하'
                                    WHEN '2' THEN ' 공중' END) +
        CAST(A.건물본번 AS VARCHAR(5)) +(CASE A.건물부번 WHEN 0 THEN ''
                                         ELSE '-'+CAST(A.건물부번 AS VARCHAR(5)) END) +
        CASE WHEN (B.읍면동구분 = '0' AND D.공동주택여부 ='0') THEN ''
             WHEN (B.읍면동구분 = '0' AND D.공동주택여부 ='1') THEN (CASE D.시군구 건물명 WHEN '' THEN '' ELSE ' (' + D.시군구 건물명+ ')' END )
             WHEN (B.읍면동구분 = '1' AND D.공동주택여부 ='0') THEN ' ('+B.읍면동명+')' 
             WHEN (B.읍면동구분 = '1' AND D.공동주택여부 ='1') THEN ' ('+B.읍면동명 + (CASE D.시군구 건물명 WHEN '' THEN '' ELSE  ',' + D.시군구 건물명 END ) +')'
        END AS 도로명주소,
        dbo.translate(B.도로명, '01234567890','') ORD_RN, dbo.fn_getNumData(B.도로명)
   FROM 부가정보 D, 도로명주소 A, 도로명코드 B
  WHERE B.도로명코드 = A.도로명코드
    AND B.읍면동일련번호 = A.읍면동일련번호
    AND A.관리번호 = D.관리번호
    AND B.시도명 LIKE Param1 || '%'
    AND B.시군구명 LIKE Param2 || '%'
    AND B.도로명 LIKE Param3 || '%'
    AND A.건물본번 = Param4
    AND A.건물부번 = Param5
   ORDER BY B.시도명, B.시군구명, dbo.translate(B.도로명, '01234567890',''), dbo.fn_getNumData(B.도로명), A.건물본번, A.건물부번
  ;

 

 

첨부파일 function_qry.zip

댓글쓰기

댓글쓰기