After messing around quite a while, I was finally able to get a topogeometry converted from my geometry table following the example of toTopoGeom. I have the topology extension, with a topology schema (SRID = 5367) and a table in the public schema with a topogeometry column.

However, after running a couple of minutes I quit the query because it would take too long. Now I have around 4000 rows in the topogeometry table and only ~10% converted.

Then I wanted to convert the topogeometry to a topojson following the example of the AsTopoJSON. It works ok, but it takes 7 seconds to generate 4000 rows, which is really slow in my opinion.

So the question is; is this a common problem and is there an alternative approach to speed things up?

used code

  1. Insert topogeometry from geometry:

    INSERT INTO grid_topo(topo)
    SELECT topology.toTopoGeom(geom, 'grid_topo', 1)
    FROM grid
    WHERE gid >= 0 AND id < 500;

(added the condition to limit the running time).

  1. Generate TopoJSON from topogeometry:

    SELECT AsTopoJSON(topo, null) from grid_topo
  • It might help to see the query you're using and to know what coordinate system your geometries are stored in. For example, whether your table geometries are stored in a geographic or a cartesian coordinate system might make a difference. – elrobis Feb 12 '16 at 15:55
  • Also--it might help to disable AutoVaccuum while you execute the query. – elrobis Feb 12 '16 at 15:58
  • Using ALTER TABLE grid_topo SET ( autovacuum_enabled = true, toast.autovacuum_enabled = true ); – API Feb 15 '16 at 14:47
  • During inserting data: With auto-vacuum = true it takes 24 seconds for 1000 rows/ With auto-vacuum = false it takes 22 seconds for 1000 rows. For generating topojson's it is not relevant if both vacuum is true or false; it takes 1.6s for 1000 rows – API Feb 15 '16 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.