0
CREATE TABLE rotated_bus AS SELECT AO_id,
RotateCoords(ST_Centroid(Geometry) FROM Substation, 45.00) AS Geometry FROM Busbar;

I am trying to rotate a line geometry (Busbar) by passing the centroid of a polygon geometry (Substation) inside the RotateCoords Function. After running the above query, I am getting an error “near FROM syntax error”. What is wrong with my query?

pavel
  • 26,538
  • 10
  • 45
  • 61
harinish
  • 261
  • 3
  • 5
  • 17

1 Answers1

1

You could join the two tables:

CREATE TABLE rotated_bus AS
SELECT Busbar.AO_id,
       RotateCoords(ST_Centroid(Substation.Geometry), 45) AS Geometry
FROM Busbar
JOIN Substation ON Busbar.AO_id = Substation.Substn_ID

Alternatively, use a correlated subquery (subqueries must be complete queries inside an extra pair of parentheses):

CREATE TABLE rotated_bus AS
SELECT AO_id,
       (SELECT RotateCoords(ST_Centroid(Geometry), 45)
        FROM Substation
        WHERE Substn_ID = Busbar.AO_id
       ) AS Geometry
FROM Busbar
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Also, take into account that you can't `CREATE TABLE AS SELECT` on SpatiaLite to get a working geometric table: http://stackoverflow.com/questions/29744932/sql-spatialite-how-to-declare-a-column-as-geometry – antonio May 15 '15 at 15:10