[postgis-users] functions

[Rémi Cura]

On a more high level:
I'm guessing you are using the recursive cte to walk trough a graph.
There are ways to accelerate this, but at the cost of increase of memory
usage and duplication of data.
It depends a lot on you graph properties (has it a lots of nodes,
connectivity, centralness, etc)

(of course you have an index on "gid" or it is a primary key (implicit
index) ).

If perf is really an issue, you could turn to more specialize grpah db
(look for spar-ql)

Cheers,
Rémi-C


2013/12/10 tommaso <tommasodb at googlemail.com>

>  Why do you do ST_AsText(e.geom)::geometry? This is equivalent to e.geom.
> Change the query so:
> SELECT e.gid, e.geom FROM electric_line e, fuses f WHERE
> ST_DWithin(e.geom, f.geom 0.01) AND f.gid=$1 .....
>
> No sure if it does help, but you you can also try so:
> SELECT e.gid, e.geom FROM electric_line e JOIN fuses f ON
> ST_DWithin(e.geom, f.geom, 0.01) WHERE f.gid=$1
>
> Also
> WHERE ST_DWithin(ST_EndPoint(f.geom),ST_StartPoint(n.geom),0.01)
> can be changed to:
> WHERE ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom)) <= 0.01
> which should be faster.
>
> Obviously you need a GIST index on booth geometry columns.
>
> If it all does not help please attach some test data.
>
> Cheers, Tommaso
>
>
>
>
> On 12/10/2013 03:01 AM, Jonatan Malaver wrote:
>
>  Another question, what can I do to improve performance on the query? The
> query can take 2 seconds or more.
>
>
>
> *From:* postgis-users-bounces at lists.osgeo.org [
> mailto:postgis-users-bounces at lists.osgeo.org<postgis-users-bounces at lists.osgeo.org>]
> *On Behalf Of *Jonatan Malaver
> *Sent:* Monday, December 09, 2013 8:52 PM
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] functions
>
>
>
> Thank you!!!
>
>
>
> Jon
>
>
>
> *From:* postgis-users-bounces at lists.osgeo.org [
> mailto:postgis-users-bounces at lists.osgeo.org<postgis-users-bounces at lists.osgeo.org>]
> *On Behalf Of *tommaso
> *Sent:* Monday, December 09, 2013 12:57 PM
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] functions
>
>
>
> 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
>
>
>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> 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/20131210/c4d38127/attachment.html>