[Mapserver-dev] Fwd: MapServer MySQL interface
Steve Lime
steve.lime at dnr.state.mn.us
Mon Jan 27 13:17:30 EST 2003
--=_CC93CAD8.016012B9
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
In case anyone is interested, and would like to make a comment...
--=_CC93CAD8.016012B9
Content-Type: message/rfc822
Received: from mail.state.mn.us
(state.mn.us [156.99.125.109])
by co5.dnr.state.mn.us; Fri, 20 Dec 2002 02:46:07 -0600
Return-Path: <bear at prometheus.org.yu>
Received: from ns0.tippnet.co.yu by mail.state.mn.us with ESMTP for steve.lime at dnr.state.mn.us; Fri, 20 Dec 2002 02:46:01 -0600
Received: from there (mx.manufacture.co.yu [62.108.119.242])
by tina2.tippnet.co.yu (8.11.6/linuxconf) with SMTP id gBK8ldM16786;
Fri, 20 Dec 2002 09:47:40 +0100
Message-Id: <200212200847.gBK8ldM16786 at tina2.tippnet.co.yu>
From: Attila Csipa <bear at prometheus.org.yu>
Organization: Prometheus
To: Jan Hartmann <jhart at frw.uva.nl>
Subject: MapServer MySQL interface
Date: Fri, 20 Dec 2002 10:41:34 +0100
X-Mailer: KMail [version 1.3.1]
References: <000501c2a24a$bce77440$0100a8c0 at mapping12> <200212131357.gBDDvqs05231 at tina2.tippnet.co.yu> <3DF9EF22.1080206 at frw.uva.nl>
In-Reply-To: <3DF9EF22.1080206 at frw.uva.nl>
Cc: "Steve Lime" <steve.lime at dnr.state.mn.us>
X-RAVMilter-Version: 8.4.1(snapshot 20020919) (tina2.tippnet.co.yu)
MIME-Version: 1.0
Content-Type: Multipart/Mixed;
boundary="------------Boundary-00=_A9WET610XG5GEOD089JM"
--------------Boundary-00=_A9WET610XG5GEOD089JM
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
Subject:
On Friday 13 December 2002 15:30, you wrote:
> BTW, Steve Lime manages all CVS access, so he has to decide if your code
> can get in. I would be happy however to have a closer look at what you
> have done; a working link to MySQL could be a valuable addition. I saw a
Attached is the mysql interface module. The mapserver code has to be slightly
modified on several other places (mostly in switch statements and constants
to handle the new connectiontype and the makefile), but I can send a diff or
eventually put it up in cvs. Keep in mind that this is an early development
version (most definitely not in time for the 3.7 version, but rather for the
following).
the basic tables:
CREATE TABLE layer (
id int(11) NOT NULL auto_increment,
map int(11) NOT NULL default '0',
name varchar(60) default NULL,
type
enum('MS_LAYER_LINE','MS_LAYER_POLYGON','MS_LAYER_ANNOTATION','MS_LAYER_POINT','MS_LAYER_CIRCLE','MS_LAYER_RASTER','MS_LAYER_QUERY')
default 'MS_LAYER_POINT',
base int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY map (map)
) TYPE=MyISAM;
CREATE TABLE shape (
id int(11) NOT NULL default '0',
layer int(11) NOT NULL default '0',
x1 double default NULL,
y1 double default NULL,
z1 double default NULL,
x2 double default NULL,
y2 double default NULL,
z2 double default NULL,
type enum('arc','polygon','point','annotation','raster') default 'point',
vertices int(11) NOT NULL default '2',
attr enum('yes','no') NOT NULL default 'no',
PRIMARY KEY (id),
KEY layer (layer),
KEY turbo (x2,y2,x1,y1)
) TYPE=MyISAM;
CREATE TABLE vertex (
id int(11) NOT NULL auto_increment,
shape int(11) NOT NULL default '0',
x double default NULL,
y double default NULL,
z double default NULL,
PRIMARY KEY (id),
KEY shape (shape),
KEY x (x,y,z)
) TYPE=MyISAM;
--------------Boundary-00=_A9WET610XG5GEOD089JM
Content-Type: text/x-c; charset="iso-8859-1"; name="mapmygis.c"
Content-Transfer-Encoding: Quoted-Printable
Content-Disposition: ATTACHMENT; filename="mapmygis.c"
#include "map.h"
#ifndef FLT_MAX
#define FLT_MAX 25000000.0
#endif
#define USE_MYGIS
#ifdef USE_MYGIS
#ifndef LITTLE_ENDIAN
#define LITTLE_ENDIAN 1
#endif
#ifndef BIG_ENDIAN
#define BIG_ENDIAN 2
#endif
#include <mysql/mysql.h>
#include <string.h>
void mysql_NOTICE_HANDLER(void *arg, const char *message);
char *DATAERRORMESSAGE(char *dataString, char *preamble)
{
=09char=09*message;
=09char=09tmp[5000];
=09message =3D malloc(7000);
=09sprintf(message,"%s",preamble);
=09=09sprintf(tmp,"Error parsing MYGIS data variable. You specified '%s'.<b=
r>\nStandard ways of specifiying are : <br>\n(1) 'geometry_column from geom=
etry_table' <br>\n(2) 'geometry_column from (<sub query>) as foo usin=
g unique <column name> using SRID=3D<srid#>' <br><br>\n<br>\n",=
=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09 dataString);
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"NOTE: for (2) 'using unique' and 'SRID=3D' are optional,=
but its highly recommended that you use them!!! <br><br>\n<br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"The most common problem with (1) is incorrectly uploadin=
g your data. There must be an entry in the geometry_columns table. This w=
ill be automatically done if you used the shp2pgsql program or created your=
geometry column with the AddGeometryColumn() MYGIS function. <br><br>\n<br=
>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"Another important thing to check is that the MYGIS user =
specified in the CONNECTION string does have SELECT permissions on the tabl=
e(s) specified in your DATA string. <br><br>\n<br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"If you are using the (2) method, you've probably made a =
typo.<br>\nExample: 'the_geom from (select the_geom,oid from mytable) as f=
oo using unique oid using SRID=3D76'<br>\nThis is very much like the (1) ex=
ample. The subquery ('select the_geom,oid from mytable') will be executed,=
and mapserver will use 'oid' (a postgresql system column) for uniquely spe=
cifying a geometry (for mapserver queries). The geometry (the_geom) must h=
ave a SRID of 76. <br><br>\n<br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"Example: 'roads from (select table1.roads,table1.rd_seg=
ment_id,table2.rd_name,table2.rd_type from table1,table2 where table1.rd_se=
gment_id=3Dtable2.rd_segment_id) as foo using unique rd_segment_id using SR=
ID=3D89' <br><Br>\n<br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"This is a more complex sub-query involving joining two t=
ables. The resulting geometry (column 'roads') has SRID=3D89, and mapserve=
r will use rd_segment_id to uniquely identify a geometry. The attributes r=
d_type and rd_name are useable by other parts of mapserver.<br><br>\n<br>\n=
");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"To use a view, do something like:<BR>\n'<geometry_column=
> from (SELECT * FROM <view>) as foo using unique <column name> using SRID=3D=
<srid#>'<br>\nFor example: 'the_geom from (SELECT * FROM myview) as foo usi=
ng unique gid using SRID=3D-1' <br><br>\n<br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"NOTE: for the (2) case, the ' as foo ' is requred. The =
'using unique <column>' and 'using SRID=3D' are case sensitive.<br>\n=
");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"NOTE: 'using unique <column>' would normally be th=
e system column 'oid', but for views and joins you'll almost certainly want=
to use a real column in one of your tables. <Br><br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"NOTE: you'll want to build a spatial index on your geome=
tric data:<br><br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"CREATE INDEX <indexname> ON <table> USING GI=
ST (<geometrycolumn> GIST_GEOMETRY_OPS ) <br><br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"You'll also want to put an index on either oid or whatev=
er you used for your unique column:<br><br>\n");
=09=09strcat(message,tmp);
=09=09sprintf(tmp,"CREATE INDEX <indexname> ON <table> (<uni=
quecolumn>)");
=09=09strcat(message,tmp);
=09return message;
}
typedef struct ms_MYGIS_layer_info_t
{
=09char=09=09*sql;=09=09//sql query to send to DB
=09MYSQL mysql,*conn;
=09MYSQL_RES *query_result;
=09MYSQL_RES *query2_result;
=09long=09 =09row_num; =09//what row is the NEXT to be read (for random ac=
cess)
=09long=09 =09total_num; =09//what row is the NEXT to be read (for random =
access)
=09char=09 *query;=09
=09char=09 *query2;=09
=09char=09 *fields;=09 // results from EXPLAIN VERBOSE (or null)
=09char=09=09*urid_name; // name of user-specified unique identifier or OID=
=09char=09=09*user_srid; //zero length =3D calculate, non-zero means using =
this value!
} msMYGISLayerInfo;
MYSQL *prevconn=3DNULL;
char *prevdata=3DNULL; =20
int msMYGISLayerParseData(char *data, char *geom_column_name,
=09=09=09=09=09char *table_name, char *urid_name,char *user_srid);
void mysql_NOTICE_HANDLER(void *arg, const char *message)
{
=09char=09*str,*str2;
=09char *result;
=09if (strstr(message,"QUERY DUMP"))
=09{
=09=09if (=09((msMYGISLayerInfo *) arg)->fields)
=09=09{
=09=09=09free(((msMYGISLayerInfo *) arg)->fields); =09// free up space
=09=09}
=09=09=09result =3D malloc ( 6000) ;
=09=09=09((msMYGISLayerInfo *) arg)->fields =3D result;
=09=09=09result[0] =3D 0; //null terminate it
=09=09//need to parse it a bit
=09=09str =3D (char *) message;
=09=09while (str !=3D NULL)
=09=09{
=09=09=09str =3D strstr(str," :resname ");
=09=09=09if (str !=3D NULL)
=09=09=09{
=09=09=09=09str++; // now points at ":"
=09=09=09=09str=3D strstr(str," ");=09//now points to last " "
=09=09=09=09str++; //now points to start of next word
=09=09=09=09str2 =3D strstr(str," ");=09//points to end of next word
=09=09=09=09if (strncmp(str, "<>", (str2-str))) { // Not a bogus resname
=09=09=09=09if (strlen(result) > 0)
=09=09=09=09{
=09=09=09=09=09strcat(result,",");
=09=09=09=09}
=09=09=09=09=09strncat(result,str, (str2-str) );
=09=09=09=09}
=09=09=09}
=09=09}
=09=09printf("notice returns: %s<br>\n",result);
=09}
}
static int gBYTE_ORDER =3D 0;
int query(msMYGISLayerInfo *layer, char qbuf[]){
int numrows=3D-1;
int i;
if (layer->query_result) // query leftover=20
{
=09mysql_free_result(layer->query_result);
}
printf("%s<BR>\n", qbuf);
if (mysql_query(layer->conn,qbuf) < 0){
mysql_close(layer->conn);
printf("mysql query FAILED real bad...<br>\n");
return MS_FAILURE;
}
if (!(layer->query_result=3Dmysql_store_result(layer->conn))) {
mysql_close(layer->conn);
printf("mysql query FAILED...<br>\n");
return MS_FAILURE;
}
layer->query =3D strdup(qbuf);
if (layer->query_result) //There were some rows found, write 'em out for=
debug
{
numrows =3D mysql_affected_rows(&(layer->mysql));
//printf("%d rows<br>\n", numrows);
for(i=3D0;i<numrows;i++)
{
// row =3D mysql_fetch_row(layer->query_result);
// printf("(%s)<BR>\n",row[0]);
}
}
// mysql_free_result(layer->query_result); // don't free, might be used =
later
return MS_SUCCESS;
}
//open up a connection to the postgresql database using the connection stri=
ng in layer->connection
// ie. "host=3D192.168.50.3 user=3Dpostgres port=3D5555 dbname=3Dmapserv"
int msMYGISLayerOpen(layerObj *layer)
{
=09msMYGISLayerInfo=09*layerinfo;
// int=09=09=09order_test =3D 1;
=09char* DB_HOST =3D NULL;
=09char* DB_USER =3D NULL;
=09char* DB_PASSWD =3D NULL;
=09char* DB_DATABASE =3D NULL;
=09char* delim;
//printf("msMYGISLayerOpen called<br>\n");
=09if (layer->mygislayerinfo)
=09=09return MS_SUCCESS;=09//already open
=09//have to setup a connection to the database
=09layerinfo =3D (msMYGISLayerInfo *) malloc( sizeof(msMYGISLayerInfo) );
=09layerinfo->sql =3D NULL; //calc later
=09layerinfo->row_num=3D0;
=09layerinfo->query_result=3D NULL;
=09layerinfo->query2_result=3D NULL;
=09layerinfo->fields =3D NULL;
// check whether previous connection can be used
if (prevconn !=3D NULL && prevdata !=3D NULL && strcmp(prevdata, layer-=
>data)=3D=3D0){
//printf("Reusing existing connection<BR>\n");=09
layerinfo->conn =3D prevconn;
=09layer->mygislayerinfo =3D (void *) layerinfo;
=09return MS_SUCCESS;=09//already open
} else if (prevconn !=3D NULL) { // a different connection, close shop.=
.
//printf("New connection<BR>\n");=09
mysql_close(prevconn);
=09prevconn =3D NULL;
=09free(layer->mygislayerinfo);
=09layer->mygislayerinfo =3D NULL;
// mysql_close(prevconn);
// free(prevconn);
// free(prevdata);=20
}
=20
if( layer->data =3D=3D NULL )
{
msSetError(MS_QUERYERR,
=09=09=09=09=09DATAERRORMESSAGE("","Error parsing MYGIS data variable: noth=
ing specified in DATA statement.<br><br>\n\nMore Help:<br><br>\n<br>\n"),
=09=09=09=09=09"msMYGISLayerOpen()");
return(MS_FAILURE);
}
//printf("Parsing DB params...");
=09delim =3D strdup(":");
=09DB_HOST =3D strdup(strtok(layer->connection, delim));
=09DB_USER =3D strdup(strtok(NULL, delim));
=09DB_PASSWD =3D strdup(strtok(NULL, delim));
=09DB_DATABASE =3D strdup(strtok(NULL, delim));
=09if (DB_HOST =3D=3D NULL || DB_USER =3D=3D NULL || DB_PASSWD =3D=3D NULL =
|| DB_DATABASE =3D=3D NULL)
=09{
=09=09printf("DB param error %s/%s/%s/%s !\n",DB_HOST,DB_USER,DB_PASSWD,DB_=
DATABASE);
=09=09return MS_FAILURE;
=09}
=09if (strcmp(DB_PASSWD, "none") =3D=3D 0)
=09=09strcpy(DB_PASSWD, "");
//printf("msMYGISLayerOpen1 called<br>\n");
if (!(layerinfo->conn =3D mysql_connect(&(layerinfo->mysql),DB_HOST,DB_=
USER,DB_PASSWD)))
{
=09 printf("Connection SQL server failed.");
=09free(layerinfo);
return MS_FAILURE;
}
//printf("msMYGISLayerOpen2 called<br>\n");
if (mysql_select_db(layerinfo->conn,DB_DATABASE) < 0)
{
=09 printf("Database could not be opened.");
mysql_close(layerinfo->conn);
=09 free(layerinfo);
return MS_FAILURE;
}
//printf("msMYGISLayerOpen3 called<br>\n");
/* if( ((char *) &order_test)[0] =3D=3D 1 )
gBYTE_ORDER =3D LITTLE_ENDIAN;
else
gBYTE_ORDER =3D BIG_ENDIAN;
*/
=09layer->mygislayerinfo =3D (void *) layerinfo;
=09return MS_SUCCESS;
}
// Free the itemindexes array in a layer.
void msMYGISLayerFreeItemInfo(layerObj *layer)
{
//printf("msMYGISLayerFreeItemInfo called<br>\n");
=09if (layer->iteminfo)
=09free(layer->iteminfo);
=09layer->iteminfo =3D NULL;
}
//allocate the iteminfo index array - same order as the item list
int msMYGISLayerInitItemInfo(layerObj *layer)
{
=09int i;
=09int *itemindexes ;
//printf("msMYGISLayerInitItemInfo called<br>\n");
=09if (layer->numitems =3D=3D 0)
=09return MS_SUCCESS;
=09if (layer->iteminfo)
=09 =09free(layer->iteminfo);
=09if((layer->iteminfo =3D (int *)malloc(sizeof(int)*layer->numitems))=3D=3D=
NULL)
=09{
=09=09msSetError(MS_MEMERR, NULL, "msMYGISLayerInitItemInfo()");
=09 =09return(MS_FAILURE);
=09}
=09itemindexes =3D (int*)layer->iteminfo;
=09for(i=3D0;i<layer->numitems;i++)
=09{
=09=09itemindexes[i] =3D i; //last one is always the geometry one - the res=
t are non-geom
=09}
=09return(MS_SUCCESS);
}
//int prep_DB(char=09*geom_table,char *geom_column,layerObj *layer, PGresu=
lt **sql_results,rectObj rect,char *query_string, char *urid_name, char *us=
er_srid)
int prep_DB(char=09*geom_table,char *geom_column,layerObj *layer, MYSQL_RE=
S **sql_results,rectObj rect,char *query_string, char *urid_name, char *use=
r_srid)
{
=09char=09columns_wanted[5000];
=09char=09query_string_0_5[6000];
=09char=09query_string_0_5_real[6000];
//=09char=09query_string_0_6[6000];
=09char=09box3d[200];
=09msMYGISLayerInfo *layerinfo;
=09char *pos_from, *pos_ftab, *pos_space, *pos_paren;
=09char f_table_name[5000];
=09layerinfo =3D (msMYGISLayerInfo *) layer->mygislayerinfo;
=09/* Set the urid name */
=09layerinfo->urid_name =3D urid_name;
=09/* Extract the proper f_table_name from the geom_table string.
=09 * We are expecting the geom_table to be either a single word
=09 * or a sub-select clause that possibly includes a join --
=09 *
=09 * (select column[,column[,...]] from ftab[ natural join table2]) as foo=
=09 *
=09 * We are expecting whitespace or a ')' after the ftab name.
=09 *
=09 */
=09pos_from =3D strstr(geom_table, " from ");
=09if (pos_from =3D=3D NULL) {
=09=09strcpy(f_table_name, geom_table);
=09}
=09else { // geom_table is a sub-select clause
=09=09pos_ftab =3D pos_from + 6; // This should be the start of the ftab na=
me
=09=09pos_space =3D strstr(pos_ftab, " "); // First space
=09=09pos_paren =3D strstr(pos_ftab, ")"); // Closing paren of clause
=09=09if ( (pos_space =3D=3DNULL) || (pos_paren =3D=3DNULL) ) {
=09=09=09 msSetError(MS_QUERYERR,
=09=09=09=09=09=09=09=09DATAERRORMESSAGE(geom_table,"Error parsing MYGIS da=
ta variable: Something is wrong with your subselect statement.<br><br>\n\nM=
ore Help:<br><br>\n<br>\n"),
=09=09=09=09=09"prep_DB()");
=09=09=09return(MS_FAILURE);
=09=09}
=09=09if (pos_paren < pos_space) { // closing parenthesis preceeds any spac=
e
=09=09=09strncpy(f_table_name, pos_ftab, pos_paren - pos_ftab);
=09=09}
=09=09else {
=09=09=09strncpy(f_table_name, pos_ftab, pos_space - pos_ftab);
=09=09}
=09}
=09columns_wanted[1] =3D 0; //len=3D0
=09columns_wanted[0] =3D '*'; //len=3D0
//=09sprintf(box3d,"'BOX3D(%.15g %.15g,%.15g %.15g)'::BOX3D",rect.minx, rec=
t.miny, rect.maxx, rect.maxy);
=09sprintf(box3d,"(shape.x2 > %.15g AND shape.y2 > %.15g AND shape.x1 < %.1=
5g AND shape.y1 < %.15g)",rect.minx, rect.miny, rect.maxx, rect.maxy);
=09// substitute token '!BOX!' in geom_table with the box3d - do at most 1 =
substitution
=09=09if (strstr(geom_table,"!BOX!"))
=09=09{
=09=09=09=09// need to do a substition
=09=09=09=09char=09*start, *end;
=09=09=09=09char=09*result;
=09=09=09=09result =3D malloc(7000);
=09=09=09=09start =3D strstr(geom_table,"!BOX!");
=09=09=09=09end =3D start+5;
=09=09=09=09start[0] =3D0;
=09=09=09=09result[0]=3D0;
=09=09=09=09strcat(result,geom_table);
=09=09=09=09strcat(result,box3d);
=09=09=09=09strcat(result,end);
=09=09=09=09geom_table=3D result;
=09=09}
// =09query(layerinfo, "SELECT "); // attrib ?
=09if (layer->filter.string =3D=3D NULL)
=09{
//=09=09sprintf(query_string_0_5,"SELECT count(%s) from %s WHERE %s && %s",=
//=09=09=09=09=09=09columns_wanted,geom_table,geom_column,box3d);
=09=09sprintf(query_string_0_5,"SELECT count(%s) from %s WHERE %s",
=09=09=09=09=09=09columns_wanted,geom_table,box3d);
=09=09sprintf(query_string_0_5_real,"SELECT shape.id, shape.vertices, shape=
type, vertex.x, vertex.y, vertex.z from %s WHERE %s",
=09=09=09=09=09=09geom_table,box3d);
/*=09=09if (strlen(user_srid) =3D=3D 0)
=09=09{
=09=09=09sprintf(query_string_0_6,"DECLARE mycursor BINARY CURSOR FOR SELEC=
T %s from %s WHERE %s && setSRID(%s, find_srid('','%s','%s') )",
=09=09=09=09=09=09columns_wanted,geom_table,geom_column,box3d,f_table_name,=
geom_column);
=09=09}
=09=09else=09//use the user specified version
=09=09{
=09=09=09sprintf(query_string_0_6,"DECLARE mycursor BINARY CURSOR FOR SELEC=
T %s from %s WHERE %s && setSRID(%s, %s )",
=09=09=09=09=09=09columns_wanted,geom_table,geom_column,box3d,user_srid);
=09=09}
*/=09}
=09else
=09{
//=09=09sprintf(query_string_0_5,"SELECT count(%s) from %s WHERE (%s) and (=
%s && %s)",
//=09=09=09=09=09=09columns_wanted,geom_table,layer->filter.string,geom_col=
umn,box3d);
=09=09sprintf(query_string_0_5,"SELECT count(%s) from %s WHERE (%s) and (%s=
)",
=09=09=09=09=09=09columns_wanted,geom_table,layer->filter.string,box3d);
=09=09sprintf(query_string_0_5_real,"SELECT shape.id, shape.vertices, shape=
type, vertex.x, vertex.y, vertex.z from %s WHERE (%s) AND (%s)",
=09=09=09=09=09=09geom_table,layer->filter.string,box3d);
/*=09=09if (strlen(user_srid) =3D=3D 0)
=09=09{
=09=09=09sprintf(query_string_0_6,"DECLARE mycursor BINARY CURSOR FOR SELEC=
T %s from %s WHERE (%s) and (%s && setSRID( %s,find_srid('','%s','%s') ))",=
=09=09=09=09=09=09columns_wanted,geom_table,layer->filter.string,geom_colum=
n,box3d,f_table_name,geom_column);
=09=09}
=09=09else
=09=09{
=09=09=09sprintf(query_string_0_6,"DECLARE mycursor BINARY CURSOR FOR SELEC=
T %s from %s WHERE (%s) and (%s && setSRID( %s,%s) )",
=09=09=09=09=09=09columns_wanted,geom_table,layer->filter.string,geom_colum=
n,box3d,user_srid);
=09=09}
*/=09}
=09query(layerinfo, query_string_0_5_real);
=09layerinfo->total_num =3D 10000000;
=09return (MS_SUCCESS);
}
// build the neccessary SQL
// allocate a cursor for the SQL query
// get ready to read from the cursor
//
// For queries, we need to also retreive the OID for each of the rows
// So GetShape() can randomly access a row.
int msMYGISLayerWhichShapes(layerObj *layer, rectObj rect)
{
=09char=09*query_str;
=09char=09table_name[5000];
=09char=09geom_column_name[5000];
=09char=09urid_name[5000];
=09char=09user_srid[5000];
=09int=09set_up_result;
=09msMYGISLayerInfo=09*layerinfo;
//printf("msMYGISLayerWhichShapes called<br>\n");
=09layerinfo =3D (msMYGISLayerInfo *) layer->mygislayerinfo;
=09if (layerinfo =3D=3D NULL)
=09{
=09=09//layer not opened yet
=09=09msSetError(MS_QUERYERR, "msMYGISLayerWhichShapes called on unopened l=
ayer (layerinfo =3D NULL)",
"msMYGISLayerWhichShapes()");
=09=09return(MS_FAILURE);
=09}
if( layer->data =3D=3D NULL )
{
msSetError(MS_QUERYERR,
"Missing DATA clause in MYGIS Layer definition. DAT=
A statement must contain 'geometry_column from table_name' or 'geometry_col=
umn from (sub-query) as foo'.",
"msMYGISLayerWhichShapes()");
return(MS_FAILURE);
}
=09query_str =3D (char *) malloc(6000); //should be big enough
=09memset(query_str,0,6000);=09=09//zero it out
//=09printf("%s/%s/%s/%s/%s<br>\n", layer->data, geom_column_name, table_na=
me, urid_name,user_srid);
=09msMYGISLayerParseData(layer->data, geom_column_name, table_name, urid_na=
me,user_srid);
//=09printf("%s<br>\n", layer->data);
//=09printf("%s/%s/%s/%s/%s<br>\n", layer->data, geom_column_name, table_na=
me, urid_name,user_srid);
=09set_up_result=3D prep_DB(table_name,geom_column_name, layer, &(layerinfo=
->query_result), rect,query_str, urid_name,user_srid);
=09if (set_up_result !=3D MS_SUCCESS)
=09=09return set_up_result; //relay error
=09layerinfo->sql =3D query_str;
=09layerinfo->row_num =3D0;
=09 return(MS_SUCCESS);
}
// Close the MYGIS record set and connection
int msMYGISLayerClose(layerObj *layer)
{
=09msMYGISLayerInfo=09*layerinfo;
//printf("msMYGISLayerClose called<br>\n");
=09layerinfo =3D (msMYGISLayerInfo *) layer->mygislayerinfo;
=09if (layerinfo !=3D NULL)
=09{
// let's not overreact, we'll skip over the sql closing nice and easy so a =
following connection can be made=20
prevconn =3D layerinfo->conn;
prevdata =3D strdup(layer->data);
/* mysql_close(layerinfo->conn);
=09layerinfo->conn =3D NULL;
=09free(layerinfo);
=09layer->mygislayerinfo =3D NULL;
*/=09}
=09if (setvbuf(stdout, NULL, _IONBF , 0)){
=09=09printf("Whoops...");
=09};
//=09fflush(NULL);
=09return(MS_SUCCESS);
}
//*******************************************************
// wkb is assumed to be 2d (force_2d)
// and wkb is a GEOMETRYCOLLECTION (force_collection)
// and wkb is in the endian of this computer (asbinary(...,'[XN]DR'))
// each of the sub-geom inside the collection are point,linestring, or poly=
gon
//
// also, int is 32bits long
// double is 64bits long
//*******************************************************
// convert the wkb into points
//=09points -> pass through
//=09lines-> constituent points
//=09polys-> treat ring like line and pull out the consituent points
//int=09force_to_points(char=09*wkb, shapeObj *shape)
int=09force_to_points(MYSQL_ROW row, MYSQL_RES* qresult, shapeObj *shape, l=
ong *cnt)
{
=09//we're going to make a 'line' for each entity (point, line or ring) in =
the geom collection
=09int ngeoms ;
=09int=09t, points=3D1;
=09char* type;
=09lineObj=09line=3D{0,NULL};
=09shape->type =3D MS_SHAPE_NULL; //nothing in it
=09ngeoms =3D atoi(row[1]);
=09type =3D row[2];
=09shape->type =3D MS_SHAPE_POINT;
=09line.numpoints =3D ngeoms;
=09line.point =3D (pointObj *) malloc (ngeoms * sizeof(pointObj));
=09line.point[0].x =3D atof(row[3]);
=09line.point[0].y =3D atof(row[4]);
=09line.point[0].m =3D 0;
=09for (t=3D1; t<ngeoms; t++)
=09{
=09 row =3D mysql_fetch_row(qresult);
=09=09if (row=3D=3DNULL){
=09=09=09printf("INTERNAL nullfetch<BR>\n");
=09=09=09return(MS_FAILURE);
=09=09}
=09=09line.point[t].x =3D atof(row[3]);
=09=09line.point[t].y =3D atof(row[4]);
=09=09line.point[t].m =3D 0;
=09}
=09if (ngeoms !=3D points)
=09=09printf("Warning ng%d/p%d\n", ngeoms, points);
=09msAddLine(shape,&line);
=09free(line.point);
=09return(MS_SUCCESS);
}
//convert the wkb into lines
// points-> remove
// lines -> pass through
// polys -> treat rings as lines
//int=09force_to_lines(char=09*wkb, shapeObj *shape)
int=09force_to_lines(MYSQL_ROW row, MYSQL_RES* qresult, shapeObj *shape, lo=
ng *cnt)
{
=09int ngeoms ;
=09int=09t, points=3D1;
=09float x,y;
=09char* type;
=09lineObj=09line=3D{0,NULL};
=09shape->type =3D MS_SHAPE_NULL; //nothing in it
=09ngeoms =3D atoi(row[1]);
=09x =3D atof(row[3]);
=09y =3D atof(row[4]);
=09type =3D row[2];
=09line.point =3D (pointObj *) malloc (sizeof(pointObj)* ngeoms ); //point =
struct
=09for (t=3D1; t<ngeoms; t++)
=09{
=09=09int id =3D atoi(row[0]);
=09=09row =3D mysql_fetch_row(qresult);
=09=09if (row=3D=3DNULL){
=09=09=09printf("INTERNAL nullfetch (id%i, t%d, ng%d)<BR>\n", id, t, ngeoms=
);
=09=09=09return(MS_FAILURE);
=09=09}
//=09 printf("(%s/%s/%s/%s/%s/%s/%i)<BR>\n",row[0],row[1],row[2],row=
[3],row[4],row[5],cnt);
=09=09if (strcmp(type, "arc") =3D=3D 0) //linestring
=09=09{
=09=09=09shape->type =3D MS_SHAPE_LINE;
//=09=09=09memcpy(&line.numpoints, wkb[offset+5],4); //num points
=09=09=09points =3D 2;
=09=09=09
=09=09=09line.point[0].x =3D x;
=09=09=09line.point[0].y =3D y;
=09=09=09line.point[0].m =3D 0;
=09=09=09line.point[1].x =3D atof(row[3]);
=09=09=09line.point[1].y =3D atof(row[4]);
=09=09=09line.point[1].m =3D 0;
=09=09=09x =3D atof(row[3]);
=09=09=09y =3D atof(row[4]);
=09=09}
=09=09if (strcmp(type, "polygon") =3D=3D 0) //polygon
=09=09{
=09=09=09if (t =3D=3D 1){
=09=09=09=09line.point[0].x =3D x;
=09=09=09=09line.point[0].y =3D y;
=09=09=09=09line.point[0].m =3D 0;
=09=09=09}
=09=09=09shape->type =3D MS_SHAPE_LINE;
=09=09=09=09line.point[t].x =3D atof(row[3]);
=09=09=09=09line.point[t].y =3D atof(row[4]);
=09=09=09=09line.point[t].m =3D 0;
=09=09=09=09points++;
=09=09}
=09}
=09if (ngeoms !=3D points)
=09=09printf("Warning ng%d/p%d\n", ngeoms, points);
=09line.numpoints=3Dpoints;
=09if (points > 1){
=09=09msAddLine(shape,&line);
//=09=09printf("points: %d<BR>\n",points);
=09}=09
=09free(line.point);
=09return(MS_SUCCESS);
}
// point -> reject
// line -> reject
// polygon -> lines of linear rings
int=09force_to_polygons(MYSQL_ROW row, MYSQL_RES* qresult, shapeObj *shape,=
long *cntchar)
{
=09int ngeoms ;
=09int=09t,points=3D1;
=09float x,y;
=09char *type;
=09lineObj=09line=3D{0,NULL};
=09shape->type =3D MS_SHAPE_NULL; //nothing in it
=09ngeoms =3D atoi(row[1]);
=09x =3D atof(row[3]);
=09y =3D atof(row[4]);
=09type =3D row[2];
=09
=09//we do one shape per call -> all geoms in this shape are the point of t=
he poly
=09line.point =3D (pointObj *) malloc (sizeof(pointObj)* ngeoms ); //point =
struct
=09line.point[0].x =3D x;
=09line.point[0].y =3D y;
=09line.point[0].m =3D 0;
=09line.numpoints =3D ngeoms;
=09for (t=3D1; t<ngeoms; t++)
=09{
=09=09//cannot do anything with a point
=09 row =3D mysql_fetch_row(qresult);
=09=09if (row=3D=3DNULL){
=09=09=09printf("INTERNAL nullfetch<BR>\n");
=09=09=09return(MS_FAILURE);
=09=09}
=09=09if (strcmp(type, "polygon") =3D=3D 0) //polygon
=09=09{
=09=09=09shape->type =3D MS_SHAPE_POLYGON;
=09=09=09
=09=09=09line.point[t].x =3D atof(row[3]);
=09=09=09line.point[t].y =3D atof(row[4]);
=09=09=09line.point[t].m =3D 0;
=09=09=09points++;
=09=09}
=09}
=09line.numpoints =3D points;
=09if (ngeoms !=3D points)
=09=09printf("Warning ng%d/p%d\n", ngeoms, points);
=09msAddLine(shape,&line);
=09free(line.point);
=09return(MS_SUCCESS);
}
// if there is any polygon in wkb, return force_polygon
// if there is any line in wkb, return force_line
// otherwise return force_point
int=09dont_force(MYSQL_ROW row, MYSQL_RES* qresult, shapeObj *shape, long *=
cntchar)
//int=09dont_force(char=09*wkb, shapeObj *shape)
{
=09int=09best_type;
=09char* type;
//printf("dont force");
=09best_type =3D MS_SHAPE_NULL; //nothing in it
=09type =3D row[2];
=09if (strcmp(type, "point")=3D=3D0)
=09=09=09best_type =3D MS_SHAPE_POINT;
=09if (strcmp(type, "annotation")=3D=3D0)
=09=09=09best_type =3D MS_SHAPE_POINT;
=09if (strcmp(type, "arc")=3D=3D0)
=09=09=09best_type =3D MS_SHAPE_LINE;
=09if (strcmp(type, "polygon")=3D=3D0)
=09=09=09best_type =3D MS_SHAPE_POLYGON;
=09if (best_type =3D=3D MS_SHAPE_POINT)
=09{
=09=09return force_to_points(row, qresult, shape, cntchar);
=09}
=09if (best_type =3D=3D MS_SHAPE_LINE)
=09{
=09=09return force_to_lines(row, qresult, shape, cntchar);
=09}
=09if (best_type =3D=3D MS_SHAPE_POLYGON)
=09{
=09=09return force_to_polygons(row, qresult, shape, cntchar);
=09}
=09printf("unkntype %s, ", type);
=09return(MS_FAILURE); //unknown type
}
//find the bounds of the shape
void find_bounds(shapeObj *shape)
{
=09int t,u;
=09int first_one =3D 1;
=09for (t=3D0; t< shape->numlines; t++)
=09{
=09=09for(u=3D0;u<shape->line[t].numpoints; u++)
=09=09{
=09=09=09if (first_one)
=09=09=09{
=09=09=09=09shape->bounds.minx =3D shape->line[t].point[u].x;
=09=09=09=09shape->bounds.maxx =3D shape->line[t].point[u].x;
=09=09=09=09shape->bounds.miny =3D shape->line[t].point[u].y;
=09=09=09=09shape->bounds.maxy =3D shape->line[t].point[u].y;
=09=09=09=09first_one =3D 0;
=09=09=09}
=09=09=09else
=09=09=09{
=09=09=09=09if (shape->line[t].point[u].x < shape->bounds.minx)
=09=09=09=09=09shape->bounds.minx =3D shape->line[t].point[u].x;
=09=09=09=09if (shape->line[t].point[u].x > shape->bounds.maxx)
=09=09=09=09=09shape->bounds.maxx =3D shape->line[t].point[u].x;
=09=09=09=09if (shape->line[t].point[u].y < shape->bounds.miny)
=09=09=09=09=09shape->bounds.miny =3D shape->line[t].point[u].y;
=09=09=09=09if (shape->line[t].point[u].y > shape->bounds.maxy)
=09=09=09=09=09shape->bounds.maxy =3D shape->line[t].point[u].y;
=09=09=09}
=09=09}
=09}
}
//find the next shape with the appropriate shape type (convert it if necess=
ary)
// also, load in the attribute data
//MS_DONE =3D> no more data
int msMYGISLayerNextShape(layerObj *layer, shapeObj *shape)
{
=09int=09result;
=09msMYGISLayerInfo=09*layerinfo;
=09layerinfo =3D (msMYGISLayerInfo *) layer->mygislayerinfo;
//printf("msMYGISLayerNextShape called<br>\n");
=09if (layerinfo =3D=3D NULL)
=09{
=09msSetError(MS_QUERYERR, "NextShape called with layerinfo =3D NUL=
L",
"msMYGISLayerNextShape()");
=09=09return(MS_FAILURE);
=09}
=09result=3D msMYGISLayerGetShapeRandom(layer, shape, &(layerinfo->row_num)=
);
=09// getshaperandom will increment the row_num
=09if (result)
=09=09layerinfo->row_num ++;
//printf("RES%i\n",result);
=09return result;
}
//Used by NextShape() to access a shape in the query set
// TODO: only fetch 1000 rows at a time. This should check to see if the
// requested feature is in the set. If it is, return it, otherwise
// =09 grab the next 1000 rows.
int msMYGISLayerGetShapeRandom(layerObj *layer, shapeObj *shape, long *reco=
rd)
{
=09msMYGISLayerInfo=09*layerinfo;
=09int=09=09=09result,t;
=09char=09=09=09=09tmpstr[500];
MYSQL_ROW row;
MYSQL_ROW row_attr;
int numrows2 =3D 0;
=09layerinfo =3D (msMYGISLayerInfo *) layer->mygislayerinfo;
//printf("msMYGISLayerGetShapeRandom : called row %li<br>\n",*record);
=09if (layerinfo =3D=3D NULL)
=09{
=09msSetError(MS_QUERYERR, "GetShape called with layerinfo =3D NULL=
",
"msMYGISLayerGetShape()");
=09=09return(MS_FAILURE);
=09}
=09if (layerinfo->conn =3D=3D NULL)
=09{
=09msSetError(MS_QUERYERR, "NextShape called on MYGIS layer with no=
connection to DB.",
"msMYGISLayerGetShape()");
=09=09return(MS_FAILURE);
=09}
=09if (layerinfo->query_result =3D=3D NULL)
=09{
=09msSetError(MS_QUERYERR, "GetShape called on MYGIS layer with inv=
alid DB query results.",
"msMYGISLayerGetShapeRandom()");
=09=09return(MS_FAILURE);
=09}
=09msInitShape(shape);
=09shape->type =3D MS_SHAPE_NULL;
//return (MS_FAILURE); // a little cheating can't harm...
=09while(shape->type =3D=3D MS_SHAPE_NULL)
=09{
=09=09if ( (*record) < layerinfo->total_num )
=09=09{
=09=09=09//retreive an item
=09 row =3D mysql_fetch_row(layerinfo->query_result);
=09=09if (row=3D=3DNULL){
//=09=09=09printf("nullfetch<BR>\n");
=09=09=09return(MS_DONE);
=09=09}
//=09=09 layerinfo->total_num =3D row[0];
//=09 printf("HEAD (%s/%s/%s/%s/%s/%s/%s)<BR>\n",row[0],row[1],r=
ow[2],row[3],row[4],row[5],row[6]);
=09=09=09//wkb =3D (char *) PQgetvalue(layerinfo->query_result, (*record), =
layer->numitems);
=09=09=09switch(layer->type)
=09=09=09{
=09=09=09=09case MS_LAYER_POINT:
=09=09=09=09=09result =3D force_to_points(row, layerinfo->query_result, sha=
pe, record);
=09=09=09=09=09break;
=09=09=09=09case MS_LAYER_LINE:
=09=09=09=09=09result =3D force_to_lines(row, layerinfo->query_result, shap=
e, record);
=09=09=09=09=09break;
=09=09=09=09case MS_LAYER_POLYGON:
=09=09=09=09=09result =3D force_to_polygons(row, layerinfo->query_result, s=
hape, record);
=09=09=09=09=09break;
=09=09=09=09case MS_LAYER_ANNOTATION:
=09=09=09=09case MS_LAYER_QUERY:
=09=09=09=09=09result =3D dont_force(row, layerinfo->query_result, shape, r=
ecord);
=09=09=09=09=09break;
case MS_LAYER_RASTER:
msDebug( "Ignoring MS_LAYER_RASTER =
in mapMYGIS.c<br>\n" );
break;
case MS_LAYER_CIRCLE:
msDebug( "Ignoring MS_LAYER_CIRCLE =
in mapMYGIS.c<br>\n" );
break;
=09=09=09}
=09=09=09if (shape->type !=3D MS_SHAPE_NULL)
=09=09=09{
=09=09=09=09//have to retreive the attributes
=09=09=09 shape->values =3D (char **) malloc(sizeof(char *) * layer-=
>numitems);
=09=09=09=09shape->index =3D atoi(row[0]);
=09=09=09=09shape->numvalues =3D layer->numitems;
=09=09=09=09if (layer->numitems > 0){
=09=09=09=09=09for (t=3D0;t<layer->numitems;t++){
=09=09=09=09=09=09sprintf(tmpstr, "%d", t);
=09=09=09=09=09=09shape->values[t]=3Dstrdup("");
=09=09=09=09=09}
=09=09=09=09=09sprintf(tmpstr,"select attribute, value from shape_attr wher=
e shape_attr.shape=3D'%d'", shape->index);
=09=09=09=09=09 if (layerinfo->query2_result !=3D NULL) // query leftover=
=20
=09=09=09=09=09 {
=09=09=09=09=09=09mysql_free_result(layerinfo->query2_result);
=09=09=09=09=09 }
=09=09=09=09=09printf("%s<BR>\n", tmpstr);
=09=09=09=09=09 if (mysql_query(layerinfo->conn,tmpstr) < 0){
=09=09=09=09=09 mysql_close(layerinfo->conn);
=09=09=09=09=09printf("mysql query FAILED real bad...<br>\n");
=09=09=09=09=09=09return MS_FAILURE;
=09=09=09=09=09 }
=09=09=09=09=09 if (!(layerinfo->query2_result=3Dmysql_store_result(laye=
rinfo->conn))) {
=09=09=09=09=09 mysql_close(layerinfo->conn);
=09=09=09=09=09printf("mysql query FAILED...<br>\n");
=09=09=09=09=09=09return MS_FAILURE;
=09=09=09=09=09 }
=09=09=09=09=09 layerinfo->query2 =3D strdup(tmpstr);
=09=09=09=09=09 if (layerinfo->query2_result) //There were some rows foun=
d, write 'em out for debug
=09=09=09=09=09 {
//=09=09=09=09=09 numrows2 =3D mysql_affected_rows(&(layerinfo->mysql=
));
//=09=09=09=09=09 printf("%d rows<br>\n", numrows2);
=09=09=09=09=09=09=09=09=20
//=09=09=09=09=09=09for(t=3D0;t<numrows2;t++)
=09=09=09=09=09=09while ( (row_attr =3D mysql_fetch_row(layerinfo->query2_r=
esult)) )
=09=09=09=09=09=09{
=09=09=09=09=09=09=09if (row_attr=3D=3DNULL){
=09=09=09=09=09=09=09=09printf("attr_nullfetch(%s-%d/%d)<BR>\n",tmpstr,t,nu=
mrows2);
//=09=09=09=09=09=09=09=09return(MS_DONE);
=09=09=09=09=09=09=09}
//=09=09=09=09=09 printf("%s,%s,%s/%s,%s<BR>\n", layer->labelite=
m, layer->labelsizeitem, layer->labelangleitem, row_attr[0], row_attr[1]);
=09=09=09=09=09=09 if (layer->labelitem && strlen(row_attr[1]) > 0 && st=
rcmp(row_attr[0], layer->labelitem)=3D=3D0){
=09=09=09=09=09=09 =09shape->values[layer->labelitemindex]=3Dstrdup(row_=
attr[1]);
=09=09=09=09=09=09 }
=09=09=09=09=09=09 if (layer->labelsizeitem && strlen(row_attr[1]) > 0 &=
& strcmp(row_attr[0], layer->labelsizeitem)=3D=3D0){
=09=09=09=09=09=09 =09shape->values[layer->labelsizeitemindex]=3Dstrdup(=
row_attr[1]);
=09=09=09=09=09=09 }
=09=09=09=09=09=09 if (layer->labelangleitem && strlen(row_attr[1]) > 0 =
&& strcmp(row_attr[0], layer->labelangleitem)=3D=3D0){
=09=09=09=09=09=09 =09shape->values[layer->labelangleitemindex]=3Dstrdup=
(row_attr[1]);
=09=09=09=09=09=09 }
=09=09=09=09=09=09}
=09=09=09=09=09 }
=09=09=09=09}
=09=09=09=09find_bounds(shape);
=09=09=09=09(*record)++; =09=09//move to next shape
=09=09=09=09return (MS_SUCCESS);
=09=09=09}
=09=09=09else
=09=09=09{
=09=09=09=09(*record)++; //move to next shape
=09=09=09}
=09=09}
=09=09else
=09=09{
=09=09=09return (MS_DONE);
=09=09}
=09}
=09msFreeShape(shape);
=09return(MS_FAILURE);
}
// Execute a query on the DB based on record being an OID.
int msMYGISLayerGetShape(layerObj *layer, shapeObj *shape, long record)
{
=09char=09*query_str;
=09char=09table_name[5000];
=09char=09geom_column_name[5000];
=09char=09urid_name[5000];
=09char=09user_srid[5000];
=09//int=09nitems;
=09char=09columns_wanted[5000];
=09char=09temp[5000];
=09msMYGISLayerInfo=09*layerinfo;
=09int=09t;
printf("msMYGISLayerGetShape called for record =3D %u<br>\n",record);
=09layerinfo =3D (msMYGISLayerInfo *) layer->mygislayerinfo;
=09if (layerinfo =3D=3D NULL)
=09{
=09=09//layer not opened yet
=09=09msSetError(MS_QUERYERR, "msMYGISLayerGetShape called on unopened laye=
r (layerinfo =3D NULL)",
"msMYGISLayerGetShape()");
=09=09return(MS_FAILURE);
=09}
=09query_str =3D (char *) malloc(6000); //should be big enough
=09memset(query_str,0,6000);=09=09//zero it out
=09msMYGISLayerParseData(layer->data, geom_column_name, table_name, urid_na=
me,user_srid);
=09if (layer->numitems =3D=3D0) //dont need the oid since its really record=
=09{
=09=09if (gBYTE_ORDER =3D=3D LITTLE_ENDIAN)
=09=09=09sprintf(columns_wanted,"asbinary(force_collection(force_2d(%s)),'N=
DR')", geom_column_name);
=09=09else
=09=09=09sprintf(columns_wanted,"asbinary(force_collection(force_2d(%s)),'X=
DR')", geom_column_name);
=09}
=09else
=09{
=09=09columns_wanted[0] =3D 0; //len=3D0
=09=09for (t=3D0;t<layer->numitems; t++)
=09=09{
=09=09=09sprintf(temp,"%s::text,",layer->items[t]);
=09=09=09strcat(columns_wanted,temp);
=09=09}
=09=09if (gBYTE_ORDER =3D=3D LITTLE_ENDIAN)
=09=09=09sprintf(temp,"asbinary(force_collection(force_2d(%s)),'NDR')", geo=
m_column_name);
=09=09else
=09=09=09sprintf(temp,"asbinary(force_collection(force_2d(%s)),'XDR')", geo=
m_column_name);
=09=09strcat(columns_wanted,temp);
=09}
=09=09sprintf(query_str,"DECLARE mycursor BINARY CURSOR FOR SELECT %s from =
%s WHERE %s =3D %li", columns_wanted,table_name,urid_name,record);
printf("msMYGISLayerGetShape: %s <br>\n",query_str);
/* query_result =3D PQexec(layerinfo->conn, "BEGIN");
if (!(query_result) || PQresultStatus(query_result) !=3D PGRES_COMMAND_=
OK)
{
=09 msSetError(MS_QUERYERR, "Error executing MYGIS BEGIN statement.=
",
"msMYGISLayerGetShape()");
=09PQclear(query_result);
=09 =09query_result =3D NULL;
=09=09return(MS_FAILURE);
}
query_result =3D PQexec(layerinfo->conn, "set enable_seqscan =3D off");=
if (!(query_result) || PQresultStatus(query_result) !=3D PGRES_COMMAND_=
OK)
{
=09 msSetError(MS_QUERYERR, "Error executing MYGIS 'set enable_seqsca=
n off' statement.",
"msMYGISLayerGetShape()");
=09PQclear(query_result);
=09 =09query_result =3D NULL;
=09=09return(MS_FAILURE);
}
PQclear(query_result);
query_result =3D PQexec(layerinfo->conn, query_str );
if (!(query_result) || PQresultStatus(query_result) !=3D PGRES_COMMAND_=
OK)
{
=09=09char tmp[4000];
=09=09sprintf(tmp, "Error executing MYGIS SQL statement: %s", query_str)=
;
=09msSetError(MS_QUERYERR, tmp,
"msMYGISLayerGetShape()");
=09PQclear(query_result);
=09 =09query_result =3D NULL;
=09=09return(MS_FAILURE);
}
PQclear(query_result);
query_result =3D PQexec(layerinfo->conn, "FETCH ALL in mycursor");
if (!(query_result) || PQresultStatus(query_result) !=3D PGRES_TUPLES_=
OK)
{
=09=09char tmp[4000];
=09=09sprintf(tmp, "Error executing MYGIS SQL statement (in FETCH ALL): =
%s <br><br>\n\nMore Help:", query_str);
=09msSetError(MS_QUERYERR, tmp,
"msMYGISLayerWhichShapes()");
=09PQclear(query_result);
=09 =09query_result =3D NULL;
=09=09return(MS_FAILURE);
}
=09=09=09//query has been done, so we can retreive the results
=09shape->type =3D MS_SHAPE_NULL;
=09=09if ( 0 < PQntuples(query_result) ) //only need to get one shape
=09=09{
=09=09=09//retreive an item
=09=09=09wkb =3D (char *) PQgetvalue(query_result, 0, layer->numitems); //=
layer->numitems is the wkt column
=09=09=09switch(layer->type)
=09=09=09{
=09=09=09=09case MS_LAYER_POINT:
=09=09=09=09=09result =3D force_to_points(wkb, shape);
=09=09=09=09=09break;
=09=09=09=09case MS_LAYER_LINE:
=09=09=09=09=09result =3D force_to_lines(wkb, shape);
=09=09=09=09=09break;
=09=09=09=09case MS_LAYER_POLYGON:
=09=09=09=09=09result =3D =09force_to_polygons(wkb, shape);
=09=09=09=09=09break;
=09=09=09=09case MS_LAYER_ANNOTATION:
=09=09=09=09case MS_LAYER_QUERY:
=09=09=09=09=09result =3D dont_force(wkb,shape);
=09=09=09=09=09break;
case MS_LAYER_RASTER:
msDebug( "Ignoring MS_LAYER_RASTER =
in mapMYGIS.c<br>\n" );
break;
case MS_LAYER_CIRCLE:
msDebug( "Ignoring MS_LAYER_RASTER =
in mapMYGIS.c<br>\n" );
=09=09=09}
=09=09=09if (shape->type !=3D MS_SHAPE_NULL)
=09=09=09{
=09=09=09=09//have to retreive the attributes
=09=09=09=09shape->values =3D (char **) malloc(sizeof(char *) * layer->numi=
tems);
=09=09=09=09for (t=3D0;t<layer->numitems;t++)
=09=09=09=09{
printf("msMYGISLayerGetShape: finding attribute info for '%s'<br>\n",layer-=
>items[t]);
=09=09=09=09=09 temp1=3D (char *) PQgetvalue(query_result, 0, t);
=09=09=09=09=09 size =3D PQgetlength(query_result,0, t ) ;
=09=09=09=09=09 temp2 =3D (char *) malloc(size+1 );
=09=09=09=09=09 memcpy(temp2, temp1, size);
=09=09=09=09=09 temp2[size] =3D 0; //null terminate it
=09=09=09=09=09 shape->values[t] =3D temp2;
printf("msMYGISLayerGetShape: shape->values[%i] has value '%s'<br>\n",t,sha=
pe->values[t]);
=09=09=09=09}
=09=09=09=09shape->index =3D record;
=09=09=09=09shape->numvalues =3D layer->numitems;
=09=09=09=09find_bounds(shape);
=09=09=09=09return (MS_SUCCESS);
=09=09=09}
=09=09}
=09=09else
=09=09{
=09=09=09return (MS_DONE);
=09=09}
=09msFreeShape(shape);
*/
=09return(MS_FAILURE);
}
//query the DB for info about the requested table
//
// CHEAT: dont look in the system tables, get query optimization infomation=
//
// get the table name, return a list of the possible columns (except GEOMET=
RY column)
//
// found out this is called during a query
int msMYGISLayerGetItems(layerObj *layer)
{
=09msMYGISLayerInfo=09*layerinfo;
=09char=09=09=09=09table_name[5000];
=09char=09=09=09=09geom_column_name[5000];
=09char=09urid_name[5000];
=09char user_srid[5000];
=09char=09=09=09=09sql[6000];
=09//int=09=09=09=09nitems;
printf( "in msMYGISLayerGetItems (find column names)<br>\n");
=09layerinfo =3D (msMYGISLayerInfo *) layer->mygislayerinfo;
=09if (layerinfo =3D=3D NULL)
=09{
=09=09//layer not opened yet
=09=09msSetError(MS_QUERYERR, "msMYGISLayerGetItems called on unopened laye=
r",
"msMYGISLayerGetItems()");
=09=09return(MS_FAILURE);
=09}
=09if (layerinfo->conn =3D=3D NULL)
=09{
=09msSetError(MS_QUERYERR, "msMYGISLayerGetItems called on MYGIS la=
yer with no connection to DB.",
"msMYGISLayerGetItems()");
=09=09return(MS_FAILURE);
=09}
=09//get the table name and geometry column name
=09msMYGISLayerParseData(layer->data, geom_column_name, table_name, urid_na=
me, user_srid);
=09// two cases here. One, its a table (use select * from table) otherwise=
, just use the select clause
=09sprintf(sql,"EXPLAIN VERBOSE SELECT * FROM %s",table_name);
=09=09// this will call the mysql_NOTICE_HANDLER() callback! layer->fields=
will be populated
/*=09query_result =3D PQexec(layerinfo->conn, sql );
if (!(query_result) || PQresultStatus(query_result) !=3D PGRES_COMMAND_=
OK)
{
=09=09char tmp[4000];
=09=09sprintf(tmp, "Error executing MYGIS SQL statement: %s", sql);
=09msSetError(MS_QUERYERR, tmp,
"msMYGISLayerGetItems()");
=09PQclear(query_result);
=09 =09query_result =3D NULL;
=09=09return(MS_FAILURE);
}
=09//layerinfo->fields is a string with a list of all the columns
=09=09// # of items is number of "," in string + 1
=09=09//layerinfo->fields looks like "geo_value,geo_id,desc"
=09=09=09//since we dont want to return the geometry column, we remove it.
=09=09=09=09// # columns is reduced by 1
=09=09str =3D layerinfo->fields;
=09=09t =3D 0;
=09=09while ((str =3D strstr(str,",")))
=09=09{
=09=09=09str++;
=09=09=09t++;
=09=09}
=09layer->numitems =3D t; // one less because dont want to do anything wit=
h geometry column
=09layer->items =3D malloc (sizeof(char *) * layer->numitems);
=09str =3D layerinfo->fields ;
=09t =3D 0;
=09while (str !=3D NULL)
=09{
=09=09str2 =3D strstr(str,","); //NULL or points to the next ","
=09=09if (str2 !=3D NULL)
=09=09{
=09=09=09if (strncmp(geom_column_name, str, str2-str) !=3D 0)
=09=09=09{
=09=09=09=09//its not the geometry column
=09=09=09=09layer->items[t] =3D malloc(str2-str +1) ;
=09=09=09=09memset(layer->items[t],0, str2-str +1);
=09=09=09=09strncpy(layer->items[t], str, str2-str);
=09=09=09=09=09//memset will null terminate it
=09=09=09=09t++;
=09=09=09}
=09=09}
=09=09else
=09=09{
=09=09=09if (strcmp(geom_column_name, str) !=3D 0)
=09=09=09{
=09=09=09=09layer->items[t] =3D malloc( strlen(str)+1 ) ;
=09=09=09=09memset(layer->items[t],0, strlen(str) +1);
=09=09=09=09strcpy(layer->items[t], str);
=09=09=09=09t++;
=09=09=09}
=09=09}
=09=09if (str2 !=3D NULL)
=09=09{
=09=09=09str =3D str2;
=09=09=09str++;
=09=09}
=09=09else
=09=09{
=09=09=09str =3D NULL;
=09=09}
=09}
*/
=09return msMYGISLayerInitItemInfo(layer);
}
//we return an infinite extent
// we could call the SQL AGGREGATE extent(GEOMETRY), but that would take FO=
REVER
// to return (it has to read the entire table).
// So, we just tell it that we're everywhere and lets the spatial indexing =
figure things out for us
//
// Never seen this function actually called
int msMYGISLayerGetExtent(layerObj *layer, rectObj *extent)
{
printf("msMYGISLayerGetExtent called<br>\n");
=09extent->minx =3D extent->miny =3D -1.0*FLT_MAX ;
=09extent->maxx =3D extent->maxy =3D FLT_MAX;
=09return(MS_SUCCESS);
}
/* Function to parse the Mapserver DATA parameter for geometry
* column name, table name and name of a column to serve as a
* unique record id
*/
int msMYGISLayerParseData(char *data, char *geom_column_name,
=09char *table_name, char *urid_name,char *user_srid)
{
=09char *pos_opt, *pos_scn, *tmp, *pos_srid;
=09int =09slength;
//printf("msMYGISLayerParseData called<BR>\n");
=09/* given a string of the from 'geom from ctivalues' or 'geom from () as =
foo'
=09 * return geom_column_name as 'geom'
=09 * and table name as 'ctivalues' or 'geom from () as foo'
=09 */
=09/* First look for the optional ' using unique ID' string */
=09pos_opt =3D strstr(data, " using unique ");
=09if (pos_opt =3D=3D NULL) {
=09=09/* No user specified unique id so we will use the Postgesql OID */
=09=09strcpy(urid_name, "OID");
=09}
=09else {
=09=09// CHANGE - protect the trailing edge for thing like 'using unique ft=
ab_id using srid=3D33'
=09=09tmp =3D strstr(pos_opt + 14," ");
=09=09if (tmp =3D=3D NULL) //it lookes like 'using unique ftab_id'
=09=09{
=09=09=09strcpy(urid_name, pos_opt + 14);
=09=09}
=09=09else
=09=09{
=09=09=09//looks like ' using unique ftab_id ' (space at end)
=09=09=09strncpy(urid_name, pos_opt + 14, tmp-(pos_opt + 14 ) );
=09=09}
=09}
=09pos_srid =3D strstr(data," using SRID=3D");
=09if (pos_srid =3D=3D NULL)
=09{
=09=09user_srid[0] =3D 0; // =3D ""
=09}
=09else
=09{
=09=09//find the srid
=09=09slength=3Dstrspn(pos_srid+12,"-0123456789");
=09=09if (slength =3D=3D 0)
=09=09{
=09=09=09msSetError(MS_QUERYERR,
=09=09=09=09=09DATAERRORMESSAGE(data,"Error parsing MYGIS data variable: Yo=
u specified 'using SRID=3D#' but didnt have any numbers!<br><br>\n\nMore He=
lp:<br><br>\n<br>\n"),
=09=09=09=09=09"msMYGISLayerParseData()");
=09=09=09return(MS_FAILURE);
=09=09}
=09=09else
=09=09{
=09=09=09strncpy(user_srid,pos_srid+12,slength);
=09=09=09user_srid[slength] =3D 0; // null terminate it
=09=09}
=09}
=09// this is a little hack so the rest of the code works. If the ' using =
SRID=3D' comes before
=09// the ' using unique ', then make sure pos_opt points to where the ' us=
ing SRID' starts!
=09if (pos_opt =3D=3D NULL)
=09{
=09=09pos_opt =3D pos_srid;
=09}
=09else
=09{
=09=09if (pos_srid !=3D NULL)
=09=09{
=09=09=09if (pos_opt>pos_srid)
=09=09=09=09pos_opt =3D pos_srid;
=09=09}
=09}
=09/* Scan for the table or sub-select clause */
=09pos_scn =3D strstr(data, " from ");
=09if (pos_scn =3D=3D NULL) {
=09=09msSetError(MS_QUERYERR,
=09=09=09=09=09DATAERRORMESSAGE(data,"Error parsing MYGIS data variable. M=
ust contain 'geometry_column from table_name' or 'geom from (subselect) as =
foo' (couldnt find ' from '). More help: <br><br>\n<br>\n"),
=09=09=09=09=09"msMYGISLayerParseData()");
=09=09//msSetError(MS_QUERYERR, "Error parsing MYGIS data variable. Must c=
ontain 'geometry_column from table_name' or 'geom from (subselect) as foo' =
(couldnt find ' from ').", "msMYGISLayerParseData()");
=09=09return(MS_FAILURE);
=09}
=09/* Copy the geometry column name */
=09memcpy(geom_column_name, data, (pos_scn)-(data));
=09geom_column_name[(pos_scn)-(data)] =3D 0; //null terminate it
=09/* Copy out the table name or sub-select clause */
=09if (pos_opt =3D=3D NULL) {
=09=09strcpy(table_name, pos_scn + 6);=09//table name or sub-select clause
=09}
=09else {
=09=09strncpy(table_name, pos_scn + 6, (pos_opt) - (pos_scn + 6));
=09=09table_name[(pos_opt) - (pos_scn + 6)] =3D 0; //null terminate it
=09}
=09if ( (strlen(table_name) < 1 ) || (strlen(geom_column_name) < 1 ) ) {
=09=09msSetError(MS_QUERYERR,
=09=09=09=09=09DATAERRORMESSAGE(data,"Error parsing MYGIS data variable. M=
ust contain 'geometry_column from table_name' or 'geom from (subselect) as =
foo' (couldnt find a geometry_column or table/subselect). More help: <br><=
br>\n<br>\n"),
=09=09=09=09=09"msMYGISLayerParseData()");
=09=09return(MS_FAILURE);
=09}
//printf("unique column =3D %s, srid=3D'%s'<br>\n", urid_name,user_srid);
=09return(MS_SUCCESS);
}
#else
//prototypes if MYGIS isnt supposed to be compiled
int msMYGISLayerOpen(layerObj *layer)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerOpen called but unimplemented! =
(mapserver not compiled with MYGIS support)",
"msMYGISLayerOpen()");
=09=09return(MS_FAILURE);
}
void msMYGISLayerFreeItemInfo(layerObj *layer)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerFreeItemInfo called but unimplem=
ented!(mapserver not compiled with MYGIS support)",
"msMYGISLayerFreeItemInfo()");
}
int msMYGISLayerInitItemInfo(layerObj *layer)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerInitItemInfo called but unimplem=
ented!(mapserver not compiled with MYGIS support)",
"msMYGISLayerInitItemInfo()");
=09=09return(MS_FAILURE);
}
int msMYGISLayerWhichShapes(layerObj *layer, rectObj rect)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerWhichShapes called but unimpleme=
nted!(mapserver not compiled with MYGIS support)",
"msMYGISLayerWhichShapes()");
=09=09return(MS_FAILURE);
}
int msMYGISLayerClose(layerObj *layer)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerClose called but unimplemented!(=
mapserver not compiled with MYGIS support)",
"msMYGISLayerClose()");
=09=09return(MS_FAILURE);
}
int msMYGISLayerNextShape(layerObj *layer, shapeObj *shape)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerNextShape called but unimplement=
ed!(mapserver not compiled with MYGIS support)",
"msMYGISLayerNextShape()");
=09=09return(MS_FAILURE);
}
int msMYGISLayerGetShape(layerObj *layer, shapeObj *shape, long record)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerGetShape called but unimplemente=
d!(mapserver not compiled with MYGIS support)",
"msMYGISLayerGetShape()");
=09=09return(MS_FAILURE);
}
int msMYGISLayerGetExtent(layerObj *layer, rectObj *extent)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerGetExtent called but unimplement=
ed!(mapserver not compiled with MYGIS support)",
"msMYGISLayerGetExtent()");
=09=09return(MS_FAILURE);
}
int msMYGISLayerGetShapeRandom(layerObj *layer, shapeObj *shape, long *reco=
rd)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerGetShapeRandom called but unimpl=
emented!(mapserver not compiled with MYGIS support)",
"msMYGISLayerGetShapeRandom()");
=09=09return(MS_FAILURE);
}
int msMYGISLayerGetItems(layerObj *layer)
{
=09=09msSetError(MS_QUERYERR, "msMYGISLayerGetItems called but unimplemente=
d!(mapserver not compiled with MYGIS support)",
"msMYGISLayerGetItems()");
=09=09return(MS_FAILURE);
}
// end above's #ifdef USE_MYGIS
#endif
--------------Boundary-00=_A9WET610XG5GEOD089JM--
--=_CC93CAD8.016012B9--
More information about the mapserver-dev
mailing list