[postgis-users] Storing geometries in variables
shishaozhong at gmail.com
Mon Aug 24 13:32:11 PDT 2020
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?
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>
> > 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.
> 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.
> postgis-users mailing list
> postgis-users at lists.osgeo.org
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users