Hi,
After the PostGIS 3.4.3 release, that fixed an issue with sphere based
calculations for geography type, I decided to run a quick test to see if
sphere based calculations would give a performance benefit for my
workflow. Due to the existing bug, I had been using spheroid based
calculations exclusively up to now.
According to the Help (https://postgis.net/docs/ST_Area.html), the
sphere based calculations should be faster. However, looking at the
timings listed below, it appears there is no significant difference?
SQL used:
DROP MATERIALIZED VIEW IF EXISTS atest;
CREATE MATERIALIZED VIEW atest AS SELECT
ST_AREA(way::geography,true/false) FROM planet_osm_polygon
The test data was the Geofabrik Italy extract, and used the polygon
table of an osm2pgsql import containing +/- 19M records, containing
polygons of varying sizes up to country size. The tests were run on a
local dedicated system with plenty of IO and CPU.
Marco
*** SPHEROID ***:
Start time 2024-10-01 08:07:05.984
Finish time 2024-10-01 08:07:15.248
Start time 2024-10-01 08:08:13.881
Finish time 2024-10-01 08:08:24.362
Start time 2024-10-01 08:08:42.772
Finish time 2024-10-01 08:08:51.998
Start time 2024-10-01 08:09:16.137
Finish time 2024-10-01 08:09:26.484
*** SPHERE ***
Start time 2024-10-01 08:10:35.261
Finish time 2024-10-01 08:10:45.846
Start time 2024-10-01 08:11:05.856
Finish time 2024-10-01 08:11:16.110
Start time 2024-10-01 08:11:35.278
Finish time 2024-10-01 08:11:44.499
Start time 2024-10-01 08:12:18.769
Finish time 2024-10-01 08:12:29.020
