SQL Spatial Examples

Posting these here for my own reference, but use them yourself if you’d like.

Select Well Known Text from Geometry
select [Geometry Column].STAsText() from [Table];

Create WKT from lat/long fields, good for insert/update statements
select GEOGRAPHY::STGEomFromText(‘POINT (‘ + convert(varchar(20),[LONGITUDE]) + ‘ ‘ + convert(varchar(20),[LATITUDE]) +’)’, 4269) from [TABLE];

Intersection Example:
SELECT g.* , g.[Geometry Field].STAsText() FROM [Schema].[Table] g WHERE [Geometry Field].STIntersects(geometry::STGeomFromText(‘POLYGON ((-131.232584635417 55.4096875, -131.230598958333 55.4096875, -131.230598958333 55.4100455729167, -131.232584635417 55.4100455729167, -131.232584635417 55.4096875))’, [EPSG ID])) = 1

Get Area of Intersection
select
t1.[GEOMETRY_FIELD].STIntersection(t2.[GEOMETRY_FIELD]).STArea() AREA
from [TABLE_NAME_1] t2,
[TABLE_NAME_2] t1

Create Spatial Index:
CREATE SPATIAL INDEX [sidx_tablename_geom] ON [Schema].[Table]
(
[Geometry Field]
)USING GEOMETRY_GRID
WITH (
BOUNDING_BOX =(-173.116318241729, 52.7677349180939, -129.473430300096, 71.3903800225212), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Get Bounding Box of Feature Class:
WITH
ENVELOPE as
(
SELECT [Geometry Field].STEnvelope() as envelope from [Schema].[Table]
),
CORNERS as
(
SELECT envelope.STPointN(1) as point from ENVELOPE
UNION ALL select envelope.STPointN(3) from ENVELOPE
)

SELECT
MIN(point.STX) as MinX,
MIN(point.STY) as MinY,
MAX(point.STX) as MaxX,
MAX(point.STY) as MaxY
FROM
CORNERS;

Use GDAL to Insert Data from Shapefile to MS SQL:
ogr2ogr -f MSSQLSpatial “MSSQL:server=[Server Name];database=[DB Name];trusted_connection=yes” “…\[Shapefile Name].shp”

SRID from geometry field
select [Geometry Field Name].STSrid FROM [TABLE NAME];

Leave a Reply

Your email address will not be published.

87 + = 91