Calculating Fourier Transforms in SQL

  • Dennis Marten
  • Holger MeyerEmail author
  • Andreas Heuer
Conference paper
Part of the Lecture Notes in Computer Science book series (LNCS, volume 11695)


The Fourier transform is an important tool for analyzing, transforming and searching multi-media content in databases. SQL is the lingua franca for querying structured data. Implementing the Discrete Fourier Transform (DFT) in SQL itself has several benefits. The DFT can directly be executed in the database system. It can be reused for several, different content processing steps from feature extraction to query transformation and evaluation.

We not only discuss different algorithmic aspects but also do a performance evaluation on top of different database systems of different architectures, i.e. row and column stores. The SQL-based implementation is also compared to a Python-based implementation on the client side. There is no variant that always performs best.


Fourier transform SQL Databases Multi-media Performance evaluation 


  1. 1.
    Agrawal, R., Equitz, W.R., Faloutsos, C., Flickner, M.D., Swami, A.N.: Method for high-dimensionality indexing in a multi-media database, US Patent 5,647,058, July 1997Google Scholar
  2. 2.
    Agrawal, R., Faloutsos, C., Swami, A.: Efficient similarity search in sequence databases. In: Lomet, D.B. (ed.) FODO 1993. LNCS, vol. 730, pp. 69–84. Springer, Heidelberg (1993). Scholar
  3. 3.
    Brown, P.G.: Overview of SciDB: large scale array storage, processing and analysis. In: Proceedings of the ACM SIGMOD International Conference on Management of Data, SIGMOD 2010, Indianapolis, Indiana, USA, 6–10 June 2010, pp. 963–968 (2010).
  4. 4.
    Celentano, A., Di Lecce, V.: FFT-based technique for image-signature generation. In: Storage and Retrieval for Image and Video Databases V, vol. 3022, pp. 457–467. International Society for Optics and Photonics (1997)Google Scholar
  5. 5.
    Chang, Y., Zeng, W., Kamel, I., Alonso, R.: Integrated image and speech analysis for content-based video indexing. In: Proceedings of the IEEE International Conference on Multimedia Computing and Systems, ICMCS 1996, Hiroshima, Japan, 17–23 June 1996, pp. 306–313. IEEE (1996)Google Scholar
  6. 6.
    Di Gregorio, F., Varrazzo, D.: Psycopg – PostgreSQL database adapter for Python.
  7. 7.
    Grunert, H., Heuer, A.: Query rewriting by contract under privacy constraints. OJIOT 4(1), 54–69 (2018)Google Scholar
  8. 8.
    Hellerstein, J.M., et al.: The MADlib analytics library or MAD skills, the SQL. Technical report, UCB/EECS-2012-38, EECS Department, University of California, Berkeley, April 2012CrossRefGoogle Scholar
  9. 9.
    Kekre, H., Mishra, D.: CBIR using upper six FFT sectors of color images for feature vector generation. Int. J. Eng. Technol. 2(2), 49–54 (2010)Google Scholar
  10. 10.
    Kiranyaz, S., Qureshi, A.F., Gabbouj, M.: A generic audio classification and segmentation approach for multimedia indexing and retrieval. IEEE Trans. Audio Speech Lang. Process. 14(3), 1062–1081 (2006)CrossRefGoogle Scholar
  11. 11.
    Lajus, J., Mühleisen, H.: Efficient data management and statistics with zero-copy integration. In: Proceedings of the 26th International Conference on Scientific and Statistical Database Management, SSDBM 2014, pp. 12:1–12:10. ACM, New York (2014).
  12. 12.
    Luo, S., Gao, Z.J., Gubanov, M., Perez, L.L., Jermaine, C.: Scalable linear algebra on a relational database system. In: 2017 IEEE 33rd International Conference on Data Engineering (ICDE), pp. 523–534, April 2017.
  13. 13.
    Mao, R., Miranker, W.L., Miranker, D.P.: Dimension reduction for distance-based indexing. In: Proceedings of the Third International Conference on SImilarity Search and APplications, pp. 25–32. ACM (2010)Google Scholar
  14. 14.
    Mao, R., Miranker, W.L., Miranker, D.P.: Pivot selection: dimension reduction for distance-based indexing. J. Discrete Algorithms 13, 32–46 (2012)MathSciNetCrossRefGoogle Scholar
  15. 15.
    Marten, D., Heuer, A.: Machine learning on large databases: transforming hidden Markov models to SQL statements. Open J. Databases (OJDB) 4(1), 22–42 (2017)Google Scholar
  16. 16.
    Marten, D., Meyer, H., Dietrich, D., Heuer, A.: Sparse and dense linear algebra for machine learning on parallel-RDBMS using SQL. OJBD 5(1), 1–34 (2019)Google Scholar
  17. 17.
    Navas, M., Ordonez, C.: Efficient computation of PCA with SVD in SQL. In: Proceedings of the 2nd ACM SIGKDD Workshop on Data Mining using Matrices and Tensors, Paris, France, 28 June 2009 (2009).
  18. 18.
    Obe, R., Hsu, L.: PostgreSQL: Up and Running. O’Reilly Media, Inc. (2012)Google Scholar
  19. 19.
    Rao, K.R., Kim, D.N., Hwang, J.J.: Fast Fourier Transform - Algorithms and Applications, 1st edn. Springer, Dordrecht (2010). Scholar
  20. 20.
    Sabharwal, C.L., Subramanya, S.R.: Indexing image databases using wavelet and discrete Fourier transform. In: Proceedings of the 2001 ACM Symposium on Applied Computing (SAC), 11–14 March 2001, Las Vegas, NV, USA, pp. 434–439 (2001).
  21. 21.
    Subramanya, S., Simha, R., Narahari, B., Youssef, A.: Transform-based indexing of audio data for multimedia databases. In: Proceedings of IEEE International Conference on Multimedia Computing and Systems, pp. 211–218. IEEE (1997)Google Scholar
  22. 22.
    Tsapatsoulis, N., Avrithis, Y.S., Kollias, S.D.: Facial image indexing in multimedia databases. Pattern Anal. Appl. 4(2–3), 93–107 (2001)MathSciNetCrossRefGoogle Scholar
  23. 23.
    van der Walt, S., Colbert, S.C., Varoquaux, G.: The NumPy Array: a structure for efficient numerical computation. Comput. Sci. Eng. 13(2), 22–30 (2011). Scholar
  24. 24.
    Weihs, C., Ligges, U., Mörchen, F., Müllensiefen, D.: Classification in music research. Adv. Data Anal. Classif. 1(3), 255–291 (2007). Scholar
  25. 25.
    Yang, C.: MACS: music audio characteristic sequence indexing for similarity retrieval. In: Proceedings of the 2001 IEEE Workshop on the Applications of Signal Processing to Audio and Acoustics (Cat. No. 01TH8575), pp. 123–126. IEEE (2001)Google Scholar
  26. 26.
    Zhang, Y., Herodotou, H., Yang, J.: RIOT: I/O-Efficient Numerical Computing without SQL. CoRR abs/0909.1766 (2009)Google Scholar
  27. 27.
    Zukowski, M., Boncz, P.: From x100 to Vectorwise: opportunities, challenges and things most researchers do not think about. In: Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data, SIGMOD 2012, pp. 861–862. ACM, New York (2012).

Copyright information

© Springer Nature Switzerland AG 2019

Authors and Affiliations

  1. 1.Institute of Computer ScienceRostock UniversityRostockGermany

Personalised recommendations