[GIS] How to perform SQL query using .STContains in MS SQL Server

geometrysqlsql server

I have a table that stores polygons and a table that stores points with a value field. Both sets of data are stored using the Geometry type in MS SQL Server.

I have a simple requirement – I need to retrieve the records of points that are contained within the polygons and then sum the result based on a value field in the points table. So, the end result of the query is two columns:
– polygon names
– sum of the value field in the point table

DECLARE @area GEOMETRY  = (SELECT Shape FROM GREENFIELDAREAS WHERE AREANAME = 'Aerodrome')
SELECT NETDWELLINGS, Shape FROM ORA_NETDWELLINGCOMPLETIONS WHERE @area.STIntersects(Shape) = 1

The above query returns point geometries but only for 1 polygon (Aerodrome). What I need is to sum NETDWELLINGS and return that value for all the polygons.

Best Answer

SELECT g.id , SUM(o.netdwellings) as sum from g , o  WHERE g.geom.STIntersects(o.geom) = 1 group by g.id

That would return sum of all o.geoms that intersect with g.geom. (did i understood question wrong ?)

Related Solutions

[GIS] select by location point in polygon with sql query

SQL Server – Selecting Polygons Nested Within Larger Polygon

In theory the queries that you have done should return the polygons you said haven't been returned. That makes me suspect that you might be encountering floating point error issues that SQL Server has with it spatial data types. Hence my comment about buffering the bounding polygon with a minimal amount.
So something like the following should get the results you want.

SELECT a.bg10 
FROM gis.usa_10_block_group a
    JOIN gis.usa_10_mkt_definition b
        ON a.shape.STWithin(b.shape.STBuffer(0.0001)) = 1
WHERE b.mktname = 'Loop'

Here's a quick example of the expected behaviour of a few of the spatial methods.

SELECT Geometry::STGeomFromText(WKT,0), Description
    , Geometry::STGeomFromText('POLYGON((0 0, 100 0, 100 100, 0 100, 0 0))',0).STIntersects(Geometry::STGeomFromText(WKT,0)) Intersects
    , Geometry::STGeomFromText('POLYGON((0 0, 100 0, 100 100, 0 100, 0 0))',0).STContains(Geometry::STGeomFromText(WKT,0)) Contained
    , Geometry::STGeomFromText('POLYGON((0 0, 100 0, 100 100, 0 100, 0 0))',0).STOverlaps(Geometry::STGeomFromText(WKT,0)) Overlaps
    , Geometry::STGeomFromText('POLYGON((0 0, 100 0, 100 100, 0 100, 0 0))',0).STTouches(Geometry::STGeomFromText(WKT,0)) Touches
FROM (VALUES
    ('POLYGON((0 0, 20 0, 20 20, 0 20, 0 0))'            ,'Interior corner')
    ,('POLYGON((90 90, 100 90, 100 100, 90 100, 90 90))' ,'Interior corner')
    ,('POLYGON((20 20, 40 20, 40 40, 20 40, 20 20))'     ,'Interior')
    ,('POLYGON((50 0, 70 0, 70 20, 50 20, 50 0))'        ,'Interior edge')
    ,('POLYGON((50 80, 70 80, 70 100, 50 100, 50 80))'   ,'Interior edge')
    ,('POLYGON((80 50, 100 50, 100 70, 80 70, 80 50))'   ,'Interior edge')
    ,('POLYGON((90 0, 110 0, 110 20, 90 20, 90 0))'      ,'Overlap')
    ,('POLYGON((100 50, 120 50, 120 70, 100 70, 100 50))','Exterior edge')
    )P(WKT,Description)
UNION ALL 
SELECT Geometry::STGeomFromText('POLYGON((0 0, 100 0, 100 100, 0 100, 0 0))',0),'Bounding Area',null,null,null,null

Results

Description     Intersects Contained Overlaps Touches
--------------- ---------- --------- -------- -------
Interior corner 1          1         0        0
Interior corner 1          1         0        0
Interior        1          1         0        0
Interior edge   1          1         0        0
Interior edge   1          1         0        0
Interior edge   1          1         0        0
Overlap         1          0         1        0
Exterior edge   1          0         0        1
Bounding Area   NULL       NULL      NULL     NULL

Best Answer

Related Solutions

[GIS] select by location point in polygon with sql query

SQL Server – Selecting Polygons Nested Within Larger Polygon

Related Question