[postgis-users] Configuration and performance of PostGIS

[Shaozhong SHI]

On Fri, 22 Apr 2022 at 22:14, Imre Samu <pella.samu at gmail.com> wrote:

> >  as St_intersects or recursive query used,
>
> The other alternative  (  ~ less efficient )  is  using a “noded” network
> table ( "edge_table" )
> in the recursive query.  ( and don't forget to add indexes to the
> "source"  and  "target" columns )
>
> WITH RECURSIVE walk_network(id, source, target, targetPoint) AS
>  (SELECT et.id,et.source,et.target,ST_EndPoint(the_geom) as targetPoint
>          FROM edge_table et WHERE et.id = *12*
>   UNION ALL
>     SELECT e.id, e.source, e.target ,ST_EndPoint(the_geom) as targetPoint
>     FROM edge_table e
>        , walk_network w
>     WHERE w.target = e.source
>   )
> SELECT ST_AsText(ST_MakeLine(targetPoint))
> FROM walk_network
> ;
> +---------------------------------+
> |            st_astext            |
> +---------------------------------+
> | LINESTRING(4 2,3 2,2 1,1 1,0 0) |
> +---------------------------------+
> (1 row)
>
> regards,
>  Imre
>

I will have a close look.

With the use of  WHERE w.target = e.source, it probably avoided time
consuming computation of ST_DWithin.

Would WHERE w.target = e.source force the Postgres to use index-scan?

If so, that might well be a more efficient way to do it.

Regards,

David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220423/f7fe02a0/attachment.html>