While many ‘Spatial Manager’ users operate with spatial databases, such as PostGIS or SQL Server Spatial, very few know that database Views are processed in this application in the same way as database Tables. In the sample shown in this post you can see how to use a spatial query to create a database View
Filtered PostGIS or SQL Server Spatial View in ‘Spatial Manager’
To illustrate the example (PostGIS and SQL Server Spatial), let’s assume that we want to consult the “Census” Table that has the “geom” Column (Field) defined in the “City” schema and obtain only the geometries that coincide with a rectangle whose envelope coordinates are:
- xmin: 1884515
- ymin: 451900
- xMax: 1897526
- yMax: 458931
PostGIS
The SQL statement to query and create the View would be:
SELECT *
FROM “City”.”Census”
WHERE ST_Intersects(“geom” , ST_GeometryFromText(‘SRID=26741;POLYGON((1884515 451900, 1884515 458931, 1897526 458931,1897526 451900,1884515 451900))’)::geometry)
- ST_Intersects is the function that compares each geometry of the Table. There are other functions for other types of queries: ST_Contains, ST_Crosses, ST_Intersects, ST_Touches, …
-
ST_GeometryFromText is the function to compose a polygon (or any other type of geometry) from its text representation in WKT format
- Note: For non-projected geometries that are defined in latitude-longitude, you should use ST_GeographyFromText
- SRID=26741 is the code of the coordinate reference system (CRS). It must be the same as the geometries to be queried in order to work correctly. If it is not defined, this parameter can be omitted
- POLYGON((1884515 451900, 1884515 458931, 1897526 458931,1897526 451900,1884515 451900)) WKT representation of the geometry to be used for the spatial query
The created View will appear in the USD area of the ‘Spatial Manager’ data sources panel as any database Table so you can use it as any source to import or load the filtered features:
Importing / loading results – Green for the full table “Census”, Red for the created “CensusFiltered” View:
Other more advanced types of queries can be made. In the below example, a Column “Area” is added and the features whose area is greater than 20000 and whose “BGROUP” value is equal to 2, are also filtered:
SELECT *, ST_AREA(“geom”) AS “Area”
FROM “City”.”Census”
WHERE ST_Intersects(“geom” , ST_GeometryFromText(‘SRID=26741;POLYGON((1884515 451900, 1884515 458931, 1897526 458931,1897526 451900,1884515 451900))’)::geometry)
AND ST_AREA(“geom”)>200000
AND “BGROUP”=’2′
More information about this type of queries: Using PostGIS - Data management
SQL Server Spatial
On this case, the SQL statement to query and create the View would be:
SELECT *
FROM [Census]
WHERE [Geometry].STIntersects(geometry::STGeomFromText(‘POLYGON((1884515 451900, 1884515 458931, 1897526 458931,1897526 451900,1884515 451900))’,26741))=1
- STIntersects is the function that compares each geometry of the Table (it returns 1 in case of equality). There are other functions for other types of queries: STContains, STCrosses, STIntersects, STTouches, …
-
geometry::STGeomFromText is the function to compose a polygon (or any other type of geometry) from its text representation in WKT format
- Note: For non-projected geometries that are defined in latitude-longitude, you should use geography::STGeomFromText
- POLYGON((1884515 451900, 1884515 458931, 1897526 458931,1897526 451900,1884515 451900)) WKT representation of the geometry to be used for the spatial query
- 26741 is the code of the coordinate reference system (CRS). It must be the same as the geometries to be queried in order to work correctly. If it is not defined, this parameter must be set to “0”
As in PostGIS case, the created View will appear in the USD area of the ‘Spatial Manager’ data sources panel as any database Table so you can use it as any source to import or load the filtered features. Importing / loading results – Green for the full table “Census”, Red for the created “CensusFiltered” View. Please, take a look at the above images
More information about this type of queries:
Learn more about the use and configuration of spatial databases in ‘Spatial Manager’:
