nazotteの改善

[ryo628]

やりたいこと

isucon10-practice/isuumo/webapp/go/main.go

Lines 856 to 909 in aa856ea

	func searchEstateNazotte(c echo.Context) error {
	coordinates := Coordinates{}
	err := c.Bind(&coordinates)
	if err != nil {
	c.Echo().Logger.Infof("post search estate nazotte failed : %v", err)
	return c.NoContent(http.StatusBadRequest)
	}
	if len(coordinates.Coordinates) == 0 {
	return c.NoContent(http.StatusBadRequest)
	}
	b := coordinates.getBoundingBox()
	estatesInBoundingBox := []Estate{}
	query := `SELECT * FROM estate WHERE latitude <= ? AND latitude >= ? AND longitude <= ? AND longitude >= ? ORDER BY popularity DESC, id ASC`
	err = db.Select(&estatesInBoundingBox, query, b.BottomRightCorner.Latitude, b.TopLeftCorner.Latitude, b.BottomRightCorner.Longitude, b.TopLeftCorner.Longitude)
	if err == sql.ErrNoRows {
	c.Echo().Logger.Infof("select * from estate where latitude ...", err)
	return c.JSON(http.StatusOK, EstateSearchResponse{Count: 0, Estates: []Estate{}})
	} else if err != nil {
	c.Echo().Logger.Errorf("database execution error : %v", err)
	return c.NoContent(http.StatusInternalServerError)
	}
	estatesInPolygon := []Estate{}
	for _, estate := range estatesInBoundingBox {
	validatedEstate := Estate{}
	point := fmt.Sprintf("'POINT(%f %f)'", estate.Latitude, estate.Longitude)
	query := fmt.Sprintf(`SELECT * FROM estate WHERE id = ? AND ST_Contains(ST_PolygonFromText(%s), ST_GeomFromText(%s))`, coordinates.coordinatesToText(), point)
	err = db.Get(&validatedEstate, query, estate.ID)
	if err != nil {
	if err == sql.ErrNoRows {
	continue
	} else {
	c.Echo().Logger.Errorf("db access is failed on executing validate if estate is in polygon : %v", err)
	return c.NoContent(http.StatusInternalServerError)
	}
	} else {
	estatesInPolygon = append(estatesInPolygon, validatedEstate)
	}
	}
	var re EstateSearchResponse
	re.Estates = []Estate{}
	if len(estatesInPolygon) > NazotteLimit {
	re.Estates = estatesInPolygon[:NazotteLimit]
	} else {
	re.Estates = estatesInPolygon
	}
	re.Count = int64(len(re.Estates))
	return c.JSON(http.StatusOK, re)
	}

ここの改善

ある地理図形がある地理図形に含まれているかは ST_Contains() で判断できる。

[saggggo]

go sqlxでMySQLのPOINT型が扱えない問題

[saggggo]

sqlxでGISを扱うライブラリはないっぽい
↓以下のコードは古くて動かず
jmoiron/sqlx#129