Extract TopoJSON directly from PostGIS

Question

I have a simple "region" table: id (TEXT), name (TEXT), geom (MULTIPOLYGON). Here's the query I can use to extract a GeoJSON FeatureCollection:

SELECT row_to_json(fc)
 FROM (
   SELECT 'FeatureCollection' AS type, 
     array_to_json(array_agg(f)) AS features
   FROM (
     SELECT 'Feature' AS type, 
       ST_AsGeoJSON(region.geom)::json AS geometry, 
       row_to_json((SELECT l FROM (SELECT id, name) AS l
   )
 ) AS properties
FROM region) AS f) AS fc;

Is there any equivalent query that can be used to extra TopoJSON instead? I'm aware of AsTopoJSON but no idea how to use.

Sadly it is quite complicated. http://postgis.net/docs/AsTopoJSON.html has an example though, have you tried it? — bugmenot123, Jul 28 '15 at 12:54
Unfortunately I was confused by that example literally from the first line :(. I'm thinking I'll just use the topojson package within node.js to transform the GeoJSON once I have retrieved it from PostGIS using the above query. — rgareth4, Jul 29 '15 at 07:13
@rgareth4, I can refer you to "Outputting and consuming TopoJSON" from the "Chapter 7: PostGIS – Creating Simple WebGIS Applications" in Dominik Mikiewicz & Michal Mackiewicz & Tomasz Nycz. (2017). Mastering PostGIS. They describe preparing a new table with topo geometry based on the original geometry imported from a NaturalEarth shapefile. Then they generate a TopoJSON file. — Yaroslav, Apr 16 '21 at 15:11

score 1 · Answer 1 · answered Apr 19 '23 at 00:10

I think this way of converting GeoJSON output to TopoJSON using the ST_AsTopoJSON function in PostGIS might help (note that this function is only available starting with PostGIS 3.1). Here's an example of how you can modify your original query to generate TopoJSON output:

WITH geojson AS (
    SELECT row_to_json(fc) AS gj
    FROM (
        SELECT 'FeatureCollection' AS type,
            array_to_json(array_agg(f)) AS features
        FROM (
            SELECT 'Feature' AS type,
                ST_AsGeoJSON(region.geom)::json AS geometry,
                row_to_json((SELECT l FROM (SELECT id, name) AS l)) AS properties
            FROM region
        ) AS f
    ) AS fc
)
SELECT ST_AsTopoJSON(gj) AS topojson
FROM geojson;

Note that the resulting TopoJSON will not be as optimized as the one generated by a dedicated tool such as topojson command-line utility or the topojson-server npm package.

Extract TopoJSON directly from PostGIS

1 Answers1