SQL Server Circle

Question

I'm trying to create a circle in a SQL Server based on a midpoint and a radius.

This seems to be close as a solution, but it creates an oval or an ellipse vs a circle. Is there another way to create a circle?

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT(-88.0 44.5)', 4326);
select @g.BufferWithTolerance(5, .01, 1)

I'm currently using SQL Server 2008.

This code also demonstrates the problem. The spatial results look like a circle, but when I draw the circle on google maps it is oval. Also, when I use STContains to see what points are in the circle, it is definitely following the oval outline.

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] WHERE [name] = N'##circle')
    DROP TABLE ##circle;

CREATE TABLE ##circle
(
    [Polygon] [geometry] NOT NULL
)

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT(-88.0 44.5)', 4326);

insert into ##circle (Polygon) 
values (@g.BufferWithTolerance(.5,.01,1))

select Polygon from ##circle

Rust

I ran both of your code snippets in 2012 and it produced circles. It is possible you have some funky stuff going on with your global temp table. I would switch to a local/session specific temp table and see if your problem persists. – Neurophysiology

Answer 1

Your problem is that you draw a circle in geographical coordinates. Google Maps is using web mercator projection https://en.wikipedia.org/wiki/Web_Mercator, so that your circle is going to be an oval. If you want to make something that looks like circle in Google Maps, you must make it in a data set with the web mercator projection. (I am deliberately saying "looks like a circle" since if you Project it to another system, e.g. utm for a large scale map, it may end up as an oval again.)

the epsg code for web_mercator is 3857, so if you project your x and y coordinates to web_mercator

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT(-9796115.18981 5543147.20386)', 3857);
SELECT @g.BufferWithTolerance(5, .01, 1)

Should work (just input the -9796... 5543... Are the web_mercator coordinates for your geographical X and Y)

It seems like you need to use SQL server spatial Tools (https://gis.stackexchange.com/questions/2723/is-it-possible-to-reproject-spatial-data-using-sql-server) or an external tool to do the reprojection. If you have just a few Points, http://cs2cs.mygeodata.eu/ may be useful.

Hormonal