Link to home
Start Free TrialLog in
Avatar of Ali Shah
Ali ShahFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Help creating a spatial object in SQL Server

Hi Guys
Please see my attached SQL script CreateObjectsScript.sql. This contains two tables
FullUKPostCodes

Open in new window

and
AreaShape

Open in new window

.
I want to create polygons for all UK Outcodes (first part of the postcodes) however when i run the stored procedure
USP_CreateAreaForAllUKOutcodes

Open in new window

it creates the polygon but when i run the query to see the postcodes for the area
"FK2" it contains postcodes from the area "FK1" and other neighbouring areas too.
 DECLARE @polygon GEOGRAPHY = (SELECT AreaShape FROM PhysicalSpace.AreaShape WHERE AreaName ='FK2')
 SELECT 
                       
                       Outcode, PostalCode
                FROM    dbo.FullUKPostCodes
                WHERE   @Polygon.STIntersects(Point) = 1
                ORDER BY PostalCode; 

Open in new window

To simplify i have restricted my sproc to create the polygon for one area only "FK2"
I have tried with LIneString, PolyGon and CircularString (this fails when no of points are even)
Attached is the data Containing all of the FK postcodes FKArea.xlsx
Here is the query result User generated image.
This is a rough FK1,2,3,4,5 area shape

Is it possible that i create the polygon / area with its own postcodes (lat/long) only? Please note that the data is provided from the Royal Mail which i suppose should be correct

regards,
FKAreas.PNG
SOLUTION
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ali Shah

ASKER

Hi Snarf,

Thanks a lot for your answer. There might be Zones posted by Royal Mail or someone for the UK Outcodes and even if we do get them it solves it partially as our business requirement is to create zones that can have multiple postcodes (from any neighbouring areas as described by Royal Mail) or an area consisting of multiple outcodes.
So for example if we get all of zones from FK postcodes and our requirement is to then create our own polygons for lets say one for FK1, FK2, FK3 and one for FK1, FK5, FK6 still i would have to use the SQL Server functions to create those. So for our personalised polygons (areas) the problem would still be same.

Do you recommend that i cache the postcodes when i create the areas and don't use spatial functions STIntersect etc when querying them back? At the moment there is no requirement to draw the shapes on the map as saved in SQL Server
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot Snarf, Now my next goal is to find out whether business is willing to find accurate zones from a third party if yes then find the provider who provides the zone.
Then using your suggestions create customised areas based on accurate zones.

Thanks a lot again

regards,