[postgis-users] 3D topology nodes?

[John Morrison]

Hello Sandro;

Thanks again for the help.

Re: profiling - My own application program (obviously running in its
own process, communicating with postgres via the usual SQL queries)
seems to be largely idle.  "top" and other system tools show the
postgres daemon utilizing a large fraction of CPU.

Is there some "howto" that can tell me how to rebuild postgis in such
a way as to get useful, hard, quantitative profiling data?

As to showing how I am building the topology - I am using some
decidedly non-standard tools - the PostModern Common Lisp SQL library
running under 64-bit SBCL.  I am happy to send you the program, but it
might be more trouble than it is worth to you.

The short version of my current, naive, dumb-as-a-box-of-rocks
approach:

(1) I load a 36MBpixel SRTM into PostGIS raster via raster2pgsql (the
non-python loader), tiling (for now) into about 3700-ish 100x100 pixel
tiles.

(2) Pixel-by-pixel reading of the raster values from PostGIS seemed to
be too slow, as did both row-at-a-time reading via cross joins with a
column-sequence, and also entire-tile-at-once reading via cross joins
with row/column sequences.  I expect this is a common operation, so I
had my first "I must be doing something stupid" (but what?) moment.
So, I grabbed each tile's raw bits using PostGIS' internal raster rep,
and decoded them into arrays - this takes about a minute for all
3700-ish tiles.

(3) I then walk the a tile's array, inserting topology nodes, then
edges, then faces to make a triangulated height map (I guess you'd
call it).  This seems to take about 10 minutes of wall clock time per
tile (and I have 3700 or so).

This is where I had my second "I must be going about this entirely the
wrong way" moment.  Hence my post.  Advice on strategy and/or
profiling would be greatly appreciated.  I presume I need to move the
processing closer to the data (i.e., on the postgres end of the
database connection).  I figure this must fall into a well-understood
class of problems which yield to an equally well-understood approach
(e.g., SQL program-ish queries that operate by side-effect from the
point of view of my application, or some SQL-ish language
plug-in/extension).

Again, apologies if these questions are startlingly naive, and also
thanks for pointing me in the right direction.

-jm


On Monday 04 June 2012, Sandro Santilli wrote:
> On Fri, Jun 01, 2012 at 08:18:02PM -0400, John Morrison wrote:
> > (1) Performance (or lack thereof): I am attempting to create topology
> > out of a rather large (36MPixel) SRTM elevation raster.  I am
> > currently using a very small tile as the processing times I am seeing
> > suggest using a calendar rather than a stopwatch to profile my code.
> > Is there some obvious thing I should be doing to speed things up?
> > Perhaps something analogous to how raster2pgsql emits an SQL file
> > rather than doing a pixel-at-a-time update?  Or perhaps I am going
> > about this entirely the wrong way?  (I had similar performance
> > problems in simply reading the raster out of PostGIS, but that was
> > more straightforward to work around...)
> 
> You're probably doing nothing wrong, and topology building is known
> to be slow. Showing how you did it would give us more informations,
> but again, we know it's slow. Your help in profiling might help
> speeding things up. Patches are also very welcome.
> 
> > (2) Are there any "gotchas" of which I should be aware due to "no
> > proper 3D topology support?"  False co-planarity issues?  Limitations
> > to modeling road overpasses, etc?
> 
> No road overpass, no vertical lines, false co-planarity.
> 
> --strk;
> 
>   ,------o-.
> 
>   |   __/  |    Delivering high quality PostGIS 2.0 !
>   |  
>   |  / 2.0 |    http://strk.keybit.net - http://vizzuality.com
> 
>   `-o------'
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 
--- John Morrison
--- john.nmi.morrison at gmail.com