<div dir="ltr">Hi, Steve,<div><br></div><div>I have already read that. I am reading this one now. <a href="https://www.postgresql.org/docs/9.0/plpython.html">https://www.postgresql.org/docs/9.0/plpython.html</a></div><div><br></div><div>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?</div><div><br></div><div>Would this add on processing power of Python onto PostgreSQL/PostGIS?</div><div><br></div><div>I wonder who has experience on this.</div><div><br></div><div>Regards,</div><div><br></div><div>Shao</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, 21 Aug 2020 at 19:46, Stephen Woodbridge <<a href="mailto:stephenwoodbridge37@gmail.com">stephenwoodbridge37@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Shao,<br>
<br>
You need to read up on plpgsql stored procedures. This allows you to <br>
write functions that you can call from SQL. Sql is not a programming <br>
language like Python/Perl/Basic/etc, It is a query language for working <br>
with relational databases and operates on sets of records.<br>
<br>
PostgreSQL stored procedures allow you to write and apply functions to <br>
records or columns via the SQL language. This is what PostGIS does. If <br>
you have a table of polygons, you can apply an area calculation to the <br>
polygons in the table of some subset of them like:<br>
<br>
select *, st_area(geom) as area from my_table;<br>
select *, st_area(geom) as area from my_table where gid=1234;<br>
<br>
If you want to write some complex analysis, write a stored procedure and <br>
then apply it like above. plpgsql has flow control, variables, etc like <br>
many other languages.<br>
<br>
-Steve<br>
<br>
On 8/21/2020 2:33 PM, Shaozhong SHI wrote:<br>
> Hi, Ruven Brooks,<br>
><br>
> I am trying to explore its potential and versatility.<br>
><br>
> Can standard SQL work like a program?<br>
><br>
> As I understand, DO statement is similar to a function.<br>
><br>
> What I am interested in is to fully understand its capabilities and <br>
> working like a program.<br>
><br>
> Working like to powerful program would allow data manipulation.<br>
><br>
> Have you tried writing DO statement with Python?<br>
><br>
> Regards,<br>
><br>
> Shao<br>
><br>
> On Fri, 21 Aug 2020 at 18:52, <<a href="mailto:ruvenml@beamerbrooks.com" target="_blank">ruvenml@beamerbrooks.com</a> <br>
> <mailto:<a href="mailto:ruvenml@beamerbrooks.com" target="_blank">ruvenml@beamerbrooks.com</a>>> wrote:<br>
><br>
><br>
> The DO statement is not in standard SQL; it is a PostgreSQL<br>
> extension used to implement procedural languages like PL/SQL. Are<br>
> you writing PL/SQL code? If so, it would be useful to have an<br>
> explanation of what you are trying to compute, particularly if the<br>
> computations are geometric ones.<br>
><br>
> Ruven Brooks<br>
><br>
> On 8/21/2020 11:18 AM, Shaozhong SHI wrote:<br>
>> Hi, Ruven Brooks,<br>
>><br>
>> This is a good point.<br>
>><br>
>> I was testing in a Do statement. I created a geometry variable. <br>
>> It seems that it stored a geometry object. However, a very long<br>
>> code appeared. It does not seem that the geometry object was not<br>
>> actually stored.<br>
>><br>
>> Regards,<br>
>><br>
>> Shao<br>
>><br>
>> On Fri, 21 Aug 2020 at 15:32, <<a href="mailto:ruvenml@beamerbrooks.com" target="_blank">ruvenml@beamerbrooks.com</a><br>
>> <mailto:<a href="mailto:ruvenml@beamerbrooks.com" target="_blank">ruvenml@beamerbrooks.com</a>>> wrote:<br>
>><br>
>> SQL itself has no variables. What programming language are<br>
>> you using and how does it call SQL? PosgGIS supports WKT<br>
>> format so pretty much any programming language which can<br>
>> store strings can store geometry.<br>
>><br>
>> Ruven Brooks<br>
>><br>
>> On 8/21/2020 7:54 AM, Shaozhong SHI wrote:<br>
>>> Has anyone got experience in storing geometries in<br>
>>> variables, so that these can be used in a program?<br>
>>><br>
>>> Regards,<br>
>>><br>
>>> Shao<br>
>>><br>
>>> _______________________________________________<br>
>>> postgis-users mailing list<br>
>>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
>>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
>><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>