[postgis-users] Storing geometries in variables

[Shaozhong SHI]

Hi, Paul Ramsay,

I tried TEMP tables and found that it is useful.

I had to write LOOPs in PL/PgSQL since I had to intersect each geometry in
a table with each geometry in another table.  The logic needs to be
iterated.  If that is wrong, how can I write different SQL to do it?  Do
you have an example to enlighten me?

About using Python, are you suggesting that it is not a good idea?

Regards,

Shao

On Mon, 24 Aug 2020 at 18:35, Paul Ramsey <pramsey at cleverelephant.ca> wrote:

>
>
> On Sat, Aug 22, 2020 at 5:16 PM Simon Greener <simon at spatialdbadvisor.com>
> wrote:
> >
> > Python, unless I am wrong, is a mid/client tier language only; plpgsql
> can only be deployed in the data tier.
>
> Python can be used in the client side or on the server side via PL/Python.
>
> However:
>
> The server-side Python binding (create extension plpythonu) is "untrusted"
> which means it is frequently not available (for example, on AWS RDS)
> because it represents a server-side security risk. That means that
> developing large chunks of functionality in PL/Python risks making your
> application limited in terms of the deployment environments you can use.
>
> The downside of doing Python processing on the *client* side is that all
> the data you process has to traverse from the database to the client, and
> back, and that is frequently a non-trivial overhead.
>
> All this speaks to doing processing in PL/PgSQL (which is universally
> available, and runs local to the database) when it makes sense for you.
>
> With respect to optimization, it makes sense to use full SQL statements
> often. If you find yourself writing LOOPs in PL/PgSQL it's possible you've
> done something wrong, logically. If you let your processing be defined in
> SQL you increase the odds of the planner finding optimizations that you
> might miss if you're trying to be smart and control execution.
>
> That said, some multi-stage processes benefit from writing temporary
> results into TEMP tables and building indexes on them, as you go from stage
> to stage. YMMV, depends on the problem. Again though, avoiding pushing the
> data back and forth over the wire is a good thing.
>
> ATB,
> P
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200824/519af4aa/attachment.html>