You can do all sorts of cool things with the spatial data support in SQL Anywhere 12. One of the engineering team shared this function with me.
This function creates a grid, much like a sheet of graph paper, with x and y co-ordinates defined in the function call.
DROP FUNCTION IF EXISTS F_GraphPaper; CREATE FUNCTION F_GraphPaper( xmin INT DEFAULT 0, xmax INT DEFAULT 100, ymin INT DEFAULT 0, ymax INT DEFAULT 100 ) RETURNS ST_Geometry BEGIN DECLARE @ret ST_MultiLineString; SELECT ST_MultiLineString::ST_MultiLineStringAggr( ls ) INTO @ret FROM ( SELECT Ry.row_num y, NEW ST_LineString( NEW ST_Point( 0, y ), NEW ST_Point( 100, y ) ) ls FROM sa_rowgenerator(ymin,ymax,10) Ry UNION ALL SELECT Ry.row_num x, NEW ST_LineString( NEW ST_Point( x, 0 ), NEW ST_Point( x, 100 ) ) ls FROM sa_rowgenerator(xmin,xmax,10) Ry ) DT; RETURN @ret; END;
How can we use this? Well, you can use the function to display geometries on the graph paper. The following example creates a function that places the provided geometry on the grid and returns an SVG representation of the result.
DROP FUNCTION IF EXISTS F_OnGraphPaper; CREATE FUNCTION F_OnGraphPaper( geo ST_Geometry, xmin INT DEFAULT 0, xmax INT DEFAULT 100, ymin INT DEFAULT 0, ymax INT DEFAULT 100 ) RETURNS XML BEGIN DECLARE @ret XML; SELECT ST_Geometry::ST_AsSVGAggr( geo, attr ) INTO @ret FROM ( SELECT 'attribute=stroke="lightgrey" stroke-width="0.1"' attr, F_GraphPaper(xmin,xmax,ymin,ymax) geo UNION ALL SELECT 'attribute=fill="lightblue"', geo ) D2; RETURN @ret; END;
For example, the following select statement returns an SVG document (which you can display in DBISQL, or in your web browser) which overlays a triangle on the grid. The resulting image as displayed in DBISQL is shown below.
SELECT F_OnGraphPaper(NEW ST_Polygon( 'Polygon(( 0 0, 0 20, 60 10, 0 0 ))' ))
You might be thinking, ‘That’s cool, but where would I ever use this?’. Well, you could use this as the basis for building a simple heatmap or density map. For example, you might draw a map on the grid, and color each grid square according to a business statistic, like revenue, number of customers, population, etc… In future posts, we will leverage these procedures to demonstrate how we can use spatial data to build useful reports.