leveraging sql server to improve vector display through point clustering
DESCRIPTION
Presented at the 2011 Texas GIS ForumTRANSCRIPT
![Page 1: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/1.jpg)
Leveraging SQL Server to Improve Vector
Display through Point Clustering
![Page 2: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/2.jpg)
Due to Java Script limitation, Large Numbers of Vectors don’t draw in Web-Mapping Environments.
Cutoff is ~ 300-500 Features
The Problem…
![Page 3: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/3.jpg)
Current ways to solve… 1. Use Silverlight or Flash
Increases capacity roughly an order of magnitude, but similar limitation exists.
2. Show Pictures, Query Features
Has the capability to show and query ALL features for a pleasant user experience, but takes time to build tile cash (the pretty pictures).
However, Tile Caches are Large and are, basically, redundant storage.
3. Use a WMS to feed ‘pictures’ dynamically and then use a separate, spatial query for feature attributes.
Extra ‘moving’ parts in the solution.
Possibly extra cost in licenses.
![Page 4: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/4.jpg)
Original Design… Texas Hydrologic Information System (TexasHIS) www.waterdatafortexas.org Client desires immediate access to vector data
and attributes
Data Characteristics Lots of tightly packed ‘Sites’ ~ 300K+ data with gobs of related attribute data ~
5,000,000+ related records!
Why Invent a ‘Better’ Solution???
![Page 5: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/5.jpg)
Factors Affecting a Good ‘Clustering’ Solution…
The Data Density
Distribution
The Scale Large – Basically Draw everything
‘Medium’ – Draw some, Cluster some – Lots of Code and Logic!
Small – Basically Cluster everything
Logic Thresholds/Limits of when to Cluster vs. When to Draw
How to Carve the display to deal with Data Distribution
Complex Algorithms to determine the optimal number of features to draw.
![Page 6: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/6.jpg)
Create a ‘smart’ solution that…
Isn’t affected by the java script limit
Only queries the database one time (other solutions can query the database for image creation and attribute retrieval.
Is still capable of retrieving vector attributes for single features
![Page 7: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/7.jpg)
Solution Basics…
1. Drawing Thresholds
When to Draw everything
When to Cluster
When to take no action
2. Carving The Display
At first glance, a 16 x 16 ‘Grid’ would be best. It would yield 256 Cluster ‘Features’
3. Clustering Logic
Draw the Singles
Cluster Everything Else
![Page 8: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/8.jpg)
Solution Basics: Thresholds…
1. Drawing Thresholds Draw Everything – if the total number
of Points to ‘cluster’ is below 500
Cluster – If there are 500 (Lower Limit) to 5000 (Upper Limit)
Above the Upper Limit – Take no action. The Clustering Query becomes too expensive. So much so that user experience is drastically diminished.
![Page 9: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/9.jpg)
Solution Basics: Carving up the Display and Applying the Logic Rules…
Spatial Indexes are Similar in concept
16 x 16 creates 256 Cells – Near the upper limit of our display threshold
Polygons with more than 1 Site are ‘Cluster Candidates’. The Centroid of the polygon will represent the cluster spatially.
Threshold values are variables so that they are easily ‘tuned’ in a series of runs.
![Page 10: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/10.jpg)
Solution Basics: How it works…
The Envelope of the Display is passed as a parameter
First Function, a Scalar-Valued Function, determines how ‘big’ the cells will be in the X and Y direction – Returns a comma separated number pair – “0.3456778,0.3456777”
Second Function, a Table-Valued Function, returns a table of polygon cells that are ‘built’ from the output of the SVF and the starting point of the Display Envelope. A ‘cutter’ variable is used to build-out polygons with @var_Y rows and @var_X columns.
The results of these functions are then used to 0perate on the ‘Sites’ point dataset.
![Page 11: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/11.jpg)
X
Y
![Page 12: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/12.jpg)
![Page 13: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/13.jpg)
At Long Last…
DEMO!!!
![Page 14: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/14.jpg)
Further Improvements…
Add logic to always draw ‘up-to’ the lower limit.
Possibly add some ‘weight’ to the clustered point so that it doesn’t show in such a ‘regular’ way.
Others…
![Page 15: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/15.jpg)
TNRIS Information Services…
Richard Wade – Team Lead
Ryan Mitchell – Web Systems and Data Czar
Yvette Giraud – Web Development
Ragunath Jayabalakrishnan – Developer (Contract)
Chris Williams – Database Administrator
![Page 16: Leveraging sql server to improve vector display through point clustering](https://reader034.vdocuments.us/reader034/viewer/2022042715/557dcc5cd8b42ae4688b4a4d/html5/thumbnails/16.jpg)
www.foursquare.com