[postgis-users] creating topology data is slow

[Lars Aksel Opsahl]

Hi

Yes it becomes slower.

When I run 1 thread we start with out about 150 rows pr second, 
but after 18 mill. edges we are down to about half the performance 

With 15 threads we start out with adding 1600 edges pr. second and here also we end with about the half the
performance after 18 mill edges. 

When adding edges that is covers by many cells the performance goes down a lot but I have not had any time to look at this because this is not a big issue since the total time is down to 16 hours.

More info at http://www.slideshare.net/laopsahl/foss4-g-topologyjuly162015

Lars

________________________________________
Fra: postgis-users-bounces at lists.osgeo.org [postgis-users-bounces at lists.osgeo.org] på vegne av Marc-André Goderre [magoderre at cgq.qc.ca]
Sendt: 4. juni 2015 17:21
Til: PostGIS Users Discussion
Emne: Re: [postgis-users] creating topology data is slow

Does the processing became slower when the edge_data table became bigger?

Marc

-----Message d'origine-----
De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Lars Aksel Opsahl
Envoyé : 3 juin 2015 01:56
À : PostGIS Users Discussion
Objet : Re: [postgis-users] creating topology data is slow

We are working quite big datasets (about 1 billion points and 20 million lines) that we convert from simple feature to topology. I ran a test yesterday and we added these 20 million lines in about 9 hours with attached attributes. This means heavy parallel pressing and grid usage on a fast database, but we use the Postgis topology package mostly as it is (picked from trunk about a moth ago) .

We will give a talk about how we do this at foss4g 2015 in Como in July.

Lars

________________________________
Fra: postgis-users-bounces at lists.osgeo.org [postgis-users-bounces at lists.osgeo.org] på vegne av Rémi Cura [remi.cura at gmail.com]
Sendt: 2. juni 2015 21:33
Til: PostGIS Users Discussion
Emne: Re: [postgis-users] creating topology data is slow

I'm working on a batch way to import topology, it is not ready yet.

If you now for sure your data is correct, you can skip a lot of testing and be efficient in a batch way.

You could use grass and the topological export.

Cheers,
Rémi-C

2015-06-02 19:25 GMT+02:00 Marc-André Goderre <magoderre at cgq.qc.ca<mailto:magoderre at cgq.qc.ca>>:
Hello all,
I'm creating topology data from almost all world road OSM data ( More than 77 Million ways).
Processing the data  with postgis topology in one shot is so long than that I created a hexagonal grid that let me create topological data from only one or many cell of the grid.
Even this way, It' s going to take many month to process the world's data.
Is there a way to make it faster?
This is my function that I use to process data.

CREATE OR REPLACE FUNCTION cm_update_tile_data(tile_id integer)
  RETURNS character varying AS
$BODY$
DECLARE r RECORD;

BEGIN
  FOR r IN SELECT * FROM way, join world_grid  on line&& the_geom where gid=tile_id and topo_geom is NULL LOOP
    BEGIN
      UPDATE way
SET topo_geom = topology.toTopoGeom(st_force2d(line), 'way_topo', 1)
      WHERE id = r.id<http://r.id>;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE WARNING 'Loading of record % failed: %', r.id<http://r.id>, SQLERRM;
    END;
  END LOOP;
  UPDATE world_grid set last_update = now() where gid=tile_id;
  return 'OK';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Thanks

Marc


_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users