[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Has anyone got novel Python function example
From: "Regina Obe" <lr () pcorp ! us>
Date: 2022-12-04 5:31:34
Message-ID: 003101d907a1$ac82c280$05884780$ () pcorp ! us
[Download RAW message or body]
This is a multipart message in MIME format.
[Attachment #2 (multipart/alternative)]
This is a multipart message in MIME format.
Just a side note because a lot of people seem to be deploying on Database as a \
Service platforms.
The big DbaaS providers do not offer plpython as an extension because it is \
untrusted. You probably won't find any PL language that is untrusted on any of the \
top 4 cloud providers.
So if you are using Amazon RDS, Aurora, Microsoft Azure, or Google Cloud DbaaS, you \
should expect to not have it.
Note I'm not talking about running your own VMS on these, if you have PostgreSQL \
installed on your own VM, then the DbaaS restrictions don't affect you.
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of Imre \
Samu
Sent: Saturday, December 3, 2022 11:10 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] Has anyone got novel Python function example
> It will be interesting to see novel Python functions in PostGIS.
Server side?
1. Postgis Raster see
"11.3.4. Use PLPython to dump out images via SQL"
https://postgis.net/docs/using_raster_dataman.html
2.) book: "PostGIS-Cookbook" \
https://www.packtpub.com/product/postgis-cookbook-second-edition/9781788299329
- "Writing PostGIS functions with PL/Python"
https://github.com/PacktPublishing/PostGIS-Cookbook-Second-Edition/blob/master/Chapter08/code%20snippets/chp08_R4.sql
- "Geocoding with geopy and PL/Python"
https://github.com/PacktPublishing/PostGIS-Cookbook-Second-Edition/blob/master/Chapter08/code%20snippets/chp08_R7.sql
3.) The plpygis has some simple examples: \
https://plpygis.readthedocs.io/en/latest/examples.html
"plpygis is a Python conveter to and from the PostGIS geometry type, WKB, EWKB, \
GeoJSON and Shapely geometries and additionally supports __geo_interface__. plpygis \
is intended for use in PL/Python functions."
see more:
- https://plpygis.readthedocs.io/en/latest/
- https://github.com/bosth/plpygis
- slide ( 2017 ) https://2017.foss4g.org/post_conference/Extending-PostGIS-with-Python.pdf
4.) CartoDB is also plpythonu based.
- https://github.com/search?q=repo%3ACartoDB%2Fcartodb+plpython \
<https://github.com/search?q=repo%3ACartoDB%2Fcartodb+plpython&type=code> &type=code
- crankshaft ( CARTO Spatial Analysis extension for PostgreSQL ) \
https://github.com/CartoDB/crankshaft
5.) (tutorial) Map Matching in PostGIS with Valhalla and PL/Python
https://gis-ops.com/map-matching-postgis-plpython/
6.) Apache MADlib ( Graph, Deep learning, Statistics, .. )
https://madlib.apache.org/docs/latest/index.html ( mostly plpythonu based )
....
Be careful:
"PL/Python is only available as an "untrusted" language, meaning it does not offer \
any way of restricting what users can do in it and is therefore named plpython3u. A \
trusted variant plpython might become available in the future if a secure execution \
mechanism is developed in Python. The writer of a function in untrusted PL/Python \
must take care that the function cannot be used to do anything unwanted, since it \
will be able to do anything that could be done by a user logged in as the database \
administrator. Only superusers can create functions in untrusted languages such as \
plpython3u."
https://www.postgresql.org/docs/15/plpython.html
https://dba.stackexchange.com/questions/132352/why-is-pl-python-untrusted
Regards,
Imre
Shaozhong SHI <shishaozhong@gmail.com <mailto:shishaozhong@gmail.com> > ezt írta \
(időpont: 2022. dec. 3., Szo, 21:59):
It will be interesting to see novel Python functions in PostGIS.
Regards,
David
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type \
content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 \
(filtered medium)"><style><!-- /* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
code
{mso-style-priority:99;
font-family:"Courier New";}
span.gmail-pre
{mso-style-name:gmail-pre;}
span.gmail-quote
{mso-style-name:gmail-quote;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div \
class=WordSection1><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Just a side \
note because a lot of people seem to be deploying on Database as a Service \
platforms.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The big DbaaS \
providers do not offer plpython as an extension because it is untrusted. You probably \
won't find any PL language that is untrusted on any of the top 4 cloud \
providers.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So if you are \
using Amazon RDS, Aurora, Microsoft Azure, or Google Cloud DbaaS, you should expect \
to not have it.<o:p></o:p></span></p><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Note I'm not \
talking about running your own VMS on these, if you have PostgreSQL installed on \
your own VM, then the DbaaS restrictions don't affect you. <o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div \
style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal><b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users \
[mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Imre \
Samu<br><b>Sent:</b> Saturday, December 3, 2022 11:10 PM<br><b>To:</b> PostGIS Users \
Discussion <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: \
[postgis-users] Has anyone got novel Python function \
example<o:p></o:p></span></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>> It will be \
interesting to see novel Python functions in PostGIS.<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Server \
side?<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>1. Postgis Raster see<o:p></o:p></p></div><div><p \
class=MsoNormal> "11.3.4. Use PLPython to dump out images via \
SQL" <o:p></o:p></p></div><div><p class=MsoNormal> <a \
href="https://postgis.net/docs/using_raster_dataman.html">https://postgis.net/docs/using_raster_dataman.html</a><o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>2.) book: \
"PostGIS-Cookbook" <a \
href="https://www.packtpub.com/product/postgis-cookbook-second-edition/9781788299329"> \
https://www.packtpub.com/product/postgis-cookbook-second-edition/9781788299329</a><o:p></o:p></p></div><div><p \
class=MsoNormal> - "Writing PostGIS functions with \
PL/Python"<o:p></o:p></p></div><div><p class=MsoNormal> <a \
href="https://github.com/PacktPublishing/PostGIS-Cookbook-Second-Edition/blob/master/C \
hapter08/code%20snippets/chp08_R4.sql">https://github.com/PacktPublishing/PostGIS-Cook \
book-Second-Edition/blob/master/Chapter08/code%20snippets/chp08_R4.sql</a><o:p></o:p></p></div><div><p \
class=MsoNormal> - "Geocoding with geopy and \
PL/Python" <o:p></o:p></p></div><div><p class=MsoNormal> \
<a href="https://github.com/PacktPublishing/PostGIS-Cookbook-Second-Edition/blo \
b/master/Chapter08/code%20snippets/chp08_R7.sql">https://github.com/PacktPublishing/Po \
stGIS-Cookbook-Second-Edition/blob/master/Chapter08/code%20snippets/chp08_R7.sql</a><o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div></div><div><p class=MsoNormal>3.) The \
plpygis has some simple examples: <a \
href="https://plpygis.readthedocs.io/en/latest/examples.html">https://plpygis.readthedocs.io/en/latest/examples.html</a><o:p></o:p></p></div><div><p \
class=MsoNormal>"<span class=gmail-pre><span \
style='font-size:11.0pt;font-family:"Courier \
New";background:#ECF0F3'>plpygis</span></span><span \
style='font-size:13.0pt;color:#3E4349'> is a Python conveter to and from the \
PostGIS </span><span class=gmail-pre><span \
style='font-size:11.0pt;font-family:"Courier \
New";background:#ECF0F3'>geometry</span></span><span \
style='font-size:13.0pt;color:#3E4349'> type, WKB, EWKB, GeoJSON and Shapely \
geometries and additionally supports </span><span class=gmail-pre><span \
style='font-size:11.0pt;font-family:"Courier \
New";background:#ECF0F3'>__geo_interface__</span></span><span \
style='font-size:13.0pt;color:#3E4349'>. </span><span class=gmail-pre><span \
style='font-size:11.0pt;font-family:"Courier \
New";background:#ECF0F3'>plpygis</span></span><span \
style='font-size:13.0pt;color:#3E4349'> is intended for use in PL/Python \
functions."</span><o:p></o:p></p></div><div><p class=MsoNormal>see \
more: <o:p></o:p></p></div><div><p class=MsoNormal>- <a \
href="https://plpygis.readthedocs.io/en/latest/">https://plpygis.readthedocs.io/en/latest/</a><o:p></o:p></p></div><div><p \
class=MsoNormal>- <a \
href="https://github.com/bosth/plpygis">https://github.com/bosth/plpygis</a><o:p></o:p></p></div><div><p \
class=MsoNormal>- slide ( 2017 ) <a \
href="https://2017.foss4g.org/post_conference/Extending-PostGIS-with-Python.pdf">https \
://2017.foss4g.org/post_conference/Extending-PostGIS-with-Python.pdf</a><o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>4.) CartoDB \
is also plpythonu based.<o:p></o:p></p></div><div><p class=MsoNormal>- <a \
href="https://github.com/search?q=repo%3ACartoDB%2Fcartodb+plpython&type=code">htt \
ps://github.com/search?q=repo%3ACartoDB%2Fcartodb+plpython&type=code</a><o:p></o:p></p></div><div><p \
class=MsoNormal>- crankshaft ( CARTO Spatial Analysis extension for \
PostgreSQL ) <a \
href="https://github.com/CartoDB/crankshaft">https://github.com/CartoDB/crankshaft</a><o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>5.) \
(tutorial) Map Matching in PostGIS with Valhalla and \
PL/Python<o:p></o:p></p></div><div><p class=MsoNormal><a \
href="https://gis-ops.com/map-matching-postgis-plpython/">https://gis-ops.com/map-matching-postgis-plpython/</a><o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>6.) Apache MADlib \
( Graph, Deep learning, Statistics, .. ) <o:p></o:p></p></div><div><p \
class=MsoNormal><a href="https://madlib.apache.org/docs/latest/index.html">https://madlib.apache.org/docs/latest/index.html</a> \
( mostly plpythonu based ) <o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>....<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Be \
careful:<o:p></o:p></p></div><div><p class=MsoNormal><i>"</i><i><span \
style='font-size:11.0pt;font-family:"Arial",sans-serif;color:black'>PL/Python is only \
available as an <span class=gmail-quote>"untrusted"</span> language, \
meaning it does not offer any way of restricting what users can do in it and is \
therefore named </span></i><code><i><span \
style='font-size:11.0pt;color:black'>plpython3u</span></i></code><i><span \
style='font-size:11.0pt;font-family:"Arial",sans-serif;color:black'>. A trusted \
variant </span></i><code><i><span \
style='font-size:11.0pt;color:black'>plpython</span></i></code><i><span \
style='font-size:11.0pt;font-family:"Arial",sans-serif;color:black'> might \
become available in the future if a secure execution mechanism is developed in \
Python. The writer of a function in untrusted PL/Python must take care that the \
function cannot be used to do anything unwanted, since it will be able to do anything \
that could be done by a user logged in as the database administrator. Only superusers \
can create functions in untrusted languages such as </span></i><code><i><span \
style='font-size:11.0pt;color:black'>plpython3u</span></i></code><i><span \
style='font-size:11.0pt;font-family:"Arial",sans-serif;color:black'>."</span></i><o:p></o:p></p></div><div><p \
class=MsoNormal><a href="https://www.postgresql.org/docs/15/plpython.html">https://www.postgresql.org/docs/15/plpython.html</a><o:p></o:p></p></div><div><p \
class=MsoNormal><a href="https://dba.stackexchange.com/questions/132352/why-is-pl-pyth \
on-untrusted">https://dba.stackexchange.com/questions/132352/why-is-pl-python-untrusted</a><o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>Regards,<o:p></o:p></p></div><div><p \
class=MsoNormal> Imre<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Shaozhong SHI \
<<a href="mailto:shishaozhong@gmail.com">shishaozhong@gmail.com</a>> ezt írta \
(időpont: 2022. dec. 3., Szo, 21:59):<o:p></o:p></p></div><blockquote \
style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in \
6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal>It will be interesting \
to see novel Python functions in PostGIS.<o:p></o:p></p><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>Regards,<o:p></o:p></p></div><div><p \
class=MsoNormal>David<o:p></o:p></p></div><p \
class=MsoNormal>_______________________________________________<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" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p></blockquote></div></div></div></body></html>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic