T-SQL Geography and Geometry

geo.sql

INSERT Locations (Name, Position)
VALUES ('Golden Gate Bridge', geography::STPointFromText('POINT(-122.478255 37.819929)', 4326))

-- POINT(Longitude, Latitude)


-- UPDATE [dbo].[Landmark]
-- SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326)
-- GO



-- STGeomFromText
-- POINT, POLYGON, LINESTRING, MULTIPOINT, MULTIPOLYGON, MULTILINESTRING, GEOMETRYCOLLECTION

-- Find nearest Location
SELECT TOP 10 *
FROM Locations
ORDER BY LatLon.STDistance(geography::Point(40.701,-74.045, 4326))

-- Circle around
SELECT Geography::EnvelopeAggregate(LatLon) Envelope
FROM Locations
WHERE Name LIKE 'Golden Gate %'

-- Autocreate Geo-Histogram
EXEC sp_help_spatial_geography_histogram
	@tabname='Locations',
	@colname='latlon',
	@resolution=1000,
	@sample=100


INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
       ( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
       ( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
       ( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
       ( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
       ( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
       ( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )