Subscribe to this thread
Home - General / All posts - Create square polygon around point
ashleym52 post(s)
#20-Feb-15 18:25

Hello, I am wondering what is the correct way to create a square polygon of specified meters to form around a given centroid?

I know the centroid transform gives me the centroid of a polygon, but now i'm wondering how to the opposite?

Any advice is appreciated, thank you.

danb

2,064 post(s)
#20-Feb-15 19:54

This should get you started

SELECT BoundingBox(Buffer(Centroid([Geom (I)]), 100))[BOX]

FROM [Drawing];

The distance here is 100.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

ashleym52 post(s)
#20-Feb-15 20:28

Hi, thanks for the start. I tried pasting this into a script (create-->script) and get syntax error. My "drawing" layer has a single point to start. I'm not familiar with SQL.. could you let me know what I have to fill in?

tjhb
10,094 post(s)
#20-Feb-15 20:41

It's a query, in SQL, not a script.

(Sometimes people will say 'an SQL script' but that's inaccurate. The difference, apart from the language--well, completely bound up with the language--is that a script is a series of steps, taken one after the other. Instead, a query describes a logical relationship between sets, in the basic case just an input set and an output set, but there can be many intermediate sets in between. These are logical 'steps', but the sequence of operations to carry them out is left for the query engine to figure out and may be quite different from the logical steps.)

So use Create - Query.

ashleym52 post(s)
#20-Feb-15 21:52

thanks for the info! it worked!!!

tjhb
10,094 post(s)
#20-Feb-15 22:09

There are a couple of extra steps: either linking a new drawing from the query (for which an OPTIONS CoordSys() clause should be added to the current query); or making it into an INSERT INTO query so that the square areas are added to the source drawing (or some other drawing).

tjhb
10,094 post(s)
#21-Feb-15 21:21

Just to finish this off, here are examples of queries you could use for each of those methods. The queries are almost the same, but do different things with their output.

To use query 1 you go File>Link>Drawing... This Project (OK)... Type [Table with geometry column] Source [Query 1] Columns [Box] Geometry [Box] (OK). You can then Unlink the resulting drawing if you want to make it fixed. This is a cumbersome sequence but you'll use it a lot.

To use query 2, if you want the squares to be inserted into the source drawing, then you just run it. If you want the squares in a different drawing then duplicate the original drawing, empty the duplicate, and adjust the query to INSERT INTO the new drawing.

I'm using a different method than Dan. It looks less efficient (perhaps) but it directly creates just the four points that are needed for each square, rather than making a buffer (with 32 coordinates) then calculating its bounding box.

In both cases you enter the length of the side of the output squares as a parameter. It would be fairly easy to add a rotation parameter if you needed one. Both of these things could also come from point attributes, if they needed to vary.

Lastly you might want to include data from source points for each square. Omitted for now.

-- Query 1

OPTIONS CoordSys("Drawing" AS COMPONENT);

PARAMETERS [Side] DOUBLE;

SELECT

    AssignCoordSys(

        ConvertToArea(NewLine(

            NewPoint([X] - [dX][Y] - [dY]),

            NewPoint([X] - [dX][Y] + [dY]),

            NewPoint([X] + [dX][Y] + [dY]),

            NewPoint([X] + [dX][Y] - [dY])

            )),

        CoordSys("Drawing" AS COMPONENT)

        ) AS [Box]

FROM 

    (SELECT

        CentroidX([ID]AS [X],

        CentroidY([ID]AS [Y]

    FROM [Drawing]

    WHERE IsPoint([ID])

    ) AS [D]

    CROSS JOIN

    (VALUES ([Side] / 2, [Side] / 2) NAMES ([dX][dY])

    ) AS [T]

;

.

-- Query 2

PARAMETERS [Side] DOUBLE;

INSERT INTO [Drawing] -- or an empty duplicate

    ([Geom (I)])

SELECT

    AssignCoordSys(

        ConvertToArea(NewLine(

            NewPoint([X] - [dX][Y] - [dY]),

            NewPoint([X] - [dX][Y] + [dY]),

            NewPoint([X] + [dX][Y] + [dY]),

            NewPoint([X] + [dX][Y] - [dY])

            )),

        CoordSys("Drawing" AS COMPONENT)

        )

FROM 

    (SELECT

        CentroidX([ID]AS [X],

        CentroidY([ID]AS [Y]

    FROM [Drawing]

    WHERE IsPoint([ID])

    ) AS [D]

    CROSS JOIN

    (VALUES ([Side] / 2, [Side] / 2) NAMES ([dX][dY])

    ) AS [T]

;

You can see the thing about sets here. Start at the inside and work out, not at the top then down. (So here start at (SELECT...) AS [D] CROSS JOIN (VALUES...) AS [T].) Two simple sets in, one set out.

The input sets are given by two tables: [D], derived from the source drawing's table, with fields [X] and [Y]; and [T], created within the query by the VALUES statement, with fields [dX] and [dY] (which here are the same, half the [Side] parameter). The CROSS JOIN makes the cross product of the two sets: for each row on the left (from [D]), each row on the right (from [T], here just one row) is listed beside it, forming a new table (combined set). That combined set is processed in the outer SELECT statement, to produce a new set (table) for output.

It helps I think to get into the habit of thinking quite strongly of tables in a join as being on the left and the right. SQL is inherently a two-handed operation.

(P.s. if it seems strange to use CentroidX() and CentroidY() to get the X and Y coordinates of a point, well, a lot of people would agree with you I think.)

Attachments:
Query 1.txt
Query 2.txt

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.