[postgis-users] Configuration and performance of PostGIS
Imre Samu
pella.samu at gmail.com
Sat May 7 18:13:00 PDT 2022
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220508/cada61f2/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/cada61f2/attachment.png>
More information about the postgis-users
mailing list