SQL / SQL Spatial Examples from presentation by InfoGeographics

gistfile1.sql

-- ----------------------------------------------------------------------------
-- 1.  SQL 101
-- ----------------------------------------------------------------------------
-- 1A. SELECT RECORDS 
-- ----------------------------------------------------------------------------

SELECT 
  *
FROM 
  PARCEL_TEST.DBO.PARCEL_EQ p

-- ----------------------------------------------------------------------------
-- 1B. FILTER E.Q. ALL PARCELS WITHIN A TOWNSHIP OR SECTION
-- 
--     SHOW ATTRIBUTES AND SPATIAL RESULTS
-- ----------------------------------------------------------------------------

SELECT 
  *
FROM 
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.PIN LIKE '61-09%'

-- ----------------------------------------------------------------------------
-- 1C. FILTER E.Q. SOLD THIS YEAR
-- ----------------------------------------------------------------------------

SELECT 
  p.PIN, 
  p.LAST_SALE_PRICE, 
  p.LAST_SALE_DATE
FROM 
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.LAST_SALE_DATE >= '01-JAN-2014';

-- ----------------------------------------------------------------------------
-- 1D. AGGREGATION E.Q. TOTAL COUNTY SALES WITHIN 2014 
-- ----------------------------------------------------------------------------

SELECT 
  COUNT (*),
  SUM(p.LAST_SALE_PRICE)
FROM 
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.LAST_SALE_DATE >= '01-JAN-2014';

-- ----------------------------------------------------------------------------
-- 2.  SPATIAL SQL
-- ----------------------------------------------------------------------------
-- 2A. SPATIAL FILTER - create a 3 mile buffer
--    
--     SHOW SPATIAL RESULTS
-- ----------------------------------------------------------------------------

SELECT 
  p.SHAPE.STBuffer(3 * 5280)
FROM
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.PIN = '61-24-205-223-0001-00'

-- ----------------------------------------------------------------------------
-- 2B. HOW MANY PARCELS WITHIN THE BUFFER?
-- ----------------------------------------------------------------------------

DECLARE @PropertyBuffer GEOMETRY;

SELECT @PropertyBuffer = 
  p.SHAPE.STBuffer(3 * 5280)
FROM
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.PIN = '61-24-205-223-0001-00';

SELECT 
  count (*)
FROM 
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.SHAPE.STIntersects(@PropertyBuffer) = 1;

-- ----------------------------------------------------------------------------
-- HOW MANY PARCELS WITHIN THE BUFFER SOLD IN 2014?
-- ----------------------------------------------------------------------------

DECLARE @PropertyBuffer GEOMETRY;

SELECT @PropertyBuffer = 
  p.SHAPE.STBuffer(3 * 5280)
FROM
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.PIN = '61-24-205-223-0001-00';

SELECT 
  COUNT (*)
FROM 
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.SHAPE.STIntersects(@PropertyBuffer) = 1
AND
  p.LAST_SALE_DATE >= '01-JAN-2014';

-- ----------------------------------------------------------------------------
-- WHAT'S THEIR TOTAL VALUE
-- ----------------------------------------------------------------------------

DECLARE @PropertyBuffer GEOMETRY;

SELECT @PropertyBuffer = 
  p.SHAPE.STBuffer(3 * 5280)
FROM
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.PIN = '61-24-205-223-0001-00';

SELECT 
  COUNT (*),
  SUM(p.LAST_SALE_PRICE)
FROM 
  PARCEL_TEST.DBO.PARCEL_EQ p
WHERE
  p.SHAPE.STIntersects(@PropertyBuffer) = 1
AND
  p.LAST_SALE_DATE >= '01-JAN-2014';