grass-pg.html 4.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159
  1. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  2. <html>
  3. <head>
  4. <title>GRASS GIS manual: GRASS-PostgreSQL DB driver</title>
  5. <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  6. <link rel="stylesheet" href="grassdocs.css" type="text/css">
  7. </head>
  8. <body bgcolor="white">
  9. <img src="grass_logo.png" alt="GRASS logo"><hr align=center size=6 noshade>
  10. <!-- meta page description: PostgreSQL driver -->
  11. <h1>PostgreSQL DB driver in GRASS</h1>
  12. <h2>KEYWORDS</h2>
  13. database, attribute table, driver
  14. <h2>DESCRIPTION</h2>
  15. The DB (database) driver name in GRASS is <b>pg</b>.
  16. <h2>Creating a PostgreSQL database</h2>
  17. A new database is created with <tt>createdb</tt>, see
  18. the <a href="http://www.postgresql.org/docs/manuals/">PostgreSQL
  19. manual</a> for details.
  20. <h2>Connecting GRASS to PostgreSQL</h2>
  21. <div class="code"><pre>
  22. # example for connecting to a PostgreSQL server:
  23. db.connect driver=pg database="host=myserver.osgeo.org,dbname=mydb"
  24. # password is asked interactively if not specified:
  25. db.login user=myname [pass=secret]
  26. db.connect -p
  27. db.tables -p
  28. </pre></div>
  29. <h2>Supported SQL commands</h2>
  30. All SQL commands supported by PostgreSQL.
  31. It's not possible to use C-like escapes (with backslash like \n etc)
  32. within the SQL syntax.
  33. <h2>Operators available in conditions</h2>
  34. All SQL operators supported by PostgreSQL.
  35. <h2>Adding an unique ID column</h2>
  36. Import vector module require an unique ID column which can
  37. be generated as follows in a PostgreSQL table:
  38. <div class="code"><pre>
  39. db.execute sql="ALTER TABLE mytable ADD ID integer"
  40. db.execute sql="CREATE SEQUENCE mytable_seq"
  41. db.execute sql="UPDATE mytable SET ID = nextval('mytable_seq')"
  42. db.execute sql="DROP SEQUENCE mytable_seq"
  43. </pre></div>
  44. <h2>Attribute import into PostgreSQL</h2>
  45. CSV import into PostgreSQL:
  46. <div class="code"><pre>
  47. \h copy
  48. COPY t1 FROM 'filename' USING DELIMITERS ',';
  49. </pre></div>
  50. <h2>Geometry import from PostgreSQL table into GRASS</h2>
  51. <em><a href="v.in.db.html">v.in.db</a></em> creates a new vector
  52. (points) map from a database table containing
  53. coordinates. See <a href="v.in.db.html">here</a> for examples.
  54. <h2>PostGIS: PostgreSQL with vector geometry</h2>
  55. <a href="http://postgis.refractions.net/">PostGIS</a>:
  56. adds geographic object support to PostgreSQL.
  57. <h3>Example: Import from PostGIS</h3>
  58. In an existing PostGIS database, create the following table:
  59. <div class="code"><pre>
  60. CREATE TABLE test
  61. (
  62. id serial NOT NULL,
  63. mytime timestamp DEFAULT now(),
  64. text varchar,
  65. wkb_geometry geometry,
  66. CONSTRAINT test_pkey PRIMARY KEY (id)
  67. ) WITHOUT OIDS;
  68. # insert value
  69. INSERT INTO test (text, wkb_geometry)
  70. VALUES ('Name',geometryFromText('POLYGON((600000 200000,650000
  71. 200000,650000 250000,600000 250000,600000 200000))',-1));
  72. # register geometry column
  73. select AddGeometryColumn ('postgis', 'test', 'geometry', -1, 'GEOMETRY', 2);
  74. </pre></div>
  75. GRASS can import this PostGIS polygon map as follows:
  76. <div class="code"><pre>
  77. v.in.ogr dsn="PG:host=localhost dbname=postgis user=neteler" layer=test \
  78. output=test type=boundary,centroid
  79. v.db.select test
  80. v.info -t test
  81. </pre></div>
  82. <h4>Geometry Converters</h4>
  83. <ul>
  84. <li><a href="http://postgis.refractions.net/download/">PostGIS with shp2pgsql</a>:<br>
  85. <tt>shp2pgsql -D lakespy2 lakespy2 test > lakespy2.sql</tt>
  86. </li>
  87. <li><a href="http://e00pg.sourceforge.net/">e00pg</a>: E00 to PostGIS filter,
  88. see also <em><a href="v.in.e00.html">v.in.e00</a></em>.
  89. </li>
  90. <li>GDAL/OGR <a href="http://www.gdal.org/ogr/">ogrinfo and ogr2ogr</a>:
  91. GIS vector format converter and library, e.g. ArcInfo or SHAPE to PostGIS.<br>
  92. <tt>ogr2ogr -f "PostgreSQL" shapefile ??</tt>
  93. </li>
  94. </ul>
  95. <h2>SEE ALSO</h2>
  96. <em>
  97. <a href="db.connect.html">db.connect</a>,
  98. <a href="db.execute.html">db.execute</a>
  99. </em>
  100. <p>
  101. <a href="databaseintro.html">Database management in GRASS GIS</a><br>
  102. <a href="database.html">Help pages for database modules</a><br>
  103. <a href="sql.html">SQL support in GRASS GIS</a><br>
  104. <h2>REFERENCES</h2>
  105. <ul>
  106. <li><a href="http://www.postgresql.org/">PostgreSQL web site</a></li>
  107. <li><a href="http://www.pgadmin.org/">pgAdmin graphical user interface</a></li>
  108. <li><a href="http://www.gdal.org/ogr/drv_pg.html">GDAL/OGR PostgreSQL
  109. driver documentation</a></li>
  110. </ul>
  111. <p>
  112. <i>Last changed: $Date$</i>
  113. <hr>
  114. <p><a href="index.html">Main index</a> - <a href="database.html">Database index</a> - <a href="topics.html">Topics index</a> - <a href="keywords.html">Keywords Index</a> - <a href="full_index.html">Full index</a></p>
  115. <p>&copy; 2003-2013 <a href="http://grass.osgeo.org">GRASS Development Team</a>, GRASS GIS 7.0.svn Reference Manual</p>
  116. </body>
  117. </html>