[postgis-users] Configuration and performance of PostGIS
Shaozhong SHI
shishaozhong at gmail.com
Mon May 9 11:20:23 PDT 2022
Hello, Ruven Brooks,
Thank you for sharing the insight. That is very interesting for advancing
out understanding.
What do you think about the following strategy for conducting traversing
test?
Step 1. Find all circular loops formed by lines in a data set.
Step 2. Remove all such loops in the data set.
Step 3. Apply Walk the Network to carry out traversing test.
Regards,
David
On Mon, 9 May 2022 at 18:42, <ruvenml at beamerbrooks.com> wrote:
> The Walk the Network algorithm returns all points reachable from a
> particular starting point. The result is a tree. It only appears to be a
> single line because the network given as an example has been constructed
> without branches.
>
> Try adding:
> insert into network values ('linestring(3 4, 2 3)', 14);
>
> Note that what is returned is now a tree.
>
> Now, try adding:
> insert into network values ('linestring(0 0, 2 3)', 15);
>
> If you are patient enough, it will blow up with a memory allocation error
> because it creates a loop in the network.
>
> (Again, my appreciation to Paul Ramsey for constructing such a focused
> example.)
>
> Does a tree structure of paths starting at a designated node and ending at
> any node which has no outgoing edges satisfy your requirements or do you
> want the minimum cost/distance path? If so, you have lots of algorithms
> to choose from and watching some videos on graph theory might be time well
> spent.
>
> Ruven Brooks
>
>
>
>
>
>
>
>
> On 5/9/2022 7:39 AM, Shaozhong SHI wrote:
>
> Hi, Imre,
>
> What happens if more than 1 result from the Walk the Network?
>
> Can recursive query return all possible results?
>
> How to handle such results?
>
> My guess that memory allocation error occurred because that more than 1
> result is found and the recursive query does not know what to do.
>
> What is your thought?
>
> Regards,
>
> David
>
> 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
>>
>>
>> Imre Samu <pella.samu at gmail.com> ezt írta (időpont: 2022. ápr. 22., P,
>> 16:39):
>>
>>> > With a large data set,
>>>
>>> :-)
>>> please give more detail:
>>> - How large?
>>> - and what is your real "business problem"? what type of network?
>>>
>>>
>>> > I tried to use this
>>> http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html in
>>> the PostGIS.
>>>
>>> As I see this is a directed "network graph", and I will try using the
>>> pgRouting tool - for a large graph!
>>> *( "pgRouting extends the PostGIS/PostgreSQL geospatial database to
>>> provide geospatial routing and other network analysis functionality." )*
>>> The pgRouting project did not exist in 2010/07 when this blogpost was
>>> written!
>>>
>>> [image: image.png]
>>>
>>> so I have adapted the example network ( from the original blogpost )
>>> to pgRouting and this is my sample result
>>>
>>> ---------- ALL "downstream path" from "all deadends" sorted by
>>> descending cost ---------
>>>
>>> +------------+-----------+---------+-------------------------------------+--------------+
>>> | route_cost | start_vid | end_vid | the_geom_text
>>> | edge_ids |
>>>
>>> +------------+-----------+---------+-------------------------------------+--------------+
>>> | 6.24 | 3044 | 3000 | LINESTRING(4 4,3 4,2 3,1 2,1 1,0 0)
>>> | {13,9,6,3,1} |
>>> | 5.83 | 3043 | 3000 | *LINESTRING(4 3,4 2,3 2,2 1,1 1,0
>>> 0) | {12,8,5,2,1} |*
>>> | 4.83 | 3024 | 3000 | LINESTRING(2 4,2 3,1 2,1 1,0 0)
>>> | {10,6,3,1} |
>>> | 4.41 | 3014 | 3000 | LINESTRING(1 4,1 3,1 2,1 1,0 0)
>>> | {11,7,3,1} |
>>> | 3.41 | 3031 | 3000 | LINESTRING(3 1,2 1,1 1,0 0)
>>> | {4,2,1} |
>>>
>>> +------------+-----------+---------+-------------------------------------+--------------+
>>> and the second line is same as in the blogpost ( *"Downstream(12)" *example)
>>> ,
>>> just with an extra "deadends" points ; the edges :* {12,8,5,2,1} *
>>>
>>> start_vid : starting node/vertex id ( "deadends" in this example )
>>> end_vid : ending node/vertex id constant 3000 (0,0)
>>> node/vertex id = 3000 + X*10+Y coordinate // ( 2,1 ) --> 3021 ;
>>> (0,0) --> 3000
>>>
>>>
>>> > Whenever geospatial functions such as St_intersects or recursive query
>>> used,
>>>
>>> IMHO: A good scalable data model is extremely important.
>>> pgRouting has 2 important (separated) steps.
>>> - creating a routing topology - route optimized database ( with "start"
>>> - and "end" node/vertex )
>>> - fast routing/graph/"network-walking" functions - without the
>>> geometry ( using Boost Graph c++ library )
>>> ( in this example I have used
>>> https://docs.pgrouting.org/3.3/en/pgr_dijkstra.html )
>>>
>>>
>>> and this is my adapted "routing" topology edge table :
>>>
>>> DROP TABLE IF EXISTS edge_table CASCADE;
>>> CREATE TABLE edge_table (
>>> id bigint primary key,
>>> source bigint,
>>> target bigint,
>>> cost float,
>>> reverse_cost float,
>>> the_geom geometry
>>> );
>>> -- network example from
>>> --
>>> http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html
>>> INSERT INTO edge_table VALUES( 1, 3011, 3000, 1, -1, 'LINESTRING(1 1, 0
>>> 0)');
>>> INSERT INTO edge_table VALUES( 2, 3021, 3011, 1, -1, 'LINESTRING(2 1, 1
>>> 1)');
>>> INSERT INTO edge_table VALUES( 3, 3012, 3011, 1, -1, 'LINESTRING(1 2, 1
>>> 1)');
>>> INSERT INTO edge_table VALUES( 4, 3031, 3021, 1, -1, 'LINESTRING(3 1, 2
>>> 1)');
>>> INSERT INTO edge_table VALUES( 5, 3032, 3021, 1, -1, 'LINESTRING(3 2, 2
>>> 1)');
>>> INSERT INTO edge_table VALUES( 6, 3023, 3012, 1, -1, 'LINESTRING(2 3, 1
>>> 2)');
>>> INSERT INTO edge_table VALUES( 7, 3013, 3012, 1, -1, 'LINESTRING(1 3, 1
>>> 2)');
>>> INSERT INTO edge_table VALUES( 8, 3042, 3032, 1, -1, 'LINESTRING(4 2, 3
>>> 2)');
>>> INSERT INTO edge_table VALUES( 9, 3034, 3023, 1, -1, 'LINESTRING(3 4, 2
>>> 3)');
>>> INSERT INTO edge_table VALUES(10, 3024, 3023, 1, -1, 'LINESTRING(2 4, 2
>>> 3)');
>>> INSERT INTO edge_table VALUES(11, 3014, 3013, 1, -1, 'LINESTRING(1 4, 1
>>> 3)');
>>> INSERT INTO edge_table VALUES(12, 3043, 3042, 1, -1, 'LINESTRING(4 3, 4
>>> 2)');
>>> INSERT INTO edge_table VALUES(13, 3044, 3034, 1, -1, 'LINESTRING(4 4, 3
>>> 4)');
>>>
>>> full example code - with data&code:
>>> https://gist.github.com/ImreSamu/efda6093b67391a0edafff39d8056cb5
>>>
>>> if you are interested in more examples.. check the pgRouting tutorial
>>> for example: *"Pre-processing waterways data"*
>>>
>>> https://workshop.pgrouting.org/2.7/en/un_sdg/sdg11-cities.html#pre-processing-waterways-data
>>>
>>> regards,
>>> Imre
>>>
>>>
>>> Shaozhong SHI <shishaozhong at gmail.com> ezt írta (időpont: 2022. ápr.
>>> 22., P, 1:22):
>>>
>>>> Whenever geospatial functions such as St_intersects or recursive query
>>>> used, the PostGIS appears to spawn away to many child queries and just
>>>> obliterate the CPU. Nothing finishes.
>>>>
>>>> That forced me to try out to do the some tasks on the FME server.
>>>>
>>>> I tried to use this http://blog.cleverelephant.ca/2010/07/network
>>>> -walking-in-postgis.html in the PostGIS.
>>>>
>>>> I tried to linecombiner in FME. LineCombiner | FME (safe.com)
>>>> <https://www.safe.com/transformers/line-combiner/>.
>>>>
>>>> With a large data set, the running of processors were monitored. It
>>>> was estimated the PostGIS one would take 16 days to complete.
>>>>
>>>> But, it only took a few minute to do the same thing in FME.
>>>>
>>>> This suggests that something is not right with the PostGIS Server.
>>>>
>>>> Have anyone got experience with configuration and improving perfomance
>>>> of PostGIS Server?
>>>>
>>>> Regards,
>>>>
>>>> David
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>
>>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220509/736460c8/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 22381 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220509/736460c8/attachment.png>
More information about the postgis-users
mailing list