postgis spatial query

상황 : Elasticsearch와 postgres를 같이 사용하고,cold backup 용으로 ( couchbase 에서 ) postgres를 사용해야한다.

 

문제 :

1 postgres와 elasticsearch의 geo 타입이 좀 다르다.

2 기존에 사용하던 Elasticsearch와 가지고있는 polygon 동일하고, 조회했을 시에 return되는 document들이 같아야한다. (당연히)

조회는 coordinates 기준.

 

 

방안 :

1 postgres에 jsonb 타입과 geometry 타입을 둘다 만들어서 보관해둔다.

(문제가 있거나 index를 새로 만들어야할 때 elasticsearch에 바로 보낼 수 있게) postgres에서 geometry 타입을 사용하려면 데이터 형식이 좀 바뀌면 되고, 그렇게 하면 pgadmin으로 geometry viewer로 볼 수도 있다 (ㄹㅇ편한 세상)

postgres의 geometry타입은 콤마가 빠져있다 ㅋ

 

2 postgis 함수 중에서 elasticsearch contains 쿼리와 동일한 리턴을 하는 함수를 찾는다. (보통 within과 contains는 다른것인데, postgres에서 정밀도 항목을 줄 수 있는 것은 within함수였다.)

지금 사용중인 elasticsearch 인덱스 에서는 매핑타입이 이렇게 되어있음

{
  "properties": {
    "boundaries": { 
      "type": "geo_shape",
      "ignore_malformed": true,
      "precision": "1.0m",
      "strategy": "recursive",
      "distance_error_pct": 0.009
    }
  }
}

아래는 내가 테스트해본 쿼리의 예시. postgis를 사용하면 아래와같은 함수를 이용할 수 있다.

 

(예시)

 
SELECT region_id, region_name
FROM json_b_test_region_100_005
WHERE ST_DWithin(
        polygon,
        'POINT (-97.081857 32.9170445)'::GEOMETRY,
        10
      );

SELECT region_id, region_name
FROM json_b_test_region_100_005
WHERE ST_DWithin(
        polygon, ST_GeometryFromText( 'POINT (-97.081857 32.9170445)'),
        10
      );

 

---

SELECT *
FROM json_b_test_region_100_006
WHERE ST_DWithin(
        polygon, ST_GeometryFromText( 'POINT(127.0619248 37.4940372)'),
        0.01
      );
     

ST_DWithin으로 검색하면 이렇고.

 

 

POST 요청 

https://엘라스틱서치_호스트명/요청보낼_인덱스명/_search

{
  "query": {
    "bool": {
      "filter": {
        "geo_shape": {
          "boundaries": {
            "shape": {
              "type": "Point",
              "coordinates": [127.0619248, 37.4940372]
            },
            "relation": "contains"
          }
        }
      }
    }
  }
}

 

엘라스틱서치에서 조회했을때는, 이것과 결과가 같이 나온다. 

 

여담으로 (현재 springboot 프로젝트로 개발중) multiple datasource 를 사용할까 하다 그냥 postgres 하나만 사용해도 되었기에, 프로젝트에서는 

@SpringBootApplication //(exclude = { HibernateJpaAutoConfiguration.class }) exclude = { DataSourceAutoConfiguration.class, }, scanBasePackageClasses = ExpediaRegionCountryRepository.class
@EnableJpaRepositories(basePackages = {"패키지명.repository" })
public class Application extends WebMvcConfigurerAdapter {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

이런식으로 DataSourceAutoConfiguration 을 사용하는 식으로 변경하였다. (기존에는 exclude 해두었음...)

---

 

참고

 

[왜?]
- es 만 쓸수없고,
- postgres 가 편하고
- couchbase는 존나 느리고 커뮤니티 적어서 정보도 없음

[작업목표]
- 저장한 geojson 이용해서  -> 위경도로 조회되는지 테스트
-> 안되면 postgis에 geometry라는 컬럼 추가하고 테스트
-> 되면 데이터 생성하고 운영에 반영
-> 관리자페이지 수정

---------------------------------------------------------------------------

[봐야했던 문서]
- https://livebook.manning.com/book/postgis-in-action-second-edition/chapter-2/1
- https://www.postgresql.org/docs/current/functions-geometry.html
- https://stackoverflow.com/questions/49014940/how-can-i-return-all-the-rows-in-a-postgresql-postgis-table-within-a-radius-of-x
- https://postgis.net/docs/ST_DWithin.html
- ST_DWithin의 사용 : http://postgis.net/2013/08/26/tip_ST_DWithin/
- ST_Contains : https://postgis.net/docs/ST_Contains.html
- http://postgis.net/workshops/postgis-intro/geometries.html
- 멀티폴리곤 사용 하려면 위와 이것 참고: https://postgis.net/docs/using_postgis_dbmanagement.html
- 집어넣기  : https://stackoverflow.com/questions/38937455/creating-a-table-for-polygon-values-in-postgis-and-inserting
- postgres 스파샬 쿼리 쓰는법 : http://postgis.net/workshops/postgis-intro/spatial_relationships.html#st-within-and-st-contains