Creating SQL Views

Feature classes can be based on SQL views as well as tables, and these views can be created from the Feature Class Admin > New… form using the “SQL…” button. In the form (shown below), type/paste a SQL statement into the large box, and specify the name of the new view (selecting the schema to contain the view if required). The example shown selects a subset of records from the table “ProtSites_polygon”, outputting all attributes plus a column called “geombuff” which is the original geometry buffered by 200 metres.

SQL script form

The “Verify” button checks the entered SQL against the database and confirms whether or not it is valid. Once valid SQL has been entered and a view name specified, click “OK” to save the view and return to the New Feature Class form. If possible, the “Id column” and “Geometry column” will be automatically detected, but these should be checked and changed if necessary. Valid identity and geometry fields must be included in the view for it to be used as a valid GISquirrel feature class.

See also the guidance on editing feature classes based on views.

To edit the SQL of an existing view, open the Feature Class Admin > Edit… form, then click the SQL… button. The form shown above will appear, containing the existing SQL statement for the view. The SQL can be edited, and the Verify button used to check its validity. Click OK to save the changes to the view. Depending on what has changed, you may need to alter other properties on the Edit Feature Class form. In the example above, there are two geometry columns, “geom” contains the original features, and “geombuff” contains these buffered by 200 metres; the feature class could use either, but in this case we want to use “geombuff”.

This interface has been provided for expert users, in order to create feature classes based on SQL statements without leaving ArcMap. Editing SQL in this way requires significant skills and knowledge of the underlying data structures. It may be easier to create and edit views using SQL Server or pgAdmin administrative tools.

Further Reading

Details of SQL Server spatial functions can be found at http://msdn.microsoft.com/en-us/library/bb933973.aspx (or by searching for “geometry data type method reference”.

Details of PostGIS spatial functions can be found at http://postgis.net/docs/manual-1.3/ch06.html

Modified 2/20/2013
Site Map | Printable View | © 2008 - 2024 Idox Software Ltd