CODEWITHSUNDEEP
c#sql-servernhibernategeolocationgeospatial
Nikola Sivkov
Nikola Sivkov

Reputation: 2852

operations with Spatial data from Google Maps in SQL Server 2008 r2

How can i store a rectangle - consisting of 2 points NorthEast and SouthWest each point is a coordinate of lattitude and longitude

And add a circle consisting of a center ( lat-lng ) and a radius (int/float value)

what is the best way to store and later on query if a lat-lng is within the bounds of a any circle or rectangle ? also , can i store an array of those ? say 10 rectangles and 5 circles in a single record ? Can i use Nhibernate to ease the pain?

Sorry if this seems noobish , i have never done anything with spatial data and i don't even have clue from where to start. Any samples and pointers are helpful ! Thanks in advance.

Upvotes: 1

Views: 2178

Answers (1)

nickrobison
nickrobison

Reputation: 150

Here's how I would approach this problem using TSQL.

For a rectangle, the simplest method is to extrapolate the extra 2 points by using the relevant coordinates from the original points. e.g.

NorthEast (lat1, lon1) NorthWest* (lat1, lon2)

SouthEast* (lat2, lon1) SouthWest (lat2, lon2)

*New point

That doesn't give you a true rectangle (in a mathematical sense) but it's a common method in GIS (it's how geohashes are formed) what you get is a rough rectangle with varying size based on the distance from the equator. If you need an extact rectangle of a certain height/width you should look into using the Haversine formula to calculate the remaining 2 points, that will take into account bearing, and great circle distance.

http://www.movable-type.co.uk/scripts/latlong.html

To store the rectangle, I'd create a SQL table with a GEOGRAPHY type column, this will allow you assign additional attributes (e.g. name) along with a spatial index that will make future queries much faster.

CREATE TABLE dbo.geographies
(
    NAME VARCHAR(50)
    ,GEOG GEOGRAPHY
)

INSERT INTO dbo.geographies (NAME, GEOG)
VALUES ('Rectangle', geography::STPolyFromText('POLYGON((lon1 lat1, lon2 lat1, lon2 lat2, lon1 lat2, lon1 lat1))', 4326))

Note that both the first point and the last point are the same, this is required to 'close' the polygon, and the final number denotes the SRID, or coordinate system, in this case WGS84. You can reference this page: http://msdn.microsoft.com/en-us/library/bb933971

As to the circle, it's simple to store a point and then use the radius to apply a buffer around the point:

INSERT INTO dbo.geographies (NAME, GEOG)
VALUES ('Circle with Radius', geography::STPointFromText('POINT(lon lat)', 4326).STBuffer([radius]))

Note that the buffer takes its input in meters so you may need to apply a conversion, more notes on this page: http://msdn.microsoft.com/en-us/library/bb933979

Now the fun part, it's quite easy to check for intersection on a point using the STIntersects method.

http://msdn.microsoft.com/en-us/library/bb933962.aspx

DECLARE @point GEOGRAPHY = geography::STPointFromText('POINT(lon lat)', 4326)

SELECT * FROM dbo.geographies
WHERE @point.STIntersects(GEOG) = 1

The code sample takes a point and returns a list of all the geographies that the point is found within. It's important the the SRIDs of the new point and the geographies in the table match, otherwise you'll get zero matches (and probably pound you head against a wall for a while until you realize your mistake, at least, that's what I do).

As to integrating this with C#, I'm not sure how much help I can be, but it shouldn't be too much of a challenge to return the SQLGeography type

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeography.aspx

Hopefully this at least points you in the right direction.

Upvotes: 2

Related Questions