From b335f520c359c5a9f8e349e3544ec999d85b9935 Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Tue, 19 Mar 2013 18:29:38 +0000 Subject: [PATCH] Add AsTopoJSON(TopoGeometry) function (#2228) git-svn-id: http://svn.osgeo.org/postgis/trunk@11183 b70326c6-7e19-0410-871a-916f4a2858ee --- NEWS | 1 + doc/extras_topology.xml | 98 ++++++++++++++++ topology/sql/export/TopoJSON.sql.in | 171 ++++++++++++++++++++++++++++ topology/test/Makefile.in | 1 + topology/test/regress/topojson.sql | 63 ++++++++++ topology/topology.sql.in | 3 +- 6 files changed, 336 insertions(+), 1 deletion(-) create mode 100644 topology/sql/export/TopoJSON.sql.in create mode 100644 topology/test/regress/topojson.sql diff --git a/NEWS b/NEWS index f7f64057a..36e047629 100644 --- a/NEWS +++ b/NEWS @@ -70,6 +70,7 @@ PostGIS 2.1.0 - #2210, ST_MinConvexHull(raster) - lwgeom_from_geojson in liblwgeom (Sandro Santilli / Vizzuality) - #1687, ST_Simplify for TopoGeometry (Sandro Santilli / Vizzuality) + - #2228, TopoJSON output for TopoGeometry (Sandro Santilli / Vizzuality) * Enhancements * - #823, tiger geocoder: Make loader_generate_script download portion diff --git a/doc/extras_topology.xml b/doc/extras_topology.xml index 2b41c4da5..78ccb3458 100644 --- a/doc/extras_topology.xml +++ b/doc/extras_topology.xml @@ -3120,5 +3120,103 @@ CREATE TABLE visited ( , + + + AsTopoJSON + + Returns the TopoJSON representation of a topogeometry. + + + + + + text AsTopoJSON + topogeometry tg + regclass edgeMapTable + + + + + + Description + + Returns the TopoJSON representation of a topogeometry. If edgeMapTable is not null, it will be used as a lookup/storage mapping of edge identifiers to arc indices. This is to be able to allow for a compact "arcs" array in the final document. + + + +The table, if given, is expected to have an "arc_id" field of type "serial" and an "edge_id" of type integer; the code will query the table for "edge_id" so it is recommended to add an index on that field. + + + +A full TopoJSON document will be need to contain, in addition to the snippets returned by this function, the actual arcs plus some headers. See the TopoJSON specification. + + + + Availability: 2.1.0 + + + + + + See Also + + + + + Examples + +CREATE TEMP TABLE edgemap(arc_id serial, edge_id int unique); + +-- header +SELECT '{ "type": "Topology", "transform": { "scale": [1,1], "translate": [0,0] }, "objects": {'; + +-- objects +SELECT '"' || feature_name || '": ' || AsTopoJSON(feature, 'edgemap') +FROM features.land_parcels; + +-- arcs +SELECT '}, "arcs": [' + UNION ALL +SELECT (regexp_matches(ST_AsGEOJSON(ST_SnapToGrid(e.geom,1)), '\[.*\]'))[1] as t +FROM edgemap m, city_data.edge e WHERE e.edge_id = m.edge_id; + +-- footer +SELECT ']}'::text as t + +-- Result: +{ "type": "Topology", "transform": { "scale": [1,1], "translate": [0,0] }, "objects": { +"P1": { "type": "Polygon", "arcs": [[-1,-2,2,3,4,-6]]} +"P2": { "type": "Polygon", "arcs": [[-7,-8,0,5,8,-10]]} +"P3": { "type": "Polygon", "arcs": [[-11,-12,6,9,12,-14]]} +"P4": { "type": "Polygon", "arcs": [[-15]]} +"P5": { "type": "Polygon", "arcs": [[-16][16]]} +"F3": { "type": "Polygon", "arcs": [[4,-6,-18,3]]} +"F6": { "type": "Polygon", "arcs": [[17,-1,-2,2]]} +"F3F4": { "type": "Polygon", "arcs": [[4,8,-10,18,-18,3]]} +"F1": { "type": "Polygon", "arcs": [[-16][16]]} +}, "arcs": [ +[[21,6],[21,14]] +[[9,6],[21,6]] +[[9,6],[9,14]] +[[9,14],[9,22]] +[[9,22],[21,22]] +[[21,14],[21,22]] +[[35,6],[35,14]] +[[21,6],[35,6]] +[[21,22],[35,22]] +[[35,14],[35,22]] +[[47,6],[47,14]] +[[35,6],[47,6]] +[[35,22],[47,22]] +[[47,14],[47,22]] +[[25,30],[31,30],[31,40],[17,40],[17,30],[25,30]] +[[8,30],[16,30],[16,38],[3,38],[3,30],[8,30]] +[[4,31],[7,31],[7,34],[4,34],[4,31]] +[[9,14],[21,14]] +[[35,14],[21,14]] +]} + + + diff --git a/topology/sql/export/TopoJSON.sql.in b/topology/sql/export/TopoJSON.sql.in new file mode 100644 index 000000000..f1cd1637e --- /dev/null +++ b/topology/sql/export/TopoJSON.sql.in @@ -0,0 +1,171 @@ +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- +-- PostGIS - Spatial Types for PostgreSQL +-- http://postgis.refractions.net +-- +-- Copyright (C) 2013 Sandro Santilli +-- +-- This is free software; you can redistribute and/or modify it under +-- the terms of the GNU General Public Licence. See the COPYING file. +-- +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- +-- Functions used for TopoJSON export +-- +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +--{ +-- +-- API FUNCTION +-- +-- text AsTopoJSON(TopoGeometry, edgeMapTable) +-- +-- }{ +CREATE OR REPLACE FUNCTION topology.AsTopoJSON(tg topology.TopoGeometry, edgeMapTable regclass) + RETURNS text AS +$$ +DECLARE + visited bool; + toponame text; + json text; + sql text; + bounds GEOMETRY; + rec RECORD; + rec2 RECORD; + side int; + arcid int; + arcs int[]; +BEGIN + + IF tg IS NULL THEN + RETURN NULL; + END IF; + + -- Get topology name (for subsequent queries) + SELECT name FROM topology.topology into toponame + WHERE id = tg.topology_id; + + -- Puntual TopoGeometry + IF tg.type = 1 THEN + -- TODO: implement scale ? + --json := ST_AsGeoJSON(topology.Geometry(tg)); + --return json; + RAISE EXCEPTION 'TopoJSON export does not support puntual objects'; + ELSIF tg.type = 2 THEN -- lineal + + FOR rec IN SELECT (ST_Dump(topology.Geometry(tg))).geom + LOOP -- { + + sql := 'SELECT e.*, ST_Line_Locate_Point(' + || quote_literal(rec.geom::text) + || ', ST_Line_Interpolate_Point(e.geom, 0.2)) as pos' + || ', ST_Line_Locate_Point(' + || quote_literal(rec.geom::text) + || ', ST_Line_Interpolate_Point(e.geom, 0.8)) as pos2 FROM ' + || quote_ident(toponame) + || '.edge e WHERE ST_Covers(' + || quote_literal(rec.geom::text) + || ', e.geom) ORDER BY pos'; + -- TODO: add relation to the conditional, to reduce load ? + FOR rec2 IN EXECUTE sql + LOOP -- { + + IF edgeMapTable IS NOT NULL THEN + sql := 'SELECT arc_id-1 FROM ' || edgeMapTable::text || ' WHERE edge_id = ' || rec2.edge_id; + EXECUTE sql INTO arcid; + IF arcid IS NULL THEN + EXECUTE 'INSERT INTO ' || edgeMapTable::text + || '(edge_id) VALUES (' || rec2.edge_id || ') RETURNING arc_id-1' + INTO arcid; + END IF; + ELSE + arcid := rec2.edge_id; + END IF; + + -- edge goes in opposite direction + IF rec2.pos2 < rec2.pos THEN + arcid := -(arcid+1); + END IF; + + arcs := arcs || arcid; + + END LOOP; -- } + END LOOP; -- } + + json := '{ "type": "LineString", "arcs": [' || array_to_string(arcs,',') || ']}'; + + return json; + + ELSIF tg.type = 3 THEN -- areal + + json := '{ "type": "Polygon", "arcs": ['; + + FOR rec IN SELECT (ST_DumpRings((ST_Dump(ST_ForceRHR( + topology.Geometry(tg)))).geom)).geom + LOOP -- { + + arcs := NULL; + bounds = ST_Boundary(rec.geom); + + sql := 'SELECT e.*, ST_Line_Locate_Point(' + || quote_literal(bounds::text) + || ', ST_Line_Interpolate_Point(e.geom, 0.2)) as pos' + || ', ST_Line_Locate_Point(' + || quote_literal(bounds::text) + || ', ST_Line_Interpolate_Point(e.geom, 0.8)) as pos2 FROM ' + || quote_ident(toponame) + || '.edge e WHERE ST_Covers(' + || quote_literal(bounds::text) + || ', e.geom) ORDER BY pos'; + + -- RAISE DEBUG 'SQL: %', sql; + + FOR rec2 IN EXECUTE sql + LOOP + + IF edgeMapTable IS NOT NULL THEN + sql := 'SELECT arc_id-1 FROM ' || edgeMapTable::text || ' WHERE edge_id = ' || rec2.edge_id; + EXECUTE sql INTO arcid; + IF arcid IS NULL THEN + EXECUTE 'INSERT INTO ' || edgeMapTable::text + || '(edge_id) VALUES (' || rec2.edge_id || ') RETURNING arc_id-1' + INTO arcid; + END IF; + ELSE + arcid := rec2.edge_id; + END IF; + + -- RAISE DEBUG 'Arc id: %' , arcid; + + -- edge goes in same direction + IF rec2.pos2 < rec2.pos THEN + arcid := -(arcid+1); + END IF; + + arcs := arcs || arcid; + + END LOOP; + + --RAISE DEBUG 'Ring arcs: %' , arcs; + + json := json || '[' || array_to_string(arcs,',') || ']'; + + --RAISE DEBUG 'JSON : %' , json; + + END LOOP; -- } + + json := json || ']}'; + RETURN json; + + ELSIF tg.type = 4 THEN -- collection + RAISE EXCEPTION 'Collection TopoGeometries are not supported by AsTopoJSON'; + + END IF; + + + RETURN json; + +END +$$ LANGUAGE 'plpgsql' VOLATILE; -- writes into visited table +-- } AsTopoJSON(TopoGeometry, visited_table) + diff --git a/topology/test/Makefile.in b/topology/test/Makefile.in index bcf81598a..28e2e11ba 100644 --- a/topology/test/Makefile.in +++ b/topology/test/Makefile.in @@ -51,6 +51,7 @@ TESTS = regress/legacy_validate.sql regress/legacy_predicate.sql \ regress/topogeo_addpoint.sql \ regress/topogeo_addpolygon.sql \ regress/topogeometry_type.sql \ + regress/topojson.sql \ regress/topo2.5d.sql \ regress/totopogeom.sql \ regress/droptopology.sql \ diff --git a/topology/test/regress/topojson.sql b/topology/test/regress/topojson.sql new file mode 100644 index 000000000..167d5b209 --- /dev/null +++ b/topology/test/regress/topojson.sql @@ -0,0 +1,63 @@ +set client_min_messages to WARNING; + +\i load_topology.sql +\i load_features.sql +\i hierarchy.sql + +--- Lineal non-hierarchical +SELECT feature_name||'-vanilla', topology.AsTopoJSON(feature) + FROM features.city_streets + WHERE feature_name IN ('R3', 'R4', 'R1', 'R2' ) + ORDER BY feature_name; + +--- Lineal hierarchical +SELECT feature_name||'-vanilla', topology.AsTopoJSON(feature) + FROM features.big_streets + WHERE feature_name IN ('R4', 'R1R2' ) + ORDER BY feature_name; + +--- Areal non-hierarchical +SELECT feature_name||'-vanilla', topology.AsTopoJSON(feature) + FROM features.land_parcels + WHERE feature_name IN ('P1', 'P2', 'P3', 'P4', 'P5' ) + ORDER BY feature_name; + +--- Areal hierarchical +SELECT feature_name||'-vanilla', topology.AsTopoJSON(feature) + FROM features.big_parcels + WHERE feature_name IN ('P1P2', 'P3P4') + ORDER BY feature_name; + +-- Now again with edge mapping { +CREATE TEMP TABLE edgemap (arc_id serial, edge_id int unique); + +--- Lineal non-hierarchical +SELECT feature_name||'-vanilla', topology.AsTopoJSON(feature, 'edgemap') + FROM features.city_streets + WHERE feature_name IN ('R3', 'R4', 'R1', 'R2' ) + ORDER BY feature_name; + +--- Lineal hierarchical +SELECT feature_name||'-vanilla', topology.AsTopoJSON(feature, 'edgemap') + FROM features.big_streets + WHERE feature_name IN ('R4', 'R1R2' ) + ORDER BY feature_name; + +--- Areal non-hierarchical +SELECT feature_name||'-vanilla', topology.AsTopoJSON(feature, 'edgemap') + FROM features.land_parcels + WHERE feature_name IN ('P1', 'P2', 'P3', 'P4', 'P5' ) + ORDER BY feature_name; + +--- Areal hierarchical +SELECT feature_name||'-vanilla', topology.AsTopoJSON(feature, 'edgemap') + FROM features.big_parcels + WHERE feature_name IN ('P1P2', 'P3P4') + ORDER BY feature_name; + +DROP TABLE edgemap; +-- End edge mapping } + + +SELECT topology.DropTopology('city_data'); +DROP SCHEMA features CASCADE; diff --git a/topology/topology.sql.in b/topology/topology.sql.in index 94b81f66f..ca351a743 100644 --- a/topology/topology.sql.in +++ b/topology/topology.sql.in @@ -1987,8 +1987,9 @@ LANGUAGE 'plpgsql' VOLATILE STRICT; #include "sql/topogeometry/simplify.sql.in" #include "sql/topogeometry/totopogeom.sql.in" --- GML +-- Exports #include "sql/export/gml.sql.in" +#include "sql/export/TopoJSON.sql.in" --=} POSTGIS-SPECIFIC block -- 2.40.0