6

What's the fastest way to convert a polygon to a point in PostGIS?

I don't care where the point is in relation to the geometry, but the resulting point does need to be consistent.

Using ST_Centroid takes about 19 seconds to do it for 1000 polygons, which seems very slow (I need to do it repeatedly for millions!), but that's probably because it's calculating the centroid of the feature.

I briefly flirted with ST_DumpPoints, but that seems to take even longer (over 30 seconds for the same 1000) points.

So what's the fastest way to do this?

  • You probably already did this but make sure you have indexes/vacuum/analyze/server performance... gis.stackexchange.com/a/194576/72697 – kttii Mar 3 '17 at 14:41
  • Time taken using ST_centroid will greatly depend on the complexity of the polygons. Perhaps some method using the centroid of an mbr might be faster. I am slightly puzzled at using ST_DumpPoints: this will create multiple points per polygon, whereas I am assuming you just want one? If your polygons complex then this would explain why ST_DumpPoints took a long time. – George of all trades Mar 3 '17 at 16:03
  • Would your polygons be conducive to centroids of bounding boxes? Perhaps you could pull them from the indexing? – Nate Wanner Mar 3 '17 at 16:44
  • 1
    Indexes are irrelevant here. These must be massive polygons...ST_Centroid cpu time is dominated by deserialization in my experience. – dbaston Mar 3 '17 at 20:19
  • 1
    Try the ST_XMin and ST_XMax functions and use the mid-points. – Martin F Mar 4 '17 at 1:51
10

The spped of the different approaches will, of course, depend on the complexity of the polygons involved. The algorithms to calculate a centroid, as with ST_Centroid, require you to actually deserialize the polygon as @dbaston has said, and also do the calculation using all the points. ST_Xmax, ST_Xmin and ST_Ymax, ST_Ymin will be faster, as there is less computation involved than with the centroid. Perhaps, surprisingly, adding a spatial index does not appear to increase the speed of these min/max functions, even though the bounding box is an integral part of Gist (spatial) indexing -- which, as dbaston has pointed out in the comments, is because the bbox points are stored as single precision and, are therefore, unusable by the bounding box functionns. ST_PointOnSurface is guaranteed to find a point in the polygon, but as with ST_Centroid, involves an algorithm actually using all the points, also see this answer.

ST_DumpPoints seems wasteful, as it returns an array of all points, so you have all the expense of deserializing the geometry into a point array, and you are then throwing away n-1 of the points.

The fastest method I could find was using ST_PointN in conjunction with ST_Exteriorring, as ST_PointN requires a Linestring, which ST_ExteriorRing returns. ST_PointN only needs to extract the first point (in the example below), and doesn't need to do any other calculations.

Here are some fairly arbitrary numbers for some tests using a table of 10,000 geometries each with at least 1000 points, repeating 10 times, and averaging, on my laptop, in descending speed order:

SELECT ST_PointOnSurface(geom) FROM sometable;

took 2345 ms.

SELECT ST_Centroid(geom) FROM sometable;

took 460 ms.

SELECT ST_MakePoint(ST_Xmin(geom), ST_Ymin(geom)) FROM sometable;

took 259 ms.

SELECT ST_PointN(ST_Exteriorring(geom), 1) FROM sometable;

took 112ms.

I did the same tests with smaller geometries and the gap between the ST_PointN and ST_XMax/ST_Ymax (or min equivalents) was closer, but ST_PointN was still the clear winner.

You have stated that you don't care where the point is, but that it needs to be consistent, so PointN(geom, 1) would appear to meet your purposes, as it will always return the first point and is the fastest.

  • I'm glad this works for you. I was concerned I was answering the wrong question. You must have some very, very large polgons or a very, very slow computer for some of the numbers you were reporting above. I am guessing this approach is saving a lot of time. – John Powell Mar 6 '17 at 13:08
  • 1
    Bounding boxes are stored as single-precision, which is why they aren't used for ST_XMin etc. – dbaston Mar 6 '17 at 19:53
  • @dbaston, thanks. I really need to learn to grok the source better. – John Powell Mar 6 '17 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.