TR: St_AsEwkt

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

TR: St_AsEwkt

Olivier Leprêtre

 

Hi,

 

Yes, you're certainly right, there must be other way of doing that, it's an old bigger script which I was revisiting. I just extracted a small part of it. It uses pgplsql because the purpose is to create tables of results. Using st_AsEwkt was the only way I found to get geometry from a loop and pass it in another loop.

 

Thanks,

 

Olivier

 

De : postgis-users [[hidden email]] De la part de Tom van Tilburg
Envoyé : jeudi 6 juillet 2017 10:55
À : PostGIS Users Discussion
Objet : Re: [postgis-users] St_AsEwkt

 

Somehow I feel there is no need for plpgsql in what you are doing and that indeed the ST_AsEwkt is too much.

I can't wrap my head around what this script is exactly trying to do though. Can you elaborate a bit on your use-case? What is the original data and what would you like to know from it?

 

 

Best,

 Tom

 

On Wed, Jul 5, 2017 at 7:54 PM, Olivier Leprêtre <[hidden email]> wrote:

Hi,

 

I'm using pgplsql to calculate shortest distance between points. This works but I'm wondering if my code is optimized because I'm using many ST_AsEwkt.

 

sqls='SELECT id code,ST_AsEwkt(ST_StartPoint(the_geom)) as geom1,ST_AsEwkt(ST_EndPoint(the_geom)) as geom2 FROM ' || schem || '.trench';

FOR row IN

            EXECUTE (sqls)

LOOP

 

            sqls1='SELECT id code1,ST_Distance(ST_AsEwkt(the_geom),St_AsEwkt(''%2$s'')) FROM %1$s.nodes order by ST_Distance(ST_AsEwkt(the_geom),ST_AsEwkt(''%2$s'')) LIMIT 1';

            sqls1=format(sqls1,schem,row.geom1);

 

            FOR row1 IN

                        EXECUTE (sqls1)

...

 

Would it be possible to have a smarter syntax with less ST_AsEwkt ?

 

Thanks,

 

Olivier

 

Garanti sans virus. www.avast.com


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: TR: St_AsEwkt

Felix Kunde
Hi,
in general, don't use FOR loops if you just want to iterate over query results. You can do that with plain SQL (or SQL function). You could try out the LATERAL construct if you have FOR EACH use cases.
Cheers
Felix
 


Betreff: [postgis-users] TR: St_AsEwkt

 
Hi,
 
Yes, you're certainly right, there must be other way of doing that, it's an old bigger script which I was revisiting. I just extracted a small part of it. It uses pgplsql because the purpose is to create tables of results. Using st_AsEwkt was the only way I found to get geometry from a loop and pass it in another loop.
 
Thanks,
 
Olivier
 

De : postgis-users [mailto:[hidden email]] De la part de Tom van Tilburg
Envoyé : jeudi 6 juillet 2017 10:55
À : PostGIS Users Discussion
Objet : Re: [postgis-users] St_AsEwkt
 

Somehow I feel there is no need for plpgsql in what you are doing and that indeed the ST_AsEwkt is too much.
I can't wrap my head around what this script is exactly trying to do though. Can you elaborate a bit on your use-case? What is the original data and what would you like to know from it?

 

 

Best,

 Tom

 

On Wed, Jul 5, 2017 at 7:54 PM, Olivier Leprêtre <[hidden email][mailto:[hidden email]]> wrote:

Hi,
 
I'm using pgplsql to calculate shortest distance between points. This works but I'm wondering if my code is optimized because I'm using many ST_AsEwkt.
 
sqls='SELECT id code,ST_AsEwkt(ST_StartPoint(the_geom)) as geom1,ST_AsEwkt(ST_EndPoint(the_geom)) as geom2 FROM ' || schem || '.trench';
FOR row IN
            EXECUTE (sqls)
LOOP
 
            sqls1='SELECT id code1,ST_Distance(ST_AsEwkt(the_geom),St_AsEwkt(''%2$s'')) FROM %1$s.nodes order by ST_Distance(ST_AsEwkt(the_geom),ST_AsEwkt(''%2$s'')) LIMIT 1';
            sqls1=format(sqls1,schem,row.geom1);
 
            FOR row1 IN
                        EXECUTE (sqls1)
...
 
Would it be possible to have a smarter syntax with less ST_AsEwkt ?
 
Thanks,
 
Olivier

 

[https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient]
Garanti sans virus. www.avast.com[https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient]

_______________________________________________
postgis-users mailing list
[hidden email][mailto:[hidden email]]
https://lists.osgeo.org/mailman/listinfo/postgis-users
 
_______________________________________________ postgis-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/postgis-users[https://lists.osgeo.org/mailman/listinfo/postgis-users]
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...