Hi,

Although I work mostly with local folders in my Manifold project spaces, we save everything in a server database, where each file gets a "dbo.*" prefix added to it when it is copied there.

From the information I could gather, we are using MSSQL (Microsoft SQL) as a relational database management system (RDBMS). The version is SQL Server 2014.

While using Manifold 9, there is an issue when we copy a table which has two rtree indices into this database.

Any idea how this could be solved?

As an example below, I have the table BOU_BRW_v014 Table, one drawing drawn from Geom field, and one drawing drawn from Center field. I have tested what was causing the error, and it is definitely as soon as the Table has a second rtree index, it is now longer able to copy into the SQL server.

Any help is much welcome.

I created a table in Manifold ver 9.0.180.3 with two geom fields and two rtree indexes.

I then copied and pasted it into my SQL server Ver 2017

It worked fine. So my guess is a SQL issue limited version you are running

hope that helps a bit.

That's interesting...

And any drawing based on these geom fields display correctly when opened from your SQL server?

On my side here, when I create a drawing in a Manifold workspace, these are the Schema and the Properties of the table:

And then, when the same table gets copied in the SQL server, this is the new Schema and Properties from the table:

• The name of the BTREE index was changed, from mdf_id_x to a unique key identifier.
• A rule was added (mdd_id can not be null).
• But most importantly, although Manifold shows RTREE index, it has been changed into a SPATIAL index compatible with MSSQL, as we can see when exploring the SQL directly with SQL Server Management Studio.

Any table copied in the SQL server is given a unique index identifier, which is the long series of capital letters and numbers, and it can not be null. We see these properties below. This applies for every table in the SQL.

Under "Indexes", we see the unique mfd_id index, but also the Spatial index which has been created.

We can right-click the Spatial index it to see its properties.

We can see the "Index type" as "Spatial", which is a unique type used by MSSQL and is NOT an RTREE index.

More documentation on how MSSQL manages "Spatial Indexes":

• Spatial Indexes Overview
• SQL Server Spatial Indexes

It is my understanding that Microsoft SQL Server 2014 (MSSQL14) does not natively support an "rtree" data structure. The rtree, or R-tree, is a specialized data structure commonly used for indexing and querying spatial data, such as points, rectangles, or polygons.

Instead, starting from SQL Server 2008, Microsoft introduced theSpatial Data feature, which provides support for spatial data types and spatial indexing. With the spatial data types (e.g., geometry and geography), you can store and query spatial data in MSSQL

While MSSQL's spatial data feature provides powerful capabilities for working with spatial data, it does not specifically use the rtree data structure. Instead, it uses a different indexing mechanism optimized for spatial data.

--> It is able, for example, to break down an indexed space into a grid hierarchy by using a technique called tessellation. See this page for details on this technique.

When we copy a table from a Manifold space into the SQL server, some properties are added and/or changed for this table to fit within the MSSQL schema.

To continue the investigation, for anyone with an SQL server:

1. If you are able to copy a Table with two geom fields (and showing two rtree indices in the Schema) without error, do the drawings show properly when opened from the SQL?
2. Would you be able to check the structure of your table after you copied it into your SQL server? Does it show Index type: Spatial, similarly to my screenshot above?
3. I also have issues with Images/Rasters copied into SQL server. Initially the Image Schema shows an RTREE index with three dimensions (X, Y, Tile). But after copying it into my SQL server, the RTREE index disappears completely from the Schema. Hence, when I open the Image, it doesn't display properly, it can't re-create the Image because the Spatial Index is non-existent --> Do your Images/Rasters display properly after being copied into your SQL server?

Thanks so much for the help.

Attachments:
01_btree_normal.PNG
02_before_copy_to_SQL.PNG
03_btree_in_SQL.PNG
04_after_copy_to_SQL.PNG
05_SQL_server_structure.png
06_SQL_Geom_properties.png