<div dir="ltr">Hi Moritz,<div>Hi All...</div><div><br></div><div>here my python code snippets that worked for me. I think there might be many ways to simplify the code...</div><div><br></div><div><div>import grass.script as grass</div><div>import sqlite3</div><div>import pandas</div><div><br></div><div># Def variable names</div><div>respondents_coord="respondents_coord"</div><div>respondents_points="respondents_points"</div><div>respondents_buffer="respondents_buffer"</div><div>respondents_buffer_cat_2_layer="respondents_buffer_cat_2_layer"</div><div>respondents_buffer_cat_2_raster="respondents_buffer_cat_2_raster"</div><div>correspondance_table="correspondance_table"</div><div>clc_summary_table="clc_summary_table"</div><div>clc_summary_pivot="clc_summary_pivot"</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span></div><div><br></div><div># Import points from X-Y table</div><div>grass.run_command("v.in.db",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>overwrite=True,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>table=respondents_coord,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>output=respondents_points)</div><div><br></div><div>#### Calculate buffers around points</div><div>grass.run_command("v.buffer",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>overwrite=True,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>flags="t",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>input=respondents_points,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>type="point",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>output=respondents_buffer,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>distance=50000)</div><div>grass.run_command("v.category",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>overwrite=True,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>input=respondents_buffer,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>option="add",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>layer=2,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>out=respondents_buffer_cat_2_layer)</div><div><br></div><div># Get CORINE land use special classes per buffer</div><div>correspondance_table_values = []</div><div>for line in grass.read_command('v.category',</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>input_=respondents_buffer_cat_2_layer,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>layer='1,2',</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>option='print').splitlines():</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>layers=line.split('|')</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>l1 = layers[0].split('/')</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>l2 = layers[1]</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>for cat in l1:</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>correspondance_table_values.append((cat, l2))</div><div><br></div><div>grass.run_command("db.execute",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>sql="CREATE TABLE correspondance_table (buffer_cat_1 INTEGER, buffer_cat_2 INTEGER)")</div><div><br></div><div>conn = sqlite3.connect('/path/to/sqlite/sqlite.db')</div><div>cur = conn.cursor() <span class="gmail-Apple-tab-span" style="white-space:pre"> </span></div><div>cur.executemany('INSERT INTO correspondance_table (buffer_cat_1, buffer_cat_2) values (?,?)',correspondance_table_values)</div><div>conn.commit()</div><div>conn.close()</div><div><br></div><div>grass.run_command("v.to.rast",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>overwrite=True,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>input=respondents_buffer_cat_2_layer,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>layer=2,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>output=respondents_buffer_cat_2_raster,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>use="cat")</div><div><br></div><div>if clc_summary_pivot in grass.read_command("db.tables",flags="p").split("\n"):</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>grass.run_command("db.droptable",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>flags="f",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>table=clc_summary_pivot)<span class="gmail-Apple-tab-span" style="white-space:pre"> </span></div><div><br></div><div>grass.run_command("db.execute",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>sql="CREATE TABLE {} (buffer_cat_2 INTEGER, CLC_built_up INTEGER, CLC_arable INTEGER, CLC_permanent_crops INTEGER, CLC_grassland INTEGER, CLC_forest INTEGER, CLC_others INTEGER, CLC_intertidal_coastal INTEGER, CLC_water_bodies INTEGER, CLC_sea INTEGER)".format(clc_summary_pivot))</div><div><br></div><div>clc_values = []</div><div>for line in grass.read_command("r.stats",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>flags="cn",</div><div>input="{},{}".format(respondents_buffer_cat_2_raster,"CLC_reclass@Corine_LandCover")).splitlines():</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>clc_values.append((line.split(' ')[0], line.split(' ')[1],line.split(' ')[2]))</div><div><br></div><div>df = pandas.DataFrame(clc_values, columns=['buffer_cat_2', 'CLC_class', 'count'])</div><div>CLC_class_cols = ["1","2","3","4","5","6","7","8","9"]</div><div>clc_summary_pivot_values = [tuple(x) for x in df.pivot(index='buffer_cat_2', columns='CLC_class', values='count').reindex(columns=CLC_class_cols).fillna(0).astype(int).to_records(index=True)]</div><div><br></div><div>conn = sqlite3.connect('/path/to/sqlite/sqlite.db')</div><div>cur = conn.cursor() </div><div>cur.executemany('INSERT INTO {} (buffer_cat_2, CLC_built_up, CLC_arable, CLC_permanent_crops, CLC_grassland, CLC_forest, CLC_others, CLC_intertidal_coastal, CLC_water_bodies, CLC_sea) values (?,?,?,?,?,?,?,?,?,?)'.format(clc_summary_pivot),clc_summary_pivot_values)</div><div>conn.commit()</div><div>conn.close()</div><div><br></div><div>grass.run_command("db.execute",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>sql="CREATE TABLE {} AS SELECT buffer_cat_1,SUM(CLC_built_up) AS CLC_built_up, SUM(CLC_arable) AS CLC_arable, SUM(CLC_permanent_crops) AS CLC_permanent_crops, SUM(CLC_grassland) AS CLC_grassland, SUM(CLC_forest) AS CLC_forest, SUM(CLC_others) AS CLC_others, SUM(CLC_intertidal_coastal) AS CLC_intertidal_coastal, SUM(CLC_water_bodies) AS CLC_water_bodies, SUM(CLC_sea) AS CLC_sea FROM {} AS A LEFT JOIN {} AS B ON A.buffer_cat_2=B.buffer_cat_2 GROUP BY buffer_cat_1".format(clc_summary_table,correspondance_table,clc_summary_pivot))</div><div><br></div><div># Join information on land use back to original center points of buffers</div><div>grass.run_command("v.db.join",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>map=respondents_points,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>column="id_INT",</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>other_table=clc_summary_table,</div><div><span class="gmail-Apple-tab-span" style="white-space:pre"> </span>other_column="resp_id")</div><div><br></div><div><br></div><div><br></div></div><div><br></div><div>cheers, Johannes</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Jun 14, 2017 at 11:10 AM, Moritz Lennert <span dir="ltr"><<a href="mailto:mlennert@club.worldonline.be" target="_blank">mlennert@club.worldonline.be</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Johannes,<span class=""><br>
<br>
On 07/06/17 10:20, Johannes Radinger wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Thank you Moritz,<br>
<br>
your suggestion using r.stats and the rasterized layer 2 of buffers<br>
works really nice. It took me just a while to summarize and join all<br>
data and get them back into the db in the right format. However, I think<br>
I managed this task now. Thank you for your help!<br>
</blockquote>
<br></span>
Would you be willing to share the final version of your approach ? This might be a nice seed for a module that would offer this function.<span class="HOEnZb"><font color="#888888"><br>
<br>
Moritz<br>
<br>
</font></span></blockquote></div><br></div>