Word Play with Spatial SQL

In Happy Valentine PostGIS we demonstrated how to use PostGIS raster to decipher letters from a raster, vectorize them and then reuse this vectorized letters to form new words. Admittedly the letters were a bit grainy since they were vectorizations of low res rasters and I didn't bother smoothing them. Bruce Rindahl offered a script to SVG to PostGIS geometry and using Batik to convert a font file to SVG format and gave me a hi-res converted kankin fontset. I still haven't figured out how his script works.

Bborie Park thought that was all too complicated and thought (as I have always) that we need an ST_GeomFromSVG function for PostGIS of which he is on a mission to create when he's less busy. He also suggested I wrap my letter writer function as an extension. Taking all these ideas, I formulated an extension you install with

CREATE EXTENSION postgis_letters;

postgis_letters (http://www.bostongis.com/postgisstuff/postgis_letters_extension.zip) is an sql / data extension containing mostly data, but as the name suggests relying on PostGIS. The data are geometry vectors of the kankin font. I plan to add in more free fonts later once I figure out how to use Bruce's script or Bborie comes up with a better way and also more positioning logic and handling of spaces. So its a little rough at the moment. The purpose of the extension is so I can write words on my images in reports e.g. state names or overlay labels on geometry features like roads and land. Using the power of both geometry/raster you can have a fully functioning report image writer that would return a fully formed image for use in LibreOffice (or for my ASP.NET web apps Active Reports.NET). This wouldn't rely on any mapping server to draw images (just pure PostGIS/PostgreSQL). Hopefully with new and improved binary features coming in PSQL for (looks like 9.4), outputting these raster images from psql will also be trivial. While on my mission to do something useful, I got distracted by something more entertaining: describing spatial processes with words. Here it goes.

It gets more interesting when you use raster since you can colorize them, but wanted to make this short. For viewing the queries I just used OpenJump and it randomly picked a different color for each query. The examples below you'll notice I often use ST_Boundary to return just the linework so it doesn't cover up the letters. These examples use the default behavior of the ST_LettersAsGeometry function which is position at 0,0, height 100 units and SRID unknown. If you add additonal arguments you could for example position letters on a road that are 10 meters high.

Spatial SQLWord Image
WITH word 
AS (SELECT ST_LettersAsGeometry('Triangle', 'kankin') As geom )
  SELECT ST_Collect(geom
     , ST_DelaunayTriangles(geom,10,1) )
FROM word;
ST_DelaunayTriangles is new in 2.1 and requires GEOS 3.4
WITH word 
 AS (SELECT ST_LettersAsGeometry('Concave', 'kankin') As geom )
  SELECT ST_Collect(geom
     , ST_Boundary(ST_ConcaveHull(geom,0.99)) )
FROM word;
WITH word 
 AS (SELECT ST_LettersAsGeometry('Convex', 'kankin') As geom )
  SELECT ST_Collect(geom
    , ST_Boundary(ST_ConvexHull(geom)) )
FROM word;
WITH word 
 AS (SELECT ST_LettersAsGeometry('Flip', 'kankin') As geom )
SELECT geom
  FROM word
    UNION ALL 
  SELECT ST_FlipCoordinates(geom)
FROM word;
WITH word 
 AS (SELECT ST_LettersAsGeometry('Longest', 'kankin') As geom )
   SELECT geom
    FROM word
    UNION ALL 
       SELECT ST_LongestLine(geom,geom)
  FROM word;
WITH word 
 AS (SELECT ST_LettersAsGeometry('Rotate', 'kankin') As geom )
   SELECT geom
    FROM word
    UNION ALL 
       SELECT ST_Rotate(geom,pi()/4, ST_Centroid(geom))
  FROM word;
WITH word 
 AS (SELECT ST_LettersAsGeometry('MBC', 'kankin') As geom )
   SELECT ST_Collect(geom,
  ST_Boundary(ST_MinimumBoundingCircle(geom)))
  FROM word;