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]] *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]] *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] <mailto:[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
