How do I view more than 1000 rows in spatial results pane using geometry spatial data in Sql server 2008

Question

I want to show complete spatial results.  When I execute a query with spatial data, geography based spatial data returns all rows as points, but using the same data as geometry will show only up to 1000 rows.  All rows are returned in the query results pane in either case.

How do I show more than 1000 rows in query spatial results pane using geometry spatial data?

Answer 1

AFAIK, the Spatial Results Tab will show up to 5,000 objects of either the geometry or geography datatype, which is a hard-coded value that cannot be changed. However, if your tab is only displaying 1,000 results, it's possible that this limit is reduced further based on available system resources. Do you get the "Too many spatial objects to display" message at the top of the tab? What edition of SQL Server are you running?

If you need to display more individual results than this then you'll have to export your data and display it using dedicated reporting software (or, your own application).

The Spatial Results tab in SSMS was never intended as an analytical/reporting tool - it's meant as a quick method of visualising results so that you can see what's going on, so I guess there are only relatively few occasions when you'd ever need to exceed the limit. Unfortunately, maybe you've found one - what is it that you're trying to do?

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 2

Thank you,
I do not get the "Too many spatial objects to display" message.  I am running SQL Server 2008 Developer edtion.

I am running it on a 64 bit Vista system with 4g or ram.

The problem is interesting.  Even when I request 1050 rows, the spatial viewer will not display the "map" using geometry.  What actually happens: an animated circle indicating process execution appears for a long time with no display -- a long time can be 30 minutes, b4 I close the query.

It is not a problem to "plot" data as lat,lon and attribute points with color or size fro representation.


My main goal is to use spatial referencing as a basis for spatial data mininig with demographic profiles and health outcomes using database to supply preprocessed result to GIS tool, rather than creating GIS based spatial queries or repeated "offline" analysis in a stats tool.

I have the book you linked -- very helpful

Answer 3

I'm glad that you're finding my book helpful ;)

Interesting that you're not getting the spatial results tab to display anything at all - this sounds like an bug to me, as there's no way that it should take anything like that long  to render (I'd say 30 seconds is too long, let alone 30 minutes)
You should try reporting it through MS Connect (https://connect.microsoft.com/) - the spatial team are normally pretty responsive at investigating.

Can you reproduce the bug on a fresh database? Or, can you post the schema of your table and the query that you are running and I'll try replicating it myself.

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 4

If I use a geopgraphy spatial reference all rows are shown in spatial results pane as lon,lat points exactly as expected.  When geometry is used I can only display <= 1000 polygons.  The maximum number of polygons is 4788; the number of block groups in the state og Georgia, US.  The geometry was created for block group ESRI shapefile uisng Shape2Sql, whereas, the geography was created using your text file importation method with block group centroid lon,lat as geography.

I am not using a schema, just a single table that I select rows from in a query.

I will followup with MS connect link

Thank you so much.

P.S. Your book is very useful.  As a medical geographer, I appreciate your extensive treatment of shperical geometry early in the book.  Most DBA's I have worked with have no clue about those topics.  My main reason for exploring this now is to create some examples to teach DBA's about spatial relations within data.  A topic for another book would be how to use analysis services to perform spatial correlation analyis and spatial temporal series -- very important for disease surveillance.

Answer 5

Ok, I get it now.  The number of spatial elements for polygons includes the number of verticies; such that, if there is a limit of 20K spatial elements that can be displayed (in RSR2), then small areal polygons (e.g., census block groups), which can be many in large states and have a large number of complicated vertices, may not be "fully" rendered.  There must be then a means to calculate the number of spatial elements in the form: number of polygons * number of vertices.