[Mapserver-users] Postgis "explain verbose"-Error [carl.anderson@vadose.org]
Carl Anderson
carl.anderson at vadose.org
Tue May 13 01:12:22 EDT 2003
--tThc/1wpZn/ma/RB
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
On 2003.05.13 01:00 Carl Anderson wrote:
On 2003.05.12 09:44 Christina Biakowski (CCGIS) wrote:
> Querying a Postgis-Layer (by clicking in the map) I get the
> following error-message:
>
> "msPOSTGISLayerGetShape(): Query error. Error executing POSTGIS SQL
> statement (in FETCH ALL): EXPLAIN VERBOSE SELECT * FROM staedte -
> Error with
christina and steff
PostgreSQL 7.3 changed the way in which EXPLAIN returns information.
The attached patch for mappostgis.c adds support for the 7.3.x style.
apply to CVS with
patch < mapserver-pg7.3.patch
and recompile
Note that if you have postgis tables with conflicting names in
different schemas you should think about running the find_srid patch as
well.
Note that both of these have been previously sent to the postgis list.
While both of these have been in use in my org for a month or more YMMV
so be careful. These are not the alpha patch previously mentioned.
C.
--- mappostgis.c Tue May 13 00:51:41 2003
+++ mappostgis_hack.c Tue May 13 00:52:59 2003
@@ -1314,13 +1305,16 @@
msPOSTGISLayerParseData(layer->data, geom_column_name,
table_name, urid_name, user_srid);
+ query_result = PQexec(layerinfo->conn, "set explain_pretty_print
= on");
+// in pre 7.3 databases this will fail without problems so relax
+//
// two cases here. One, its a table (use select * from table)
otherwise, just use the select clause
sprintf(sql,"EXPLAIN VERBOSE SELECT * FROM %s",table_name);
// this will call the postresql_NOTICE_HANDLER()
callback! layer->fields will be populated
query_result = PQexec(layerinfo->conn, sql );
- if (!(query_result) || PQresultStatus(query_result) !=
PGRES_COMMAND_OK)
+ if (!(query_result) || (PQresultStatus(query_result) !=
PGRES_COMMAND_OK && PQresultStatus(query_result) != PGRES_TUPLES_OK ))
{
char tmp[4000];
@@ -1335,6 +1329,44 @@
query_result = NULL;
return(MS_FAILURE);
}
+ if (PQresultStatus(query_result) == PGRES_TUPLES_OK )
+ {
+ //build layerinfo->fields from tuples passed back
+ int rows,row;
+ char *result;
+ char *tup;
+ char *str,*str2;
+
+ if ( layerinfo->fields )
+ {
+ free(layerinfo->fields); // free up space
+ }
+ result = malloc ( 6000) ;
+ layerinfo->fields = result;
+ result[0] = 0; //null terminate it
+
+ rows = PQntuples(query_result);
+ for ( row=0; row < rows; row++ )
+ {
+ tup = PQgetvalue(query_result,row,0);
+ str = strstr(tup,":resname ");
+ if ( str )
+ if ( str )
+ {
+ str += 9;
+ str2 = strstr(str," ");
+ if (strncmp(str, "<>", (str2-str)))
+ {
+ if ( strlen(result) > 0 )
+ {
+ strcat(result,",");
+ }
+ strncat(result,str,(str2-str));
+ }
+ }
+ }
+ }
+
//layerinfo->fields is a string with a list of all the columns
// # of items is number of "," in string + 1
--tThc/1wpZn/ma/RB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="mapserver-pg7.3.patch"
--- mappostgis.c Tue May 13 00:51:41 2003
+++ mappostgis_hack.c Tue May 13 00:52:59 2003
@@ -1314,13 +1305,16 @@
msPOSTGISLayerParseData(layer->data, geom_column_name, table_name, urid_name, user_srid);
+ query_result = PQexec(layerinfo->conn, "set explain_pretty_print = on");
+// in pre 7.3 databases this will fail without problems so relax
+//
// two cases here. One, its a table (use select * from table) otherwise, just use the select clause
sprintf(sql,"EXPLAIN VERBOSE SELECT * FROM %s",table_name);
// this will call the postresql_NOTICE_HANDLER() callback! layer->fields will be populated
query_result = PQexec(layerinfo->conn, sql );
- if (!(query_result) || PQresultStatus(query_result) != PGRES_COMMAND_OK)
+ if (!(query_result) || (PQresultStatus(query_result) != PGRES_COMMAND_OK && PQresultStatus(query_result) != PGRES_TUPLES_OK ))
{
char tmp[4000];
@@ -1335,6 +1329,44 @@
query_result = NULL;
return(MS_FAILURE);
}
+ if (PQresultStatus(query_result) == PGRES_TUPLES_OK )
+ {
+ //build layerinfo->fields from tuples passed back
+ int rows,row;
+ char *result;
+ char *tup;
+ char *str,*str2;
+
+ if ( layerinfo->fields )
+ {
+ free(layerinfo->fields); // free up space
+ }
+ result = malloc ( 6000) ;
+ layerinfo->fields = result;
+ result[0] = 0; //null terminate it
+
+ rows = PQntuples(query_result);
+ for ( row=0; row < rows; row++ )
+ {
+ tup = PQgetvalue(query_result,row,0);
+ str = strstr(tup,":resname ");
+ if ( str )
+ if ( str )
+ {
+ str += 9;
+ str2 = strstr(str," ");
+ if (strncmp(str, "<>", (str2-str)))
+ {
+ if ( strlen(result) > 0 )
+ {
+ strcat(result,",");
+ }
+ strncat(result,str,(str2-str));
+ }
+ }
+ }
+ }
+
//layerinfo->fields is a string with a list of all the columns
// # of items is number of "," in string + 1
--tThc/1wpZn/ma/RB
Content-Type: message/rfc822; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Date: Mon, 17 Mar 2003 20:25:22 -0500
From: Carl Anderson <carl.anderson at vadose.org>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Subject: find_srid() change request
Message-ID: <20030318012522.GA10435 at vadose.dvg>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
X-Mailer: Balsa 2.0.9
Status: RO
To allow support for schema enabled Postgis in Mapserver 6.3
I request that find_srid () be changed in the following way.
This is clearly a hack but will extend the use of PostGIS w/ schemas
into Mapserver 3.6.x
preserve existing behaviour
extend behavior to automatically promote a namespace.table reference
into a schema in the place of parameter 1
into a table in place of parameter 2
Mapserver 3.6.x converts
CONNECTIONTYPE postgis
DATA 'shape from cities.annex_cases'
into a Binary Cursor containing
.. find_srid('','cities.annex_cases','shape') ...
We have geotables in schemas to provide a level of sanity and
context to the geotables;
Postgresql 7.3 by default will search the users named schema and then
the public schema. This is an unacceptable exposure of the schema owner
to web traffic. We prefer to use extremely limited accounts for
mapserver. So when the Pg user is different from the schema the tables
are in the tables are not found. The propsed style will allow a user
to explicitly specify the schema.
the the above example the public.geometry_columns table contains
f_table_catalog | f_table_schema | f_table_name | f_geometry_column
| coord_dimension | srid | type |
-----------------+----------------+----------------+-------------------+-----------------+------+--------------
| cities | annex_cases | shape
|
2 | -1 | MULTIPOLYGON
proposed new find_srid()
------------------
-- FIND_SRID( <schema/database>, <table>, <geom col> )
CREATE FUNCTION find_srid(varchar,varchar,varchar) RETURNS int4 AS
'DECLARE
schem text;
tabl text;
sr int4;
BEGIN
schem = $1;
tabl = $2;
-- if the table contains a . and the schema is empty
-- split the table into a schema and a table
-- otherwise drop through to default behavior
if ( schem = '''' and tabl ~ ''\.'' ) then
schem = substr(tabl,1,strpos(tabl,''.'')-1);
tabl = substr(tabl,length(schem)+2);
else
schem = schem || ''%'';
end if;
select SRID into sr from geometry_columns where f_table_schema like
schem and f_table_name = tabl and f_geometry_column = $3;
return sr;
END;
'
LANGUAGE 'plpgsql' WITH (iscachable,isstrict);
C.
--tThc/1wpZn/ma/RB--
More information about the mapserver-users
mailing list