SQL Server 2008 R2 Spatial Queries

0
votes

I am very new to the spatial realm of SQL Server and need some help. I have a waypoint organizing app and I am trying to generate some queries that follow along the premise of finding waypoints that are part of geographic polygons like lakes, rivers, etc. I have preloaded my tables with data I have downloaded. I used shape2sql.exe to load shapefiles into the appropriate db tables.

Tables are as follows:
Water table - id, name, geog(geography data type)
State table - id, state_name, state_abbr, geog(geography data type)
County table - id, name, state_name, geog(geography data type)
Waypoint table - id, name, lat, lon, waterid

How do I write queries against these tables to return things like:
- all waypoints in 'michigan'
- all waypoints on 'bass lake' in 'montcalm' county in 'michigan' (there are multiple bass lakes in michigan and the country hence the county/state part)
- auto assign the water id column of the waypoint table by "processing" a group of waypoints and finding what lake they actually belong to
- etc.

Thanks!

Learned so far:
select geog.ToString() as Points, geog.STArea() as Area, geog.STLength() as Length from water where name like '%bass lake%' and STATE = 'mi'

will return the record for Bass Lake and the polygon with the actual coordinates for the lake.
POLYGON ((-87.670498549804691 46.304831340698243, -87.670543549804691 46.307117340698241, -87.676573549804687 46.313480340698241, -87.68120854980468 46.314821340698245, -87.685168549804686 46.315703340698242, -87.6877605498047 46.313390340698241, -87.685051549804683 46.308827340698244, -87.682360549804685 46.305650340698243, -87.677734549804683 46.304768340698246, -87.674440549804686 46.304336340698242, -87.670498549804691 46.304831340698243)) 1022083.96662664 4027.52433709888

2
sql-server-2008-r2spatial

2 Answers

0
votes

Shooting from the hip, here, but maybe like this:

UPDATE  waypoints
SET waypoints.WaterId = water.Id
FROM    dbo.Waypoints AS waypoints LEFT JOIN
    dbo.Water AS water ON geography::Point(waypoints.Lat, waypoints.Lon, 4326).STIntersects(water.geog)

Should set the waterId on the wapoints table to one of the matching water ids, from the water table.

This should get you all the waypoints on BASS LAKE

SELECT  waypoints.*
FROM    dbo.Waypoints as waypoints INNER JOIN
    dbo.Water AS water ON geography::Point(waypoints.Lat, waypoints.Lon, 4326).STIntersects(water.geog) = 1
WHERE   water.Name = 'BASS_LAKE' -- OR WHATEVER
0
votes

Ok - learning as I go so here are some answers to my own questions for anyone what would like to know.

Here is one query for finding various waypoints with conditions in the where clause:
SELECT * FROM WaypointTable wp
JOIN WaterTable w
ON wp.geogcolumn.STIntersects(w.geogcolumn) = 1
WHERE w.name LIKE '%bass lake%'
AND w.state = 'mi';

Here is a query for assigning water id's to waypoints based on where they 'fit':
UPDATE WaypointTable wp
SET WaterID = (
SELECT ID
FROM WaterTable
WHERE geogcolumns.STIntersects(wp.geogcolumn) = 1
);

Both of these queries work extremely well and fast! Love it!