SQL Server – Select Within Polygon

Marketing
Marketing
  • Updated

by David Crowther

SQL_Server_DC_01.png

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.

SQL_Server_DC_02.jpg

This analysis can also be done within SQL Server using the following SQL Script:

SQL_Server_DC_03.jpg

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.

SQL_Server_DC_04.jpg

 

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.