Could cockroachDb build geo spatial index on a table column of Geometry type which has multi different srid data?

eg.
Geometry
srid 2100;Point(1,2)
srid 2199;Point(3,4)
srid 4300;Point(5,6)

Hi jievince,

For GEOMETRY, it is planar geometry and not spherical, so we don’t try to normalize to 4326. For planar geometry, we need a bounding box that will (usually) contain the indexed shapes, in order to construct the index. When we have an SRID declared for that column, we construct the bounding box from the bounds defined for that SRID.

If the SRID is not declared for that column we use [-(1 << 31), (1 << 31)-1) as the bounds for each of X and Y. This is likely to result in poorer indexing since the number of cells inside the bounding box is fixed. If one uses a much bigger bounding box than needed, the cell size will increase resulting in poorer fidelity.

Hope that helps!

Hi @laurenbarker , thanks for your detailed explanation.

For planar geometry, we need a bounding box that will (usually) contain the indexed shapes, in order to construct the index.
The idea to construct a bounding box for GEOMETRY is a little similarly to R-tree. Is it the same as MBR(minimum bounding rectangle) that is used in R-tree?
If the SRID is not declared for that column we use [-(1 << 31), (1 << 31)-1) as the bounds for each of X and Y. This is likely to result in poorer indexing since the number of cells inside the bounding box is fixed. If one uses a much bigger bounding box than needed, the cell size will increase resulting in poorer fidelity.

I don’t get it. If the SRID is not declared for the column schema, you use the same biggest bounding box for all different geometry data?

The idea to construct a bounding box for GEOMETRY is a little similarly to R-tree. Is it the same as MBR(minimum bounding rectangle) that is used in R-tree?

An R-tree depends on the shapes being indexed, so the MBR can take the actual shapes into account. In our indexing approach, which “divides the space”, we need to bound the space up front. A good comparison is provided in our blog post, How We Built Scalable Spatial Data & Spatial Indexing in CockroachDB.

I don’t get it. If the SRID is not declared for the column schema, you use the same biggest bounding box for all different geometry data?

We recommend specifying the bounds explicitly on index creation. The details are explained in these docs: Spatial Indexes | CockroachDB Docs.