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

[Mike Toews]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100524/eeba76dc/attachment.html>