Created
August 27, 2011 18:07
-
-
Save jmelesky/1175674 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Make changes to the table that we created in the ESRI model builder script | |
-- Eden contains null values set metro empty values to null so they will match during union | |
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
SET RNO = NULL | |
WHERE RNO = '' | |
-- Eden contains null values set metro empty values to null so they will match during union | |
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
SET OWNER1 = NULL | |
WHERE OWNER1 = '' | |
-- Eden contains null values set metro empty values to null so they will match during union | |
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
SET OWNER2 = NULL | |
WHERE OWNER2 = '' | |
-- Eden contains null values..set metro empty values to null so they will match during union | |
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
SET OWNER3 = NULL | |
WHERE OWNER3 = '' | |
--UPDATE THE SITEADDR SO THAT IT HAS A FAKE STREET NUMBER AND NAME SO EDEN WILL ACCEPT IT INTO THE DATABASE | |
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
SET SITEADDR='123 NO SITUS' | |
WHERE SITEADDR='NO SITUS' OR SITEADDR='' | |
--UPDATE THE OWNER ADDRESS SO THAT IT HAS A FAKE STREET NUMBER AND NAME SO EDEN WILL ACCEPT IT INTO THE DATABASE | |
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
SET OWNERADDR='123 NO MAILING', OWNERCITY = 'WILSONVILLE', OWNERSTATE = 'OR', OWNERZIP = '97219' | |
where OWNERADDR = '' or OWNERADDR = 'NO MAILING ADDRESS' | |
--Create Temp Metro table to update the tlid to match eden apn | |
-- Check if temp table exists, and if it does drop it | |
IF OBJECT_ID('tempdb..#GG_Temp_Parcel','local') IS NOT NULL | |
BEGIN | |
DROP TABLE #GG_Temp_Parcel | |
Print 'table #GG_Temp_Parcel deleted' | |
END | |
-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to | |
-- by pass the openquery syntax | |
CREATE TABLE #GG_Temp_Parcel( | |
TLID NVARCHAR(16), | |
COUNTY NVARCHAR(1), | |
EDEN_APN NVARCHAR(29), | |
FirstSix nvarchar(6), | |
NextTwo nvarchar(2), | |
LastFive nvarchar(5) ) | |
INSERT INTO #GG_Temp_Parcel(TLID, COUNTY) | |
SELECT TLID, COUNTY | |
FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
--TRIM TRAILING SPACES DUE TO EDEN USING CHAR FIELDS | |
UPDATE #GG_Temp_Parcel | |
SET TLID = RTRIM(TLID) | |
-- START PROCESS TO TRANSITION TLID TO EDEN APN | |
UPDATE #GG_Temp_Parcel | |
SET EDEN_APN = TLID | |
-- | |
Update #GG_Temp_Parcel | |
Set EDEN_APN = replace(EDEN_APN, '31W', '3S1W') | |
WHERE COUNTY = 'C'; | |
Update #GG_Temp_Parcel | |
Set EDEN_APN = replace(EDEN_APN, '31E', '3S1E'); | |
Update #GG_Temp_Parcel | |
Set EDEN_APN = replace(EDEN_APN, '2S1', '2S1W'); | |
Update #GG_Temp_Parcel | |
Set EDEN_APN = replace(EDEN_APN, '1S1', '1S1W'); | |
Update #GG_Temp_Parcel | |
Set EDEN_APN = replace(EDEN_APN, '3S1', '3S1W') | |
WHERE COUNTY = 'W'; | |
Update #GG_Temp_Parcel | |
Set EDEN_APN = replace(EDEN_APN, ' ', '__'); | |
Update #GG_Temp_Parcel | |
Set EDEN_APN = replace(EDEN_APN, ' ', '_'); | |
Update #GG_Temp_Parcel | |
Set FirstSix = SUBSTRING(EDEN_APN, 1, 6) | |
Update #GG_Temp_Parcel | |
Set NextTwo = SUBSTRING(EDEN_APN, 7, 2) | |
Update #GG_Temp_Parcel | |
Set LastFive = SUBSTRING(EDEN_APN, 9, 5) | |
Update #GG_Temp_Parcel | |
Set NEXTTWO = replace(NEXTTWO, '0', '_') | |
WHERE NEXTTWO LIKE '%0%'; | |
Update #GG_Temp_Parcel | |
Set EDEN_APN = (FirstSix+NextTwo+LastFive) | |
--UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.EDEN_APN TO CONTAIN THE UPDATED EDEN STYLE PARCEL NUMBER | |
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
SET sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.EDEN_APN= #GG_Temp_Parcel.EDEN_APN | |
FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO, #GG_Temp_Parcel | |
WHERE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.TLID = #GG_Temp_Parcel.TLID | |
-------- | |
--Exlude from eden updates data that would not be clean | |
-- Check if temp table exists, and if it does drop it | |
IF OBJECT_ID('tempdb..#GG_Temp_Eden_Exclude','local') IS NOT NULL | |
BEGIN | |
DROP TABLE #GG_Temp_Eden_Exclude | |
Print 'table #GG_Temp_Eden_Exclude deleted' | |
END | |
-- Exlude from eden updates data that would not be clean | |
-- | |
CREATE TABLE #GG_Temp_Eden_Exclude( | |
APN NVARCHAR(29)) | |
--REPORT: LIST ITEMS IN EDEN FOR CLEANUP | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '-----------------------THIS UPDATE FROM METRO AS THE FOLLOWING ISSUES ----------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT 'SPLIT POLYGONS: HAVING DUPLICATE EDEN_APN(APN) AND RNO(TAX_ID)' | |
SELECT COUNT(*), EDEN_APN, RNO FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
GROUP BY EDEN_APN, RNO | |
HAVING COUNT(*) > 1 | |
--PUT RESULTS INTO EXCLUDE FILE | |
--Insert into #GG_Temp_Eden_Exclude(APN) | |
--SELECT EDEN_APN AS APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
--GROUP BY EDEN_APN, RNO | |
--HAVING COUNT(*) > 1 | |
-- END EXCLUDE ADD | |
PRINT 'METRO TAXLOTS HAVING A BLANK RNO(TAX_ID)' | |
SELECT TLID, RNO, EDEN_APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
WHERE RNO = '' OR RNO IS NULL | |
PRINT 'METRO TAXLOTS HAVING A BLANK OWNER1' | |
SELECT TLID, RNO, OWNER1, OWNER2, EDEN_APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
WHERE OWNER1 = '' OR OWNER1 IS NULL | |
--PUT RESULTS INTO EXCLUDE FILE | |
--Insert into #GG_Temp_Eden_Exclude(APN) | |
--SELECT EDEN_APN AS APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO | |
--WHERE RNO = '' OR RNO IS NULL OR OWNER1 = '' OR OWNER1 IS NULL | |
--GROUP BY EDEN_APN | |
-- END EXCLUDE ADD | |
PRINT '--------------------------------------------------------------------------------------------' | |
-------- | |
-- Check if temp table exists, and if it does drop it | |
IF OBJECT_ID('tempdb..#GG_Temp_Eden','local') IS NOT NULL | |
BEGIN | |
DROP TABLE #GG_Temp_Eden | |
Print 'table ggtempeden deleted' | |
END | |
-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to | |
-- by pass the openquery syntax | |
CREATE TABLE #GG_Temp_Eden( | |
APN NVARCHAR(29), | |
RNO NVARCHAR(10), | |
OWNER1 NVARCHAR(30), | |
OWNER2 NVARCHAR(20), | |
JOIN_ID INT, | |
OWN_ID INT, | |
SDATE DATETIME ) | |
-- Insert our data into temp table from EDEN | |
INSERT INTO #GG_Temp_Eden | |
SELECT P.APN AS APN, P.TAX_ID AS RNO, O.LNAME AS OWNER1, O.FNAME AS OWNER2, OJ.JOIN_ID AS JOIN_ID, OJ.OWN_ID AS OWN_ID, OJ.START_DATE AS SDATE | |
FROM EDENSQL.ParcelTest.dbo.ESLPARCR P | |
LEFT OUTER JOIN EDENSQL.ParcelTest.dbo.ESLOWNRJ OJ | |
ON P.PARC_ID = OJ.JOIN_ID | |
LEFT OUTER JOIN EDENSQL.ParcelTest.dbo.ESLOWNRR O | |
ON OJ.OWN_ID = O.OWN_ID | |
WHERE P.RETIRED_DATE IS NULL AND OJ.REL_TYPE = 'P' | |
--AND P.TAX_ID IS NOT NULL AND P.TAX_ID <> '' | |
--REMOVE TRAILING SPACES FROM EDEN TABLE | |
UPDATE #GG_Temp_Eden | |
SET RNO = RTRIM(RNO), | |
APN = RTRIM(APN), | |
OWNER1 = RTRIM(OWNER1), | |
OWNER2 = RTRIM(OWNER2) | |
--REPORT: LIST ITEMS IN EDEN FOR CLEANUP | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------- EDEN DATA ISSUES -----------------------------------------' | |
PRINT '-----------------------THIS UPDATE FROM METRO AS THE FOLLOWING ISSUES ----------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT 'DUPLICATE APN FROM EDEN: Constraints- These are flaged as NOT RETIRED(RETIRED_DATE IS NULL)' | |
SELECT * FROM #GG_Temp_Eden | |
WHERE APN IN ( | |
SELECT APN | |
FROM #GG_Temp_Eden | |
GROUP BY APN | |
HAVING (COUNT(APN ) > 1)) | |
ORDER BY APN, RNO | |
-- Insert APN's that we will exclude from the final table creation for the EDEN Update | |
--Insert into #GG_Temp_Eden_Exclude(APN) | |
--SELECT DISTINCT APN FROM #GG_Temp_Eden | |
--WHERE APN IN ( | |
-- SELECT APN | |
-- FROM #GG_Temp_Eden | |
-- GROUP BY APN | |
-- HAVING (COUNT(APN ) > 1)) | |
-- | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT 'NULL OR BLANK TAX_ID FROM EDEN: Constraints- These are flaged as NOT RETIRED(RETIRED_DATE IS NULL)' | |
SELECT APN, RNO AS TAX_ID, OWNER1 AS LNAME, OWNER2 AS FNAME, JOIN_ID, OWN_ID FROM #GG_Temp_Eden | |
WHERE RNO IS NULL OR RNO = '' | |
-- Insert APN's that we will exclude from the final table creation for the EDEN Update | |
--Insert into #GG_Temp_Eden_Exclude(APN) | |
--SELECT APN FROM #GG_Temp_Eden | |
--WHERE RNO IS NULL OR RNO = '' | |
-- | |
PRINT '--------------------------------------------------------------------------------------------' | |
--END REPORT: | |
-- Check if temp table exists, and if it does drop it | |
IF OBJECT_ID('tempdb..#GG_Temp_Eden_ParcelRename','local') IS NOT NULL | |
BEGIN | |
DROP TABLE #GG_Temp_Eden_ParcelRename | |
Print 'table GG_Temp_Eden_ParcelRename deleted' | |
END | |
-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to | |
-- by pass the openquery syntax | |
CREATE TABLE #GG_Temp_Eden_ParcelRename( | |
APN NVARCHAR(29), | |
RNOADDRESS NVARCHAR(17) | |
) | |
INSERT INTO #GG_Temp_Eden_ParcelRename(APN, RNOADDRESS) | |
SELECT M.EDEN_APN AS APN, M.RNO + LEFT(M.OWNER1, 3) AS RNOADDRESS FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] as M | |
WHERE Not EXISTS | |
(SELECT * | |
FROM EDENSQL.ParcelTest.dbo.ESLPARCR AS E | |
WHERE M.EDEN_APN = E.APN) | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------- EDEN DATA ISSUES -----------------------------------------' | |
PRINT '-----------------PARCELS IN METRO WITH NEW APN CHECK FOR RENAMES IN EDEN--------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
SELECT PR.APN AS NEW_APN, TE.APN AS OLD_APN, TE.RNO, TE.OWNER1, TE.OWNER2, TE.JOIN_ID AS PARCEL_ID FROM #GG_Temp_Eden_ParcelRename AS PR,#GG_Temp_Eden AS TE | |
WHERE PR.RNOADDRESS = TE.RNO + LEFT(TE.OWNER1, 3) | |
-- Insert APN's that we will exclude from the final table creation for the EDEN Update | |
--Insert into #GG_Temp_Eden_Exclude(APN) | |
--SELECT PR.APN FROM #GG_Temp_Eden_ParcelRename AS PR,#GG_Temp_Eden AS TE | |
--WHERE PR.RNOADDRESS = TE.RNO + LEFT(TE.OWNER1, 3) | |
-- Check if temp table exists, and if it does drop it | |
IF OBJECT_ID('sde_vector.dbo.GG_EDEN_Update_ALL') IS NOT NULL | |
BEGIN | |
DROP TABLE sde_vector.dbo.GG_EDEN_Update_ALL | |
Print 'Table sde_vector.dbo.GG_EDEN_Update_ALL deleted' | |
END | |
CREATE | |
TABLE sde_vector.dbo.GG_EDEN_Update_ALL( | |
APN NVARCHAR(29), | |
RNO NVARCHAR(10), | |
OWNER1 NVARCHAR(30), | |
OWNER2 NVARCHAR(20), | |
OWNERADDR NVARCHAR(35), | |
OWNER_ADDR NVARCHAR(35), | |
OWNER_APT_SUITE NVARCHAR(35), | |
OWNERCITY NVARCHAR(30), | |
OWNERSTATE NVARCHAR(2), | |
OWNERZIP NVARCHAR(10), | |
SITEADDR NVARCHAR(35), | |
SITECITY NVARCHAR(15), | |
SITESTATE NVARCHAR(2), | |
SITEZIP NVARCHAR(30)) | |
INSERT INTO sde_vector.dbo.GG_EDEN_Update_ALL(APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITESTATE, SITEZIP) | |
select EDEN_APN AS APN, RNO, LEFT(OWNER1, 30), LEFT(OWNER2, 20), OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, 'OR' as SITESTATE, SITEZIP | |
FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] AS M | |
GROUP BY EDEN_APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITEZIP | |
UPDATE sde_vector.dbo.GG_EDEN_Update_ALL | |
SET RNO = RTRIM(RNO), | |
APN = RTRIM(APN), | |
OWNER1 = RTRIM(OWNER1), | |
OWNER2 = RTRIM(OWNER2), | |
OWNERADDR = RTRIM(OWNERADDR), | |
OWNERCITY = RTRIM(OWNERCITY), | |
OWNERSTATE = RTRIM(OWNERSTATE), | |
OWNERZIP = RTRIM(OWNERZIP), | |
SITEADDR = RTRIM(SITEADDR), | |
SITECITY = RTRIM(SITECITY), | |
SITEZIP = RTRIM(SITEZIP) | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------- EDEN DATA REFRESH ----------------------------------------' | |
PRINT '-----------------PARCELS IN METRO THAT ARE ON THE EDEN EXCLUDE LIST-------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
PRINT '--------------------------------------------------------------------------------------------' | |
select EDEN_APN AS APN, RNO, LEFT(OWNER1, 30), LEFT(OWNER2, 20), OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, 'OR' as SITESTATE, SITEZIP | |
FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] AS M, #GG_Temp_Eden_Exclude AS E | |
WHERE EXISTS | |
(SELECT DISTINCT E.APN | |
FROM #GG_Temp_Eden_Exclude AS E | |
WHERE M.EDEN_APN = E.APN) | |
GROUP BY EDEN_APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITEZIP | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment