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

[郭家成]

Mike, Thank you for your enthusiastic, you're so kind.

Yes, the user needs the view, and the view must exists until the user drop
it.

I think I have two options:
A. Create a permanent spatial table, it doesn't need a spatial index.
    When user drags a rectangle, I'll insert the rectangle into the
permanent table.
    When user drops a view, I'll also delete the rectangle.

B. Convert the user rectangle to WKT, and the SQL will be
    "create view "view" as select * from "table" where
ST_Intersects("geocol", ST_GeomFromText('...'));"

Thank you very much, you taught me a lot things I don't know before.

Regards

Mike Toews <mwtoews at gmail.com> 於 2010年5月25日上午4:06 寫道：

> Does the user need the view? Does the view need to exist after the user
> exits the application? If not, then you can use temporary tables (these can
> also be regarded as temporary geometry objects). You can also create
> temporary views too, but it all depends on what your app does and what the
> user needs from it. An other strategy is to create a temporary table when
> the app gets initialized:
>
> create temp geo_table (id integer primary key, geocol geometry);
> create index geo_table_geocol_idx on geo_table using gist(geotable);
>
> Then whenever a user selects from source_geo_table, use:
>
> truncate geo_table; -- in case there was a previous selection
> insert into geo_table(id, geocol)
>   select id, geocol from source_geo_table
>   where st_intersects(geocol, $1);
>
> After the connection is closed (or the transaction is otherwise lost), the
> table will no longer exist.
>
> Most tables and views -- temporary or not -- behave the same to client
> applications (selecting, using functions, etc), so long as you have control
> from a low-level API to control how objects are created and used during the
> session.
>
> -Mike
>
>  2010/5/24 郭家成 <iron1103 at gmail.com>
>
>> Hey Mike, I made a mistake.
>>
>> I tried to create a function to create view, but Postgre still gave me
>> that errmsg, "there is no parameter $1".
>>
>> I think there is only one solution in my case:
>> 1. Create a table that only has one geometry column
>> 2. Insert a geometry
>> 3. Create View
>> 4. Drop that table
>>
>> Do u have better idea?
>>
>> Thank you for your enthusiastic !
>>
>> 郭家成 <iron1103 at gmail.com> 於 2010年5月24日下午4:04 寫道：
>>
>>  Thanks a lot, Mike !  That's a great hint.
>>>
>>> I made a windows application, that allow user to browse spatial tables,
>>> and create
>>> a view by dragging a rectangle on my application.
>>>
>>> $1 is that rectangle made by user.
>>>
>>> Can I create a temporary geometric object ? How to do that ?
>>>
>>>  2010/5/24 Mike Toews <mwtoews at gmail.com>
>>>
>>>>
>>>>   2010/5/24 郭家成 <iron1103 at gmail.com>
>>>>
>>>>  So, everything works fine without "CREATE VIEW", weird.
>>>>>
>>>>
>>>> I should have seen that earlier .. views don't have parameters, so that
>>>> is why test 1-4 don't work. Views need to have existing columns. What is $1
>>>> supposed to be? A geometry column from a different table, or the same table?
>>>> If you needs are only temporary, then explore the uses of "select * into
>>>> *temp *tt from ..." then use tt like any other table until you don't
>>>> need it anymore in the transaction, then it disappears out of sight.
>>>>
>>>> -Mike
>>>>
>>>> _______________________________________________
>>>> 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
>>
>>
>
> _______________________________________________
> 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/20100525/cc14c5d8/attachment.html>