# Creating circle in PostGIS?

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) (
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.

• How did you measure the radii to find out they are not 600 km? – underdark May 28 '11 at 12:13
• @underdark I know distance between two cities and circle generated using as centre one of this city do not extend to other city. I verify distance between two cities using gmap-pedometer.com – angelcervera May 28 '11 at 12:54
• This sounds related to gis.stackexchange.com/questions/10148/… – underdark May 28 '11 at 13:45
• @underdark Using `SELECT ST_Distance( (select point from points where id = 7), (select point from points where id = 42));` return more than 100km of difference respect gmap-pedometer.com, but if i suppose this length as correct, circle radius is perfect. But people use the first distance as radius, no the distance returned by st_distance. Is possible transform first distance to second one? – angelcervera May 28 '11 at 14:35

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.

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.

• @nicklas-aven Is it possible 100km of difference in a 600km distance because i am using 900913 projection and not 4326? – angelcervera May 28 '11 at 17:01
• yes it is possible. check Pauls comment her when I couldn't belive that inaccurancy gis.stackexchange.com/questions/3264/…. the thing is that 4326 is not projected. – Nicklas Avén May 29 '11 at 10:10

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?

• +1 for using geography type for maps covering great areas. the problem here is that there is no native buffer function for geography type in postgis. but there is an inbuilt cast to "best srid", building the buffer and then back to 4326. So knowing your data and choosing a srid manually gives a better control. – Nicklas Avén May 29 '11 at 10:05
• Nicklas, you are correct about the casting and I agree with you 100% about the local projection (i.e knowing your data). Nevertheless, if the answer is that he just wants to have "world-wide" data, IMHO, the casting logic inside the Geography type will do a much better job at figuring out what SR is appropriate for that particular distance operation rather than any other complicated custom logic. Another good thing about the geography type is that although a lot of the operations are currently casted to cartesian math, the operations keep getting updated to use sphere mathematics. – Ragi Yaser Burhum May 29 '11 at 16:58

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()`).