I have a vector layer consisting of polygons. I'm calculating the area of each area by the query:

SELECT *, ST_Area(the_geom::geography,true) alan FROM pl_park

I'm using Carto to query the data. The result is as I wanted:

Query Result

But when I want to continue with this query and select, let's say, areas greater than some value, I simply can't, because I can't save the column.


You could either use that query as a subquery:

SELECT subquery.*
  [your query]
) AS subquery
WHERE subquery.alan > [some_value]

or you have to add a column and populate it in your table via any DB client (I don't know how Carto let's you access one of their hosted DBs. Or do you host your DB yourself?)

EDIT: It seems like you can query the DB to alter the data structure just as normal via SQL. In that case you can add and populate a new column like so:

ALTER TABLE pl_park ADD COLUMN area numeric;
UPDATE pl_park SET area = ST_Area(pl_park.the_geom::geography);

Not-asked-for-advice: it´s not always best practice to simply add arbitrary calculated data as new columns to your DB tables. You can use views or prepared queries to quickly get calculated values from your tables. In this case, though...why not.

  • You should probably mention that adding a column like this will fragment the table and degrade performance (which will need a full VACUUM to repair), and that updating a geometry will not update the area, and that columns which will be queried should have indexes. – Vince Aug 18 '17 at 10:57

Why not just add longer custom SQL? No need to save table or column for filtering. For example:

SELECT *, ST_Area(the_geom::geography,true) alan FROM pl_park WHERE ST_Area(the_geom::geography,true) > 1000
  • Lets say I don't have any GIS program and I want to change the data permanently. Extending the SQL can only help me to visualize data the way I wanted. – Capan Sep 6 '17 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.