-2

TLDR: What is the best way to store spatial data in an SQL database to be used in R-Trees?

Long question:

I am writing a feature that incorporates spatial data. The goal is to store POIs and being able to retrieve the data quickly, perform clustering etc.

My understanding is that R*-Trees are a good solution for this kind of task. I am planning on using: https://github.com/davidmoten/rtree.

SQLite seems to offer R-Trees, but I can use only SQL. What would be the most efficient way to store this data?

A.L.
  • 144
  • 2
  • 9
  • What does "*but I can use only SQL*" mean? – PM 77-1 Jul 20 '17 at 20:48
  • 1
    MySQL offers r-tree indexes. https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html but basically any database that uses SQL as a query language seems to me to qualify as using SQL, so I'm not sure why SQLite doesn't work for you. – Alex Jul 20 '17 at 20:48
  • @AlexR Yes, that is what I was inept to find.. Thanks mate! – A.L. Jul 20 '17 at 21:02

1 Answers1

1

Get a database that has R trees.

For example SQLite, PostgreSQL, Oracle, ...

But beware that the query performance of these databases will usually be pretty bad compared to an in-memory index such as ELKIs. In particular if you want nearest neighbor with haversine distance, which is what I need mostly.

Often, their R tree index is a ugly hack. It seems they usually create a table to store the pages of the tree, so querying means repeatedly selecting rows from that table.

Has QUIT--Anony-Mousse
  • 76,138
  • 12
  • 138
  • 194