nodejs sequelize (+postgis) geojson by extent

2021.06.15 - [Nodejs/Sequelize] - Nodejs Sequelize + gdal 을 이용한 shp 업로드

Nodejs Sequelize + gdal 을 이용한 shp 업로드

 

전 포스팅에서 gdal 을 이용해 shp 파일을 공간테이블에 저장했다. 이제 공간테이블을 조회하여 벡터데이터를 리턴받는WFS (Web Feature Service) 가 제공되어야 할 것이다. WFS 는 보통 Openlayers 나 Leaflet 에서 boundary ( minx, miny, maxx, maxy ) 에 따라 요청하여 지도상에 렌더링한다.  따라서 공간정보를 포함한 DB Table 의 데이터를 boudnary 에 의해 조회 후 geojson 형태로 반환하는 API 에 대해 알아보려고 한다.

 

일단 특정 DBMS 에 공간테이블이 이미 존재한다고 가정하겠다. 전에 미리 만들어놨던 공간데이터 시군구 데이터를 기반으로 API 를 생성해 볼 것이다.

※ 여기서는 Postgresql(+postgis) , 이 전 블로그 포스팅 반드시 참조바람!

 

router 로 api 를 생성한다.

./routes/api/shp/index.js

router.get('/sig', shpController.getGeojsonSigByExtent)

 

controller 에 위에 만든 함수를 생성한다.

./routes/api/shp/shp.controller.js

    async getGeojsonSigByExtent(req, res) {
        try {
            const { extent } = req.query;
            const geojson = await shpService.getGeojsonSigByExtent(extent);
            res.status(200).send(geojson);
        } catch (e) {
            res.status(500).send('server error');
        }
    }

여기서 request 내의 Query string 에서 가져오는 extent 값은 배열( [minx, miny, maxx, maxy] )이어야한다.

※ Query string 에서 extent 값을 배열로 가져와도 되고, minx, miny, maxx, maxy 의 값으로 가져와도 된다.

  ex) GET /api/v1/shp/sig?extent=[number, number, number, number] ( /api/v1/shp/sig?extent=minx& extent=miny&extent=maxx&extent=maxy ) or GET /api/v1/shp/sig?minx=number&miny=number&maxx=number&maxy=number

 

실제 로직함수를 생성하기전에 ORM 이 아닌 실제 쿼리가 어떻게 날라가는지 먼저 확인해보는것이 좋다.

select
	json_build_object(
		'type', 
		'FeatureCollection', 
		'features', 
		jsonb_agg(ST_AsGeoJSON(geom)::JSON)
	) as geojson
from
	tl_scco_sig as tlSccoSig
where
	tlSccoSig.geom && st_makeenvelope(
	953491.1847528779,
	1951775.89631406,
	954401.1847528779,
	1952312.39631406);

DBeaver 사용

여기서 중요한 것은 where절에서 postgis 내장 함수인 st_makeenvelope 함수를 통해 바운더리에 대한 사각형을 만들고 해당 테이블의 지오메트리(geom) 값이 postgis 의 공간쿼리 중 && 를 사용하여 오버랩되는 값만 조회한다. 이 후, 조건에 만족하는 값들을 json_build_object 함수를 통해 geojson 형태로 파싱한다. 이 때, features 키에 해당하는 값은 n개 이상의 값(json array) 이므로 geometry 를 postgis의 ST_AsGeoJSON 함수로 파싱하고 JSON 으로 형변환 해준 뒤 jsonb_agg 함수를 통해 json array 를 가공해서 features 키의 값으로 넣어주면 된다.

 

이제 service 단에서 실제 로직함수를 생성한다.

./service/shp.service.js

    async getGeojsonSigByExtent(extent) {
        try {
            const [minx, miny, maxx, maxy] = extent;
            const geojson = await tlSccoSig.findOne({
                attributes: [
                    [
                        sequelize.fn('json_build_object',
                            'type', 'FeatureCollection', 'features',
                            sequelize.fn('jsonb_agg',
                                sequelize.cast(sequelize.fn('ST_AsGeoJSON', sequelize.col('geom')), 'json')
                            )
                        ),
                        'geojson'
                    ]
                ],
                where: {
                    geom: {
                        [Op.overlap]: sequelize.fn('st_makeenvelope', minx, miny, maxx, maxy)
                    }
                }
            });
            return geojson;
        } catch (e) {
            console.error(e);
            throw e;
        }
    }

• Line 3 : 매개변수로 넘겨받은 extent 값을 구조분해할당을 통해 각 값을 가져온다.(minx, miny, maxx, maxy)
• Line 6~14 : 위에 있는 네이티브 쿼리에서 postgresql or postgis의 내장함수는 sequelize.fn 함수를 통해 사용할 수 있다. 첫번째 매개변수는 함수명이 되고 그 다음 매개변수부터 내장함수의 매개변수가 순차적으로 들어간다고 생각하면 된다. 또한 네이티브 쿼리에서 형변환은 속성::Type 으로 쉽게 가능하지만 sequelize 에서는 cast 함수를 사용해야한다. 그리고 함수내에서 사용할 컬럼은 sequelize 의 col 함수를 통해 사용해야만 한다는 것도 주의해야한다. 속성은 배열 값으로 순차적으로 조회되는데, 이 때 [ 값, 별칭텍스트 ] 형태로 alias 를 할당할 수 있다.
• Line 16~20 : where 절 중 geom(지오메트리) 을 비교하기 위해 네이티브 쿼리에서 확인했던 && 비교문은 sequelize 의 Op.overlap 으로 사용해야 한다.

기능이 완성되었으면 Postman 을 통해 조회해보면 정상적으로 geojson 형태가 조회되는 것을 확인할 수 있다.

api 요청 시 배열값으로 query string 을 보내려면 같은 값을 순차적으로 써줘야함.

※ postman 을 통한 테스트를 위해 extent 값을 순차적으로 써서 배열로 백엔드에서 받았는데, 실제 프론트엔드에서 요청할 때 axios 라이브러리를 사용한다면 get 요청 시 extent 배열값을 그대로 보내도 됨!

 

여기서 속성값(properties)도 geojson 에 넣고 싶다면 service단의 소스 중 sequelize.col('geom') 에서 sequelize.col('tlSccoSig.*) 으로 변경만 해주면 된다.

async getGeojsonSigByExtent(extent) {
        try {
            const [minx, miny, maxx, maxy] = extent;
            const geojson = await tlSccoSig.findOne({
                attributes: [
                    [
                        sequelize.fn('json_build_object',
                            'type', 'FeatureCollection', 'features',
                            sequelize.fn('jsonb_agg',
                                sequelize.cast(sequelize.fn('ST_AsGeoJSON', sequelize.col('tlSccoSig.*')), 'json')
                            )
                        ),
                        'geojson'
                    ]
                ],
                where: {
                    geom: {
                        [Op.overlap]: sequelize.fn('st_makeenvelope', minx, miny, maxx, maxy)
                    }
                }
            });
            return geojson;
        } catch (e) {
            logger.error(e);
            throw e;
        }
    }

 

하지만 속성을 넣기 위해 위와 같이 하는 방법은 postgis3 이상부터 된다고 한다.. 3 미만은 아래와 같이 하길 추천한다. (쿼리가 너무 복잡해서 native query 사용)

    async getGeojsonSigByExtent(extent) {
        try {
            const [minx, miny, maxx, maxy] = extent;
            const results = await sequelize.query(
                `select
                    row_to_json(fc) as geojson
                from
                    (
                    select
                        'FeatureCollection' as type,
                        array_to_json(array_agg(f)) as features
                    from
                        (
                            select
                                'Feature' as type,
                                ST_AsGeoJSON(temp.geom)::json as geometry,
                                row_to_json((temp)) as properties
                            from tl_scco_sig as temp
                            where temp.geom && st_makeenvelope(:minx, :miny, :maxx, :maxy)
                        ) as f
                    ) as fc`
                , {
                    replacements: {
                        minx,
                        miny,
                        maxx,
                        maxy
                    },
                    type: sequelize.QueryTypes.SELECT,
                });

            return results[0];
        } catch (e) {
            logger.error(e.message);
            throw e;
        }
    }

 

postman에서 요청해보면 properties 값이 정상적으로 들어간 것을 확인할 수 있다.

 

참고소스

https://github.com/sbjang123456/nodejs-sequelize-spatial.git

GitHub - sbjang123456/nodejs-sequelize-spatial: sequelize 공간데이터 업로드 및 조회