Question

Kelly Nicole O'Donnell on Fri, 18 Dec 2015 04:48:23


Hi,

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?

Thanks

Replies

Bob Beauchemin on Sat, 19 Dec 2015 03:42:58


An ordinary SQL update statement with a spatial predicate as join predicate would do:

UPDATE Melbprojects
SET postcode = p.POA_NAME_2011
FROM Melbprojects m
JOIN POA_2011_Aust p
ON m.Point.STIntersects(p.Poly)=1

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 ).
System.FormatException: 
   at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeDouble()
   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)