Generating SVG from WKT
-
Tuesday, April 10, 2012 1:12 PM
I recently generated SVG (Scalable Vector Graphics) from the WKT (Well Known Text) representation of MULTIPOLYGONs. This was done in order to view some selected zip code areas and allow for further editing in vector based graphics programs. Seeing as it was not all that difficult to achieve I thought it may be of interest to others as well. Below is an example of how it was done for the zip code areas, but I expect that not much modification would have to be made in order for it to work with other types of areas as well. It should provide a good starting point for anyone who needs to produce SVG from WKT using SQL.
---------------------------------------- Generate SVG ------------------------------------ declare @xOffset varchar(42), @yOffset varchar(42); select @xOffset = CAST(-AVG(ZipCodeArea.STCentroid().STX) as DECIMAL(19,10)), @yOffset = CAST(-AVG(ZipCodeArea.STCentroid().STY) as DECIMAL(19,10)) from MyZipCodeAreaTable where left(ZipCode, 2) in ('41', '42', '43', '44'); declare @header varchar(max) = '<?xml version="1.0" encoding="utf-8"?>' + '<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">' + '<svg xmlns="http://www.w3.org/2000/svg" version="1.1">' + '<g id="viewport" transform="translate(' + @xOffset + ', ' + @yOffset + ')">' + '<g stroke="black" fill-rule="evenodd">'; declare @middler varchar(max) = '</g>' + '<g font-family="sans-serif" fill="black" text-anchor="middle" text-align="center" vertical-align="middle">'; declare @footer varchar(max) = '</g>' + '</g>' + '</svg>'; declare @areas xml = ( select ZipCode as [@id], case ZipCode % 9 when 0 then '#99CC33' when 1 then '#66CC33' when 2 then '#339900' when 3 then '#336600' when 4 then '#FF6600' when 5 then '#CC9900' when 6 then '#FFCC33' when 7 then '#FF9933' when 8 then '#FFCC66' end as [@fill], REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ZipCodeArea.STAsText(), 'MULTIPOLYGON (((', 'M'), ')), ((', ' Z M'), '), (', ' Z M'), ', ', ' L'), ')))', ' Z') as [@d] from MyZipCodeAreaTable where left(ZipCode, 2) in ('41', '42', '43', '44') for xml path ('path') ); declare @labels xml = ( select cast(ZipCodeArea.STCentroid().STX as decimal(28,12)) as [@x], cast(ZipCodeArea.STCentroid().STY as decimal(28,12)) as [@y], cast(cast(case when sqrt(ZipCodeArea.STArea())/5 < 10 then 10 else sqrt(ZipCodeArea.STArea())/5 end as int) as varchar(42)) + 'pt' as [@font-size], cast(ZipCode as char(5)) from MyZipCodeAreaTable where left(ZipCode, 2) in ('41', '42', '43', '44') for xml path ('text') ); select @header + cast(@areas as varchar(max)) + @middler + cast(@labels as varchar(max)) + @footer;
Lars Rönnbäck -- http://www.anchormodeling.com -- An Agile Modeling Technique for Evolving Information
- Changed Type amber zhangModerator Wednesday, April 18, 2012 1:44 AM
All Replies
-
Wednesday, April 18, 2012 1:49 AMModerator
Hi Lasa,
Thank you for sharing with us. That is quite a good starting point and useful.Regards, Amber zhang