특정 좌표가 어느 위치에 포함되어 있는지 찾기(ST_CONTAINS)

이동하는 장비가 어느 블록 내에 있는지 찾아야 하는 경우가 생겼다.

장비의 이동 위치는 위,경도 값으로 DB 에 저장되어 있었고, 블록의 위, 경도 정보는 xml 파일에 있는 상태라 우선 xml 파일의 정보를 DB 에 저장했다.

1. Block 의 Polygon 정보가 들어 있는 xml 파일

<Location version="201904250931">
	<Bound id="BE-VX02" che="true"> 
		<Point x="31.744928" y="45.758862" />
		<Point x="31.742374" y="45.761635" />
		<Point x="31.742670" y="45.761791" />
		<Point x="31.745214" y="45.759014" />
  	</Bound>
	<Bound id="BE-VX01" che="true">
		<Point x="31.742182" y="45.761986" />
		<Point x="31.747015" y="45.763622" />
		<Point x="32.747166" y="45.763371" />
		<Point x="31.742338" y="45.761734" />
  	</Bound>
	...
	<Bound id="BE-VX65" che="true">
		<Point x="31.753398" y="45.762699" />
		<Point x="31.753183" y="45.762687" />
		<Point x="31.753007" y="45.762760" />
		<Point x="31.752147" y="45.763534" />
		<Point x="31.751848" y="45.763610" />
		<Point x="31.751342" y="45.763477" />
		<Point x="31.749565" y="45.764622" />
		<Point x="31.749275" y="45.764671" />
		...
</Location>

 

2. xml 파일에서 Bound ID 와 Polygon 좌표 정보를 저장할 Table 생성(mariaDB)

CREATE TABLE `evt_bound` (
      `reg_seq` bigint(20) NOT NULL AUTO_INCREMENT,
      `bnd_id` varchar(20) DEFAULT NULL,
      `bnd_pol` polygon DEFAULT NULL, PRIMARY KEY (`reg_seq`) )
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

3. xml 파일 읽어 데이터를 Table 에 저장하는 스크립트

import xml.etree.ElementTree as elemTree
import pymysql
from mysql.connector import Error

def main():
    tree = elemTree.parse('D:/Geofence_UTF_HSEDIT.xml')
    try:
        con = pymysql.connect(host="db_ip", user="db_id", password="db_pwd",
                              db='db_name', charset='utf8')
        cur = con.cursor()
        for bound in tree.findall('./Bound'):
            bound_id = bound.get('id')
            points = bound.findall('./Point')
            pol_text = ''
            for point in points:
                # print(point.attrib)
                pol_text += point.get('y') + ' ' +  point.get('x') + ', '
            pol_text += points[0].get('y') + ' ' + points[0].get('x')
            pol_text = 'POLYGON((' + pol_text + '))'

            # insert into 테이블 (POLYGON_TYPE_COLUMN명) 
            # values (POLYGONFROMTEXT('POLYGON(('위치0 위치1 위치2 ... 위치0))'))
            sql = """insert into evt_bound ( bnd_id, bnd_pol )
              values (%s, POLYGONFROMTEXT(%s))
            """
            cur.execute(sql, (bound_id, pol_text))
            con.commit()
    except Error as e:
        print("Error MySQL ", e)
    finally:
        cur.close()
        con.close()
        print("MySQL connection is closed")

if __name__ == '__main__':
    main()

스크립트의 코멘트로 기록해두었지만 mariaDB(mySQL) 의 Polygon 타입 컬럼에 값 저장할 때는 좌표값을 ','으로 구분하여 나열하면 되는데 단, 닫힌 도형이 되어야 하므로 첫 번째 좌표값을 마지막에 다시 위치시켜줘야 한다. 그러므로 실제 저장되는 형태는 다음과 같은 식이 될 것이다.

insert into evt_bound ( bnd_id, bon_pol )
values ( 'BE-VX02', PolygonFromText('POLYGON((40.758862 29.74492, 40.761635 29.742374, 40.761791 29.742670, 40.759014 29.745214, 40.758862 29.74492))') )

준비는 다 되었고 이제 장비의 좌표값이 들어 있는 테이블과 연결하여 어느 블록 안에 있는지 찾아보자.

SELECT a.mach_id, a.pos_lat, a.pos_lon, b.bnd_id
FROM evt_mach_pos_hst a, evt_bound b
WHERE ST_CONTAINS(b.bnd_pol, GEOMFROMTEXT(CONCAT('POINT(',pos_lat,' ',pos_lon,')' )))

mariaDB 에서 제공하는 ST_CONTAINS 함수를 이용하여 장비가 들어가 있는 bound 의 ID 를 구해봤다.