Skip to main content

Calculating Fourier Transforms in SQL

Part of the Lecture Notes in Computer Science book series (LNISA,volume 11695)

Abstract

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.

Keywords

  • Fourier transform
  • SQL
  • Databases
  • Multi-media
  • Performance evaluation

This is a preview of subscription content, access via your institution.

Buying options

Chapter
USD   29.95
Price excludes VAT (USA)
  • DOI: 10.1007/978-3-030-28730-6_10
  • Chapter length: 16 pages
  • Instant PDF download
  • Readable on all devices
  • Own it forever
  • Exclusive offer for individuals only
  • Tax calculation will be finalised during checkout
eBook
USD   69.99
Price excludes VAT (USA)
  • ISBN: 978-3-030-28730-6
  • Instant PDF download
  • Readable on all devices
  • Own it forever
  • Exclusive offer for individuals only
  • Tax calculation will be finalised during checkout
Softcover Book
USD   89.99
Price excludes VAT (USA)
Fig. 1.
Fig. 2.
Fig. 3.
Fig. 4.
Fig. 5.
Fig. 6.
Fig. 7.

References

  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 1997

    Google Scholar 

  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). https://doi.org/10.1007/3-540-57301-1_5

    CrossRef  Google Scholar 

  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). https://doi.org/10.1145/1807167.1807271

  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. 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. Di Gregorio, F., Varrazzo, D.: Psycopg – PostgreSQL database adapter for Python. http://initd.org/psycopg/docs/

  7. Grunert, H., Heuer, A.: Query rewriting by contract under privacy constraints. OJIOT 4(1), 54–69 (2018)

    Google Scholar 

  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 2012

    CrossRef  Google Scholar 

  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. 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)

    CrossRef  Google Scholar 

  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). https://doi.org/10.1145/2618243.2618265

  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. https://doi.org/10.1109/ICDE.2017.108

  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. Mao, R., Miranker, W.L., Miranker, D.P.: Pivot selection: dimension reduction for distance-based indexing. J. Discrete Algorithms 13, 32–46 (2012)

    MathSciNet  CrossRef  Google Scholar 

  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. 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. 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). https://doi.org/10.1145/1581114.1581119

  18. Obe, R., Hsu, L.: PostgreSQL: Up and Running. O’Reilly Media, Inc. (2012)

    Google Scholar 

  19. Rao, K.R., Kim, D.N., Hwang, J.J.: Fast Fourier Transform - Algorithms and Applications, 1st edn. Springer, Dordrecht (2010). https://doi.org/10.1007/978-1-4020-6629-0

    CrossRef  MATH  Google Scholar 

  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). https://doi.org/10.1145/372202.372395

  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. Tsapatsoulis, N., Avrithis, Y.S., Kollias, S.D.: Facial image indexing in multimedia databases. Pattern Anal. Appl. 4(2–3), 93–107 (2001)

    MathSciNet  CrossRef  Google Scholar 

  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). https://doi.org/10.1109/MCSE.2011.37

    CrossRef  Google Scholar 

  24. Weihs, C., Ligges, U., Mörchen, F., Müllensiefen, D.: Classification in music research. Adv. Data Anal. Classif. 1(3), 255–291 (2007). https://doi.org/10.1007/s11634-007-0016-x

    MathSciNet  CrossRef  MATH  Google Scholar 

  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. Zhang, Y., Herodotou, H., Yang, J.: RIOT: I/O-Efficient Numerical Computing without SQL. CoRR abs/0909.1766 (2009)

    Google Scholar 

  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). https://doi.org/10.1145/2213836.2213967

Download references

Author information

Authors and Affiliations

Authors

Corresponding author

Correspondence to Holger Meyer .

Editor information

Editors and Affiliations

Rights and permissions

Reprints and Permissions

Copyright information

© 2019 Springer Nature Switzerland AG

About this paper

Verify currency and authenticity via CrossMark

Cite this paper

Marten, D., Meyer, H., Heuer, A. (2019). Calculating Fourier Transforms in SQL. In: Welzer, T., Eder, J., Podgorelec, V., Kamišalić Latifić, A. (eds) Advances in Databases and Information Systems. ADBIS 2019. Lecture Notes in Computer Science(), vol 11695. Springer, Cham. https://doi.org/10.1007/978-3-030-28730-6_10

Download citation

  • DOI: https://doi.org/10.1007/978-3-030-28730-6_10

  • Published:

  • Publisher Name: Springer, Cham

  • Print ISBN: 978-3-030-28729-0

  • Online ISBN: 978-3-030-28730-6

  • eBook Packages: Computer ScienceComputer Science (R0)