[GRASS-dev] About v.distance, v.what.vect (wrt "count points within...").

Nikos_Alexandris1 · August 13, 2010, 5:24am

On Thursday 12 of August 2010 18:58:39 Moritz Lennert wrote:

On 12/08/10 16:50, Moritz Lennert wrote:
> I don't have time right now, but I'll look into adding the index
> creation into v.db.dropcol, if you confirm that this is the problem.

Committed to grass6_devbranch as rev 43068.

Candidate for 6.4.1.

For grass7, I think the change should be:

--- v.db.dropcolumn.py 2010-08-12 18:48:03.000000000 +0200
+++ /home/mlennert/v.db.dropcolumn.py 2010-08-12 18:47:29.000000000 +0200
@@ -102,6 +102,7 @@
             "DROP TABLE ${table}",
             "CREATE TABLE ${table}(${coldef})",
             "INSERT INTO ${table} SELECT ${colnames} FROM ${table}_backup",
+ "CREATE UNIQUE INDEX ${table}_cat ON ${table ( ${keycol} )"

+ "CREATE UNIQUE INDEX ${table}_cat ON ${table ( ${keycol} )" ,
#missing comma?

             "DROP TABLE ${table}_backup",
             "COMMIT"
             ]

But I'll have to update my svn tree first and see if it works, before
committing anything. If anyone wants to go ahead, please feel free.

Something is wrong... :
--%<---
v.db.dropcolumn pareto_classification_V___pareto_classification_500m_1
column=value

Traceback (most recent call last):
  File "/geo/osgeo/src/grass_trunk/dist.x86_64-unknown-linux-
gnu/scripts/v.db.dropcolumn", line 123, in <module>
    main()
  File "/geo/osgeo/src/grass_trunk/dist.x86_64-unknown-linux-
gnu/scripts/v.db.dropcolumn", line 110, in main
    sql = tmpl.substitute(table = table, coldef = coltypes, colnames =
colnames)
  File "/usr/lib/python2.6/string.py", line 172, in substitute
    return self.pattern.sub(convert, self.template)
  File "/usr/lib/python2.6/string.py", line 162, in convert
    val = mapping[named]
KeyError: 'keycol'
-->%---

Nikos

martinl · August 13, 2010, 6:14am

Hi,

2010/8/13 Nikos Alexandris <nikos.alexandris@felis.uni-freiburg.de>:

--- v.db.dropcolumn.py 2010-08-12 18:48:03.000000000 +0200
+++ /home/mlennert/v.db.dropcolumn.py 2010-08-12 18:47:29.000000000 +0200
@@ -102,6 +102,7 @@
"DROP TABLE ${table}",
"CREATE TABLE ${table}(${coldef})",
"INSERT INTO ${table} SELECT ${colnames} FROM ${table}_backup",
+ "CREATE UNIQUE INDEX ${table}_cat ON ${table ( ${keycol} )"

+ "CREATE UNIQUE INDEX ${table}_cat ON ${table ( ${keycol} )" ,
#missing comma?
         &quot;DROP TABLE $\{table\}\_backup&quot;,
         &quot;COMMIT&quot;
         \]
sql = tmpl.substitute(table = table, coldef = coltypes, colnames =
colnames)

change to

sql = tmpl.substitute(table = table, coldef = coltypes, colnames =
colnames, keycol = keycol)

Martin

--
Martin Landa <landa.martin gmail.com> * http://gama.fsv.cvut.cz/~landa

Nikos_Alexandris1 · August 13, 2010, 6:47am

Moritz L:

>> --- v.db.dropcolumn.py 2010-08-12 18:48:03.000000000 +0200
>> +++ /home/mlennert/v.db.dropcolumn.py 2010-08-12 18:47:29.000000000
>> +0200 @@ -102,6 +102,7 @@
>> "DROP TABLE ${table}",
>> "CREATE TABLE ${table}(${coldef})",
>> "INSERT INTO ${table} SELECT ${colnames} FROM
>> ${table}_backup", + "CREATE UNIQUE INDEX ${table}_cat ON
>> ${table ( ${keycol} )"

Nikos A:

> + "CREATE UNIQUE INDEX ${table}_cat ON ${table ( ${keycol}
> )" , #missing comma?
>
>> "DROP TABLE ${table}_backup",
>> "COMMIT"
>> ]

> sql = tmpl.substitute(table = table, coldef = coltypes, colnames =
> colnames)

Martin L:

change to

sql = tmpl.substitute(table = table, coldef = coltypes, colnames =
colnames, keycol = keycol)

Everything works fine now in grass_trunk, that is:

a. "v.db.dropcolumn" on its own
b. "v.distance" is fast even after dropping a column (=index is there)

Nikos (happy user today)

Nikos_Alexandris1 · August 13, 2010, 6:53am

Nikos A:

> time v.what.vect --v vector=pareto_ref_points___pareto_ref_30m
> qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m_
> __ ON___classification_V___classification_500m_1 column=gridcell_1
> qcolumn=cat Finding nearest lines...

1. Before:

> Finding nearest areas...
> ^C
> real 317m9.500s
> user 0m12.230s

2. Skipping the v.db.dropcol step I get:

292317 records updated
real 0m47.338s <~ !^1000
user 0m23.100s

3. After latest changes, that is without skipping the "v.db.dropcolumn" in
grass7, the same command:

292317 records updated
real 0m54.029s
user 0m25.830s

Just a (small) bit slower than before.

Nikos

Nikos_Alexandris1 · August 13, 2010, 7:00am

On Saturday 07 of August 2010 09:16:20 Nikos Alexandris wrote:

Just something more,

my intention to ask an outsider is purely aiming to take-off some work-load
of the grass-dev core team. Solely that. And of course, this in case it is
really (a.) a problem and (b.) it is known and the question is just about
who is going to spend time on it.

Not of interesed anymore!

Nikos

mlennert · August 13, 2010, 7:24am

On 13/08/10 08:47, Nikos Alexandris wrote:

Moritz L:

--- v.db.dropcolumn.py 2010-08-12 18:48:03.000000000 +0200
+++ /home/mlennert/v.db.dropcolumn.py 2010-08-12 18:47:29.000000000
+0200 @@ -102,6 +102,7 @@
              "DROP TABLE ${table}",
              "CREATE TABLE ${table}(${coldef})",
              "INSERT INTO ${table} SELECT ${colnames} FROM
${table}_backup", + "CREATE UNIQUE INDEX ${table}_cat ON
${table ( ${keycol} )"

Nikos A:

+ "CREATE UNIQUE INDEX ${table}_cat ON ${table ( ${keycol}
)" , #missing comma?

              "DROP TABLE ${table}_backup",
              "COMMIT"
              ]

    sql = tmpl.substitute(table = table, coldef = coltypes, colnames =
colnames)

Martin L:

change to

sql = tmpl.substitute(table = table, coldef = coltypes, colnames =
colnames, keycol = keycol)

Everything works fine now in grass_trunk, that is:

a. "v.db.dropcolumn" on its own
b. "v.distance" is fast even after dropping a column (=index is there)

Nikos (happy user today)

Committed in trunk as 43083.

Moritz

mlennert · August 13, 2010, 8:25am

On 12/08/10 16:51, Markus Metz wrote:

Start from scratch:

I've put a new module in grass-addons, called v.vect.stats. It counts
points in polygons, fairly fast, but, as the name implies, can do a
bit more. Optionally, it calculates statistics for areas based on the
attributes of all points falling into an area. Currently supported
methods are sum, average, median, mode, min, min_cat, max, max_cat,
range, stddev, variance, diversity. The meaning of min_cat and max_cat
is the category value corresponding to the observed minimum/maximum
value, e.g. the hospital with the largest number of beds and not the
largest number of beds.

The module is meant as the companion to v.rast.stats, update area
attributes from raster: v.vect.stats, update area attributes from
vector

Have fun,

Markus M

PS: No entry on wiki because I am not sure what will happen with the
module. If it's regarded nonsense, it will be removed again. OTOH, it
might end up in trunk.

No nonsense in my eyes. Very nice and useful little module.

In terms of usage time, the parts that take most time with large files (the 600,000 points and 100x100 grid used before) are the creation of the spatial index and (if a stats method is applied) the collecting of attributes from the points vector (at one point we will probably have to attack db driver efficiency issues in GRASS... example: do we really have to create a select cursor and loop through every line this returns ?). The actual point-in-areas is very fast !

A few small questions/remarks:

- Any special reason why you construct a whole SQL statement to add the columns, instead of using db_add_column() (defined in lib/db/dbmi_client/c_add_col.c) ?

- Maybe add a key to point_column_opt to call it point_column (instead of the default "column")

- If one sets a method and a stats_column, but no point_column, the first two settings are simply ignored. Maybe a fatal error would be better (I took me a bit of time to understand that I had forgotten the point_column.

No time for more, but I really think this would be a useful addition to trunk...

Moritz

Markus_GRASS · August 13, 2010, 8:52am

Moritz Lennert wrote:

Markus Metz wrote:

Start from scratch:

I've put a new module in grass-addons, called v.vect.stats. It counts
points in polygons, fairly fast, but, as the name implies, can do a
bit more. Optionally, it calculates statistics for areas based on the
attributes of all points falling into an area. Currently supported
methods are sum, average, median, mode, min, min_cat, max, max_cat,
range, stddev, variance, diversity. The meaning of min_cat and max_cat
is the category value corresponding to the observed minimum/maximum
value, e.g. the hospital with the largest number of beds and not the
largest number of beds.

The module is meant as the companion to v.rast.stats, update area
attributes from raster: v.vect.stats, update area attributes from
vector

Have fun,

Markus M

PS: No entry on wiki because I am not sure what will happen with the
module. If it's regarded nonsense, it will be removed again. OTOH, it
might end up in trunk.

No nonsense in my eyes. Very nice and useful little module.

In terms of usage time, the parts that take most time with large files (the
600,000 points and 100x100 grid used before) are the creation of the spatial
index

That's why it's faster in grass 7

and (if a stats method is applied) the collecting of attributes from
the points vector (at one point we will probably have to attack db driver
efficiency issues in GRASS... example: do we really have to create a select
cursor and loop through every line this returns ?). The actual
point-in-areas is very fast !

A few small questions/remarks:

- Any special reason why you construct a whole SQL statement to add the
columns, instead of using db_add_column() (defined in
lib/db/dbmi_client/c_add_col.c) ?

No special reason, I simply forgot about db_add_column(), thanks for the tip!

- Maybe add a key to point_column_opt to call it point_column (instead of
the default "column")

Yes. I was also thinking about pcolumn, ccolumn, scolumn as keys,
these would allow shortcuts like pc, cc, sc. Other modules also use
e.g. qcolumn instead of query_column.

- If one sets a method and a stats_column, but no point_column, the first
two settings are simply ignored. Maybe a fatal error would be better (I took
me a bit of time to understand that I had forgotten the point_column.

Ah, ok, will do.

No time for more, but I really think this would be a useful addition to
trunk...

Thanks for testing!

Markus M

Nikos_Alexandris1 · August 13, 2010, 9:25am

Markus M:

I've put a new module in grass-addons, called v.vect.stats.

...

I just want to confirm that this is _fast_. Tested under grass6_devel. The
module fails to compile though under grass64, grass_trunk (see attached
files).

Nikos

(attachments)

make.v.vect.stats.error_grass6 (253 Bytes)
make.v.vect.stats.error_grass7 (421 Bytes)

Markus_GRASS · August 13, 2010, 10:42am

Nikos Alexandris wrote:

Markus M:

I've put a new module in grass-addons, called v.vect.stats.

...

... The
module fails to compile though under grass64, grass_trunk

compiles now in 64, but no longer in 65 (stats library in 65 is
different from 64), 65 is a development version and will probably
never be an officially stable version

does not compile in trunk because it's written for 65, now 64

Markus M

Nikos_Alexandris1 · August 13, 2010, 12:31pm

[...]

On Friday 13 of August 2010 12:42:57 Markus Metz wrote:

compiles now in 64, but no longer in 65 (stats library in 65 is
different from 64), 65 is a development version and will probably
never be an officially stable version

does not compile in trunk because it's written for 65, now 64

Working now, Thanks.

Hamish · August 13, 2010, 9:05pm

Markus Metz wrote:

compiles now in 64, but no longer in 65 (stats library
in 65 is different from 64),

til now we have only really worried about backward-
compatibility at the data and command-line level.
None the less, all attempts should be made to keep the C
API function calls stable. I'm not talking about new
functions here, I'm talking about modified ones.

In the past we used new function calls like G_fn_name2()
when there was a function change within the major version.
(ie for fns already released in a stable branch)

needing patches to build on diff't versions of 6.x is a
clear sign on failure on our behalf.

.. so is statsvalue() a module-private or libgis-public function? https://trac.osgeo.org/grass/changeset/43093
(no prob if it's an internal module fn)

thanks,
Hamish

Markus_GRASS · August 13, 2010, 9:26pm

Hamish wrote:

Markus Metz wrote:

compiles now in 64, but no longer in 65 (stats library
in 65 is different from 64),

til now we have only really worried about backward-
compatibility at the data and command-line level.
None the less, all attempts should be made to keep the C
API function calls stable. I'm not talking about new
functions here, I'm talking about modified ones.

In the past we used new function calls like G_fn_name2()
when there was a function change within the major version.
(ie for fns already released in a stable branch)

needing patches to build on diff't versions of 6.x is a
clear sign on failure on our behalf.

FYI, the change in question happened 11 months ago, on Sep 4th 2009,
in lib/stats. IOW, this is not an internal module fn, it's a grass
library fn, a change that should have been backported months ago.
There have been a number of changes to existing fn calls within minor
versions in grass6 (grass 6.0.0 was released around March 2005 which
is some time ago...)

Markus M