Vector Database Management: Difference between revisions

From GRASS-Wiki
Jump to navigation Jump to search
m (→‎Common tasks: 61->63)
m (61->63)
Line 3: Line 3:
==Vector data processing==
==Vector data processing==


* See the GRASS [http://grass.itc.it/grass61/manuals/html61_user/vectorintro.html vector data processing] help page.
* See the GRASS [http://grass.osgeo.org/grass63/manuals/html63_user/vectorintro.html vector data processing] help page.
* See also the GRASS [http://grass.itc.it/grass61/manuals/html61_user/databaseintro.html Database management] help page.
* See also the GRASS [http://grass.osgeo.org/grass63/manuals/html63_user/databaseintro.html Database management] help page.


* See also the [[Openoffice.org with SQL Databases]] wiki page
* See also the [[Openoffice.org with SQL Databases]] wiki page
Line 13: Line 13:


The GRASS 6 vector engine supports the following databases:
The GRASS 6 vector engine supports the following databases:
* [http://grass.ibiblio.org/grass61/manuals/html61_user/v.in.ascii.html v.in.ascii] module help page
* [http://grass.osgeo.org/grass63/manuals/html63_user/v.in.ascii.html v.in.ascii] module help page
* [http://grass.ibiblio.org/grass61/manuals/html61_user/v.out.ascii.html v.out.ascii] module help page
* [http://grass.osgeo.org/grass63/manuals/html63_user/v.out.ascii.html v.out.ascii] module help page


==== DBF ====
==== DBF ====
DBF is the default (local) DB used for GRASS vector attributes. It is easy to use but with the simplicity comes limited features. Such limits are 10 chars per column name and no support for SQL calculations in SELECT statements.
DBF is the default (local) DB used for GRASS vector attributes. It is easy to use but with the simplicity comes limited features. Such limits are 10 chars per column name and no support for SQL calculations in SELECT statements.


* GRASS [http://grass.ibiblio.org/grass61/manuals/html61_user/dbf.html DBF driver page]
* GRASS [http://grass.osgeo.org/grass63/manuals/html63_user/dbf.html DBF driver page]


==== SQLite ====  
==== SQLite ====  
Line 25: Line 25:
SQLite is another local database format, but much more featureful than DBF. It basically combines the power of real SQL databases with the advantage of local data storage (no server needed). A nice tool to directly work in the SQLite database is [http://sqlitebrowser.sourceforge.net/ SQLite Database Browser].
SQLite is another local database format, but much more featureful than DBF. It basically combines the power of real SQL databases with the advantage of local data storage (no server needed). A nice tool to directly work in the SQLite database is [http://sqlitebrowser.sourceforge.net/ SQLite Database Browser].


* GRASS [http://grass.ibiblio.org/grass61/manuals/html61_user/sqlite.html SQLite driver page]
* GRASS [http://grass.osgeo.org/grass63/manuals/html63_user/sqlite.html SQLite driver page]
* [http://www.sqlite.org SQLite Homepage]
* [http://www.sqlite.org SQLite Homepage]


==== MySQL ====  
==== MySQL ====  
* GRASS [http://grass.ibiblio.org/grass61/manuals/html61_user/mysql.html MySQL driver page]
* GRASS [http://grass.osgeo.org/grass63/manuals/html63_user/mysql.html MySQL driver page]
* GRASS [http://grass.ibiblio.org/grass61/manuals/html61_user/mesql.html MSQL embedded driver page]
* GRASS [http://grass.osgeo.org/grass63/manuals/html63_user/mesql.html MSQL embedded driver page]
* [http://www.mysql.com MySQL Homepage]
* [http://www.mysql.com MySQL Homepage]


==== PostgreSQL ====  
==== PostgreSQL ====  
* GRASS [http://grass.ibiblio.org/grass61/manuals/html61_user/pg.html PostgreSQL driver page]
* GRASS [http://grass.osgeo.org/grass63/manuals/html63_user/pg.html PostgreSQL driver page]
* [http://www.postgresql.org PostgreSQL Homepage]
* [http://www.postgresql.org PostgreSQL Homepage]


Line 87: Line 87:
   FileUsage      = 1
   FileUsage      = 1


* GRASS [http://grass.ibiblio.org/grass61/manuals/html61_user/odbc.html ODBC driver page]
* GRASS [http://grass.osgeo.org/grass63/manuals/html63_user/odbc.html ODBC driver page]


===Concepts and jargon===
===Concepts and jargon===
Line 116: Line 116:
===More Help===
===More Help===


* [http://grass.ibiblio.org/grass61/manuals/html61_user/database.html Database modules help pages]
* [http://grass.osgeo.org/grass63/manuals/html63_user/database.html Database modules help pages]
* [http://grass.ibiblio.org/grass61/manuals/html61_user/vector.html Vector modules help pages]
* [http://grass.osgeo.org/grass63/manuals/html63_user/vector.html Vector modules help pages]
* [http://grass.ibiblio.org/grass61/manuals/html61_user/sql.html GRASS SQL query help page]
* [http://grass.osgeo.org/grass63/manuals/html63_user/sql.html GRASS SQL query help page]
* [http://grass.itc.it/grass57/tutorial/links.html SQL reference links]
* [http://grass.osgeo.org/grass57/tutorial/links.html SQL reference links]




[[Category:Documentation]]
[[Category:Documentation]]

Revision as of 12:04, 25 May 2008

This page is a work in progress.
Please contribute if you have experience with anything that is still poorly documented.

Vector data processing

Database Support

AsciiText (.csv, etc.)

The GRASS 6 vector engine supports the following databases:

DBF

DBF is the default (local) DB used for GRASS vector attributes. It is easy to use but with the simplicity comes limited features. Such limits are 10 chars per column name and no support for SQL calculations in SELECT statements.

SQLite

(GRASS 6.1 or newer): SQLite is another local database format, but much more featureful than DBF. It basically combines the power of real SQL databases with the advantage of local data storage (no server needed). A nice tool to directly work in the SQLite database is SQLite Database Browser.

MySQL

PostgreSQL

FileMaker Pro

William Kyngesburye wrote on the grass-user mailing list:

I think the key to access FileMaker DBs from GRASS is that the FileMaker ODBC connector is for OSX's iODBC, not UnixODBC.

GRASS 6.2 does not have a configure option to build with iODBC, tho it can be hacked.

GRASS 6.3 does have an iODBC configure option. It's the same --with- odbc-* options, it just tries iodbc if it can't find unixodbc.

Oracle

This has been reported to work. Try ODBC and search the mailing list archive.

Insert details here

ODBC

  • External DB support via ODBC (e.g. FileMaker Pro)
  • unixODBC is required to make it work
  • to configure you could use the graphical frontend ODBCConfig to configure your ODBC connection.

Example-entry in ~/.odbc.ini for usage of ODBC with PostgreSQL

  [dbname]
  Description         = PostgreSQL database for my project
  Driver              = postgres
  Trace               = No
  TraceFile           =
  Database            = mydb
  Servername          = myserver
  UserName            = myusername
  Password            = mysecretpasswd
  Port                = 5432
  Protocol            = 8.0.3
  ReadOnly            = No
  RowVersioning       = No
  ShowSystemTables    = No
  ShowOidColumn       = No
  FakeOidIndex        = No
  ConnSettings        =

Additionall you need to define the libraries to use for the different drivers in /etc/odbcinst.ini.

  [postgres]
  Description     = ODBC for postgres
  Driver          = /usr/lib/unixODBC/libodbcpsql.so
  Setup           = /usr/lib/unixODBC/libodbcpsqlS.so
  FileUsage       = 1

Concepts and jargon

  • Table
    • Table column
    • Table row
    • Vector map layer
    • Each vector file has a special data field named "cat" (derived originally from "category"), filled with integers, that serves to identify each vector object. The 'cat' field also serves as a "key field" that can link each vector object with a corresponding record in an attributes table of a database (NB: 'cat' values do NOT have to be unique for vector objects, but DO have to be unique in an attributes table, permitting both one-to-one and many-to-one relationships). The attributes table must contain a key field, filled with integers (only integers are permitted to serve as key fields in GRASS), that matches the values in the vector 'cat' field.
    • A vector can be linked with more than one attribute table, using LAYERS. By default, every vector file has a LAYER 1 with a 'cat' field, filled with integers to identify each vector object. Additional *LAYERS*, along with their associated 'cat' fields, can be created using v.category. Each LAYER has its own independent 'cat' field that can be used to link with a separate attributes table.
    • For example, a vector file of cities can have *LAYER 1* whose 'cat' field links the vector points with an attributes table of demographic data; it can also have a LAYER 2, with an associated 'cat' field (independent of the 'cat' field of LAYER 1), linked to a different attributes table of economic data. Values in the 'cat' fields of different LAYERS can be the same or different. The 'cat' values and linked attributes table can be queried independently for each LAYER. In the example, one can query the cities by population from the demographics attributes table linked with the 'cat' field of LAYER 1, or query the cities by household income from the economic attributes table linked with the same vector points through the 'cat' field of LAYER 2. _MichaelBarton - 12 Nov 2005_

Common tasks

Background info find in Vector data processing in GRASS GIS.

  • Connect a DB (db.connect,v.db.connect)
  • Copy a table (db.copy)
  • Copy selected columns from a table
  • Create a new table (v.db.addtable)
  • Create a new column (v.db.addcol)
  • Extract data via SQL query (v.extract, db.select)
  • Low level access to DB
  • The db.execute module
  • Populate a DB (v.db.update etc.)

More Help