[postgis-users] Storing geometries in variables

Sat Aug 22 17:05:58 PDT 2020

> The postgreSQL query optimizer does not optimize pl/SQL functions so the programmer must be extremely careful to write these efficiently.  

Hi, I'm interested in any articles that describe this aspect of the optimizer. Do you have some?

A comment. 

It is not just the case of one language (plpgsql) vs another (Python etc), but the architectural choices that drive the development. 

Python, unless I am wrong, is a mid/client tier language only; plpgsql can only be deployed in the data tier. 

Databases offer their data and services in an application neutral manner. Developing and deploying in the database may be best where application neutrality is important.

In short some functions are more efficiently developed for, and deployed into, the database. Plpgsql was created for just such a need.

But so was PL/Java, PL/R, PL/Ruby etc.

A developer should always have stored procedure language experience (plpgsql) to have as diverse a toolset available when deciding how to implement requirements in the most efficient manner possible.

My 2c

Simon

⁣Get BlueMail for Android 

On 22 Aug 2020, 21:09, at 21:09, Shaozhong SHI <shishaozhong at gmail.com> wrote:
>Hi, Ruven Brooks,
>
>Thanks a lot.
>
>I know Python well, and have been doing a lot of coding lately.
>
>Your advice does help me to understand the matter clearly.
>
>Thanks again.
>
>Regards,
>
>Shao
>
>On Fri, 21 Aug 2020 at 23:11, <ruvenml at beamerbrooks.com> wrote:
>
>> You're far better off going the other way.   It's really easy to call
>> PostgreSQL/PostGIS from Python and there are lots of tutorials and
>> examples.  The advantage of doing it this way is you can also use
>Python
>> for user interface interaction and graphical data display.
>>
>> An alternative to consider if maps are particularly of interest is to
>> write most of your code in Javascript.   PostgresSQL can create JSON
>which
>> can be fed directly to Leaflet or Google Maps to display as maps.
>>
>> It turns out that SQL is Turing Complete (without pl/SQL).  This
>means
>> that any computation you can write in Python or any other programming
>> language, you can write in SQL.  In other words, SQL is already as
>powerful
>> as Python.  What this claim doesn't say is how large and long the
>program
>> will be and how fast it will run.  Unfortunately, that depends on the
>> specific computation and the answer could go either way.
>>
>> The typical usage of pl/SQL and pl/Python are like the one Stephen
>> Woodbridge gave, writing a function like st_area which takes just a
>single
>> column of a single row as input and returns a floating point value. 
>The
>> postgreSQL query optimizer does not optimize pl/SQL functions so the
>> programmer must be extremely careful to write these efficiently.
>>
>> Ruven Brooks
>> On 8/21/2020 3:00 PM, Shaozhong SHI wrote:
>>
>> Hi, Steve,
>>
>> I have already read that.  I am reading this one now.
>> https://www.postgresql.org/docs/9.0/plpython.html
>>
>> I find it interesting and intriguing.  If a Do statement or a
>function can
>> also be written in Python, would that imply that highly complex
>processing
>> can be done this way?
>>
>> Would this add on processing power of Python onto PostgreSQL/PostGIS?
>>
>> I wonder who has experience on this.
>>
>> Regards,
>>
>> Shao
>>
>> On Fri, 21 Aug 2020 at 19:46, Stephen Woodbridge <
>> stephenwoodbridge37 at gmail.com> wrote:
>>
>>> Hi Shao,
>>>
>>> You need to read up on plpgsql stored procedures. This allows you to
>>> write functions that you can call from SQL. Sql is not a programming
>>> language like Python/Perl/Basic/etc, It is a query language for
>working
>>> with relational databases and operates on sets of records.
>>>
>>> PostgreSQL stored procedures allow you to write and apply functions
>to
>>> records or columns via the SQL language. This is what PostGIS does.
>If
>>> you have a table of polygons, you can apply an area calculation to
>the
>>> polygons in the table of some subset of them like:
>>>
>>> select *, st_area(geom) as area from my_table;
>>> select *, st_area(geom) as area from my_table where gid=1234;
>>>
>>> If you want to write some complex analysis, write a stored procedure
>and
>>> then apply it like above. plpgsql has flow control, variables, etc
>like
>>> many other languages.
>>>
>>> -Steve
>>>
>>> On 8/21/2020 2:33 PM, Shaozhong SHI wrote:
>>> > Hi, Ruven Brooks,
>>> >
>>> > I am trying to explore its potential and versatility.
>>> >
>>> > Can standard SQL work like a program?
>>> >
>>> > As I understand, DO statement is similar to a function.
>>> >
>>> > What I am interested in is to fully understand its capabilities
>and
>>> > working like a program.
>>> >
>>> > Working like to powerful program would allow data manipulation.
>>> >
>>> > Have you tried writing DO statement with Python?
>>> >
>>> > Regards,
>>> >
>>> > Shao
>>> >
>>> > On Fri, 21 Aug 2020 at 18:52, <ruvenml at beamerbrooks.com
>>> > <mailto:ruvenml at beamerbrooks.com>> wrote:
>>> >
>>> >
>>> >     The DO statement is not in standard SQL; it is a PostgreSQL
>>> >     extension used to implement procedural languages like PL/SQL.
>Are
>>> >     you writing PL/SQL code?   If so, it would be useful to have
>an
>>> >     explanation of what you are trying to compute, particularly if
>the
>>> >     computations are geometric ones.
>>> >
>>> >     Ruven Brooks
>>> >
>>> >     On 8/21/2020 11:18 AM, Shaozhong SHI wrote:
>>> >>     Hi, Ruven Brooks,
>>> >>
>>> >>     This is a good point.
>>> >>
>>> >>     I was testing in a Do statement.  I created a geometry
>variable.
>>> >>     It seems that it stored a geometry object.  However, a very
>long
>>> >>     code appeared.  It does not seem that the geometry object was
>not
>>> >>     actually stored.
>>> >>
>>> >>     Regards,
>>> >>
>>> >>     Shao
>>> >>
>>> >>     On Fri, 21 Aug 2020 at 15:32, <ruvenml at beamerbrooks.com
>>> >>     <mailto:ruvenml at beamerbrooks.com>> wrote:
>>> >>
>>> >>         SQL itself has no variables.   What programming language
>are
>>> >>         you using and how does it call SQL? PosgGIS supports WKT
>>> >>         format so pretty much any programming language which can
>>> >>         store strings can store geometry.
>>> >>
>>> >>         Ruven Brooks
>>> >>
>>> >>         On 8/21/2020 7:54 AM, Shaozhong SHI wrote:
>>> >>>         Has anyone got experience in storing geometries in
>>> >>>         variables, so that these can be used in a program?
>>> >>>
>>> >>>         Regards,
>>> >>>
>>> >>>         Shao
>>> >>>
>>> >>>         _______________________________________________
>>> >>>         postgis-users mailing list
>>> >>>         postgis-users at lists.osgeo.org  <mailto:
>>> postgis-users at lists.osgeo.org>
>>> >>>         https://lists.osgeo.org/mailman/listinfo/postgis-users
>>> >>
>>> >>         _______________________________________________
>>> >>         postgis-users mailing list
>>> >>         postgis-users at lists.osgeo.org
>>> >>         <mailto:postgis-users at lists.osgeo.org>
>>> >>         https://lists.osgeo.org/mailman/listinfo/postgis-users
>>> >>
>>> >>
>>> >>     _______________________________________________
>>> >>     postgis-users mailing list
>>> >>     postgis-users at lists.osgeo.org  <mailto:
>>> postgis-users at lists.osgeo.org>
>>> >>     https://lists.osgeo.org/mailman/listinfo/postgis-users
>>> >
>>> >     _______________________________________________
>>> >     postgis-users mailing list
>>> >     postgis-users at lists.osgeo.org
><mailto: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 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
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>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/20200823/b53c9ef0/attachment.html>