[GRASS-user] Re: error importing postgresql db

Mon Jun 1 09:16:40 EDT 2009

On Mon, 1 Jun 2009 07:40:28 +0200,
Markus Neteler <neteler at osgeo.org> wrote:

> On Mon, Jun 1, 2009 at 1:50 AM, Seb <spluque at gmail.com> wrote:
>> On Mon, 01 Jun 2009 00:54:05 +0200,
>> Nikos Alexandris <nikos.alexandris at felis.uni-freiburg.de> wrote:

>> Seb:
> ...
>>> Not sure if this is a conflict with SQL commands, but you could try
>>> to _feed_ new names for your columns by using the "cname=" paramater
>>> of "v.in.ogr" and see what happens?

>> I tried that but the problem remains.  It seems as if there's
>> problems importing such datetime columns.  Thanks.

> Can you please post cmd line and error?

Ok, I set this up differently because I found yesterday that it's a
better approach in my case to import just the vector geometry and then
connect the vector to the database (it seems more efficient not to
duplicate the information by having it in the database and GRASS).  The
table I'd be importing is actually a view, so it was very simple to
modify the view to give names that don't collide with SQL if unquoted
(although it would be nice to avoid this inconvenience).  However, I'm
still running into problems:

---<--------------------cut here---------------start------------------->---
# We're in an AEA projection, data in lon/lat so extract first to reproject
$ db.select table='locs' database='dbname=locations,port=5433' \
    driver='pg' > locs_ll; head locs_ll
sample_id|project|species|sex|flipper_tag|date_time_deployed|claw_marks|mass|body_length|girth|fat_depth|locid|prognum|date_time|class|lon|lat
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27867|01142|2008-8-12 8:28:08|1|-63.076|58.448
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27868|01142|2008-8-12 9:41:28|A|-63.101|58.431
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27869|01142|2008-8-12 11:22:01|A|-63.086|58.407
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27870|01142|2008-8-12 11:48:25|2|-63.086|58.413
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27871|01142|2008-8-12 12:58:22|B|-63.119|58.463
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27872|01142|2008-8-12 13:39:50|0|-63.106|58.428
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27873|01142|2008-8-12 13:51:36|B|-63.067|58.398
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27874|01142|2008-8-12 14:54:49|A|-63.061|58.442
SAGB-08-0001|Fjord|Ph|male|1316|2008-8-11 18:30:00||25|90|82.5||27875|01142|2008-8-12 15:20:01|0|-63.134|58.463

# Reproject to AEA
$ awk -F"|" 'NR > 1 {print $16, $17}' locs_ll | \
    cs2cs +init=epsg:4326 +to $(g.proj -jf) | \
    awk -v OFS="|" 'BEGIN {print "lon_aea", "lat_aea"}; {print $1, $2}' \
    > locs_aea; head locs_aea
lon_aea|lat_aea
-4.44|22.06
-5.90|20.17
-5.03|17.49
-5.03|18.16
-6.94|23.74
-6.19|19.84
-3.92|16.49
-3.56|21.39
-7.82|23.74

# Paste back unique ID (locid, for category) and pass to v.in.ascii
$ cut -d'|' -f12 locs_ll | paste -d"|" - locs_aea | \
    v.in.ascii out=locs_pts_import skip=1 x=2 y=3 cat=1
Scanning input for column types...
Maximum input row length: 22
Maximum number of columns: 3
Minimum number of columns: 3
Importing points...

Building topology for vector map <locs_pts_import>...
Registering primitives...
1311 primitives registered
1311 vertices registered
Building areas...

0 areas built
0 isles built
Attaching islands...
Attaching centroids...

Topology was built
Number of nodes: 976
Number of primitives: 1311
Number of points: 1311
Number of lines: 0
Number of boundaries: 0
Number of centroids: 0
Number of areas: 0
Number of isles: 0
v.in.ascii complete.

# So far so good (data even plots ok). Connect to postgresql (no DBF
# created by default, so no need to remove any previous connection)
$ v.db.connect map=locs_pts_import driver='pg' database='dbname=locations,port=5433' \
    table='locs' key='locid'
> The table <locs> is now part of vector map
<locs_pts_import> and may be deleted or overwritten by GRASS modules
DBMI-Postgres driver error:
Cannot create index:
create unique index locs_locid on locs ( locid )
ERROR:  "locs" is not a table

WARNING: Cannot create index
Select privileges were granted on the table

# Try to remove the vector -- OMG it tries to drop the view in
# Postgresql (how to avoid this even if it worked?!)
$ g.remove -f vect=locs_pts_import
Removing vector <locs_pts_import>
DBMI-Postgres driver error:
Cannot execute: 
drop table locs
ERROR:  "locs" is not a table
HINT:  Use DROP VIEW to remove a view.

WARNING: Unable to drop table: 'drop table locs'
Segmentation fault

$ g.version -b
GRASS 6.4.0RC4 (2009) 

# ./configure  --host=x86_64-linux-gnu --build=x86_64-linux-gnu --prefix=/usr/lib --sysconfdir=/etc --sharedstatedir=/var --enable-socket --enable-shared --with-postgres --with-mysql --with-cxx --with-x --with-gdal --with-freetype --with-motif --with-readline --with-nls --with-odbc --with-sqlite --enable-largefile --with-freetype-includes=/usr/include/freetype2 --with-tcltk-includes=/usr/include/tcl --with-postgres-includes=/usr/include/postgresql --with-mysql-includes=/usr/include/mysql --with-proj-share=/usr/share/proj --with-wxwidgets=/usr/lib/wx/config/gtk2-unicode-release-2.8 --with-python=/usr/bin/python-config --with-cairo
---<--------------------cut here---------------end--------------------->---

So it seems we cannot work with views because an index cannot to be
created.  I also learned that attempting to remove a vector from GRASS
also drops its linked table from an external database, which fortunately
failed in this case.  At this point, I don't know what the best way to
proceed is.  Thanks in advance for any further feedback.

-- 
Seb