[postgis-users] functions

[tommaso]

This should work:

create or replace function flow(integer) RETURNS TABLE (gid int, geom 
geometry, geojson text) AS
$$

WITH RECURSIVE flow(gid, geom) AS (

     SELECT e.gid, e.geom FROM electric_line e, fuses f WHERE 
ST_DWithin(ST_AsText(e.geom)::geometry, ST_AsText(f.geom)::geometry, 
0.01) AND f.gid=$1

   UNION ALL

     SELECT n.gid, n.geom

     FROM electric_line n, flow f

     WHERE ST_DWithin(ST_EndPoint(f.geom),ST_StartPoint(n.geom),0.01)

   )

SELECT *, ST_ASGeoJSON(ST_Transform(geom,4326),6) AS geojson

FROM flow;

  $$
LANGUAGE SQL;


SELECT * FROM flow(1);


On 12/09/2013 03:57 PM, Jonatan Malaver wrote:
>
> Hello,
>
>   I'm new to postgis/postgresql. I was wondering if anyone could tell 
> me how to make a function out of this sql query:
>
> WITH RECURSIVE flow(gid, geom) AS (
>
>     SELECT e.gid, e.geom FROM electric_line e, fuses f WHERE 
> ST_DWithin(ST_AsText(e.geom)::geometry, ST_AsText(f.geom)::geometry, 
> 0.01) AND f.gid=' . $_GET['fuse'] . '
>
>   UNION ALL
>
>     SELECT n.gid, n.geom
>
>     FROM electric_line n, flow f
>
>     WHERE ST_DWithin(ST_EndPoint(f.geom),ST_StartPoint(n.geom),0.01)
>
>   )
>
> SELECT *, ST_ASGeoJSON(ST_Transform(geom,4326),6) AS geojson
>
> FROM flow;
>
>    I'd like to make a function flow(gid)
>
> Thanks,
>
> Jon
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131209/0a55803b/attachment.html>