[postgis-users] functions

[Jonatan Malaver]

OK, so after making the changes suggested by Tommaso the query runs almost 50% faster! Here’s the new query
WITH RECURSIVE flow(gid, geom) AS (
    SELECT e.gid, e.geom FROM electric_line e, fuses f WHERE ST_DWithin(e.geom,f.geom, 0.01) AND f.gid=15
  UNION ALL
    SELECT n.gid, n.geom
    FROM electric_line n, flow f
    WHERE ST_Distance(ST_EndPoint(f.geom),ST_StartPoint(n.geom)) <= 0.01
  )
SELECT *, ST_ASGeoJSON(ST_Transform(geom,4326),6) AS geojson
FROM flow;

Rémi-C, I am trying to make a walk through from the fuse down to the last electric line. The performance is great when less than 400 linestrings. On resources, the sever currently runs with 2 CPUs and 6GB of ram. I could add more CPU and memory if you think it will make a difference. Also, I’m running on pgsql 9.2 and POSTGIS 2.0.1 r9979

Thanks,
Jon

From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Rémi Cura
Sent: Tuesday, December 10, 2013 5:21 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] functions

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<mailto: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> [mailto: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> [mailto: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<mailto:postgis-users at lists.osgeo.org>

http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



_______________________________________________

postgis-users mailing list

postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>

http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto: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/280d19cf/attachment.html>