[GRASS-user] SQLite Error

[Mark Wynter]

Thanks Moritz.  I am getting an SQLite error when trying to run v.net.allpairs.

This is my latest workflow, incl. my checks and output messages of each step.

#(1) CREATE A COPY OF MY ROAD NETWORK, AND DROP THE ATTRIBUTE TABLE (CURRENTLY SET WITH PG DRIVER)
db.connect driver=pg database="host=localhost,dbname=osm_au"
db.login user=postgres [pass=password]
db.connect -p
db.tables -p
g.copy --overwrite vect=nsw_road_network_final_connected,nsw_road_network_final_connected_copy
v.db.droptable nsw_road_network_final_connected_copy
v.db.droptable nsw_road_network_final_connected_copy -f

# Outputs confirm my copied map no longer is connected to an attribute table
Dropping table <grass.nsw_road_network_final_connected_copy>...
Current attribute table link(s):
Map <nsw_road_network_final_connected_copy> is not connected to a database


#(2) RESET THE DATABASE DRIVER FOR THE CURRENT MAPSET TO SQLITE
db.connect driver=sqlite database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db' schema=grass
db.connect -p

#Output
driver: sqlite
database: /var/tmp/nodeclean/PERMANENT/sqlite/sqlite.db
schema: grass
group: 

#(3) COPY THE ATTRIBUTE TABLE OF THE ORIGINAL ROAD NETWORK IN POSTGRESQL TO THE NEW SQLITE DB
db.copy from_driver=pg  from_database="host=localhost,dbname=osm_au" from_table=grass.nsw_road_network_final_connected to_driver=sqlite to_database="/var/tmp/nodeclean/PERMANENT/sqlite/sqlite.db" to_table=nsw_road_network_final_connected_sqlite
db.copy from_driver=pg  from_database="host=localhost,dbname=osm_au" from_table=grass.nsw_tz_centroids_nodes to_driver=sqlite to_database="/var/tmp/nodeclean/PERMANENT/sqlite/sqlite.db" to_table=nsw_tz_centroids_nodes_sqlite

db.tables -p

#Output confirms table successfully copied
nsw_road_network_final_connected_sqlite
nsw_tz_centroids_nodes_sqlite


#(4) CONNECT THE COPIED ATTRIBUTE TABLES TO THE COPIED MAP IN (1)
v.db.connect map=nsw_road_network_final_connected_copy table=nsw_road_network_final_connected_sqlite layer=1
v.db.connect map=nsw_road_network_final_connected_copy table=nsw_tz_centroids_nodes_sqlite layer=2
v.category nsw_road_network_final_connected_copy op=report

v.category nsw_road_network_final_connected_copy op=report
Layer/table: 1/nsw_road_network_final_connected_sqlite
type       count        min        max
point          0          0          0
line      467531          1     465943
boundary       0          0          0
centroid       0          0          0
area           0          0          0
face           0          0          0
kernel         0          0          0
all       467531          1     465943
Layer: 2
type       count        min        max
point       3514          1       3514
line           0          0          0
boundary       0          0          0
centroid       0          0          0
area           0          0          0
face           0          0          0
kernel         0          0          0
all         3514          1       3514

v.info -c nsw_road_network_final_connected_copy layer=1
Displaying column types/names for database connection of layer <1>:
INTEGER|cat
INTEGER|lid
INTEGER|osm_id
CHARACTER|name
INTEGER|bridge
INTEGER|tunnel
INTEGER|oneway
CHARACTER|type
INTEGER|tid
CHARACTER|poa_code
INTEGER|reversed
INTEGER|open
DOUBLE PRECISION|length
DOUBLE PRECISION|fwdcost
DOUBLE PRECISION|bwdcost

v.info -c nsw_road_network_final_connected_copy layer=2
Displaying column types/names for database connection of layer <2>:
INTEGER|cat
INTEGER|tz_code11
CHARACTER|tz_name11
INTEGER|sa2_main11
INTEGER|sa3_code11

#(5) Run v.net.allpairs….
g.remove -f type=vector pattern="temp**"
v.net.allpairs  --overwrite in=nsw_road_network_final_connected_copy cats=1-10  out=temp_8 afcolumn=fwdcost abcolumn=bwdcost
DBMI-SQLite driver error:
Error in sqlite3_prepare():
unknown database grass

DBMI-SQLite driver error:
Error in sqlite3_prepare():
unknown database grass

ERROR: Unable to create table: 'create table grass.temp_8_1 ( cat integer,
       from_cat integer, to_cat integer, cost double precision)'
Segmentation fault




On 12/02/15 00:12, Mark Wynter wrote:
> Within the same mapset, is it possible to specify SQLITE as the database
> for the output layer, whilst using a map as an input that has a PG driver?
> 
> I?ve read the manual and tried a few approaches, but I found the outputs
> still get written to PG.  May be I?ve missed a critical step?  See
> messages below. I also experimented with v.add.table and v.add.columns
> to the empty map, to no avail.

You should use db.connect to set your default settings to SQLite, then 
run your command. By overwriting your map in the v.net.allpairs call you 
overwrite everything, including any db connections set before and you 
use the default mapset connection settings which seem to be set to PG.

Moritz


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/grass-user/attachments/20150212/bb7b3ec6/attachment.html>