01-20-2019 04:16 AM
Does anyone aware if there are any guidelines or someone's experience in tuning / setting spatial database in MS SQL Server?
We make GIS projects for corporate customers (dozens, hundreds users, 100+ layers, dozens WMS, etc.).
We use GeoMedia WebMap + SDI + Geospatial Portal.
We'd like to achieve better performance for web customers.
01-21-2019 11:22 AM
Attached is an old copy of GMPSpatialUtilities - 'GeoMedia Spatial Data Utilities for SQL Server spatial data'.
Has not been maintained for years, but still works.
If your not familiar with creating SQL Server spatial indexes it can help. Please keep in mind it was written back in 2013/2014 so may not be right up to date with latest SQL Server spatial indexing optimization but should provide a start.
Provided as is were is.
Run the .sql file attached against a database to deploy it.
Then exec GMPCreateSpatialIndexes which will create any missing indexes. Can also use GMPDropSpatialIndexes prior to drop existing indexes if they need recreating.
Otherwise typical dba - database stuff
If still not fast enough could consider publishing from SQL Server spatial to GeoMedia SmartStore. You would then need to republish on a periodically basis and manage / maintain the SmartStores and processes. Can do that from a Windows task scheduler. I'm not sure of current figures, but about 5 years ago indicative figures was that SmartStore was around 30% faster than source database - dependent on a lot of environmental factors though. https://hexagongeospatial.fluidtopics.net/reader/BeGuPafzOAVuNDX2NE~ghg/SQkwlWFSTfNIw6NpHkygbw
01-21-2019 10:02 PM
Along with the spatial indexing plans, you can perform some other useful tasks that can affect the rendering of data over web:
a) Check for duplicate data and remove them.
b) Simplify geometry to reduce nodes for layers wherever you can.
c) You should use GeoMedia Desktop to create scale based rendering of your layers (It's very effective)
d) Partition your data and divide them in multiple databases in SQL server and do indexing for primary keys and columns you will extensively use.
e) Use WMTS against WMS
f) Keep WMPS specifically for querying