elasticsearch envelope search in postgis

상황

elasticsearch에서 조회해오던 지역 객체들을 postgis로 조회해온다.

 

 

방법

1 geometry 타입 컬럼으로 하나 만들어 둠.

2 postgis `ST_MakeEnvelope` 연산 사용.

 

 

해결

 

나의 경우엔 이렇게 썼음~

SELECT *
FROM expedia_region_union
WHERE boundaries && ST_MakeEnvelope(10.9351, 49.3866, 11.201, 49.5138, 4326);

 

 

 

 

참조

# https://github.com/perrygeo/spatial-search-showdown


EXPLAIN ANALYZE
SELECT name, country
FROM geoname
WHERE geoname.the_geom && ST_MakeEnvelope(10.9351, 49.3866, 11.201, 49.5138, 4326)
AND (geoname.name LIKE '%hof%'
  OR geoname.alternatenames LIKE '%hof%');
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on geoname  (cost=9.85..761.09 rows=1 width=17) (actual time=0.148..0.254 rows=37 loops=1)
   Recheck Cond: (the_geom && '0103000020E61000000100000005000000ADFA5C6DC5DE25401A51DA1B7CB14840ADFA5C6DC5DE2540A857CA32C4C14840F4FDD478E9662640A857CA32C4C14840F4FDD478E96626401A51DA1B7CB14840ADFA5C6DC5DE25401A51DA1B7CB14840'::geometry)
   Filter: (((name)::text ~~ '%hof%'::text) OR (alternatenames ~~ '%hof%'::text))
   Rows Removed by Filter: 191
   ->  Bitmap Index Scan on idx_geoname  (cost=0.00..9.85 rows=191 width=0) (actual time=0.134..0.134 rows=228 loops=1)
         Index Cond: (the_geom && '0103000020E61000000100000005000000ADFA5C6DC5DE25401A51DA1B7CB14840ADFA5C6DC5DE2540A857CA32C4C14840F4FDD478E9662640A857CA32C4C14840F4FDD478E96626401A51DA1B7CB14840ADFA5C6DC5DE25401A51DA1B7CB14840'::geometry)
 Total runtime: 0.350 ms
(7 rows)