T-sql spatial query to update a column in a point table where the point intersects polgyon from another table
Category: sql server spatial
Kelly Nicole O'Donnell on Fri, 18 Dec 2015 04:48:23
I have a two spatial tables in a database called residential - the tables are below
- Melbprojects - a point table which contains residential sites and which has a postcode field that I want to update from a spatial join from the other table POA_2011_Aust
- POA_2011_Aust – is polygon table which contains postcode boundaries, with a field called POA_NAME_2011 which contains the postcode of an area.
I would like to do a spatial join where the Melbprojects point sits within the postcode boundary, then I would like to update Melbprojects.postode.
Hopefully someone can help me with this?
Bob Beauchemin on Sat, 19 Dec 2015 03:42:58
An ordinary SQL update statement with a spatial predicate as join predicate would do:
SET postcode = p.POA_NAME_2011
FROM Melbprojects m
JOIN POA_2011_Aust p
where "Point" and "Poly" should be replaced with the names of your columns that contain your points and polygons, respectively.
If your Melbprojects table has a lot of rows, you'll likely improve performance by adding a spatial index on the Point column and/or doing the update in batches or multiple steps.
Hope this helps, Bob
Kelly Nicole O'Donnell on Sun, 20 Dec 2015 22:45:51
Many thanks Bob. Worked perfectly!
ogrget.amit on Sat, 29 Dec 2018 07:09:13
I am getting this error.
Msg 6522, Level 16, State 1, Line 53
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24141: A number is expected at position 14 of the input. The input has ).
at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses)
at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)