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:
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:
I now notice it is the due to different SRID.
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