Geoquery 2008

Using the data from the Shape file example, a variety of queries can be constructed. The examples below use the new CAST and CONVERT features of SQL Server 2008 to 'format' HEX characters for colorizing the results.

Australian states by population

/* Shade states by population (solid green is highest) */
DECLARE @max FLOAT       -- Find the highest value
SELECT  @max = MAX(pop_admin) 
FROM    admin00 where gmi_cntry = 'AUS'
SELECT        -- view all
  admin_name
, geog
, '#' + CONVERT (
  VARCHAR(MAX)
  , CONVERT (VARBINARY(MAX), 
-- min saturation will be 15,  not 0
-- max saturation will be 215, not 255
    CAST (ROUND ((pop_admin/@max * 200)+15, 0) AS TINYINT) )
  , 2) + '00FF00' AS [Fill]    
, '#8800FF00' AS [Color]       
, '0.15' AS [Thickness]
FROM admin00 where gmi_cntry = 'AUS'
ORDER BY pop_admin DESC

USA states by population

Simply alter the AUS in the above query to any other country code in the admin00 table to view the relevant statistics. Once again Alaska looks overstated in 'flat' projections..
Shape tab

but it looks a little better in the Sphere tab

World states/provinces by population

Removing the WHERE clause completing shows the population concentrations across the world, so you can see within China & India where the majority of the population lives. Without some sort of logarithmic scale, most other 'states/provinces' pale in comparison; only California, Texas and England seem to register...

/* Shade administrative zones by population */
DECLARE @max FLOAT       -- Find the highest value
SELECT  @max = MAX(pop_admin) FROM admin00 
SELECT      -- View all
  admin_name
, cntry_name
, pop_admin
, geog
, '#' + CONVERT (
  VARCHAR(MAX)
  , CONVERT (VARBINARY(MAX), 
    CAST (ROUND ((pop_admin/@max * 240)+15, 0) AS TINYINT) )
  , 2) + '00FF00' AS [Fill]    
, '#8800FF00' AS [Color]       
, '0.15' AS [Thickness]
FROM admin00 
ORDER BY pop_admin DESC

Map tab

Grid tab