• Blog posts

    Bio

    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