Sybase iAnywhere SQL AAnywhere Mobile and Embedded Database

Rows and Columns


SQL Anywhere perspective on technology

header image

SQL Graph Paper

February 16th, 2011 · No Comments

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 ))' ))
SQL Graph Paper Result

SQL Graph Paper Result

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.

Be Sociable, Share!

Tags: Development · Spatial · SQL Anywhere