Last active
August 4, 2021 20:26
-
-
Save philippwiddra/290f964a61b806919fddb67ac3e3442e to your computer and use it in GitHub Desktop.
T-SQL Geography and Geometry
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment