Double tables with MS Sql databases

Description

Reported by Steve Paplanus

On the SQL Server listing, it usually displays the same table/layer twice in the tree as shown below:

Environment

None

Activity

Show:
Sergei Leschinsky
January 7, 2016, 11:48 AM

Also there is a case when there are multiple geometry types in the table and GEOMETRY_COLUMNS table is present. It seems that GDAL supports only one record / type per geometry column specified on layer creation. While QGis provides option to ignore GEOMETRY_COLUMNS table. Then presumably all the types can be enumerated.

Paul Meems
January 7, 2016, 3:12 PM

I've talked with Steve about your analysis and this is his response:

It is true that SQL Server supports multiple geometry columns per table and each are unique, but I haven’t seen it supported in many implementations. It appears ESRI’s solution is to take the first geometry/geography column it finds, and utilizes that. If you need to use another column, you must create your own spatial view against it. ESRI’s export routine and default writing to MSSQL is to not populate the GEOMETRY_COLUMNS table, though it does create it anyway. Looking at QGIS and GDAL (via the GEOMETRY_NAME option), they don’t support creating layers that have multiple geometry columns, and you need to specify a column in that case.

If I had a vote, I would take his first approach, which is to grab the first geometry or geography type from the first geometry/geography column. I don’t know how many people actually would have multiple geom columns in the same table, since it is not an option to have that in ESRI nor GRASS products(I am not as familiar with QGIS). You may wish to add a dialog box allowing the user to specify a different geometry column in a properties box or an option on the right click to change the field, if you wish to be complete. The other two methods are good (especially the 2nd option for the conservative portion of myself), but don’t make as much practical sense in this case.

Since multiple columns isn't widely used and can be solved with views I agree with Steve and go for the first approach as well.

Sergei Leschinsky
January 7, 2016, 3:24 PM

Thanks for the input.Yes, the support of multiple geometry columns per table is an option, but hardly a priority. Right now I've already implemented the third option for MS SQL, and fixed the second one for other databases.

Sergei Leschinsky
January 9, 2016, 12:20 PM

I've made changes to determine geometry type from the first shape in the table. The other 2 approaches are also supported (can be used with a simple change of flag in the source). However it's still significantly slows the listing of layers. The recommended way to do it is to use GEOMETRY_COLUMNS table.

Paul Meems
January 12, 2016, 8:51 PM

I can confirm this is fixed.

Assignee

Sergei Leschinsky

Reporter

Steven Paplanus

Labels

Fix versions

Affects versions

Priority

High
Configure