[postgis-users] Configuration and performance of PostGIS

[Imre Samu]

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220422/e0c6bc75/attachment.html>
-------------- 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/20220422/e0c6bc75/attachment.png>