CODEWITHSUNDEEP
sqlpostgresqlpostgiscommon-table-expression
Borealis
Borealis

Reputation: 8470

How to use a table in SQL WITH statement?

I am trying to use a pre-existing table in the SQL statement at the bottom of the question rather than the data that is being generated in the SQL statement. Currently, there is some data that is generated using:

WITH polys(poly_id, geom) AS (VALUES  (1, 'POLYGON((1 1, 1 5, 4 5, 4 4, 2 4, 2 2, 4 2, 4 1, 1 1))'::GEOMETRY),
                                      (2, 'POLYGON((6 6, 6 10, 8 10, 9 7, 8 6, 6 6))'::GEOMETRY)),

However, let's say I already have a table named polys with the poly_id and geom columns, exactly as what would be created above. How can I insert my pre-existing polys table into this SQL statement (i.e. what syntax would I use)?

I have tried the following to add a pre-existing polys table using:

CREATE TABLE polys_pts AS
WITH polys(poly_id, geom) AS,

with the following error:

ERROR:  syntax error at or near ","
LINE 2: WITH polys(poly_id, geom) AS,
                                    ^

Full Code:

CREATE TABLE polys_pts AS
WITH polys(poly_id, geom) AS (VALUES  (1, 'POLYGON((1 1, 1 5, 4 5, 4 4, 2 4, 2 2, 4 2, 4 1, 1 1))'::GEOMETRY),
                                      (2, 'POLYGON((6 6, 6 10, 8 10, 9 7, 8 6, 6 6))'::GEOMETRY)),
pnt_clusters AS (SELECT  polys.poly_id,
      CASE
          WHEN ST_Area(polys.geom)>9 THEN ST_ClusterKMeans(pts.geom, 8) OVER(PARTITION BY polys.poly_id) 
          ELSE ST_ClusterKMeans(pts.geom, 2) OVER(PARTITION BY polys.poly_id) 
      END AS cluster_id, pts.geom FROM polys,
          LATERAL ST_Dump(ST_GeneratePoints(polys.geom, 1000, 1)) AS pts),
centroids AS (SELECT cluster_id, ST_PointOnSurface(ST_collect(geom)) AS geom FROM pnt_clusters GROUP BY poly_id, cluster_id),
neg_buffer AS (SELECT poly_id, (ST_Buffer(geom, -0.4, 'endcap=flat join=round')) geom FROM polys GROUP BY poly_id, polys.geom),
neg_buffer_pts_out AS (SELECT a.cluster_id, (a.geom) geom FROM centroids a WHERE EXISTS (SELECT 1 FROM neg_buffer b WHERE ST_Intersects(a.geom, b.geom))),
neg_buffer_pts_in AS (SELECT a.cluster_id, (a.geom) geom FROM centroids a WHERE NOT EXISTS (SELECT 1 FROM neg_buffer b WHERE ST_Intersects(a.geom, b.geom))),
snap_pts_clusters_in AS (SELECT DISTINCT ST_ClosestPoint(ST_ExteriorRing(a.geom), b.geom) AS geom FROM neg_buffer a, neg_buffer_pts_in b),
node_pts AS (SELECT ST_StartPoint(ST_ExteriorRing(geom)) geom FROM neg_buffer),
snap_pts AS (SELECT b.cluster_id, a.geom FROM snap_pts_clusters_in a JOIN centroids b ON ST_DWithin(a.geom, b.geom, 0.4))
SELECT  a.cluster_id, (a.geom) geom FROM snap_pts a WHERE NOT EXISTS (SELECT 1 FROM node_pts b WHERE ST_Intersects(a.geom, b.geom))
UNION SELECT c.cluster_id, (c.geom) geom FROM neg_buffer_pts_out c ORDER BY cluster_id;

Upvotes: 1

Views: 124

Answers (3)

Francisco Puga
Francisco Puga

Reputation: 25149

I'm not sure of understanding your question so i give you a broad answer.

To create a table from a query you must use:

CREATE TABLE foo AS
    SELECT * FROM my_table;

CTEs are builded as:

WITH 
    tmp1 AS (
        SELECT * from my_table1
    ), -- commna

    tmp2 AS (
        SELECT * from my_table2
    )

SELECT * from tmp1 JOIN tmp2 ON tmp1.id = tmp2.id -- no comma
;

Note that the are , to separate different "temporary" tables defined in the CTE but the final sentence is not preceded with a ,

So to create a table from a CTE the syntax will be:

CREATE TABLE foo AS
    WITH 
        tmp1 AS (
            SELECT * from my_table1
        ),

        tmp2 AS (
            SELECT * from my_table2
        )

    SELECT * from tmp1 JOIN tmp2 ON tmp1.id = tmp2.id -- no comma
;

Create a table from a VALUES clause is the same as the other cases:

CREATE TABLE polys2 AS
    VALUES
        (1, 'POLYGON((1 1, 1 5, 4 5, 4 4, 2 4, 2 2, 4 2, 4 1, 1 1))'::GEOMETRY),
        (2, 'POLYGON((6 6, 6 10, 8 10, 9 7, 8 6, 6 6))'::GEOMETRY)
;

If you already have a table called polys2 that has been created for example like is shown in the previous example, you can replace

CREATE TABLE polys_pts AS
    WITH 
        polys(poly_id, geom) AS (
            VALUES
                (1, 'POLYGON((1 1, 1 5, 4 5, 4 4, 2 4, 2 2, 4 2, 4 1, 1 1))'::GEOMETRY),
                (2, 'POLYGON((6 6, 6 10, 8 10, 9 7, 8 6, 6 6))'::GEOMETRY)),
         pnt_clusters AS (SELECT  polys.poly_id, ...

with

CREATE TABLE polys_pts AS
    WITH 
        polys(poly_id, geom) AS (
            SELECT poly_id, geom FROM polys2
         ),
         pnt_clusters AS (SELECT  polys.poly_id, ...

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246473

The definition of a CTE must be a complete statement, so you have to use

WITH polys(poly_id, geom) AS (
   SELECT *
   FROM (VALUES
           (1, 'POLYGON((1 1, 1 5, 4 5, 4 4, 2 4, 2 2, 4 2, 4 1, 1 1))'::GEOMETRY),
           (2, 'POLYGON((6 6, 6 10, 8 10, 9 7, 8 6, 6 6))'::GEOMETRY)
        ) AS p(p, g)
)

Upvotes: 1

hello_earth
hello_earth

Reputation: 1562

um, the question is not 100% clear to me - ... I am not familiar with pecularities of postgresql, but my first bet would be to try

WITH polys(...) AS (...), 
     pnt_clusters AS (...)
CREATE polys_pts AS (
     SELECT .. 
     FROM polys... etc.
)

but I guess this is not allowed since WITH only goes with DML statements (data manipulation unlike data definition (DDL) statements like CREATE)

so.. my next bet would be to try using polys and pnt_clusters that you defined inside WITH clause, inline inside the SELECT statement, given that

WITH a AS (
   SELECT x, y FROM z
)
SELECT * 
FROM a

is the same as

SELECT * 
FROM (
   SELECT x, y 
   FROM z
) AS a

well, otherwise I would split the process into two steps - create some kind of temporary tables first for polys and pnt_clusters and then do the create...

Upvotes: 1

Related Questions