Use geometry column as Index

[latot]

As talked before, this is a hard feature to make, is not priority for spatial databases, but is used in some of them like OpenStreetMap, in any case we can always do some tricks like made a new index column, but obvs is a workaround and force us to break the integrity of information.

If there is time, ideally support this plis.

Thx.

[latot]

@fulghum sady, seems this is more important than we tought, MySQL is not a common to implement spatial databases, so there is not too much utilities to can upload spatial data to the db.

The only one is ogr2ogr and is needed support geometry as index.

Workaround:

1. use the tool to upload a shapefile to a MySQL or MariaDB
2. dump the table to a SQL file
3. use the dump to Dolt

(Can connect Dolt with PostgreSQL would be a way too, maybe less hard than spatial directly in MySQL)

Thx

[fulghum]

Hey @latot, thanks for the updated info. Good to know about the ogr2ogr tool and how it relies on spatial indexes.

Thanks for documenting a workaround process, too. Great that there's a path there, but of course, we'd also love to see ogr2ogr work directly with Dolt, too. 😄 I'll poke around on the team to get some more info on how much work we think spatial indexes would be and where they sit in our priority stack.

[dlscanada]

+1 for wanting spatial indexes

[timsehn]

@JCOR11599 is working on this and will have an update by end of month.

[latot]

So close!

[timsehn]

A preview version is in 0.53.1 if you want to try:

https://github.com/dolthub/dolt/releases/tag/v0.53.1

Here's the PR. It notes some of the to dos.

https://github.com/dolthub/dolt/pull/5164

[timsehn]

This is released but you need to turn it on with an environement variable:

export DOLT_ENABLE_SPATIAL_INDEX=1

Blog here: https://www.dolthub.com/blog/2023-03-03-spatial-indexes/

It will be on by default likely in the next release.

[jycor]

It's on by default on main, will have a release for that some time by end of the week

[jycor]

Hey @latot and @dlscanada. It's been a little less than a year, but SPATIAL indexes are now supported (and enabled by default) as of dolt release v0.75.1! Please try them out, let us know how it goes, and cut any issues that come up.

Only the functions ST_INTERSECT() and ST_WITHIN() take advantage of it. You can use the EXPLAIN syntax to see if a query is using the index.

tmp> create table t (p point not null srid 0, spatial index (p));
tmp> insert into t values (point(1,2));
Query OK, 1 row affected (0.00 sec)
tmp> select st_aswkt(p) from t where st_intersects(p, point(1,2));
+-------------+
| st_aswkt(p) |
+-------------+
| POINT(1 2)  |
+-------------+
1 row in set (0.00 sec)

[latot]

@JCOR11599 and @timsehn Thx! We really appreciate the developcment of Dol t :)

Even in for now only some functions take advantage of it, be supported means be able to upload data from the OSM for example, that comes with spatial index by default.

For now I'll test it with Gentoo (I already have an installer for it).

Thx!