[GRASS-user] Re: Importing lines from database

[Seb]

On Fri, 29 May 2009 10:20:33 -0700 (PDT),
Hamish <hamish_b at yahoo.com> wrote:

> Seb wrote:
>> Is this still the best solution for creating lines from a points
>> ASCII file?  So assuming one has an ASCII file like:

>> ---<--------------------cut
here---------------start-------------------> ---
id> X|Y
AGB-08-0001> -4.44|22.06 -5.90|20.17 -5.03|17.49 -5.03|18.16
AGB-08-0002> -4.44|22.06 -5.90|20.17 -5.03|17.49
AGB-08-0003> -4.44|22.06 -5.90|20.17
>> ---<--------------------cut
here---------------end---------------------> ---

>> and we'd like to create lines for each code in the "id" column, we'd
>> have to reformat the file so that it conforms to the "standard" GRASS
>> format for ASCII lines files?  If so, it seems we'd also need to
>> recode such an "id" column into integer, so that it can be used as
>> category.  Or perhaps there is a better approach to importing this
>> kind of data that are typical in animal tracking studies, where
>> coordinates correspond to measurements taken at sequential times for
>> each animal (id)?  Thanks in advance for any input.

> it's pretty easy to get that into 'v.in.mapgen -f' format, but
> attaching the labels might be a pain so maybe the awk script (if you
> can get your head around it) is easier way after all??.


> here's a little script to do it:

> # script to load in data file, then make into lines based on unique #
> first column

> INFILE="animal_track.dat" OUTFILE="animal_track.asc" MAPNAME=`echo
> "$OUTFILE" | cut -f1 -d.`

> #

> ANIMALS=`grep ^AGB "$INFILE" | cut -f1 -d'|' | sort | uniq` echo "nan
> nan" > "$OUTFILE" \rm "${OUTFILE}.table"

> i=0 for CRITTER in $ANIMALS ; do i=`expr $i + 1` grep -w "$CRITTER"
> "$INFILE" | \ awk -F'|' '{print $2 " "$3}' >> "$OUTFILE"

>    echo "nan nan" >> "$OUTFILE"

>    echo "$i|$CRITTER" >> "${OUTFILE}.table" done


> v.in.mapgen -f in="$OUTFILE" out="${MAPNAME}_import" v.category
> in="${MAPNAME}_import" out="$MAPNAME" option=add g.remove
> vect="${MAPNAME}_import"

> v.db.addtable "$MAPNAME" column="label varchar(25)"


> ## slow while read LINE ; do CAT=`echo "$LINE" | awk -F'|' '{print
> $1}'` LABEL=`echo "$LINE" | awk -F'|' '{print $2}'` v.db.update
> map="$MAPNAME" column=label value="$LABEL" where="cat = $CAT" done <
> "${OUTFILE}.table"

> ## or, faster sed -e "s/^/INSERT INTO $MAPNAME (cat,label) values (/"
> \ -e "s/|/,'/" \ -e "s/$/');/" "${OUTFILE}.table" | db.execute

> # another fast, but how to correctly quote ''?  awk -F'|' -v
> MAPNAME=$MAPNAME \ '{print "INSERT INTO " MAPNAME " (cat,label) values
> (" $1 ",\'" $2 "\');" }' \ "${OUTFILE}.table" | db.execute

> # all done



> hopefully not too badly linewrapped by yahoo's helpfulness.

> ok, still not very simple, but given free-form input data it is hard
> to tell the computer what you want.

Excellent!  I think I might have cooked something similar to what you
suggest, without using v.in.mapgen, as I'm not familiar with MATLAB
vector output (beyond what the help page says):

---<--------------------cut here---------------start------------------->---
# Locate line start and end (store in file having: beg end n id)
cut -d"|" -f1 locs | uniq -c | \
    awk 'NR > 1 {++n0; n1+=$1; print n0 + 1, n1 + 1, $1, $2; n0=n1}' \
    > locs_id_lbrks
# Create the standard file and import to GRASS
while read beg end n id; do
    awk -F"|" -v beg="${beg}" -v end="${end}" -v n="${n}" -v id="${id}" \
	'BEGIN {printf "L %s\n", n}
         NR >= beg && NR <= end {print $2, $3}' locs
done < locs_id_lbrks | v.in.ascii -n out=test format=standard
---<--------------------cut here---------------end--------------------->---

where the id column could be removed from locs_id_lbrks and the 'while'
loop for simplicity.  However, I was stuck dealing with categories and
keep the ID strings so that info is not lost, and now I see how you did
that!

Thanks so much,

-- 
Seb