[postgis-users] Create View From Spatial Table with libpq
iron1103 at gmail.com
Tue May 25 02:47:20 PDT 2010
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
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
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
Thank you very much, you taught me a lot things I don't know before.
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
> 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.
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
> postgis-users mailing list
> postgis-users at postgis.refractions.net
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users