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, <[email protected]> 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 < > [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]>> 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]>> 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]> >> >>> 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 >> >> >> >> >> >> _______________________________________________ >> >> postgis-users mailing list >> >> [email protected] <mailto: >> [email protected]> >> >> 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 >> > >> > >> > _______________________________________________ >> > 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 > > > _______________________________________________ > postgis-users mailing > [email protected]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
