Creating circle in PostGIS

Question

I'm using PostGIS 1.5.2, with geometries in SRID:900913. I need create a circles using a list of points as centre, with a radius of 600 kilometers.

I'm using this query:

INSERT INTO circles (geom) (
   SELECT ST_Buffer(point, 600000, 'quad_segs=8') 
   FROM points
);

But circles created have not 600 kilometers of radius (radius is near this length, but not exactly).

Are there other methods to create circles in PostGIS?

NOTE: Information represented is from Spain. Correct projection is 4326, but client use Google rasters, so I am storing data in 900913 to avoid reprojections and increase performance.

Answer 1

Try this:

 SELECT ST_Transform(geometry( 
            ST_Buffer(geography( 
                ST_Transform( point, 4326 )), 
                600000)), 
            900913) FROM points`

This flips into geography then uses the inbuilt SRID selection to (ironically) flip back into geometry where a good planar buffer is run then flips back. The trouble with your approach in Mercator is that Mercator doesn't preserve distance. Using a more appropriate local projection gives better results, and that is what happens in the above method.

Answer 2

You can increase the quad_seg value to get a more accurate circle. To get a real circle you have to work with a curved geometry type, but I don't know what software can show that.

The accuracy of srid 90013 is also very bad since it is a projection covering the whole world.

You will get a more accurate result with a local projection.

Answer 3

It depends where your circles are being created. Are they near the equator or closer to the poles?

Take a look at this map. Do you think Antartica or Greenland are really that big? That is the projection you are using, right?

I would recommend you do a quick read of this USGS document on projections, particularly the table below that gives you a quick idea of which projections are good for what.

And after all that, I think I should finally answer your question :)

What Nicklas said was good advice. Is there a particular projection for your local area that would work better?

Otherwise you may want to look at the new PostGIS Geography type. Nevertheless, the most appropriate answer depends on where your data is located.

UPDATE: Since now we know that your data is in Spain, did you look at storing it in a local projection like UTM Zone 31N, doing your operations using that, and then projecting them back to Google Web Mercator?

Answer 4

You can use the newish SQL/MM Part 3 geometry type CIRCULARSTRING and/or CURVEPOLYGON.

However, be warned that the support for this type is limited, both with internal functions and external programs. You can use ST_CurveToLine to help visualize it.

Furthermore, it is a bit off-putting (as of PostGIS 2.0 svn) that SELECT ST_Area('CURVEPOLYGON(CIRCULARSTRING(2 1, 1 2, 0 1, 1 0, 2 1))'::geometry) is only a rough approximation of π. (Compare 3.14033115695475 to 3.14159265358979 from pi()).