Geodatabase modeling - feature classes and SQL Server geography data type

[JonathanBailey]

I have a requirement to create a geodatabase in SQL Server, using the SQL Server geography spatial type. This geodatabase will be queried and edited by ArcGiS-based and non-ArcGIS-based applications.

SQL Server supports storing geographies of multiple types (e.g., points, lines, and polygons) in the same geography column in the same table, but ArcGIS does not support this.

Ideally, I would like to model the geodatabase so that non-ArcGIS applications can access the spatial table as a single table in SQL Server, and still allow ArcGIS applications to access the table using a separate feature class for each geometry type.

Does anyone have experience or suggestions on how to model this?

Thanks,

Jon.

[VinceAngelo]

In 18 years of database construction consulting, I've never seen this modeled
successfully. I have, unfortunately, seen several projects fail or get stop-work
orders while pursuing the "put lots of different things in one table then split
them back out with views" approach. It seems so elegant, until you try to
implement, and then the performance hit through views is so fearsome,
nothing ever goes right again.  When like things are stored with like, the
indexes are much more efficient, and you can even UNION ALL join to merge
when necessary (vice trying to edit through views).

BTW: You might want to steer clear of GEOGRAPHY if you have an international
dataset or any features that might cross the antemeridian (e.g. Alaska, Siberia,
Antarctica).   When the features are over a small enough area to make GEOGRAPHY
worthwhile, then storing in an Albers or Lambert projection will provide far less
computationally expensive access to geometric operators, and the maps look
better as well.

- V

[JonathanBailey]

Vince,

I agree with everything you've said ... it would be better to separate things out into different tables. However, these requirements are dictated by external factors and likely won't be changed due to the fact that ArcGIS is one of the clients that will be accessing the database. From a certain point of view, this approach does make sense -- the features in the single table only differ schematically in their geometry type -- all other attributes, semantics, relationships, etc. are largely the same. Also, I don't think that there's necessarily an issue with storing multiple geometry types in the same column, other than that ArcGIS doesn't play nicely with it.

There are a couple of other alternatives that we could consider, although I think that they're both extreme approaches. One approach **could** be to separate out the single table into various subtypes; if the taxonomy is broken down into sufficiently fine-grained chunks, then each type could be stored in its own table with its own (single) geometry type. However, this approach would result in hundreds of subtypes (thus, hundreds of different tables), and, as I indicated above, the subtypes are essentially identical in aspects other than geometry type.

Another approach would be to forego the geodatabase altogether (using the SQL Server database without geodatabase schema), but then we would lose out on important geodatabase features like versioning, archiving, and replication. And, we still have the problem of getting at datasets of different geometry types separately, either through views or query layers or a similar approach.

So, while I agree with you that it would be better to separate out the different geometry types, we won't be able to change this requirement. Given that, there are definitely some tradeoffs that need to be made, and I'd like to find a middle-ground solution, that, while perhaps not optimal, is at least reasonable.

If anyone has modeled or implemented something similar, I'd love to hear about it (including performance issues, as Vince has indicated that these are likely to be significant).

Thanks,

Jon.