0

I have a MSSQL table that houses a bunch of polygons. I want to select a few ( with a WHERE clause ) and subtract another bunch from these first ones.

Compare it with having all plot-outlines and building-footprints in a table.

How can i get the difference between these two sets? ( in the example, return all gardens (where a garden = plot - house)

Something along these lines: (this is obviously not working - syntax is wrong too)

SET plots = SELECT geom FROM tableA WHERE type= 'plot'
SET houses = SELECT geom FROM tableA WHERE type= 'house' 

SELECT plots.STDifference(houses) AS gardens FROM tableA

I can't change the data structure, so proposing to move plots and houses into a separate table is not helpful.

  • Do you need @ in front of your variable names? @plots – klewis Aug 8 '16 at 13:47
  • That is not even the correct syntax to create a variable i think. I just wanted to get my question across, don't pay attention to the syntax. – Max Aug 8 '16 at 14:00
2

If you are working with SQL Server 2012+ then you should be able to do this within a single query. Don't expect it to be very performent, but do make sure that you have all the appropriate indexes (especially spatial) in place.

I'm working on the assumption that you may have multiple hole making polygons that may or may not be entirely within the polygons to be cut.

The following example uses a cross apply to select and union aggregates all the polygons which intersect the target polygons (one at a time). This leaves us with 2 geometries that can be differenced.

The result should be a geometry for each plot polygon with any house polygon cut out of it.

-- Set up a test table
CREATE TABLE tableA (
    id int,
    geom geometry,
    type varchar(5)
    );

INSERT INTO tableA 
VALUES
 (1,Geometry::STGeomFromText('POLYGON(( 0 0, 10 0, 10 10, 0 10, 0 0 ))',0),'plot')
,(2,Geometry::STGeomFromText('POLYGON(( 10 0, 20 0, 20 10, 10 10, 10 0 ))',0),'plot')
,(3,Geometry::STGeomFromText('POLYGON(( 0 10, 10 10, 10 20, 0 20, 0 10 ))',0),'plot')
,(4,Geometry::STGeomFromText('POLYGON(( 2 2, 5 2, 5 5, 2 5, 2 2 ))',0),'house')
,(5,Geometry::STGeomFromText('POLYGON(( 12 2, 15 2, 15 5, 12 5, 12 2 ))',0),'house')
,(6,Geometry::STGeomFromText('POLYGON(( 17 2, 19 2, 19 5, 17 5, 17 2 ))',0),'house')
,(7,Geometry::STGeomFromText('POLYGON(( 2 12, 5 12, 5 15, 2 15, 2 12 ))',0),'house')
,(8,Geometry::STGeomFromText('POLYGON(( 5 9, 7 9, 7 11, 5 11, 5 9 ))',0),'house');

-- Return the plots with houses removed
SELECT p.id
    , p.type
    , p.geom.STDifference(x.exclude) garden -- plot with houses removed
FROM tableA p
    CROSS APPLY ( -- For each plot do the following query
        -- a union aggregate of houses that intersect the current plot
        SELECT Geometry::UnionAggregate(geom) exclude 
        FROM tableA b 
        WHERE type = 'house' 
            AND p.geom.STIntersects(b.geom) = 1
        ) x
WHERE p.type = 'plot'; -- consider plots only for difference

-- Cleanup
DROP TABLE tableA;
  • 2
    You deserve a statue for this! Exactly what i needed! – Max Aug 10 '16 at 13:08
2

UnionAggregate creates a MultiPolygon geography type, basically, it merges the adjacent or collapsed polygons and add the others to the multipolygon collection, so the result is a single geography type object.

declare @geog1 geography;
declare @geog2 geography;
select @geog1 = geography::UnionAggregate(geog_column) from table_name where clause
select @geog2 = geography::UnionAggregate(geog_column) from table_name where
select @geog1.STDifference(@geog2)
  • 1
    It would be useful to add a little bit of text describing the intended outcome here and how this achieves it. – jbchurchill Aug 8 '16 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.