[postgis-users] Querying PostgreSQL / PostGIS Databases in Python
shishaozhong at gmail.com
Fri Jul 31 09:24:42 PDT 2020
Hi, Ruven Brooks,
Good to hear from you about your insight into all these.
Some queries which I wrote for handling Big Data failed due to that these
got killed or error messages said 'out of memory'.
On attempting to write different queries, I found queries may keep running
and appeared to take up days and weeks.
I will be interested in how to write efficient queries.
On Fri, 31 Jul 2020 at 16:37, <ruvenml at beamerbrooks.com> wrote:
> There are literally thousands of languages which can be used to
> formulate queries and send them to the PostgreSQL server for processing.
> Objective-C, Swift, Go, Matlab or R to name a randomly chosen few?
> Interfaces like ODBC/JDBC make it really easy to add PostgreSQL support to
> a programming language or calculation package.
> All of these languages are handy for writing things which are difficult to
> express in SQL and all of them offer capabilities which may make it easier
> to get data into and out of the application but all of them take about the
> same amount of time to process a query because queries are processed by the
> PostgreSQL server, not by the programming language. The only time
> PL/<whatever> or one of these other languages helps with performance is
> when the algorithm used has higher performance than what is available in
> SQL. Often, the opposite turns out to be the case. In other words,
> writing things in PL/<whatever> may actually slow things down.
> What should you do? Step 1 is to make sure that you are writing efficient
> queries. You need to have a deep understanding of the results of the
> EXPLAIN command and know how to re-arrange queries to control the
> PostgreSQL processing.
> Suppose that no matter how you re-write the query, it's still slow. Step
> 2 is to treat yourself to an online course on analysis of algorithms. Not
> only will you learn ways of speeding up algorithms but you will also learn
> to recognize when it's impossible to speed up an algorithm.
> Ruven Brooks
> On 7/31/2020 6:59 AM, Shaozhong SHI wrote:
> Hi, Laura,
> Thank you for your reply.
> 1. Please look at this documentation link.
> There is also PL/sh language.
> So many varieties. I wonder the use cases for all these.
> 2. My immediate concern is to know which one is good for periodically
> processing Big Data.
> Standard queries in PostgreSQL may fail or keep running for days, weeks
> when data is very big.
> If you do a programme with standard queries of PostgreSQL, I would think
> that it could run forever.
> 3. Does that mean Python can be treated as a native language in
> PostgreSQL/PostGIS? Will it run faster?
> On Fri, 31 Jul 2020 at 12:01, Augori <augori at gmail.com> wrote:
>> Hi Shao,
>> Using Python allows you to integrate a query into a workflow that has
>> some batch component to it. Examples would be a query which allows you to
>> select a set of records that need to be updated daily. Or it could be a
>> query that needs to be run on hundreds of files to build a summary of the
>> data or it could be part of a process that is triggered by some outside
>> event, like a file being updated. I hope I haven't misinterpreted your
>> question but you seem to be asking why you'd want to automate interactions
>> with a database, a question to which there are thousands of responses.
>> Please clarify if that is not the case.
>> Kind regards,
>> On Fri, Jul 31, 2020, 5:05 AM Shaozhong SHI <shishaozhong at gmail.com>
>>> What is the advantage of querying in Python?
>>> Has anyone got much experience?
>>> What not just use standard query?
>>> What is the rationale for querying in Python?
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
> postgis-users mailing listpostgis-users at lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/postgis-users
> postgis-users mailing list
> postgis-users at lists.osgeo.org
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users