[GRASS-user] SQLite Error

Mark_Wynter · February 12, 2015, 1:14am

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

#(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

Mark_Wynter · February 12, 2015, 2:15am

As context, I’m running analysis using v.net.allpairs, whereby vector geometries are often common to more than 1 origin-destination journey path, hence the line segments appear multiple times but with unique cat values, and from_cat and to_cat combinations.

When exporting the output layer of v.net.allpairs back into PostGIS, only the third method returns the correct number of geometry features - and this requires first exporting to shapefile and then importing using ogr2ogr.
Methods (1) and (2) discard line strings that appear more than once (despite them having unique cat values). This is not what I was expecting. The third method correctly treats each linestring feature at unique, hence assigns a unique ogc_fid to each feature.

Are there flags for methods (1) and (2) that deal with this issue - or is method (3) the only feasible option?

(1) v.out.postgis --o input=temp_8 output="PG:host=localhost dbname=osm_au user=postgres password=password" olayer=jtw.temp_8_pg options="GEOMETRY_NAME=wkb_geometry,SRID=3577"
134 Features Written

(2) v.out.ogr --o input=temp_8 type=line output="PG:host=localhost dbname=osm_au user=postgres password=password" output_layer=jtw.temp_8_ogr format=PostgreSQL
134 Features Written

(3) v.out.ogr --o input=temp_8 output=/var/tmp/temp_8 type=line
ogr2ogr -overwrite -f "PostgreSQL" PG:"host=localhost dbname=osm_au user=postgres password=password" /var/tmp/temp_8/temp_8.shp -nln jtw.temp_8_ogr -s_srs EPSG:3577 -t_srs EPSG:3577 -a_srs EPSG:3577 -nlt LINESTRING
1323 Features Written

Kind regards

Mark

mlennert · February 12, 2015, 8:35am

On 12/02/15 02:14, Mark Wynter wrote:

#(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:

[...]

#(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

I don't think SQLite supports schemas.

Moritz

Mark_Wynter · February 12, 2015, 10:16am

Hi Moritz - the SQLite DB seems to be inheriting the schema from the previous PG driver? Even if I db.dropdb…. I don’t encounter this if I create a new location… e.g. test

GRASS 7.0.0svn (nodeclean):~ > db.dropdb database=‘/var/tmp/nodeclean/PERMANENT/sqlite/sqlite.db’
GRASS 7.0.0svn (nodeclean):~ > db.connect driver=sqlite database=‘$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db’
GRASS 7.0.0svn (nodeclean):~ > db.connect -p
driver: sqlite
database: /var/tmp/nodeclean/PERMANENT/sqlite/sqlite.db
schema: grass
group:

grass70 -text -c EPSG:3577 /var/tmp/test

GRASS 7.0.0svn (test):~ > db.connect -p
driver: sqlite
database: /var/tmp/test/PERMANENT/sqlite/sqlite.db
schema:
group:

On 12 Feb 2015, at 7:05 pm, Moritz Lennert <mlennert@club.worldonline.be> wrote:

On 12/02/15 02:14, Mark Wynter wrote:

#(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:

[…]

#(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

I don’t think SQLite supports schemas.

Moritz

mlennert · February 12, 2015, 11:54am

On 12/02/15 11:16, Mark Wynter wrote:

Hi Moritz - the SQLite DB seems to be inheriting the schema from the
previous PG driver? Even if I db.dropdb…. I don’t encounter this if I
create a new location… e.g. test

Can you set schema='' or something like that ?

Moritz

GRASS 7.0.0svn (nodeclean):~ > db.dropdb
database='/var/tmp/nodeclean/PERMANENT/sqlite/sqlite.db'
GRASS 7.0.0svn (nodeclean):~ > db.connect driver=sqlite
database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db'
GRASS 7.0.0svn (nodeclean):~ > db.connect -p
driver: sqlite
database: /var/tmp/nodeclean/PERMANENT/sqlite/sqlite.db
schema: grass
group:

grass70 -text -c EPSG:3577 /var/tmp/test
GRASS 7.0.0svn (test):~ > db.connect -p
driver: sqlite
database: /var/tmp/test/PERMANENT/sqlite/sqlite.db
schema:
group:

On 12 Feb 2015, at 7:05 pm, Moritz Lennert <mlennert@club.worldonline.be
<mailto:mlennert@club.worldonline.be>> wrote:

On 12/02/15 02:14, Mark Wynter wrote:

#(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:

[...]

#(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

I don't think SQLite supports schemas.

Moritz

Mark_Wynter · February 12, 2015, 12:30pm

Haven’t come across anything to that effect

Can you set schema='' or something like that ?

Moritz

I’ve reverted back to using solely PG driver - notwithstanding this SQLite issue.

As a prologue to the several issues I encountered with Grass over the last few days…

The slow rate of writing out the v.net.allpair results from PostgreSQL was due to the sheer volume of line strings, as the number of pairs increased (n^2). Simple math said stop. I’ve since changed my approach and am using v.net.distance in a novel way where the to_cat is the origin, and the from_cat is a string of destinations - this is an equivalent way of generating multiple v.net.paths in a single operation. Moreover, I’m feeding each origin - destination collection into GNU Parallel as a separate job, so it rips through the data at scale!

Still haven’t resolved the issues of v.net.allpair and v.net.distance geometry features being dropped with v.out.ogr(to postgres) and v.out.postgis. Luckily, the two step process of v.out.ogr(to shapefile), and then ogr2ogr doesn’t encounter this problem. Hence, thats what I’ve gone with.

mlennert · February 12, 2015, 3:24pm

On 12/02/15 13:30, Mark Wynter wrote:

Haven’t come across anything to that effect

Can you set schema='' or something like that ?

Moritz

I’ve reverted back to using solely PG driver - notwithstanding this
SQLite issue.

As a prologue to the several issues I encountered with Grass over the
last few days…

The slow rate of writing out the v.net.allpair results from
PostgreSQL was due to the sheer volume of line strings, as the number
of pairs increased (n^2). Simple math said stop. I’ve since
changed my approach and am using v.net.distance in a novel way where
the to_cat is the origin, and the from_cat is a string of
destinations - this is an equivalent way of generating multiple
v.net.paths in a single operation. Moreover, I’m feeding each origin
- destination collection into GNU Parallel as a separate job, so it
rips through the data at scale!

It would be great if you could put this info in a more structured form onto the GRASS wiki, possibly as a FAQ.

Moritz

Mark_Wynter · February 12, 2015, 10:03pm

Thanks Moritz - good suggestion - I just need to these as deliverables out to 2 separate clients (nice to get paid work for this stuff) - then I will write up and contribute back via Wiki.

The slow rate of writing out the v.net.allpair results from
PostgreSQL was due to the sheer volume of line strings, as the number
of pairs increased (n^2). Simple math said stop. I’ve since
changed my approach and am using v.net.distance in a novel way where
the to_cat is the origin, and the from_cat is a string of
destinations - this is an equivalent way of generating multiple
v.net.paths in a single operation. Moreover, I’m feeding each origin
- destination collection into GNU Parallel as a separate job, so it
rips through the data at scale!

It would be great if you could put this info in a more structured form onto the GRASS wiki, possibly as a FAQ.

Moritz