[postgis-users] Storing geometries in variables

[Stephen Woodbridge]

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