Search code examples
sql-servergisgeospatialspatialgeometry-surface

Why my Geometry filter in SQL Server is not working?

I have uploaded the Local Government Area Boundary of the NSW, Australia into my MS-SQL database and try to filter for Sydney Area.

Following is the my Script:

CREATE TABLE [dbo].[nsw_lga_polygon_shp](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [geom] [geometry] NULL,
    [lg_ply_pid] [nvarchar](15) NULL,
    [dt_create] [date] NULL,
    [dt_retire] [date] NULL,
    [lga_pid] [nvarchar](15) NULL,
    [nsw_lga_sh] [date] NULL,
    [nsw_lga__1] [date] NULL,
    [nsw_lga__2] [nvarchar](100) NULL,
    [nsw_lga__3] [nvarchar](100) NULL,
    [nsw_lga__4] [date] NULL,
    [nsw_lga__5] [nvarchar](15) NULL,
 CONSTRAINT [PK_nsw_lga_polygon_shp] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

GO

    select 
    Geom.STGeometryType() as ShapeType,
    geom.STBoundary().ToString() as Boundary,
    Geom,
    nsw_lga__2 
from 
    [nsw_lga_polygon_shp]
where
nsw_lga__2 like '%SYDNEY%'

Result:

ShapeType "MultiPolygon" enter image description here

However, I don't know why when I try to use the filter function, It does return me anything.

Following is my script

select 
        Geom,
        nsw_lga__2
    from 
        [nsw_lga_polygon_shp]
    where
        Geom.Filter(geometry::Parse('POLYGON((  150 -33, 152 -33 , 152 -34 , 150 -34 , 150 -33 ))')) = 1

I as expecting to see the two LGA from my first query and also all surrounding LGAs. Could anyone share a light on how to fix my problem?

Thanks in advance!

As requested, I have added the result using EnvelopeAggregate:

enter image description here

I now notice it is the due to different SRID.

Solution

  • one common issue could be the Native SRID was not set and needs to be specified in Database Utilities feature class properties, or not set.

    Run this to find out:

     -- Please replace MYTABLE with your `Table`
 select distinct SP_GEOMETRY.STSrid from dbo.MYTABLE

    Now, if the SRID is not available in the drop-down, but one can simply type your SRID for e.g. 3395 into the Native SRID field.

    After specifying the Native SRID correctly, SQL Server spatial filters work as designed.

    Also you can re-project your data using these SpatialTools

    Ref: link