Using spatial SQL

 

Don’t be intimidated by the term spatialSQL.  It is one of the most powerful tools in GIS, is easy to use, and is way more flexible than any wizard you can develop.  But, it will require you to write a couple of lines of SQL code.  This document will help you do that.

 

SQL stand for structured query language, which is the most common database language.  The spatial part means we can add spatial operators to a standard database language.  Examples of SQL and spatialSQL that you can use are the following:

 

Select all parcels with a property class of single family residential

 

SELECT * FROM PARCELS WHERE ZONING LIKE "R1"

 

Select all parcels that are either conservancy district or recreational district

 

SELECT * FROM PARCELS WHERE ZONING LIKE "CON" OR ZONING LIKE "REC"

 

Select all parcels that are single family residential or multi-family residential and have a value over $300,000

 

SELECT * FROM PARCELS WHERE ZONING LIKE "R1" AND FAIR_MKT_V > 300000

 

Select all parcels that are within 100 feet of water

 

SELECT PARCELS.* from parcels,WATER where distance(parcels.id,WATER.id) < 100

 

Select the owner name and address for  parcels zoned ag and within 100ft of water

 

SELECT Parcels.ID, last_NAME, first_name,    address_1,    CITY_STATE,    zipcode FROM PARCELS, WATER WHERE distance (parcels.id,water.id) < 100 and zoning like "ag"

 

Find total acres for each Zoning District

 

SELECT ZONING, SUM(ACRES) FROM PARCELS WHERE [ZONING] LIKE "%" GROUP BY ZONING

 

Fields in PARCELS to be used in spatial SQLs

 

aREA, PERIMETER, PINGIS, ZONING, ACRES, LAST_NAME, FIRST_NAME, ADDRESS_1, ADDRESS_2, CITY_STATE, ZIPCODE, LOCATED_AT, MUNI_NUM, COMP_NUM, SECTION, ASSESSED_V, FAIR_MKT_V, TOTAL_TAX, Land_value, impr_ment_, schoolcode