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
<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
<[email protected] <mailto:[email protected]>>
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, <[email protected]
<mailto:[email protected]>
> <mailto:[email protected]
<mailto:[email protected]>>> 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, <[email protected]
<mailto:[email protected]>
>> <mailto:[email protected]
<mailto:[email protected]>>> 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
>>> [email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
<https://lists.osgeo.org/mailman/listinfo/postgis-users>
>>
>> _______________________________________________
>> postgis-users mailing list
>> [email protected]
<mailto:[email protected]>
>> <mailto:[email protected]
<mailto:[email protected]>>
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
<https://lists.osgeo.org/mailman/listinfo/postgis-users>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> [email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
<https://lists.osgeo.org/mailman/listinfo/postgis-users>
>
> _______________________________________________
> postgis-users mailing list
> [email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
<https://lists.osgeo.org/mailman/listinfo/postgis-users>
>
>
> _______________________________________________
> postgis-users mailing list
> [email protected] <mailto:[email protected]>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
<https://lists.osgeo.org/mailman/listinfo/postgis-users>
_______________________________________________
postgis-users mailing list
[email protected] <mailto:[email protected]>
https://lists.osgeo.org/mailman/listinfo/postgis-users
<https://lists.osgeo.org/mailman/listinfo/postgis-users>
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
