Ali Shah
asked on
Help creating a spatial object in SQL Server
Hi Guys
Please see my attached SQL script CreateObjectsScript.sql. This contains two tables
I want to create polygons for all UK Outcodes (first part of the postcodes) however when i run the stored procedure
"FK2" it contains postcodes from the area "FK1" and other neighbouring areas too.
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 .
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
Please see my attached SQL script CreateObjectsScript.sql. This contains two tables
FullUKPostCodes
and
AreaShape
. I want to create polygons for all UK Outcodes (first part of the postcodes) however when i run the stored procedure
USP_CreateAreaForAllUKOutcodes
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;
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 .
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
Then using your suggestions create customised areas based on accurate zones.
Thanks a lot again
regards,
ASKER
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