locked
Points to States RRS feed

  • Question

  • Hi

    I have a dataset which contains Point data and I want to reflect "by state" information on the "USA by State Exploded" installed map in SSRS 2008 R2. Specifically, I have a list of customers that are only identified by their GPS coordinates and I want to show how many customers I have in each state by varying the color of each state's polygon (i.e. a Color Analytical Map). The wizard does not seem to support field matching based on spatial information.

    Is there any way I can find out which state each row belongs to by checking its containership within the state polygons defined in the map? Is there a better/alternative way to achieve this?

    Also, my dataset contains a lot of information. Is there any way I can work out the point-state relationship, and therefore aggregate by state before I even get to SSRS, to improve performance?

    I'm interested in answers to both approaches if possible.

    Thanks

    Russ


    Monday, July 18, 2011 11:53 AM

Answers

  • Am I correct in saying that the States table in your example is not built in to SQL Server, but rather it is a table that I would have to create myself?

    That's absolutely correct. As far as I'm aware, the state outlines provided with the SSRS map control are there for use in SSRS only - there is no way to port them back into the database. Fortunately, there are plenty of sources of country outline data available, and it sounds like you've already found at least one. 

    The former has only 202 points but the latter has 4165, although they look very similar when displayed in the Spatial results tab.

    All spatial data is an approximation. Any given feature may be approximated at many different resolutions, which are suitable for different purposes. When displaying spatial data (as is the case with the outlines in SSRS), you generally want simple shapes with relatively few points, so they are quick to render. When you're doing precise calculations in the database layer, you probably want more detailed shapes using more points.

    Regarding the query performance, take a look at the execution plan (Query -> Include Actual Execution Plan in SSMS). Do you see a Clustered Index Seek (Spatial) in there anywhere? If not, try modifying the query as follows:

    SELECT
      Customer.*
      ,fe_2007_us_state.*
    FROM
      Customer
    INNER JOIN
      fe_2007_us_state WITH(index(NAME_OF_THE_SPATIAL_INDEX_ON_THE_STATES_TABLE))
    ON
      Customer.Location.STIntersects(fe_2007_us_state.geom) = 1

     



    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, July 20, 2011 1:21 PM
    Answerer

All replies

  • I've not had any responses to my question above, so I'm wondering if the wording of it is unnecessarily complicated?

    To put it another way:

    If I only have GPS coordinates, what are my options for calculating aggregates per state?

    Wednesday, July 20, 2011 7:30 AM
  • I think perhaps you threw people off the scent with mention of SSRS field matching etc., whereas really this is just a regular spatial database query (which you happen to then want to take the results from and display them on the SSRS map control). Something like this:

    SELECT

      StateName,

      COUNT(CustomerId)

    FROM States JOIN Customers ON Customers.Location.STIntersects(State.Shape) = 1

    GROUP BY StateName

    This will give you a list as follows:

    Alabame  23

    Delaware 100

    ...

    Washington 20

     

    Which you can then use as a SSRS datasource to bing to the map control.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, July 20, 2011 8:02 AM
    Answerer
  • I think perhaps you threw people off the scent with mention of SSRS field matching etc., whereas really this is just a regular spatial database query (which you happen to then want to take the results from and display them on the SSRS map control).

    Thanks very much for taking the time to reply tanoshimi.

    I'm new to the spatial data types in SQL Server, and the reason for relating my question to SSRS was simply that SSRS includes maps that contain state information. I therefore thought that this would save me the trouble of finding a state shapefile. I found it so easy to plot my coordinates on an SSRS state map, that it seemed like an obvious next step to be able to aggregate the data.

    Am I correct in saying that the States table in your example is not built in to SQL Server, but rather it is a table that I would have to create myself? I had reached a similar conclusion and have used Morten Neilsen's Shape2SQL tool to create a table from http://www2.census.gov/geo/tiger/TIGER2007FE/fe_2007_us_state.zip.

    I just compared the shape of Massachusetts in SSRS and my new table and they are quite different. The former has only 202 points but the latter has 4165, although they look very similar when displayed in the Spatial results tab. I am slightly concerned about using one shape to calculate state membership and then another shape to display the data. I could manually extract all the Polygon information from SSRS and use that to build my table, but I wondered if that information was already available somewhere else in SQL Server?

    My customer table only has 72 records in it and I've built a spatial index on its geography column, which only contains the Point sub-type, using the following command:

    CREATE SPATIAL INDEX CustomerLocationSpatialIndex ON Customer (Location)

    I just used the basic syntax because I haven't spent any time investigating the more advanced options. My state table also has a spatial index with the same options, but that was added by the Shape2SQL tool.

    The State table contains 56 states, with an average of 11,889 points in their shape definition.

    I wrote a query very similar to your query above:

    SELECT
      Customer.*
      ,fe_2007_us_state.*
    FROM
      Customer
    INNER JOIN
      fe_2007_us_state
    ON
      Customer.Location.STIntersects(fe_2007_us_state.geom) = 1

    This query takes 1 minute to run, which is far worse performance than I was expecting. I'm only joining a 72 row table with a 56 row table, so I guess using the STIntersects method in the join must be the problem. I tried swapping the ON clause (i.e. fe_2007_us_state.geom.STIntersects(Customer.Location) = 1) but the performance (and execution plan) were identical.

    The SQL Server Help states that "Under certain conditions, spatial indexes support the following set-oriented geography methods: STIntersects(), STEquals(), and STDistance()", but it does not elaborate on what these conditions are. I know very little about reading query execution plans, but I can't see any evidence of my query using the spatial indexes. Am I doing something wrong?

    Is it worth trying to replace the STIntersects method with other methods that sound like they might do the same thing, such as STContains, STOverlaps, STTouches or STWithin? I notice that these methods only exist for the geometry sub-type and not the geography sub-type, so perhaps it would be incorrect to use them?


    Wednesday, July 20, 2011 11:26 AM
  • Am I correct in saying that the States table in your example is not built in to SQL Server, but rather it is a table that I would have to create myself?

    That's absolutely correct. As far as I'm aware, the state outlines provided with the SSRS map control are there for use in SSRS only - there is no way to port them back into the database. Fortunately, there are plenty of sources of country outline data available, and it sounds like you've already found at least one. 

    The former has only 202 points but the latter has 4165, although they look very similar when displayed in the Spatial results tab.

    All spatial data is an approximation. Any given feature may be approximated at many different resolutions, which are suitable for different purposes. When displaying spatial data (as is the case with the outlines in SSRS), you generally want simple shapes with relatively few points, so they are quick to render. When you're doing precise calculations in the database layer, you probably want more detailed shapes using more points.

    Regarding the query performance, take a look at the execution plan (Query -> Include Actual Execution Plan in SSMS). Do you see a Clustered Index Seek (Spatial) in there anywhere? If not, try modifying the query as follows:

    SELECT
      Customer.*
      ,fe_2007_us_state.*
    FROM
      Customer
    INNER JOIN
      fe_2007_us_state WITH(index(NAME_OF_THE_SPATIAL_INDEX_ON_THE_STATES_TABLE))
    ON
      Customer.Location.STIntersects(fe_2007_us_state.geom) = 1

     



    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, July 20, 2011 1:21 PM
    Answerer
  • Regarding the query performance, take a look at the execution plan (Query -> Include Actual Execution Plan in SSMS). Do you see a Clustered Index Seek (Spatial) in there anywhere? If not, try modifying the query as follows:

    SELECT
      Customer.*
      ,fe_2007_us_state.*
    FROM
      Customer
    INNER JOIN
      fe_2007_us_state WITH(index(NAME_OF_THE_SPATIAL_INDEX_ON_THE_STATES_TABLE))
    ON
      Customer.Location.STIntersects(fe_2007_us_state.geom) = 1

    Thanks again for all the extremely useful info. It's been very helpful to me and has given me confidence that I'm on the right path.

    With regards to the execution plan, it definitely wasn't showing a Clustered Index Seek (Spatial). Do you have any idea why that would be the case? Anyway, after implementing your suggestion, it now does list one (at 8% cost) and it also shows a call to a table-valued-function called GetGeographyTessellation_VarBinary (at a cost of 46%). Most importantly, the query only takes 4 seconds now. Brilliant! Thanks again.

    Wednesday, July 20, 2011 1:50 PM
  • There's a lonnnnggggggg story about spatial indexes in SQL Server, the need to provide HINTs etc., and plenty of posts on this forum about it.

    Your query plan probably *looks* a lot more complicated now, but explicitly forcing the query to use the spatial index by providing the HINT general results in better performance. And, if I were you, I'd just leave it at that ;)


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, July 20, 2011 2:00 PM
    Answerer
  • And, if I were you, I'd just leave it at that ;)

    I certainly will! Thanks very much again.
    Wednesday, July 20, 2011 2:03 PM
  • Sorry for the delay in marking your response as an answer. I didn't realise the protocol.
    Wednesday, July 20, 2011 6:11 PM
  • Hey - no worries! And glad I could help.
    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, July 20, 2011 7:10 PM
    Answerer