Word Play with Spatial SQL   Checkout our PostGIS in Action book 2nd Edition. First chapter is a free download

Postgres OnLine Journal

Blog View    Periodical View   PostGIS in Action   About the Authors   Consulting



Sunday, April 21. 2013


Word Play with Spatial SQL

Printer Friendly
Recommended Books: PostGIS in Action

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;
Posted by Leo Hsu and Regina Obe in contrib spotlight, postgis at 20:44 | Comment (1) | Trackbacks (0)

Trackbacks
Trackback specific URI for this entry

No Trackbacks


Comments
Display comments as (Linear | Threaded)

Nothing is as easy as it seems. Take the phrase "Dot you i's and cross your t's" The dot above an i must be coded as a separate path which is different from a donut hole like in the letter e. If a t is coded as two separate paths the geometries will intersect and not be valid. Both cases are handled easily in SVG.
The nasty coding is converting ellipses and bezier curves (very common in fonts/SVG) to WKT geometries.
#1 Bruce on 2013-04-22 11:04 (Reply)

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA
 
 

Entry's Links

Quicksearch

Calendar

Back December '24 Forward
Mon Tue Wed Thu Fri Sat Sun
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          

Categories

All categories

Archives

Subscribe

  • XML

Blog Administration

Open login screen