We cannot specify spatial SQL commands in QGIS as we might like (it would be a good extension in the future) but we can create "Views" of data that will be saved in PostgreSQL. Views are stored in the database and allow us to setup predefined queries for data.
Note: There are a great many variations to the steps below and some of them will work. However, the following steps have been tested repeatedly by me on a virtual machine running on a USB drive and if you vary from them you may find more problems than if you stick with them.
You can create views using dialog boxes quickly and easily.
Note: You may see a warning (triangle with an exclamation point). Expand the window and you'll see you may need to select an "SRID" and/or a "Feature id". Select "0" for the "SRID" as you can specify this later and select a unique ID for the feature ID.
If you right-click on a view and select "Properties", you'll notice a message indicating you might want to edit the view in the script editor. This is because the script editor is a more full-feature environment.
Note: Remember that you can always copy the "SELECT" portion of the query and edit it in a regular query window. This method will make it easier to see errors and debug them.
Another way to create views is with T-SQL. Open a SQL window and enter the SQL below and execute it. Then, look at the "Views" in the database and see what you find.
CREATE VIEW <name> AS SELECT * FROM cities2sp WHERE acres>1000;
You can add spatial SQL commands into the View like the one below. Now we have a way to filter our data spatially within the database.
CREATE VIEW <name> AS SELECT ST_AsText(geom) FROM cities2sp WHERE ST_Within(geom,POLYGON((0 0,1300000 0,1300000 500000,0 500000,0 0))
© Copyright 2018 HSU - All rights reserved.