grass-pg.html 4.4 KB

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