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 <[email protected]> > 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:* [email protected] [ > mailto:[email protected]<[email protected]>] > *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:* [email protected] [ > mailto:[email protected]<[email protected]>] > *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 > > [email protected] > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > _______________________________________________ > postgis-users mailing > [email protected]http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
