none
Generating SVG from WKT

    Discussion générale

  • 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

    mardi 10 avril 2012 13:12

Toutes les réponses