[GRASS5] Re: Feature-Request: Prompt for PostGIS-connection

[Radim Blazek]

On Thursday 06 November 2003 00:32, Martin Pokorny wrote:
> >> 	d.vect roads was really slow, but.... * PostGIS-Server is not on
> >> 	the same maschine G57 is running, but inside a 10Mbit-LAN *
> >> 	PostGIS-Server is 500Mhz-server Maybe this is a bottleneck?
> >
> > No the problem is in GRASS, 1) GRASS uses random access (read line
> > by line number), this is fast for native format but cannot be for
> > RDBMS, as it must send query for each line and that is slow
> > especially over network (send query and wait for respons for every
> > line). I don't know how to solve this. 2) Currently, to read one
> > line, GRASS performs more queries: - select type, NumPoints(),
> > Dimension() - BEGIN (because binary cursors must be in transaction,
> > IIRC) - DECLARE gcursor BINARY CURSOR FOR SELECT ASBINARY( "geom
> > FROM table WHERE id = ...") - FETCH ALL in gcursor - CLOSE gcursor -
> > COMMIT - SELECT field, cat FROM table WHERE id = ... this could be
> > reduced, probably return to WKT and parse results in GRASS, so from
> > 7 queries to 2, and PostGIS in GRASS will be faster in this ratio
> > (7/2) (?). It was my stupid idea that binary cursor is be faster ;)
> >
> > I am considering to either remove PostGIS support at all or do not
> > officially support in 5.8 (or how they call it).
>
> I've been working on this on and off for a few weekends now, and, in a
> prototype implementation, I've been able to speed up PostGIS use, at
> least in some cases. My benchmark has been to use v.in.ogr to import
> layers from a TIGER file directly to a postgres database (both
> attributes and geometry). Before my mods, importing the "pip" layer
> (points only) took about 24 minutes for 26303 points. I've been able
> to get the time down to around 2 1/2 minutes with various
> modifications to read_post.c, write_post.c, and open_post.c, together
> with the addition of some PL/pgSQL functions, and small mods to
> v.in.ogr/main.c.
>
> Other results are below. For reference, importing the CompleteChain
> layer alone previously took about 6 hours, now it's 11 minutes. The
> other example, I never had the patience to complete a test, but it
> would have been at least 6 hours, and now it's 25 minutes.
>
> I plan on doing more testing, but I need some advice, too (I haven't
> done much with GRASS programming before). One tactic I use to speed up
> reading is to buffer the results of a single query that encompasses
> many features at a time. However, I need to vary the size of the
> buffer depending on whether access is largely sequential (such as when
> the features are first imported) or random (such as when building
> topology). My approach appears to require two, somewhat global
> modifications: 1) an abstract buffer size (or "access pattern")
> setting function for all db types, and 2) use of this function in
> several modules, such as v.in.ogr and v.clean. One possibility is to
> set the default buffer size assuming a random access pattern (i.e,
> small), and explicitly set the larger buffer only in the v.in.*
> modules, but there are others. I don't have a good picture of the
> access patterns yet, so it's hard for me to decide. Ideas, clues or
> hints, anyone?

It would be nice to compare also PostGIS contra native, 
perferably over the network for both. I think that it is better 
to do benchmarks without attributes (v.in.ogr -t)

What is your 'buffer'? Is it sequence of queries executed 
as one transaction? 
I think, that almost everything may be done as one transaction, 
e.g. whole v.in.ogr.

PL/pgSQL is something what I want to avoid. PL/pgSQL means that another
step must be done before GRASS can be used with PostGIS. 
What I realy worry about is, that once we use PL/pgSQL functions, it must be 
compatible for ever.

Speed is not the only problem of PostGIS in GRASS!:
1) Vect_write_line, Vect_rewrite_line, Vect_delete_line are missing 
   for level 2.
   BTW!, you can run v.in.ogr (with cleaning) or v.clean on PostGIS vectors?
   It should be impossible!
2) No tests were done for simultaneous editing. There are certainly many 
   potential problems in this area.

Are there another reasons to use PostGIS in GRASS except multiuser simultaneous 
write access? I don't think so.

Can you put your modifications somewhere on the Web?

As I said, I don't plan to spend any time on this before 5.7.0. 


Radim