[postgis-users] Querying PostgreSQL / PostGIS Databases in Python

[Shaozhong SHI]

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.

Regards,

Shao

On Fri, 31 Jul 2020 at 16:37, <ruvenml at beamerbrooks.com> wrote:

>
> Shao,
>   There are literally thousands of languages which can be used to
> formulate queries and send them to the PostgreSQL server for processing.
> You mentioned Python.  How about Java, Javascript, Typescript, C, C++, C#,
> 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.
> https://www.postgresql.org/docs/9.1/external-pl.html
>
> 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?
>
> Regards,
>
> Shao
>
> 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,
>> Laura
>>
>> On Fri, Jul 31, 2020, 5:05 AM Shaozhong SHI <shishaozhong at gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> 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?
>>>
>>> Regards,
>>>
>>> Shao
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> 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
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200731/8ec15fde/attachment.html>