SqlServer 2008 provider

Topics: SharpMap v0.9 / v1.x, SharpMap v2.0
Editor
Oct 10, 2010 at 3:02 PM

Hi!

The current Sql2008 provider GetExtents method seems too slow. It iterated through the whole table, parses each individual geometry, creates a bounding box and joins them together (1 by 1) to generate the bounding box around all the data.

I propose to simplify and optimize this by utilizing sql server spatial methods (and indexes).

Instead of iterating through the table you can execute the following query:

select   min([column].STEnvelope().STPointN(1).STX)
           ,min([column].STEnvelope().STPointN(1).STY)
           ,max([column].STEnvelope().STPointN(3).STX)
           ,max([column].STEnvelope().STPointN(3).STY)
from [table]

This aggregates the data and returns the four points required to construct the bounding box.

I can submit updated code if needed.

Goran

Coordinator
Oct 11, 2010 at 6:51 AM

Hello Goran,

thanks for the proposal, a patchfile would be great.

cheers FObermaier

Editor
Oct 11, 2010 at 8:36 AM

Hi!

I did some more testing. Apparently using my method is slower. The performance gains I was getting stemmed from the fact my method was executing on the server while the current method is executed on the client (my dev laptop). 

See also: http://social.msdn.microsoft.com/forums/en-US/sqlspatial/thread/7fb96eb0-c075-4e3f-9173-20ce74af5b0b/

As far as I can tell spatial index is not used in STEnvelope method which results in slow performance.

One could still use spatial index by retrieving the information from sys.spatial_index_tessellations. You need to define an envelope when creating the spatial index but this information will not be accurate.

Oh well :)

Goran

Coordinator
Oct 11, 2010 at 9:56 AM

Hello Goran,

for the SpatiaLite provider, user TeDe proposed to store the extent in the provider so they do not have to be queried all the time. Since I don't know the SqlServer2008 provider that well, I ask you if that could be an option here, too?

cheers FObermaier

Editor
Oct 11, 2010 at 12:21 PM

Hi!

SqlServer2008 provider is based on Oracle provider. It is possible to add an extent property to store the data. This could be used as a cache or to store extent data you acquired elsewhere.

eg. I use the same extent for all my layers and it is stored in the db. When instantiating the provider I could set the property. Each time GetExtent is called it could first check to see if the property is already set and return that value or query the db and cache the results for future use. This property should be cleared whenever provider is modified.

However I think this change would necessitate a change in providers interface. There are other options I'd like to see included in the interface as well - are there any plans to extend the providers interface?

Goran

Coordinator
Oct 11, 2010 at 1:58 PM

Goran,

you are more than welcome to propose enhencements to the provider interface.

Methinks, the different (Db)providers should be brought in-line. If we do that, perhaps some provider base class (like in SharpMap v2.0) would fit in.

Of course everybody is invited to make suggestions.

Cheers FObermaier

Developer
Oct 11, 2010 at 2:37 PM

Hi Goran, 

Are you testing with SharpMap's original provider now? The Mapsui interface is much more limited.

Could the full extent of the data not be cached in a private member of the provider so that the interface can stay unchanged?

Paul

Editor
Oct 11, 2010 at 4:54 PM

Hi!

Yes, I'm using the MapsUI version but have been looking at the current SharpMap trunk. The extent could be cached in the private member but without access to the property users can't provide extent themselves so it can only be used as cache.

I think there's plenty of cases where users already have this information so it would make sense to enable them to set it themselves. Without the interface change users need to use reflection or casting to access the property.

Goran

Developer
Oct 12, 2010 at 9:58 AM
goransiska wrote:

I think there's plenty of cases where users already have this information so it would make sense to enable them to set it themselves. 

true. 

Perhaps it is also an option to put this override property on the Layer, keeping the dataproviders as simple as can be. 

btw. it is on my TODO list to put the GetExtent call also on a thread, just like data fetch itself.  It is my goal to put all the disk, web or db access a background thread.

Paul.

Editor
Oct 14, 2010 at 6:19 AM

Hi!

Adding a property to Layer still requires an interface change i think. I'm not quite sure if users expect Extent as something belonging to layer or provider.

Making providers work asynchronously would be great!

Goran