by David Crowther
Question:
How can you select All Points within a Polygon using SQL?
Answer:
This is a common task in any desktop GIS e.g. QGIS and indeed using Cadlines WebGIS – MapThat – this is also possible using the Area Search > Use Existing Feature.
As per the below, we are able to easily select the School Points (32) that fall within one Polygon – in this case Everton Ward Polygon.
This analysis can also be done within SQL Server using the following SQL Script:
DECLARE @geom1 geometry;
SELECT @geom1 = ogr_geometry FROM wards WHERE ward_id = 1;
select tab.* from (Select * from edubase where Easting is not null and Northing is not null) tab
where geometry::Point(Easting, Northing, 27700).STIntersects(@geom1) = 1
Line 1 - is declaring a variable for geometry to be used later in the query
Line 2 - is selecting a polygon from my WARDS (wards) table. It selects the Ward Polygon Everton - as this has the ward_id = 1
Line 3 - is using my Point table of Schools (edubase) to select from but only where there are no records with blank geometry.
Line 4 - then it runs the STIntersects query to find only the School Points that are within the Ward (Polygon) Everton
The results of the query will be a Selection from the Schools table (edubase) where we can see there are 32 School records that geometrically fall within Everton Ward.