def main(): indb = options['database'] prefix = options['basename'] env = grass.gisenv() #fix sqlite3 db field string multibyte character problem sys.setdefaultencoding('utf-8') # check if 3d or not if flags['z']: d3 = 'z' else: d3 = '' owrite = grass.overwrite() # check if location it is latlong if grass.locn_is_latlong(): locn = True else: locn = False # connection to sqlite geopaparazzi database import sqlite3 conn = sqlite3.connect(indb) curs = conn.cursor() # if it is not a latlong location create a latlong location on the fly if not locn: # create new location and move to it creating new gisrc file new_loc = basename(grass.tempfile(create=False)) new_loc_name = 'geopaparazzi_%s' % new_loc grass.create_location(dbase=env['GISDBASE'], epsg='4326', location=new_loc_name, desc='Temporary location for v.in.geopaparazzi') grc = os.getenv('GISRC') shutil.copyfile(grc, grc + '.old') newrc = open(grc, 'w') newrc.write('GISDBASE: %s\n' % env['GISDBASE']) newrc.write('LOCATION_NAME: %s\n' % new_loc_name) newrc.write('MAPSET: PERMANENT\n') newrc.write('GRASS_GUI: text\n') newrc.close() grass.run_command('db.connect', flags="d", quiet=True) # load bookmarks if flags['b']: # check if elements in bookmarks table are more the 0 if checkEle(curs, 'bookmarks') != 0: bookname = prefix + '_book' pois = importGeom(bookname, 'bookmarks', curs, owrite, '') sql = 'CREATE TABLE %s (cat int, text text)' % bookname grass.write_command('db.execute', input='-', stdin=sql) # select attributes sql = "select text from bookmarks order by _id" allattri = returnClear(curs, sql) # add values using insert statement idcat = 1 for row in allattri: values = "%d,'%s'" % (idcat, str(row)) sql = "insert into %s values(%s)" % (bookname, values) grass.write_command('db.execute', input='-', stdin=sql) idcat += 1 # at the end connect table to vector grass.run_command('v.db.connect', map=bookname, table=bookname, quiet=True) else: grass.warning(_("No bookmarks found, escape them")) # load images if flags['i']: # check if elements in images table are more the 0 if checkEle(curs, 'images') != 0: imagename = prefix + '_image' pois = importGeom(imagename, 'images', curs, owrite, d3) sql = 'CREATE TABLE %s (cat int, azim int, ' % imagename sql += 'path text, ts text, text text)' grass.write_command('db.execute', input='-', stdin=sql) # select attributes sql = "select azim, path, ts, text from images order by _id" allattri = returnAll(curs, sql) # add values using insert statement idcat = 1 for row in allattri: values = "%d,'%d','%s','%s','%s'" % (idcat, row[0], str(row[1]), str(row[2]), str(row[3])) sql = "insert into %s values(%s)" % (imagename, values) grass.write_command('db.execute', input='-', stdin=sql) idcat += 1 # at the end connect table to vector grass.run_command('v.db.connect', map=imagename, table=imagename, quiet=True) else: grass.warning(_("No images found, escape them")) # if tracks or nodes should be imported create a connection with sqlite3 # load notes if flags['n']: # check if elements in notes table are more the 0 if checkEle(curs, 'notes') != 0: # select each categories categories = returnClear(curs, "select cat from notes group by cat") # for each category for cat in categories: # select lat, lon for create point layer catname = prefix + '_notes_' + cat pois = importGeom(catname, 'notes', curs, owrite, d3, cat) # select form to understand the number forms = returnClear(curs, "select _id from notes where cat = '%s' " "and form is not null order by _id" % cat) # if number of form is different from 0 and number of point # remove the vector because some form it is different if len(forms) != 0 and len(forms) != len(pois): grass.run_command('g.remove', flags='f', type='vector', name=catname, quiet=True) grass.warning(_("Vector %s not imported because number" " of points and form is different")) # if form it's 0 there is no form elif len(forms) == 0: # create table without form sql = 'CREATE TABLE %s (cat int, ts text, ' % catname sql += 'text text, geopap_cat text)' grass.write_command('db.execute', input='-', stdin=sql) # select attributes sql = "select ts, text, cat from notes where "\ "cat='%s' order by _id" % cat allattri = returnAll(curs, sql) # add values using insert statement idcat = 1 for row in allattri: values = "%d,'%s','%s','%s'" % (idcat, str(row[0]), str(row[1]), str(row[2])) sql = "insert into %s values(%s)" % (catname, values) grass.write_command('db.execute', input='-', stdin=sql) idcat += 1 # at the end connect table to vector grass.run_command('v.db.connect', map=catname, table=catname, quiet=True) # create table with form else: # select all the attribute sql = "select ts, text, cat, form from notes where "\ "cat='%s' order by _id" % cat allattri = returnAll(curs, sql) # return string of form's categories too create table keys = returnFormKeys(allattri) sql = 'CREATE TABLE %s (cat int, ts text, ' % catname sql += 'text text, geopap_cat text %s)' % keys grass.write_command('db.execute', input='-', stdin=sql) # it's for the number of categories idcat = 1 # for each feature insert value for row in allattri: values = "%d,'%s','%s','%s'," % (idcat, str(row[0]), str(row[1]), str(row[2])) values += returnFormValues(row[3]) sql = "insert into %s values(%s)" % (catname, values) grass.write_command('db.execute', input='-', stdin=sql) idcat += 1 # at the end connect table with vector grass.run_command('v.db.connect', map=catname, table=catname, quiet=True) else: grass.warning(_("No notes found, escape them")) # load tracks if flags['t']: # check if elements in bookmarks table are more the 0 if checkEle(curs, 'gpslogs') != 0: tracksname = prefix + '_tracks' # define string for insert data at the end tracks = '' # return ids of tracks ids = returnClear(curs, "select _id from gpslogs") # for each track for i in ids: # select all the points coordinates tsel = "select lon, lat" if flags['z']: tsel += ", altim" tsel += " from gpslog_data where logid=%s order by _id" % i trackpoints = returnAll(curs, tsel) wpoi = '\n'.join(['|'.join([str(col) for col in row]) for row in trackpoints]) tracks += "%s\n" % wpoi if flags['z']: tracks += 'NaN|NaN|Nan\n' else: tracks += 'NaN|Nan\n' # import lines try: grass.write_command('v.in.lines', flags=d3, input='-', out=tracksname, stdin=tracks, overwrite=owrite, quiet=True) except CalledModuleError: grass.fatal(_("Error importing %s" % tracksname)) # create table for line sql = 'CREATE TABLE %s (cat int, startts text, ' % tracksname sql += 'endts text, text text, color text, width int)' grass.write_command('db.execute', input='-', stdin=sql) sql = "select logid, startts, endts, text, color, width from" \ " gpslogs, gpslogsproperties where gpslogs._id=" \ "gpslogsproperties.logid" # return attributes allattri = returnAll(curs, sql) # for each line insert attribute for row in allattri: values = "%d,'%s','%s','%s','%s',%d" % (row[0], str(row[1]), str(row[2]), str(row[3]), str(row[4]), row[5]) sql = "insert into %s values(%s)" % (tracksname, values) grass.write_command('db.execute', input='-', stdin=sql) # at the end connect map with table grass.run_command('v.db.connect', map=tracksname, table=tracksname, quiet=True) else: grass.warning(_("No tracks found, escape them")) # if location it's not latlong reproject it if not locn: # copy restore the original location shutil.copyfile(grc + '.old', grc) # reproject bookmarks if flags['b'] and checkEle(curs, 'bookmarks') != 0: grass.run_command('v.proj', quiet=True, input=bookname, location='geopaparazzi_%s' % new_loc, mapset='PERMANENT') # reproject images if flags['i'] and checkEle(curs, 'images') != 0: grass.run_command('v.proj', quiet=True, input=imagename, location='geopaparazzi_%s' % new_loc, mapset='PERMANENT') # reproject notes if flags['n'] and checkEle(curs, 'notes') != 0: for cat in categories: catname = prefix + '_node_' + cat grass.run_command('v.proj', quiet=True, input=catname, location='geopaparazzi_%s' % new_loc, mapset='PERMANENT') # reproject track if flags['t'] and checkEle(curs, 'gpslogs') != 0: grass.run_command('v.proj', quiet=True, input=tracksname, location='geopaparazzi_%s' % new_loc, mapset='PERMANENT')
def main(): indb = options["database"] prefix = options["basename"] env = grass.gisenv() # fix sqlite3 db field string multibyte character problem sys.setdefaultencoding("utf-8") # check if 3d or not if flags["z"]: d3 = "z" else: d3 = "" owrite = grass.overwrite() # check if location it is latlong if grass.locn_is_latlong(): locn = True else: locn = False # connection to sqlite geopaparazzi database import sqlite3 conn = sqlite3.connect(indb) curs = conn.cursor() # if it is not a latlong location create a latlong location on the fly if not locn: # create new location and move to it creating new gisrc file new_loc = basename(grass.tempfile(create=False)) new_loc_name = "geopaparazzi_%s" % new_loc grass.create_location( dbase=env["GISDBASE"], epsg="4326", location=new_loc_name, desc="Temporary location for v.in.geopaparazzi", ) grc = os.getenv("GISRC") shutil.copyfile(grc, grc + ".old") newrc = open(grc, "w") newrc.write("GISDBASE: %s\n" % env["GISDBASE"]) newrc.write("LOCATION_NAME: %s\n" % new_loc_name) newrc.write("MAPSET: PERMANENT\n") newrc.write("GRASS_GUI: text\n") newrc.close() grass.run_command("db.connect", flags="d", quiet=True) # load bookmarks if flags["b"]: # check if elements in bookmarks table are more the 0 if checkEle(curs, "bookmarks") != 0: bookname = prefix + "_book" pois = importGeom(bookname, "bookmarks", curs, owrite, "") sql = "CREATE TABLE %s (cat int, text text)" % bookname grass.write_command("db.execute", input="-", stdin=sql) # select attributes sql = "select text from bookmarks order by _id" allattri = returnClear(curs, sql) # add values using insert statement idcat = 1 for row in allattri: values = "%d,'%s'" % (idcat, str(row)) sql = "insert into %s values(%s)" % (bookname, values) grass.write_command("db.execute", input="-", stdin=sql) idcat += 1 # at the end connect table to vector grass.run_command("v.db.connect", map=bookname, table=bookname, quiet=True) else: grass.warning(_("No bookmarks found, escape them")) # load images if flags["i"]: # check if elements in images table are more the 0 if checkEle(curs, "images") != 0: imagename = prefix + "_image" pois = importGeom(imagename, "images", curs, owrite, d3) sql = "CREATE TABLE %s (cat int, azim int, " % imagename sql += "path text, ts text, text text)" grass.write_command("db.execute", input="-", stdin=sql) # select attributes sql = "select azim, path, ts, text from images order by _id" allattri = returnAll(curs, sql) # add values using insert statement idcat = 1 for row in allattri: values = "%d,'%d','%s','%s','%s'" % ( idcat, row[0], str(row[1]), str(row[2]), str(row[3]), ) sql = "insert into %s values(%s)" % (imagename, values) grass.write_command("db.execute", input="-", stdin=sql) idcat += 1 # at the end connect table to vector grass.run_command( "v.db.connect", map=imagename, table=imagename, quiet=True ) else: grass.warning(_("No images found, escape them")) # if tracks or nodes should be imported create a connection with sqlite3 # load notes if flags["n"]: # check if elements in notes table are more the 0 if checkEle(curs, "notes") != 0: # select each categories categories = returnClear(curs, "select cat from notes group by cat") # for each category for cat in categories: # select lat, lon for create point layer catname = prefix + "_notes_" + cat pois = importGeom(catname, "notes", curs, owrite, d3, cat) # select form to understand the number forms = returnClear( curs, "select _id from notes where cat = '%s' " "and form is not null order by _id" % cat, ) # if number of form is different from 0 and number of point # remove the vector because some form it is different if len(forms) != 0 and len(forms) != len(pois): grass.run_command( "g.remove", flags="f", type="vector", name=catname, quiet=True ) grass.warning( _( "Vector %s not imported because number" " of points and form is different" ) ) # if form it's 0 there is no form elif len(forms) == 0: # create table without form sql = "CREATE TABLE %s (cat int, ts text, " % catname sql += "text text, geopap_cat text)" grass.write_command("db.execute", input="-", stdin=sql) # select attributes sql = ( "select ts, text, cat from notes where " "cat='%s' order by _id" % cat ) allattri = returnAll(curs, sql) # add values using insert statement idcat = 1 for row in allattri: values = "%d,'%s','%s','%s'" % ( idcat, str(row[0]), str(row[1]), str(row[2]), ) sql = "insert into %s values(%s)" % (catname, values) grass.write_command("db.execute", input="-", stdin=sql) idcat += 1 # at the end connect table to vector grass.run_command( "v.db.connect", map=catname, table=catname, quiet=True ) # create table with form else: # select all the attribute sql = ( "select ts, text, cat, form from notes where " "cat='%s' order by _id" % cat ) allattri = returnAll(curs, sql) # return string of form's categories too create table keys = returnFormKeys(allattri) sql = "CREATE TABLE %s (cat int, ts text, " % catname sql += "text text, geopap_cat text %s)" % keys grass.write_command("db.execute", input="-", stdin=sql) # it's for the number of categories idcat = 1 # for each feature insert value for row in allattri: values = "%d,'%s','%s','%s'," % ( idcat, str(row[0]), str(row[1]), str(row[2]), ) values += returnFormValues(row[3]) sql = "insert into %s values(%s)" % (catname, values) grass.write_command("db.execute", input="-", stdin=sql) idcat += 1 # at the end connect table with vector grass.run_command( "v.db.connect", map=catname, table=catname, quiet=True ) else: grass.warning(_("No notes found, escape them")) # load tracks if flags["t"]: # check if elements in bookmarks table are more the 0 if checkEle(curs, "gpslogs") != 0: tracksname = prefix + "_tracks" # define string for insert data at the end tracks = "" # return ids of tracks ids = returnClear(curs, "select _id from gpslogs") # for each track for i in ids: # select all the points coordinates tsel = "select lon, lat" if flags["z"]: tsel += ", altim" tsel += " from gpslog_data where logid=%s order by _id" % i trackpoints = returnAll(curs, tsel) wpoi = "\n".join( ["|".join([str(col) for col in row]) for row in trackpoints] ) tracks += "%s\n" % wpoi if flags["z"]: tracks += "NaN|NaN|Nan\n" else: tracks += "NaN|Nan\n" # import lines try: grass.write_command( "v.in.lines", flags=d3, input="-", out=tracksname, stdin=tracks, overwrite=owrite, quiet=True, ) except CalledModuleError: grass.fatal(_("Error importing %s" % tracksname)) # create table for line sql = "CREATE TABLE %s (cat int, startts text, " % tracksname sql += "endts text, text text, color text, width int)" grass.write_command("db.execute", input="-", stdin=sql) sql = ( "select logid, startts, endts, text, color, width from" " gpslogs, gpslogsproperties where gpslogs._id=" "gpslogsproperties.logid" ) # return attributes allattri = returnAll(curs, sql) # for each line insert attribute for row in allattri: values = "%d,'%s','%s','%s','%s',%d" % ( row[0], str(row[1]), str(row[2]), str(row[3]), str(row[4]), row[5], ) sql = "insert into %s values(%s)" % (tracksname, values) grass.write_command("db.execute", input="-", stdin=sql) # at the end connect map with table grass.run_command( "v.db.connect", map=tracksname, table=tracksname, quiet=True ) else: grass.warning(_("No tracks found, escape them")) # if location it's not latlong reproject it if not locn: # copy restore the original location shutil.copyfile(grc + ".old", grc) # reproject bookmarks if flags["b"] and checkEle(curs, "bookmarks") != 0: grass.run_command( "v.proj", quiet=True, input=bookname, location="geopaparazzi_%s" % new_loc, mapset="PERMANENT", ) # reproject images if flags["i"] and checkEle(curs, "images") != 0: grass.run_command( "v.proj", quiet=True, input=imagename, location="geopaparazzi_%s" % new_loc, mapset="PERMANENT", ) # reproject notes if flags["n"] and checkEle(curs, "notes") != 0: for cat in categories: catname = prefix + "_node_" + cat grass.run_command( "v.proj", quiet=True, input=catname, location="geopaparazzi_%s" % new_loc, mapset="PERMANENT", ) # reproject track if flags["t"] and checkEle(curs, "gpslogs") != 0: grass.run_command( "v.proj", quiet=True, input=tracksname, location="geopaparazzi_%s" % new_loc, mapset="PERMANENT", )