[postgis-users] Storing geometries in variables
Shaozhong SHI
shishaozhong at gmail.com
Sat Aug 22 04:08:06 PDT 2020
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200822/54571040/attachment.html>
More information about the postgis-users
mailing list