URISA Texas Speaker Series
January 25, 2022
Thank you to our Corporate Sponsors!
For questions or comments, contact URISA Texas: info@urisatexas.org
Or visit our website for more information: urisatexas.org
with URISA Texas
Scan Here if you
want to Volunteer
Advanced Enterprise / SQL Server Database Topics
John May
Esciences
Topics covered today
1. Triggers -
Rules (SDE vs Non-SDE)
Considerations
2. Spatial and non-spatial Views
Creating a spatial view in SQL Server
Creating non-spatial views
Use case examples
3. Integrating your GIS data with SQL Server Reporting Services
Using the Visual Studio shell
Simple report design
Calling a report from your GIS data
4. Measurements in WGS
Essential reading
Essential reading
ST_Geometry Methods – Essential reading for Geoprocessing
Triggers
Rules
Triggers
Template
CREATE TRIGGER [DATABASENAME].[TableName_TR] ON [DATABASENAME].[TableName]
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for trigger here
/***Update Longitude and Latitude columns***/
UPDATE PointFeatureClass set Latitude= Shape.STY;
UPDATE PointFeatureClass set Longitude = Shape.STX;
END
GO
Triggers
SSMS Demo
Collector Demo
Database Views
Rules
Database Views
Template – Create a View from a Feature Class
CREATE VIEW [DATABASENAME].[ViewName]
AS
SELECT
,[OBJECTID]
,OTHER ATTRIBUTES
,[Shape]
FROM [DATABASENAME].[FeatureClass]
Database Views
Template – Create mappable points from a table
SELECT
CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS INT) AS OBJECTID,
FC.Attribute(s),
Shape = geometry::STPointFromText('Point('+cast(cast(FC.longitude as float)as varchar)+' '+cast(cast(FC.latitude as float)as varchar)+')', 4326)
FROM TableWithCoordinates FC;
Database Views
Template – Create mappable lines from a table
SELECT
CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS INT) AS OBJECTID,
FC.Attribute(s),
Shape = geometry::STLineFromText('LINESTRING('+cast(cast(FC.longitude as float)as varchar)+' '+cast(cast(FC.latitude as float)as varchar)+','+cast(cast(FC.Longitude2 as float)as varchar)+' '+cast(cast(FC.Latitude2 as float)as varchar)+')', 4326)
FROM TableWithCoordinates FC;
Database Views
Template – Create mappable points and lines from a table
SELECT
CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS INT) AS OBJECTID,
FC.Attribute(s),
ShapePoint = geometry::STPointFromText('Point('+cast(cast(FC.longitude as float)as varchar)+' '+cast(cast(FC.latitude as float)as varchar)+')', 4326),
ShapeLine = geometry::STLineFromText('LINESTRING('+cast(cast(FC.longitude as float)as varchar)+' '+cast(cast(FC.latitude as float)as varchar)+','+cast(cast(FC.Longitude2 as float)as varchar)+' '+cast(cast(FC.Latitude2 as float)as varchar)+')', 4326)
FROM TableWithCoordinates FC;
Database Views
SSMS Demo
Web Mapping Demo
Integration with SSRS
Rules
Integration with SSRS
Handy SQL Hacks for reports
Add linear measurements to a report
SELECT geography::STGeomFromText([shape].STAsText(),4326).STLength() * 3.28084 as LengthFeet
FROM LineFeatureClass;
Integration with SSRS
Handy SQL Hacks for reports
Add area measurements to a report
SELECT geography::STGeomFromText([shape].STAsText(),4326).STArea() * 0.000247105 as Acres
FROM PolyFeatureClass;
Integration with SSRS
Handy SQL Hacks for reports
Add a URL for a Google Map to show starting point of a line
'https://www.google.com/maps/search/?api=1&query=' + SubString(shape.STStartPoint().ToString(),28,8) +'%2C' + SubString(shape.STStartPoint().ToString(),8,8) as GoogleLink
FROM LineFeatureClass
Integrating with SSRS
SSRS Demo
Web Mapping Demo
Training
URISA Texas Speaker Series
Resources
For questions or comments, contact URISA Texas: info@urisatexas.org