Geoquery 2008
March 2008
The Geo in Geoquery is for GEOmetry and GEOgraphy - the two new types in SQL Server 2008 (code-name 'Katmai').
Geoquery Analyzer is now currently available for download!
Geoquery2008_v0.72.zip (427Kb) contains three files and a folder of cached map images:
Unzip these files onto a PC with:
to visualize spatial queries (both GEOMETRY and GEOGRAPHY datatypes) using the SQL Server 2008 (Katmai) November CTP.
Release History (see also the ConceptBlog)
| 5-Jan-08 | 0.5 beta | Initial public release, supports GEOMETRY data ONLY |
| 16-Jan-08 | 0.6 beta | First release of GEOGRAPHY data handling, with basic Export function |
| 29-Jan-08 | 0.7 beta |
- MULTIPLE result sets are now supported, so you can visualize the results of many queries without annoying UNION ALL statements.
- Better Map tab pan/zoom control (still lots to do though)
- Configurable map images (via MapSources.xml file), including Moon and Mars (just for fun)
- NEW Sphere tab to visualize GEOGRAPHY results on a globe
- Examples list expanded to include GEOGRAPHY MSDN sample queries (although many are too small to 'see' the results yet)
|
| 31-Jan-08 | 0.71 beta |
- FIX GEOGRAPHY POLYGON rendering on the Earth's curvature (but they still break across +/-180°)
|
| 4-Mar-08 | 0.72 beta |
- Fixes line and polygon rendering across +/-180 longitude
- Adds two new items to Image Source list including a local copy of Blue Marble so you can play with the map rendering without being connected to the Internet
- Font increase/decrease (useful when doing demos!)
- Zoom added to Shape tab (but doesn't quite work)
Known issues include: shape & map zooming, boundary calculations, shape display and many more...
|
Using Geoquery 2008
The different parts of the Geoquery window are described below (reflects v0.5 window):
The Connect Window
You must connect to a database (supplying authentication information that will be stored in the .config file)
Running spatial queries
|
Once you have connected to a SQL Server 2008 instance and selected a database from the drop-down list,
simply type your query and Execute it (Ctrl+E, Ctrl+R or F5). The Grid tab will show the output of the
query, the Messages tab will display any SQL Server output and the Shape tab will
display any GEOMETRY datatype columns (note that GEOGRAPHY is not currently supported).
Note the query in this screenshot uses the dbo.UnionAggregate() function discussed here
and on my blog.
|
Using the MSDN Examples
The
Examples menu lists the various functions available against the GEOMETRY datatype. It is generated from the
Tutorial.xml file in the
download zip, so you can add/edit the examples yourself.
 |
|
STUnion
When you select an example, the relevant query is populated, along with the link to the MSDN documentation for that function (as an SQL comment).
Note: Geoquery recognises the following special columns:
[Display] - the Tooltip for the shape, also appears in the Status bar when the shape is hovered over
[Color] - the color of the shape line
[Fill] - the color of the shape's fill
[Thickness] - the thickness of the shape line
|
|
BufferWithTolerance
Some example queries have been expanded from the basic MSDN example to better explain (IMO) the concept being demonstrated
|
|
STTouches, showing MSDN documentation
When Show MSDN Page is depressed, an additional tab appears, which is loaded with MSDN documentation when another example is selected.
This causes a slight delay versus using the examples without the MSDN tab showing (caused by the page loading).
|
Geography (map!) queries
It's now possible to perform visually interesting (if not appealing :) GEOGRAPHY-related
queries - using the [Color] alpha-channel on result-sets you know will overlap creates a nice effect,
even better if you use point size to represent another axis of your data.
The query below is (attempting to) show the count of votes at postal-code centroids, using color to delineate different states -- it's easy to see where Australia's populatin is concentrated!
SELECT x.*, p.Location
FROM Postcodes P ,(
select p.postcode, SUM(Totalvotes) as Votes, -- p.location,
CASE
WHEN p.Postcode < 2000 THEN '#44ff0000'
WHEN p.Postcode < 3000 THEN '#4400ff00'
WHEN p.Postcode < 4000 THEN '#440000ff'
WHEN p.Postcode < 5000 THEN '#44880088'
WHEN p.Postcode < 7000 THEN '#44FFFF00'
ELSE '#44DDDDDD'
END AS [Color]
, CASE WHEN SUM(Totalvotes) < 1000 THEN 0.25
WHEN SUM(Totalvotes) < 2000 THEN 0.5
WHEN SUM(Totalvotes) < 3000 THEN 1
WHEN SUM(Totalvotes) < 4000 THEN 2
WHEN SUM(Totalvotes) < 5000 THEN 3.5
ELSE 6 END AS [Thickness]
from Postcodes P
INNER JOIN PollingPlaces pp ON pp.Postcode = p.Postcode
INNER JOIN PollingPlaceVotes ppv ON ppv.PollingPlaceID = pp.PollingPlaceID
GROUP BY P.Postcode) x
WHERE X.Postcode = P.Postcode
Exporting query results (CSV, Shapes and Maps!)
Version 0.6 now includes an Export... toolbar button
These are the images exported from some of the Tutorial/MSDN Sample queries: BufferWithTolerance, STEnvelope, STArea, STEndPoint.
New in 0.7/0.71
View GEOGRAPHY results on a sphere - 'straight lines' now make sense!
Pan using left-mouse-drag, zoom using right-mouse-drag OR wheel.
Mulitple result sets each have their own 'grid' (only the first grid is currently saved as CSV via Export...)
Different map backgrounds can be selected, including the NASA Blue Marble tiles
