This area is working code for Postgres/Grass interface routines

Download files (32 kb)


Warning - headers and disclaimers are incomplete
This code is not stable and may cause severe problems on your system.

Modules (working) in above package:
d.site.pg
d.vect.pg
d.what.s.pg
g.select.pg
g.stats.pg         
g.column.pg
g.table.pg
d.what.s.pg
        text_functions.sql      - needed for g.stats.pg
                                - should be in system functions but alas....

I have compiled these on a linux i386 RH5.1 (plus some other stuff) system,
and tested them againt Postgres v6.4 database.
See d.site.pg-libs / d.vect.pg-libs for linked library list 
on my machine.

My Postgres version was 6.3 at the start of coding and 6.4 at the end.
All routines were tested against 6.4, g.select.pg and d.site.pg were tested 
against 6.3.

Compiling   ( on linux RH5.1 ) 
     edit grass42/src/CMD/head/
        add PQINCPATH = /usr/local/psql/include
            or whereever Postgres includes are on your system
        add PQLIB = -L/usr/local/pgsql/lib -lpq -lcrypt
            see Postgres documentation of libpq for flags for you specific port

See example Linux head file.

Don't forget to add d.site.pg, g.select.pg, g.table.pg, d.vect.pg to lists/GRASS
and then run grass42/src/CMD/MAKELINKS.my


Thses routines have been cleaned up to have only one block of code drawing on 
the display.  All other routines only construct a SQL statement and pass it 
along.  The seperation of builidng the queries and running the queries should 
be more flexible as time goes by.

I don't understand the need for a join statement in the interactive portions.
I want to strip it out force users who have complex queries to use the read 
from file option instead (anyway it seems to be strictly postquel specific).

To assist users understand SQL statements I added a message listing the exact 
SQL statement being executed.

I added pghost support to all the programs I have worked on.
It defaults to NULL if not entered. (and thus the environment variable PGHOST 
or if that is not set then localhost)

I broke from the
    psql < infile > outfile  
style interface when I was trying to get it to work against a lat/long database.
The data completely filled the column, causing the scanf on the buffer to fail.

Libpq is easier and safer and should ultimately give better performance as 
transactions can be run with conditional testing by the program inside the block.
Transactions can be established in the psql < infile style but each block must 
end before the outfile is available. Additioanlly libpq supports binary (native 
format)

Man libpq on your system if any postgres95 or later has been installed.
Three excellent sample programs are there.

In addition to these catagoery support queries from Postgres, I want to add
direct drawing of the postgres data types point,line,path,and polygon.
But I think that a one-to-one relationship between shapes and attributes may be 
worthwhile, in that case new data type will have to be constructed.

 
Does anyone think that forcing sites to be a single column of point class will cause 
user too much trouble.  They can still create a view such as
  create view testvw as select lat,long,point(lat,long) as well_loc,cat 
      from wells;
and then use the view as the table to query
  d.site.pg tab=testvw coord=well_loc, cat=cat plot=red,x,2
   

Using the point greatly reduces the complexity of the select statement for the
query in d.what.s.pg.

/* Postquel query by J.Soimasuo */
	  fprintf(fp,"SELECT unique (%s.all) \n",
		ktab->answer);
	  fprintf(fp,"where ( ( (%s.%s - %f) * (%s.%s - %f) +\n",
		ktab->answer,xcol->answer, pts->centX, 
		ktab->answer,xcol->answer, pts->centX);
	  fprintf(fp,"(%s.%s - %f) * (%s.%s - %f) ) < %f )\n",
		ktab->answer,ycol->answer, pts->centX,
		ktab->answer,ycol->answer,pts->centY, pts->rad2 );  
	}

/* Postgres Spatial Query */
    sprintf(SQL_stmt, 
     "SELECT * from %s where point(%s,%s) @ '(%f,%f,%f,%f)'::box",
           ktab->answer, xcol->answer,ycol->answer,
           pts->minX, pts->minY, pts->maxX, pts-> maxY);
            
/* use Postgres graphic operators
/*               @ operator tests for point within a box 
	 


Please email me at candrsn@mindspring.com
(Carl Anderson) with comments on this.