none
Polygon from point and with certain number of sides

    Question

  • Hi,

    I need to create a polygon from point with given radius. This is my code

    DECLARE @g AS GEOGRAPHY = geography::STPointFromText('POINT(50 10)', 4326);
    DECLARE @h AS GEOGRAPHY = @g.STBuffer(100)
    
    SELECT @g as 'Geo', 'Point' AS 'Labels',@g.STAsText() as 'Text'
    UNION ALL
    SELECT @h, 'STBuffer(100)', @h.STAsText()
    SELECT @h.STNumPoints(), 'NoPoints', ''


    Here are two questions:

    1. Does .STBuffer(100) creates a radius of 100 meters? What does the unit comes from?

    2. My polygon has 72 points ( returned by STNumPoints() ) How can I make a polygon with specific number of points/sides, for e.g. hexagon?

    Thanks,

    P.

    Thursday, March 01, 2012 8:13 AM

All replies

  • 1. Yes, in this example linear distance is measured in metres, because you're defining the coordinates of your geography instance using SRID 4326. Confirm this as follows:

    SELECT unit_of_measure FROM sys.spatial_reference_systems WHERE spatial_reference_id = 4326;

    2. You can use BufferWithTolerance() to create a simple approximation of a circular polygon containing less sides. With an appropriate tolerance, you might be able to create a hexagon, but you can't specify the number of sides and it would be slightly trial-and-error. If you really need to create specific geometric shapes you're best off creating a custom SQLCLR method to do so.


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Marked as answer by Przemo2 Thursday, March 01, 2012 1:04 PM
    • Unmarked as answer by Przemo2 Friday, March 02, 2012 11:30 PM
    Thursday, March 01, 2012 10:14 AM
    Answerer
  • Thanks. That is a some solution. But can you provide me also with a custom custom SQLCLR for polygon with given number of sides/point?
    Thursday, March 01, 2012 11:34 AM
  • http://stackoverflow.com/questions/7198144/how-to-draw-a-n-sided-regular-polygon-in-cartesian-coordinates

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Marked as answer by Przemo2 Thursday, March 01, 2012 1:04 PM
    • Unmarked as answer by Przemo2 Friday, March 02, 2012 11:30 PM
    Thursday, March 01, 2012 12:48 PM
    Answerer
  • Well, it works. I have found also some code I have later adapted.

    -- Drawing a polygon
    declare @Sides float = 8
    declare @count int = 0
    declare @size int = 10
    declare @firstpoint varchar(400)
    
    --mid point
    declare @midX int = 20
    declare @midY int = 20
    
    declare @sql varchar(8000);
    
    set @sql =  'POLYGON((' 
    
    while(@count < @Sides)
    begin
    
    set @sql = @sql + cast(@midX + SIN(RADIANS((CAST(@count AS FLOAT)
                /CAST(@Sides AS FLOAT)) 
                * 360)) * @size as varchar) 
               + ' ' +
                cast(@midY + COS(RADIANS((CAST(@count AS FLOAT)
                /CAST(@Sides AS FLOAT))
                * 360)) * @size as varchar) + ','
     
    if @count = 0
    set @firstpoint  = cast(@midX + SIN(RADIANS((CAST(@count AS FLOAT)
                /CAST(@Sides AS FLOAT)) 
                * 360)) * @size as varchar) 
               + ' ' +
                cast(@midY + COS(RADIANS((CAST(@count AS FLOAT)
                /CAST(@Sides AS FLOAT))
                * 360)) * @size as varchar) 
                
    set @count = @count + 1
    end
    
    set @sql = @sql + @firstpoint + '))'
    print @sql
    
    DECLARE @g geometry;
    SET @g = geometry::STGeomFromText(@sql, 0);
    select @g, @g.ToString()

    but this is geometry and cartesian coordinates. Although I can easly convert it into geogprahy, this still will not be a x kilometer  radius. Some proportion could be used as a relation of degrees to km, but 1 km expressed as degrees differs on each point on earth (GPS - 4326).

    Once more time, any ideas here?

    Thanks.



    • Edited by Przemo2 Friday, March 02, 2012 11:30 PM
    Friday, March 02, 2012 11:29 PM
  • But if you want to use the geography datatype, you'll no longer be defining a regular-sided 2d polygon - you'll be using arcs to join points on a curved surface.

    What's the reason for wanting to do this? Perhaps we can suggest a better approach.


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Saturday, March 03, 2012 7:26 AM
    Answerer