March 30, 2016 at 2:59 am
I'm trying to create a function to associate a postcode region with the Police Force by which it is covered. For most of the UK this is a simple task because each postcode region is covered by one Police Force. There's a slight anomaly in Hampshire because British Transport Police are based there but it was easy enough to handle that.
The major problem lies with London. Greater London is covered by one Criminal Justice Area (Metropolitan) but within that are two police forces. These are the Metropolitan (Met), which covers pretty much the whole over Greater London, and the City of London Police, who cover the square mile of the actual City of London only. Here the one-to-one relationship between police force and postcode breaks down. I can't get my function to return one row when I pass either a City of London or Greater London postcode. I know which postcode regions are covered by the City of London police and there's only about eight of them so I can hard code them easily enough. I have a feeling that I'm missing something really obvious but I'm pretty stumped.
CREATE TABLE #PostcodeRegion
(
PostcodeRegionCHAR(4)
,CriminalJusticeAreaIDCHAR(11)
)
CREATE TABLE #CriminalJusticeArea
(
CriminalJusticeAreaIDVARCHAR(11)
,CriminalJusticeAreaVARCHAR(25)
)
CREATE TABLE #Account
(
AccountIDVARCHAR(11)
,NameVARCHAR(50)
,EmailVARCHAR(50)
,CriminalJusticeAreaIDVARCHAR(11)
,AccountTypeCodeINT
,ParentAccountIDVARCHAR(11)
)
INSERT INTO #PostcodeRegion
VALUES
('BL0','UglyGUID01') --- A nice simple postcode that does what it should
,('EC1','UglyGUID02') --- A City Of London postcode
,('NW1','UglyGUID02') --- A Greater London postcode
,('SO15','UglyGUID03') --- A Hampshire postcode
INSERT INTO #CriminalJusticeArea
VALUES
('UglyGUID01','Greater Manchester')
,('UglyGUID02','Metropolitan')
,('UglyGUID03','Hampshire')
INSERT INTO #Account
VALUES
('UglyGUID05','Greater Manchester Police','ElloElloEllo@GMP.co.uk','UglyGUID01',200013,'UglyGUID04')
,('UglyGUID06','Metropolitan Police','LetsBeAvinYou@MetPolice.co.uk','UglyGUID02',200013,'UglyGUID04')
,('UglyGUID07','City Of London Police','PITA@CityofLondon.police.uk','UglyGUID02',200013,'UglyGUID04')
,('UglyGUID08','Hampshire Police','YoureComingWithMeSon@HampshirePolice.co.uk','UglyGUID03',200013,'UglyGUID04')
,('UglyGUID09','British Transport Police','NothingToSeeHere@BTP.police.uk','UglyGUID03',200013,'UglyGUID04')
DECLARE @PostcodeRegion CHAR(4) = 'SO15'
SELECT
a.Name
,a.Email
,cja.CriminalJusticeArea
FROM
#PostcodeRegionpr
LEFT JOIN #CriminalJusticeArea cjaON cja.CriminalJusticeAreaID= pr.CriminalJusticeAreaID
LEFT JOIN #AccountaON a.CriminalJusticeAreaID= cja.CriminalJusticeAreaID
ANDa.AccountTypeCode = 200013 -- The account type for Police
ANDa.ParentAccountIDIN ('UglyGUID04') --- Ensures only Police Forces are returned rather than stations/divisions etc.
AND a.AccountID<> 'UglyGUID09' ---Excludes British Transport Police. These cover the whole country but are based in one CJA
WHERE
pr.PostcodeRegion = @PostcodeRegion
GROUP BY
a.Name
,a.Email
,cja.CriminalJusticeArea
DROP TABLE
#Account
,#CriminalJusticeArea
,#PostcodeRegion
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 30, 2016 at 4:18 am
You would appear to be missing an area in which the City of London Police might operate.
Why not give the bizzies in the square mile their own area too - even if it doesn't really exist in terms of CriminalJusticeArea, it's more an "area of operations". Something like this:
drop TABLE #PostcodeRegion
CREATE TABLE #PostcodeRegion
(PostcodeRegion CHAR(4), CriminalJusticeAreaID CHAR(11))
INSERT INTO #PostcodeRegion
VALUES
('BL0','UglyGUID01') --- A nice simple postcode that does what it should
,('EC1','UglyGUID02') --- A Greater London postcode
,('EC1','UglyGUID04') --- A City Of London postcode
,('NW1','UglyGUID02') --- A Greater London postcode
,('SO15','UglyGUID03') --- A Hampshire postcode
drop TABLE #CriminalJusticeArea
CREATE TABLE #CriminalJusticeArea
(CriminalJusticeAreaIDVARCHAR(11), CriminalJusticeAreaVARCHAR(25))
INSERT INTO #CriminalJusticeArea
VALUES
('UglyGUID01','Greater Manchester')
,('UglyGUID02','Metropolitan')
,('UglyGUID03','Hampshire')
,('UglyGUID04','City of London')
DROP TABLE #Account
CREATE TABLE #Account
(AccountIDVARCHAR(11)
,NameVARCHAR(50)
,EmailVARCHAR(50)
,CriminalJusticeAreaIDVARCHAR(11)
,AccountTypeCodeINT
,ParentAccountIDVARCHAR(11))
INSERT INTO #Account
VALUES
('UglyGUID05','Greater Manchester Police','ElloElloEllo@GMP.co.uk','UglyGUID01',200013,'UglyGUID04')
,('UglyGUID06','Metropolitan Police','LetsBeAvinYou@MetPolice.co.uk','UglyGUID02',200013,'UglyGUID04')
,('UglyGUID07','City Of London Police','PITA@CityofLondon.police.uk','UglyGUID04',200013,'UglyGUID04')
,('UglyGUID08','Hampshire Police','YoureComingWithMeSon@HampshirePolice.co.uk','UglyGUID03',200013,'UglyGUID04')
,('UglyGUID09','British Transport Police','NothingToSeeHere@BTP.police.uk','UglyGUID03',200013,'UglyGUID04')
--------------------------------------------------------------
DECLARE @PostcodeRegion CHAR(4) = 'EC1'
SELECT pr.PostcodeRegion,
a.Name
,a.Email
,cja.CriminalJusticeArea
FROM #PostcodeRegion pr
LEFT JOIN #CriminalJusticeArea cja
ON cja.CriminalJusticeAreaID = pr.CriminalJusticeAreaID
LEFT JOIN #Account a
ON a.CriminalJusticeAreaID = cja.CriminalJusticeAreaID
ANDa.AccountTypeCode = 200013 -- The account type for Police
ANDa.ParentAccountIDIN ('UglyGUID04') --- Ensures only Police Forces are returned rather than stations/divisions etc.
AND a.AccountID<> 'UglyGUID09' ---Excludes British Transport Police. These cover the whole country but are based in one CJA
WHERE pr.PostcodeRegion = @PostcodeRegion
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 30, 2016 at 4:26 am
Am I missing something there Chris? That just looks like my original code re-formatted.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 30, 2016 at 4:32 am
BWFC (3/30/2016)
Am I missing something there Chris? That just looks like my original code re-formatted.
Small change #1 of 2:
,('EC1','UglyGUID02') --- A Greater London postcode
,('EC1','UglyGUID04') --- A City Of London postcode
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 30, 2016 at 5:16 am
Sorry Chris but I'm really not following you. I'm still getting two rows for the EC1 area.
However, I've got something that is doing what I need now. It's a bit of an over-simplification because although there are only four regions that are wholly within the City, there's half a street and a dustbin in WC1 that are technically in there and there's a few bits of WC2 and E1 also covered. It's going to be almost impossible to resolve that at this level but it might be fixable if when I repeat the process for individual 'clients'.
CREATE TABLE #PostcodeRegion
(
PostcodeRegionCHAR(4)
,CriminalJusticeAreaIDCHAR(11)
)
CREATE TABLE #CriminalJusticeArea
(
CriminalJusticeAreaIDVARCHAR(11)
,CriminalJusticeAreaVARCHAR(25)
)
CREATE TABLE #Account
(
AccountIDVARCHAR(11)
,NameVARCHAR(50)
,EmailVARCHAR(50)
,CriminalJusticeAreaIDVARCHAR(11)
,AccountTypeCodeINT
,ParentAccountIDVARCHAR(11)
)
INSERT INTO #PostcodeRegion
VALUES
('BL0','UglyGUID01') --- A nice simple postcode that does what it should
,('EC1','UglyGUID02') --- A City Of London postcode
,('NW1','UglyGUID02') --- A Greater London postcode
,('SO15','UglyGUID03') --- A Hampshire postcode
INSERT INTO #CriminalJusticeArea
VALUES
('UglyGUID01','Greater Manchester')
,('UglyGUID02','Metropolitan')
,('UglyGUID03','Hampshire')
INSERT INTO #Account
VALUES
('UglyGUID05','Greater Manchester Police','ElloElloEllo@GMP.co.uk','UglyGUID01',200013,'UglyGUID04')
,('UglyGUID06','Metropolitan Police','LetsBeAvinYou@MetPolice.co.uk','UglyGUID02',200013,'UglyGUID04')
,('UglyGUID07','City Of London Police','PITA@CityofLondon.police.uk','UglyGUID02',200013,'UglyGUID04')
,('UglyGUID08','Hampshire Police','YoureComingWithMeSon@HampshirePolice.co.uk','UglyGUID03',200013,'UglyGUID04')
,('UglyGUID09','British Transport Police','NothingToSeeHere@BTP.police.uk','UglyGUID03',200013,'UglyGUID04')
DECLARE @PostcodeRegion CHAR(4) = 'NW1'
SELECT
police.Name
,cja.CriminalJusticeArea
,police.Email
FROM
#PostcodeRegionpr
LEFT JOIN #CriminalJusticeArea cja
ON cja.CriminalJusticeAreaID= pr.CriminalJusticeAreaID
LEFT JOIN #Accounta
ON a.CriminalJusticeAreaID= cja.CriminalJusticeAreaID
ANDa.AccountTypeCode = 200013 -- The account type for Police
ANDa.ParentAccountIDIN ('UglyGUID04') --- Ensures only Police Forces are returned rather than stations/divisions etc.
AND a.AccountID<> 'UglyGUID09' ---Excludes British Transport Police. These cover the whole country but are based in one CJA
CROSS APPLY --- This does what I need
(
SELECT
Name = CASE WHEN CJA.CriminalJusticeArea = 'Metropolitan'
AND @PostcodeRegion <> 'EC1'---- Uses NOT IN on the real thing
THEN 'Metropolitan Police'
WHEN @PostcodeRegion = 'EC1' --- Uses IN on the real thing
THEN 'City Of London Police'
ELSE a.Name
END
,Email = CASE WHEN CJA.CriminalJusticeArea = 'Metropolitan'
AND @PostcodeRegion <> 'EC1'
THEN 'LetsBeAvinYou@MetPolice.co.uk'
WHEN @PostcodeRegion = 'EC1'
THEN 'PITA@CityofLondon.police.uk'
ELSE a.Name
END
)police
WHERE
pr.PostcodeRegion = @PostcodeRegion
GROUP BY
police.Name
,cja.CriminalJusticeArea
,police.Email
DROP TABLE
#Account
,#CriminalJusticeArea
,#PostcodeRegion
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 30, 2016 at 5:24 am
I must admit that I did not fully analyze your tables. I will also add that I know nothing about how the UK police are organized.
But I did take a short-ish look and I notice that you have one table translating a postal code to a GUID that presumably maps to a city (speculation based on seeing EC1 and NW1 both mapped to the same guid), and then another that maps the guid to a police force. That works if area of operation for a police department is defined by city, but at least in London that is no longer the case. What your query does is map both NW1 and EC1 to London, then ask what police dept is responsible for London.
It's not clear from your description if there is actually a perfect mapping of postal codes to police areas. If there is, then you can either redefine the meaning of the guids (as Chris tries), so that each guid maps to a police area instead of to a city.
However, if there are also postal codes that are divided between departments, then you are trying to solve the wrong problem. The first question you need to ask yourself is: if I give a postal code to a police officer, would they know exactly which department is responsible? For any possible postal code? Or are there also postal code when the answer would be "it depends on where in that area you are". In the latter case, there is no way you will ever get a correct answer from a database, because there is no correct answer to the question as given. In that case you need to redefine the question first.
March 30, 2016 at 6:30 am
I'll try to explain the best I can.
The basic set up is each postcode maps to part of a city or part of a county. Each city or county as a whole is covered by a police force and a criminal justice area (CJA). The CJA represents the organisations who process the offenders. The mapping for most of the country is one-to-one. Each county has one police force and one CJA. It's not a totally perfect mapping but the system is set up to handle it. In England and Wales, administrative boundaries have been changed in the past but ceremonial ones may remain. For example, my parents live in the Greater Manchester administrative area but write Lancashire on their address. Our system though knows they have a Greater Manchester postcode region so assigns them to that CJA and thus to Greater Manchester Police. I live three miles down the road. I write Lancashire and I'm actually in the Lancashire administrative area so I'm assigned, by postcode region, to Lancashire Police and Lancashire CJA.
The difficulty with London is that it's actually two entities. There's Greater London, which is the big ugly blob on the map that dominates the South-East of England and the City of London which is one square mile of extremely expensive land that lies wholly within Greater London. The City of London Police cover this square mile only while the Metropolitan Police (Met) cover the rest. Both of these police forces are looked after by the Metropolitan CJA. To all intents and purposes the mapping of postcode to Police force here is one-to-one.
What your query does is map both NW1 and EC1 to London, then ask what police dept is responsible for London.
NW1 is part of Greater London and EC1 is part of the City of London but both of them are part of the Metropolitan CJA. The query needed to establish which police force is responsible for which one. At face value it was easy enough because of the one-to-one mapping but because the only way to get from a postcode to Police Force was through CJA I was always returning two distinct rows. Changing the meaning of the GUIDs isn't possible because I'm reporting on data that doesn't actually belong to us. The process to make what would be perceived as a major change to handle what is, by and large, a rare situation. It's taken three years to get the Criminal Justice Areas added correctly and that was when it was our data.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 30, 2016 at 6:35 am
I only have a small insight to what you need to do but why can't you allocate the postcodes against the Account (police force) as this is where the actual relationship is?
March 30, 2016 at 6:49 am
Jon-183221 (3/30/2016)
I only have a small insight to what you need to do but why can't you allocate the postcodes against the Account (police force) as this is where the actual relationship is?
This has merits too. All police forces except City of London accurately map to a CJA, which has a list of postcodes which it covers, along with the area of jurisdiction of the force. City of London doesn't. It's a "sub-CJA" of the Met. I agree with Hugo, you're not going to solve this with code because the solution cannot be resolved from the data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 30, 2016 at 6:49 am
Jon-183221 (3/30/2016)
I only have a small insight to what you need to do but why can't you allocate the postcodes against the Account (police force) as this is where the actual relationship is?
I've simplified the data quite a lot to post it. Each police force covers many postcode regions in reality so there would have to be many rows added to allow for each individual postcode region covered.
Is that what you meant?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 30, 2016 at 7:44 am
ChrisM@Work (3/30/2016)
Jon-183221 (3/30/2016)
I only have a small insight to what you need to do but why can't you allocate the postcodes against the Account (police force) as this is where the actual relationship is?This has merits too. All police forces except City of London accurately map to a CJA, which has a list of postcodes which it covers, along with the area of jurisdiction of the force. City of London doesn't. It's a "sub-CJA" of the Met. I agree with Hugo, you're not going to solve this with code because the solution cannot be resolved from the data.
You're right, any solution will be fudgier than Devonshire gift shop but I've got something.
I know I've over-simplified the mapping a bit but it works. As I explained to Hugo, the system is set up in broad strokes based on the administrative areas. As things stand to us, WC1 is not part of the City of London at all. There may technically be small part of it that falls within the boundary but to us it's covered by the Met not City of London.
If I could directly assign postcodes to a police force it would be much easier but as I said above I would end up with other problems. Ironically enough, this has come about because of a request to add criminal justice areas to postcode regions and organisations where they were previously missing. The Law of Unintended Consequences has been well and truly invoked here.
Thanks for all the help every one.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 30, 2016 at 7:50 am
It's easy for me to sit from a far and make comments on a topic area I have very little knowledge on but this is what I was thinking.
The key part is originally the relationship was between PostcodeRegion and CriminalJusticeArea but this wont work for London and possibly others, so move the relationship to PostcodeRegion and Account as it would seem that each postcode is related to a single force. There are plenty of arguments if this is true in real terms but this is what I believe you were looking to achieve.
CREATE TABLE #PostcodeRegion
(
PostcodeRegionCHAR(4)
,CriminalJusticeAreaIDCHAR(11)
)
CREATE TABLE #CriminalJusticeArea
(
CriminalJusticeAreaIDVARCHAR(11)
,CriminalJusticeAreaVARCHAR(25)
)
CREATE TABLE #Account
(
AccountIDVARCHAR(11)
,NameVARCHAR(50)
,EmailVARCHAR(50)
,CriminalJusticeAreaIDVARCHAR(11)
,AccountTypeCodeINT
,ParentAccountIDVARCHAR(11)
)
INSERT INTO #PostcodeRegion
VALUES
('BL0','UglyGUID01') --- A nice simple postcode that does what it should
,('EC1','UglyGUID07') --- A City Of London postcode -Now pointing at the AccountID not the CJA
,('NW1','UglyGUID06') --- A Greater London postcode - Now pointing at the AccountID not the CJA
,('SO15','UglyGUID03') --- A Hampshire postcode
INSERT INTO #CriminalJusticeArea
VALUES
('UglyGUID01','Greater Manchester')
,('UglyGUID02','Metropolitan')
,('UglyGUID03','Hampshire')
INSERT INTO #Account
VALUES
('UglyGUID05','Greater Manchester Police','ElloElloEllo@GMP.co.uk','UglyGUID01',200013,'UglyGUID04')
,('UglyGUID06','Metropolitan Police','LetsBeAvinYou@MetPolice.co.uk','UglyGUID02',200013,'UglyGUID04')
,('UglyGUID07','City Of London Police','PITA@CityofLondon.police.uk','UglyGUID02',200013,'UglyGUID04')
,('UglyGUID08','Hampshire Police','YoureComingWithMeSon@HampshirePolice.co.uk','UglyGUID03',200013,'UglyGUID04')
,('UglyGUID09','British Transport Police','NothingToSeeHere@BTP.police.uk','UglyGUID03',200013,'UglyGUID04')
DECLARE @PostcodeRegion CHAR(4) = 'EC1'
SELECT
a.Name
,a.Email
,cja.CriminalJusticeArea
FROM
#PostcodeRegionpr
LEFT JOIN #Account aON a.AccountID= pr.CriminalJusticeAreaID
ANDa.AccountTypeCode = 200013 -- The account type for Police
ANDa.ParentAccountIDIN ('UglyGUID04') --- Ensures only Police Forces are returned rather than stations/divisions etc.
AND a.AccountID<> 'UglyGUID09'
LEFT JOIN #CriminalJusticeArea cjaON a.CriminalJusticeAreaID= cja.CriminalJusticeAreaID
WHERE
pr.PostcodeRegion = @PostcodeRegion
GROUP BY
a.Name
,a.Email
,cja.CriminalJusticeArea
March 30, 2016 at 7:52 am
Your problem is that postcodes are designed to direct mail so postcode districts do not always tie up with geographic areas of administration.
I suspect you are going to need all the full postcodes of The City to do this.
You could start by contacting the City of London Corporation or Royal Mail.
March 30, 2016 at 7:57 am
"If I could directly assign postcodes to a police force it would be much easier but as I said above I would end up with other problems."
Ok, scrap my idea as you have other constraints......isn't there always something to make our life difficult!
March 30, 2016 at 8:12 am
Jon-183221 (3/30/2016)
"If I could directly assign postcodes to a police force it would be much easier but as I said above I would end up with other problems."Ok, scrap my idea as you have other constraints......isn't there always something to make our life difficult!
The one thing I can't do easily is change the data structure. I can get things corrected and added to or removed from existing fields but if it's not already there I have to do without. I'm the reporting guy so the assumption from higher up is I can make do with what I've got. Why would I need things changed to make my life easier :crazy:
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply