[postgis-users] Configuration and performance of PostGIS
Shaozhong SHI
shishaozhong at gmail.com
Sat May 7 18:31:16 PDT 2022
Can I simply generate values for startpoint and endpoint of a line?
Put these into startpoint and endpoint column
Modify the recursive query to see whether one can walk the network?
Regards,
David
On Sun, 8 May 2022 at 02:13, Imre Samu <pella.samu at gmail.com> wrote:
> > How to generate source and target values?
>
> The simplest method:
> Use the pgrouting ready made functions / tools
> - https://docs.pgrouting.org/latest/en/topology-functions.html
> - https://docs.pgrouting.org/latest/en/pgr_createTopology.html
> * SELECT pgr_createTopology('edge_table', 0.001, 'id', 'the_geom');*
> There is a nice Pgrouting tutorial:
> https://workshop.pgrouting.org/2.7/en/index.html
> and they have a chat: https://gitter.im/pgRouting/pgrouting ( see more
> about support: https://pgrouting.org/support.html )
>
> > Can I understand that source means startpoint of a line, and target
> means an endpoint of a line?
>
> it is a "Graph"; so you have to split the lines into "edges"
> https://en.wikipedia.org/wiki/Graph_theory#Graph
>
> regards,
> Imre
>
>
> Shaozhong SHI <shishaozhong at gmail.com> ezt írta (időpont: 2022. máj. 8.,
> V, 2:37):
>
>>
>>
>> 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
>>>
>>>
>> How to generate source and target values?
>>
>> Regards,
>>
>> David
>>
>>
>>
>>> 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 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220508/221a13c4/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/20220508/221a13c4/attachment.png>
More information about the postgis-users
mailing list