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

[郭家成]

Hi Mike,
Thanks for your reply, but it still doesn't work.
The key point possibly lies in *Oid paramtypes[1]={0}*,
Where can I find the ParamType of Geometry or Bytea?

2010/5/24 Mike Toews <mwtoews at gmail.com>

> 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
>>
>>
>
> _______________________________________________
> 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/20100524/bf2670a8/attachment.html>