On Mon, 1 Jun 2009 07:40:28 +0200, Markus Neteler <[email protected]> wrote:
> On Mon, Jun 1, 2009 at 1:50 AM, Seb <[email protected]> wrote: >> On Mon, 01 Jun 2009 00:54:05 +0200, >> Nikos Alexandris <[email protected]> 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 _______________________________________________ grass-user mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/grass-user
