[postgis-devel] postgis-devel Digest, Vol 124, Issue 9

Fri Jul 19 05:41:42 PDT 2013

Kleber 0 e a outra metade da população de São
Em 18/07/2013 16:01, <postgis-devel-request at lists.osgeo.org> escreveu:

> Send postgis-devel mailing list submissions to
>         postgis-devel at lists.osgeo.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>         http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
> or, via email, send a message with subject or body 'help' to
>         postgis-devel-request at lists.osgeo.org
>
> You can reach the person managing the list at
>         postgis-devel-owner at lists.osgeo.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-devel digest..."
>
>
> Today's Topics:
>
>    1. Re: creating topology (Sandro Santilli)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Wed, 17 Jul 2013 22:46:34 +0200
> From: Sandro Santilli <strk at keybit.net>
> To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> Subject: Re: [postgis-devel] creating topology
> Message-ID: <20130717204634.GB3543 at gnash>
> Content-Type: text/plain; charset=us-ascii
>
> Hi Paul,
> sorry for the late answer, I hope you still have your use case around.
> Replies inline.
>
> On Thu, Jun 27, 2013 at 03:24:08PM +0200, Paul Goldstein wrote:
>
> > I'm creating a topology of about 70k of geometry-segments. The
> > smallest of them is just a few meters, the longest is about 10 km
> > long.
>
> [...]
>
> > # part 1 #
> > With help of strk in postgis-irc (thanks again!) it took about 3
> > hours. It was made by divide all geometries in chunks by their id. One
> > chunk were 1000 pieces.
>
> I've seen that chunks of 500 pieces were faster, did you try 100 ?
>
> [...]
>
> > # part 2 #
> > I tried to switch off autovacuum (autovacuum_enabled = false) for all
> > tables in topology-scheme and for my segment-table. This was a massive
> > speed-improvement cause it took "only" 2 hours.
> > But strangely there was a knee after 42k of updates where speed
> > raises. - I don't know why.
>
> Maybe indices started being used, for some reason ? Hard to tell.
>
> Topology building leaves a lot of dead tuples around, so possibly
> a vacuum every now and then helps. Could you try running a
> vacuum analyze after 40k updates ?
>
> It's also curious how speed decreases before 40k and before 60k
>
>
> > # part 4 #
> > strk asked me to measure another idea: creating chunks not on their id
> > instead use their geographic coordinates. so one part is e.g. (0 0, 1
> > 1) -> (1 0, 2 1) -> (2 0, 3 1) ---> (0 1, 1 2) -> (1 1, 2 2) -> (2 1,
> > 3 2) ---> ... - i hope its clear what i meant.
> > thats my appropriate statement:
> >
> > update public.segment
> >   set topogeom = topology.totopogeom(st_force_2d(geom), 'segment_topo',
> 1, 0)
> >   where (st_intersects(geom, st_envelope(st_geomfromewkt(?))) = true
> >     or st_contains(geom, st_envelope(st_geomfromewkt(?))) = true)
> >   and topogeom is null;
> >
> > where ? is this: "srid=4326;linestring(%f %f,%f %f)"
> >
> > This took very too long. I aborted it after 1,5 hours, because it was
> too slow.
>
> Pity, your fastest run lasted 2 hours...
> For sure the curve seems more "regular" :)
>
> > Now my questions:
> > Does anybody know how to improve the speed to create a topology?
>
> You already found a couple of good ways, those would be worth noting
> somewhere (postgis wiki would be a good place). If you still have that
> dataset I'd be curious to see the curve with smaller chunks, then with
> manual VACUUM ANALYZE added and maybe finally with geometries ordered
> by number of vertices (fewer vertices first).
>
> > Or is it possible to not creating my topology every night if something
> > changes in geometry. Instead alter topology in an
> > after-statement-trigger when something happens with geometries.
>
> You can surely call toTopoGeom again with the modified geometry.
> With PostGIS-2.1 the function also accepts the old TopoGeometry to
> effectively replace it (rather than leaving an orphaned TopoGeometry
> around). You'll still need to "manually" remove primitives (edges,
> nodes) that are not used anymore. They won't hurt, but with time they
> tend to slow things down.
>
> > strk said when totopogeom() is called TopoGeo_addLinestring() is
> > entered many times. Is it possible to improve that function? (Maybe
> > writing it in C - instead of plpgsql :)
>
> Yes, writing that function in C may considerably speed things up, but
> there's currently nobody working on it. Contributions are welcome.
>
> > Here you can see some graphs for the different parts:
> > http://privatepaste.com/download/ef41296b0d
> > blue is part 1: chunks of ids
> > red is part 2: chunks of ids but without autovacuum
> > yellow is part 3: as red but 96MB of shared_buffer
> > green is part 4: chunks of squared geography elements
> >
> > Thanks for any help or suggestions,
>
> Thank you for the excellent report !
>
> --strk;
>
>  http://www.cartodb.com - Map, analyze and build applications with your
> data
>
>                                        ~~ http://strk.keybit.net
>
>
>
> ------------------------------
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel
>
>
> End of postgis-devel Digest, Vol 124, Issue 9
> *********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20130719/41179f47/attachment.html>