[postgis-users] Create View From Spatial Table with libpq

[Mike Toews]

My wild guess (not having worked with libpq) is to help the planner out by
explicitly casting parameters, e.g.:

ST_Intersects(\"geocol\"::geometry, $1::geometry)


-Mike

On 21 May 2010 02:14, 郭家成 <iron1103 at gmail.com> wrote:

> Hi !
> I try to create a view from a spatial table, but it can't be done
> successfully, please give me some hints.
>
> Here is how I create my spatial table:
>
> *CREATE TABLE "table_name" ("ID" SERIAL PRIMARY KEY);
> SELECT ADDGEOMETRYCOLUMN('', 'table_name', 'geocol', -1, 'MULTIPOLYGON',
> 2);*
>
> After I inserted 1000 records into this table, I try to create a view with
> libpq:
>
> *int wkblen=0;*
> *char *pwkb = MakeWKB(&wkblen);* // I wrote a function to make wkb
> format, the function works fine.
> *Oid paramtypes[1]={0};*
> *char* paramvalues[1]={pwkb};*
> *int paramlens[1]={wkblen};
> int paramformats[1]={1};*
> *int resultformat=1;*
>
> *PGresult *r = PQexecParams(conn,*
> *              "CREATE VIEW \"view_name\" AS SELECT * FROM \"table_name\"
> WHERE ST_Intersects(\"geocol\", $1)",*
> *              1,*
> *              paramtypes,*
> *              paramvalues,*
> *              paramlens,*
> *              paramformats,*
> *              resultformat);*
> *ExecStatusType est = PQresultstatus(r);*
>
> The ExecStatusType is always be *PGRES_FATAL_ERROR*, and Error Message is
> *"could not determine data type of parameter $1"*.
> I have no idea what's going on, because when I change the SQL syntax to:
>
> *"SELECT * FROM \"table_name\" WHERE ST_Intersects(\"geocol\", $1)"*
>
> The ExecStatusType will be PGRES_TUPLES_OK.
>
> Please give me some hints, Thanks !
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100523/ed7124ac/attachment.html>