[BE] Spatial index

[janeljs]

    public List<Accommodation> findByLocationCustom(double x, double y, double rangeKm,
                                                    LocalDate startDate, LocalDate endDate, Integer numberOfPeople) {
        String sql = "SELECT * " +
                "FROM accommodation a " +
                "JOIN accommodation_address b ON a.accommodation_address_id = b.id " +
                "AND MBRContains(ST_LINESTRINGFROMTEXT(CONCAT('LINESTRING(',:x1,' ',:y1,',',:x2,' ',:y2,')')),b.location) " +
                "LEFT JOIN reservation c ON a.id = c.accommodation_id AND c.deleted = 0 " +
                "AND ( " +
                "( " +
                ":start_date <= c.start_date AND c.start_date < :end_date) " +
                "OR (:start_date <c.end_date AND c.end_date <= :end_date) " +
                "OR (c.start_date < :start_date AND :end_date < c.end_date) " +
                ") ";
        if (numberOfPeople != null) {
            sql += "JOIN detail_condition d ON a.detail_condition_id = d.id AND d.max_of_people >= " + numberOfPeople + " ";
        }
        sql += "WHERE c.id IS NULL ";
        Rectangle2D rectangle2D = LocationUtils.getRectangle(x, y, rangeKm);
        double x1 = rectangle2D.getX();
        double y1 = rectangle2D.getY();
        double x2 = x1 + rectangle2D.getWidth();
        double y2 = y1 + rectangle2D.getHeight();
        Query nativeQuery = entityManager.createNativeQuery(sql, Accommodation.class)
                .setParameter("x1", x1)
                .setParameter("y1", y1)
                .setParameter("x2", x2)
                .setParameter("y2", y2)
                .setParameter("start_date", startDate)
                .setParameter("end_date", endDate);
        return nativeQuery.getResultList();
    }

출처: https://github.com/isaac56/airbnb/blob/BE%2Ffeature%2Flogin/BE/airbnb/src/main/java/team14/airbnb/repository/AccommodationRepositoryCustomImpl.java

[janeljs]

https://en.wikipedia.org/wiki/Spatial_database

[janeljs]

공간 인덱스 https://docs.microsoft.com/ko-kr/sql/relational-databases/spatial/spatial-indexes-overview?view=sql-server-ver15

[janeljs]

https://purumae.tistory.com/198

[janeljs]

https://ssodang.tistory.com/entry/MyBatis-MySQL-POLYGON-%ED%83%80%EC%9E%85-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EC%BF%BC%EB%A6%AC

[janeljs]

포스트그레스, elastic search

[janeljs]

https://en.wikipedia.org/wiki/Apache_Lucene