Vector Database Management - Revision history

Neteler: added link to progman

2023-10-10T07:37:29Z

added link to progman

← Older revision		Revision as of 07:37, 10 October 2023
Line 168:		Line 168:
	* {{cmd\|vector}} module help pages		* {{cmd\|vector}} module help pages
	* GRASS {{cmd\|sql}} query help page		* GRASS {{cmd\|sql}} query help page
			* [https://grass.osgeo.org/programming8/vectorlib.html Vectorlib in the GRASS GIS 8 Programmer's Manual]
	* [http://grass.osgeo.org/grass57/tutorial/links.html SQL reference links]		* [http://grass.osgeo.org/grass57/tutorial/links.html SQL reference links]
	* [http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html MySQL reserved words list]		* [http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html MySQL reserved words list]

Neteler: minor updates

2023-10-10T07:29:57Z

minor updates

← Older revision		Revision as of 07:29, 10 October 2023
Line 59:		Line 59:
	===Database Support===		===Database Support===

	In GRASS GIS 7.x, SQLite is the default (local) DB driver used for GRASS vector attribute management.		Since GRASS GIS 7.x, SQLite is the default (local) DB driver used for GRASS vector attribute management.

	==== ~~AsciiText~~ (.csv, etc.) ====		==== SQLite ====

			SQLite is a local database format, but much more featureful than DBF (see below). 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 [https://sqlitebrowser.org/ SQLite Database Browser].
			Since GRASS 7, SQLite is the default (local) DB used for the vector attribute management.

			* GRASS {{cmd\|grass-sqlite}} SQLite driver help page
			* [http://www.sqlite.org SQLite Homepage]

			==== ASCII text (.csv, etc.) ====

	~~The GRASS 6~~ vector ~~engine supports the following databases~~:		ASCII vector data exchange:
	* {{cmd\|v.in.ascii}} module help page		* {{cmd\|v.in.ascii}} module help page
	* {{cmd\|v.out.ascii}} module help page		* {{cmd\|v.out.ascii}} module help page

	If your .csv file contains "quoted" strings containing commas, you can use the [[AddOns#Miscellaneous_Add-ons\|csv_dequote.pl]] script to parse them into a less-problematic form ready for import with {{cmd\|v.in.ascii}}.		If your .csv file contains "quoted" strings containing commas, you can use the [[AddOns#Miscellaneous_Add-ons\|csv_dequote.pl]] script to parse them into a less-problematic form ready for import with {{cmd\|v.in.ascii}}.

	~~==== DBF ====~~
	In GRASS 6, 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 {{cmd\|grass-dbf}} DBF driver help page

	~~==== SQLite ====~~

	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].
	~~In GRASS 7, SQLite is the default (local) DB used for GRASS vector attributes.~~

	* GRASS {{cmd\|grass-sqlite}} SQLite driver help page
	* [http://www.sqlite.org SQLite Homepage]

	==== MySQL ====		==== MySQL ====
Line 90:		Line 85:
	* GRASS {{cmd\|grass-pg}} PostgreSQL driver page		* GRASS {{cmd\|grass-pg}} PostgreSQL driver page
	* [http://www.postgresql.org PostgreSQL Homepage]		* [http://www.postgresql.org PostgreSQL Homepage]

			==== DBF ====
			In GRASS 6, DBF was 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 {{cmd\|grass-dbf}} DBF driver help page

	==== FileMaker Pro ====		==== FileMaker Pro ====

Neteler: /* Database Support */ SQLite is the default driver

2019-06-15T09:30:48Z

Database Support: SQLite is the default driver

← Older revision		Revision as of 09:30, 15 June 2019
Line 58:		Line 58:

	===Database Support===		===Database Support===

			In GRASS GIS 7.x, SQLite is the default (local) DB driver used for GRASS vector attribute management.

	==== AsciiText (.csv, etc.) ====		==== AsciiText (.csv, etc.) ====

⚠️Madi at 09:49, 23 April 2015

2015-04-23T09:49:19Z

← Older revision		Revision as of 09:49, 23 April 2015
Line 172:		Line 172:
	[[Category:Documentation]]		[[Category:Documentation]]
	[[Category:Vector]]		[[Category:Vector]]
			[[Category:database]]

Neteler: /* GRASS GIS vector management model */

2014-12-04T07:25:37Z

GRASS GIS vector management model

Neteler at 19:17, 9 November 2014

2014-11-09T19:17:45Z

← Older revision		Revision as of 19:17, 9 November 2014
Line 157:		Line 157:
	** The {{cmd\|v.to.db}} module		** The {{cmd\|v.to.db}} module

	===More Help===		== More Help ==

	* {{cmd\|database}} module help pages		* {{cmd\|database}} module help pages
Line 164:		Line 164:
	* [http://grass.osgeo.org/grass57/tutorial/links.html SQL reference links]		* [http://grass.osgeo.org/grass57/tutorial/links.html SQL reference links]
	* [http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html MySQL reserved words list]		* [http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html MySQL reserved words list]
			* https://gis.stackexchange.com/questions/61927/what-is-the-difference-between-node-and-vertex-in-gis

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

Neteler: cosmetics

2013-05-05T09:10:26Z

cosmetics

⚠️Vincent: minor syntax modifs.

2013-05-04T13:10:31Z

minor syntax modifs.

← Older revision		Revision as of 13:10, 4 May 2013
Line 8:		Line 8:
	Consider creating a map where you would handle (i) parcels with various crops, (ii) a set of owners for these parcels, and (iii) paths to access them. The latter are determined by limits between contiguous fields.		Consider creating a map where you would handle (i) parcels with various crops, (ii) a set of owners for these parcels, and (iii) paths to access them. The latter are determined by limits between contiguous fields.

	One could decide to store data in several ~~distincts~~ maps, e.g.:		One could decide to store data in several distinct maps, e.g.:
	* a map "path" containing lines, to describe paths;		* a map "path" containing lines, to describe paths;
	* a map "crop" containing areas, to describe crops;		* a map "crop" containing areas, to describe crops;
Line 22:		Line 22:

	We could consider using this ''key-id'' to directly point at remote attribute records in a table. But Grass provides a more flexible method, based on the ability :		We could consider using this ''key-id'' to directly point at remote attribute records in a table. But Grass provides a more flexible method, based on the ability :
	* to give either a single ''id'' to each feature or to group similar features by giving them all a common ''id'';		* to give either a single ''identifier'' to each feature or to group similar features by giving them all a common ''identifier'';
	* to give several ''id''s to an object if it is related to multiple thematic data.		* to give several ''identifiers'' to an object if it is related to multiple thematic data.
	These ''id''s are named '''categories''', they are organised in as many sets as you wish, that are named '''layers'''.		These ''identifiers'' are named '''categories''', they are organised in as many sets as you wish, that are named '''layers'''.

	In this example objects are categorized according to three layers. Boundaries have categories in layer 1, centroids have categories in layers 2 and 3. Note :		In this example objects are categorized according to three layers. Boundaries have categories in layer 1, centroids have categories in layers 2 and 3. Note :

⚠️Vincent at 08:36, 4 May 2013

2013-05-04T08:36:08Z

← Older revision		Revision as of 08:36, 4 May 2013
Line 2:		Line 2:

	''Looking for vector geometry management? See [[:Category:Vector\|here]]''		''Looking for vector geometry management? See [[:Category:Vector\|here]]''

			== Grass vector management model ==
			Grass vector management model allows to link map objects to database management systems (dbms). In this short section, we try to give an overview on how Grass matches map features and attributes.

			Consider creating a map where you would handle (i) parcels with various crops, (ii) a set of owners for these parcels, and (iii) paths to access them. The latter are determined by limits between contiguous fields.

			One could decide to store data in several distincts maps, e.g.:
			* a map "path" containing lines, to describe paths;
			* a map "crop" containing areas, to describe crops;
			* a map "owner" containing areas, to describe owners.
			We can point several drawbacks to this method:
			* we induce redundancy of geometric features between maps "crop" and "owner". Eventhought their attributes are independant, areas share the same geometry;
			* topological relation between paths and parcels limits won't be ensured when it's about to modify the shape of parcels.
			A better solution will resort to the concept of ''layers''. A single vector map will store geometric features, while several tables in the dbms will handle attribute contents. Figure 1 shows how it works. Let's comment this sketch.

			[[File:Catsnlayers.png\|800px\|thumb\|alt=cats and layers in Grass\|center\|Figure 1]]

			This vector map contains ''boundaries'' and ''centroids'', that determine ''areas'' (see {{cmd\|vectorintro}}). Boundaries are used as linear features to hold tracks geometry. Each feature is automatically assigned an ''internal identifier'' as soon as it is added to the map. The user cannot edit this ''id'', Grass handles it for you, and ensures the unicity of each ''id''. Here, ''id''s 1 to 12 are assigned to boundaries, ''id''s 13 to 17 correspond to centroids.

			We could consider using this ''key-id'' to directly point at remote attribute records in a table. But Grass provides a more flexible method, based on the ability :
			* to give either a single ''id'' to each feature or to group similar features by giving them all a common ''id'';
			* to give several ''id''s to an object if it is related to multiple thematic data.
			These ''id''s are named '''categories''', they are organised in as many sets as you wish, that are named '''layers'''.

			In this example objects are categorized according to three layers. Boundaries have categories in layer 1, centroids have categories in layers 2 and 3. Note :
			* 3 boundaries share category 5 within layer 1;
			* among others, object with id 7 has zero category.

			Command {{cmd\|v.category}} allows to maintain vector categories.

			Layer 1 connects the map with a table named "paths", "cat" is the key column for this relation. The name of the key column may be different, e.g. "code" for layer 2 and table "plots". Note :
			* layer 3 does not refer to any table in the database;
			* "code" value 34 within table "plots" has no corresponding category in layer 2;
			* category 14 in layer 1 does not refer to any "cat" value in table "paths";
			* database contains a table named "owner" which is not related to any layer.

			Database management in grass is assumed by db.* set of commands :
			* database connections (db.connect, db.login);
			* SQL operations (db.select, db.execute, etc.),
			while v.db.* set of commands allows to manage linked tables to a vector map:
			* layers can be listed/maintained (v.db.connect);
			* some v.db.* commands are simply db.execute frontends (v.db.addcol, v.db.join, etc.).
			{{cmd\|v.to.db}} is the command that allows to populate a table attached to a given vector layer; not only categories can be uploaded, but geometric data too (area, perimeter, ccordinates, etc.).


	==Vector attribute data processing==		==Vector attribute data processing==

Neteler: cosmetics

2013-04-23T20:29:34Z

cosmetics

← Older revision		Revision as of 20:29, 23 April 2013
Line 1:		Line 1:
	''This page is a work in progress.<BR>Please contribute if you have experience with anything that is still poorly documented.''		''This page is a work in progress.<BR>Please contribute if you have experience with anything that is still poorly documented.''

	==Vector data processing==		''Looking for vector geometry management? See [[:Category:Vector\|here]]''

			==Vector attribute data processing==

	* See the GRASS {{cmd\|vectorintro}} vector data processing help page.		* See the GRASS {{cmd\|vectorintro}} vector data processing help page.
	* See also the GRASS {{cmd\|databaseintro}} Database management help page.		* See also the GRASS {{cmd\|databaseintro}} Database management help page.

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

← Older revision		Revision as of 07:25, 4 December 2014
Line 16:		Line 16:
	* we induce redundancy of geometric features between maps "crop" and "owner". Even though their attributes are independent, areas share the same geometry;		* we induce redundancy of geometric features between maps "crop" and "owner". Even though their attributes are independent, areas share the same geometry;
	* topological relation between paths and parcels limits won't be ensured when it's about to modify the shape of parcels.		* topological relation between paths and parcels limits won't be ensured when it's about to modify the shape of parcels.

			=== The concept of layers ===

	A better solution will resort to the concept of ''layers''. A single vector map will store geometric features, while several tables in the DBMS will handle attribute contents. Figure 1 shows how it works. Let's comment this sketch.		A better solution will resort to the concept of ''layers''. A single vector map will store geometric features, while several tables in the DBMS will handle attribute contents. Figure 1 shows how it works. Let's comment this sketch.

	[[File:Catsnlayers.png\|800px\|thumb\|alt=cats and layers in GRASS GIS\|center\|Figure 1]]		[[File:Catsnlayers.png\|800px\|thumb\|alt=cats and layers in GRASS GIS\|center\|Figure 1]]

	This vector map contains ''boundaries'' and ''centroids'', that determine ''areas'' (see {{cmd\|vectorintro}}). Boundaries are used as linear features to hold tracks geometry. Each feature is automatically assigned an ''internal identifier'' as soon as it is added to the map. The user cannot edit this ''id'', ~~Grass~~ handles it for you, and ensures the uniqueness of each ''id''. Here, ''id''s 1 to 12 are assigned to boundaries, ''id''s 13 to 17 correspond to centroids.		This vector map contains ''boundaries'' and ''centroids'', that determine ''areas'' (see {{cmd\|vectorintro}}). Boundaries are used as linear features to hold tracks geometry. Each feature is automatically assigned an ''internal identifier'' as soon as it is added to the map. The user cannot edit this ''id'', GRASS GIS handles it for you, and ensures the uniqueness of each ''id''. Here, ''id''s 1 to 12 are assigned to boundaries, ''id''s 13 to 17 correspond to centroids.

	We could consider using this ''key-id'' to directly point at remote attribute records in a table. But GRASS provides a more flexible method, based on the ability :		We could consider using this ''key-id'' to directly point at remote attribute records in a table. But GRASS provides a more flexible method, based on the ability :
Line 27:		Line 30:
	These ''identifiers'' are named '''categories''', they are organised in as many sets as you wish, that are named '''layers'''.		These ''identifiers'' are named '''categories''', they are organised in as many sets as you wish, that are named '''layers'''.

	In this example objects are categorized according to three layers. Boundaries have categories in layer 1, centroids have categories in layers 2 and 3. Note :		=== The concept of categories ===

			In this example (and above sketch) objects are categorized according to three layers. Boundaries have categories in layer 1, centroids have categories in layers 2 and 3. Note :
	* 3 boundaries share category 5 within layer 1;		* 3 boundaries share category 5 within layer 1;
	* among others, object with id 7 has zero category.		* among others, object with id 7 has zero category.
Line 39:		Line 44:
	* database contains a table named "owner" which is not related to any layer.		* database contains a table named "owner" which is not related to any layer.

	Database management in ~~grass~~ is ~~assumed~~ by db.* set of commands :		Database management in GRASS GIS is provided by the db.* set of commands :
	* database connections ({{cmd\|db.connect}}, {{cmd\|db.login}});		* database connections ({{cmd\|db.connect}}, {{cmd\|db.login}});
	* SQL operations ({{cmd\|db.select}}, {{cmd\|db.execute}}, etc.),		* SQL operations ({{cmd\|db.select}}, {{cmd\|db.execute}}, etc.), while v.db.* set of commands allows to manage linked tables to a vector map:
	while v.db.* set of commands allows to manage linked tables to a vector map:
	* layers can be listed/maintained ({{cmd\|v.db.connect}});		* layers can be listed/maintained ({{cmd\|v.db.connect}});
	* some v.db.* commands are simply {{cmd\|db.execute}} frontends ({{cmd\|v.db.addcol}}, {{cmd\|v.db.join}}, etc.).		* some v.db.* commands are simply {{cmd\|db.execute}} frontends ({{cmd\|v.db.addcol}}, {{cmd\|v.db.join}}, etc.).

← Older revision		Revision as of 09:10, 5 May 2013
Line 3:		Line 3:
	''Looking for vector geometry management? See [[:Category:Vector\|here]]''		''Looking for vector geometry management? See [[:Category:Vector\|here]]''

	== ~~Grass~~ vector management model ==		== GRASS GIS vector management model ==
	~~Grass~~ vector management model allows to link map objects to database management systems (~~dbms~~). In this short section, we try to give an overview on how ~~Grass~~ matches map features and attributes.		The GRASS GIS vector management model allows to link map objects to database management systems (DBMS). In this short section, we try to give an overview on how GRASS matches map features and attributes.

	Consider creating a map where you would handle (i) parcels with various crops, (ii) a set of owners for these parcels, and (iii) paths to access them. The latter are determined by limits between contiguous fields.		Consider creating a map where you would handle (i) parcels with various crops, (ii) a set of owners for these parcels, and (iii) paths to access them. The latter are determined by limits between contiguous fields.

	One could decide to store data in several distinct maps, e.g.:		One could decide to store data in several distinct maps, e.g.:
Line 12:		Line 12:
	* a map "crop" containing areas, to describe crops;		* a map "crop" containing areas, to describe crops;
	* a map "owner" containing areas, to describe owners.		* a map "owner" containing areas, to describe owners.

	We can point several drawbacks to this method:		We can point several drawbacks to this method:
	* we induce redundancy of geometric features between maps "crop" and "owner". ~~Eventhought~~ their attributes are ~~independant~~, areas share the same geometry;		* we induce redundancy of geometric features between maps "crop" and "owner". Even though their attributes are independent, areas share the same geometry;
	* topological relation between paths and parcels limits won't be ensured when it's about to modify the shape of parcels.		* topological relation between paths and parcels limits won't be ensured when it's about to modify the shape of parcels.
	A better solution will resort to the concept of ''layers''. A single vector map will store geometric features, while several tables in the ~~dbms~~ will handle attribute contents. Figure 1 shows how it works. Let's comment this sketch.		A better solution will resort to the concept of ''layers''. A single vector map will store geometric features, while several tables in the DBMS will handle attribute contents. Figure 1 shows how it works. Let's comment this sketch.

	[[File:Catsnlayers.png\|800px\|thumb\|alt=cats and layers in ~~Grass~~\|center\|Figure 1]]		[[File:Catsnlayers.png\|800px\|thumb\|alt=cats and layers in GRASS GIS\|center\|Figure 1]]

	This vector map contains ''boundaries'' and ''centroids'', that determine ''areas'' (see {{cmd\|vectorintro}}). Boundaries are used as linear features to hold tracks geometry. Each feature is automatically assigned an ''internal identifier'' as soon as it is added to the map. The user cannot edit this ''id'', Grass handles it for you, and ensures the ~~unicity~~ of each ''id''. Here, ''id''s 1 to 12 are assigned to boundaries, ''id''s 13 to 17 correspond to centroids.		This vector map contains ''boundaries'' and ''centroids'', that determine ''areas'' (see {{cmd\|vectorintro}}). Boundaries are used as linear features to hold tracks geometry. Each feature is automatically assigned an ''internal identifier'' as soon as it is added to the map. The user cannot edit this ''id'', Grass handles it for you, and ensures the uniqueness of each ''id''. Here, ''id''s 1 to 12 are assigned to boundaries, ''id''s 13 to 17 correspond to centroids.

	We could consider using this ''key-id'' to directly point at remote attribute records in a table. But ~~Grass~~ provides a more flexible method, based on the ability :		We could consider using this ''key-id'' to directly point at remote attribute records in a table. But GRASS provides a more flexible method, based on the ability :
	* to give either a single ''identifier'' to each feature or to group similar features by giving them all a common ''identifier'';		* to give either a single ''identifier'' to each feature or to group similar features by giving them all a common ''identifier'';
	* to give several ''identifiers'' to an object if it is related to multiple thematic data.		* to give several ''identifiers'' to an object if it is related to multiple thematic data.
Line 39:		Line 40:

	Database management in grass is assumed by db.* set of commands :		Database management in grass is assumed by db.* set of commands :
	* database connections (db.connect, db.login);		* database connections ({{cmd\|db.connect}}, {{cmd\|db.login}});
	* SQL operations (db.select, db.execute, etc.),		* SQL operations ({{cmd\|db.select}}, {{cmd\|db.execute}}, etc.),
	while v.db.* set of commands allows to manage linked tables to a vector map:		while v.db.* set of commands allows to manage linked tables to a vector map:
	* layers can be listed/maintained (v.db.connect);		* layers can be listed/maintained ({{cmd\|v.db.connect}});
	* some v.db.* commands are simply db.execute frontends (v.db.addcol, v.db.join, etc.).		* some v.db.* commands are simply {{cmd\|db.execute}} frontends ({{cmd\|v.db.addcol}}, {{cmd\|v.db.join}}, etc.).
	{{cmd\|v.to.db}} is the command that allows to populate a table attached to a given vector layer; not only categories can be uploaded, but geometric data too (area, perimeter, ccordinates, etc.).		{{cmd\|v.to.db}} is the command that allows to populate a table attached to a given vector layer; not only categories can be uploaded, but geometric data too (area, perimeter, ccordinates, etc.).


	==Vector attribute data processing==		==Vector attribute data processing==