SQL 2008地理&几何 - 使用哪个？ [英] SQL 2008 geography & geometry - which to use?

问题描述

我将在地球上拥有大量的点，并且要执行各种计算在SQL中 - 例如选择一个特定的多边形中包含的所有点，或者选择XY内10km以内的所有点。

我以前从未使用过SQL空间要素。我应该为此使用 geography 还是几何数据类型？

如果您有一个存储Google的表格像这样的地图点：

  CREATE TABLE geo_locations（
 location_id uniqueidentifier NOT NULL，
 position_point geography NOT NULL 
）; 
  

然后你可以用这个存储过程填充它：

  CREATE PROCEDURE proc_AddPoint 
 @latitude decimal（9,6），
 @longitude decimal（9,6），
 @altitude smallInt 
 AS 
 
 DECLARE @point geography = NULL; 
 
 BEGIN 
 
 SET NOCOUNT ON; 
 
 SET @point = geography :: STPointFromText（'POINT（'+ CONVERT（varchar（15），@longitude）+''+ 
 CONVERT（varchar（15），@latitude） +''+ 
 CONVERT（varchar（10），@altitude）+'）'，4326）
 
 INSERT INTO geo_locations 
（
 location_id，
 position_point 
）
 VALUES 
（
 NEWID（），
 @point 
）; 
 
 END 
  

然后，如果要查询纬度，经度和海拔高度，只需使用以下查询格式即可：

  SELECT 
 geo_locations.position_point.Lat纬度，
 geo_locations.position_point.Long AS经度，
 geo_locations.position_point.Z AS高度
 FROM 
 geo_locations; 
  

I'm creating a Google map mashup and am using SQL 2008.

I will have a large number of points on the earth and will want to perform various calculations on them in SQL - such as selecting all points contained within a particular polygone, or select all points within 10km of XY.

I have never used and SQL spatial features before. Should I use the geography or the geometry datatype for this?

解决方案

Geography is the type that is intended for plotting points on the earth.

If you have a table that stores Google Maps points like this:

CREATE TABLE geo_locations (
    location_id       uniqueidentifier  NOT NULL,
    position_point    geography         NOT NULL
);

then you could fill points in it with this stored procedure:

CREATE PROCEDURE proc_AddPoint
    @latitude     decimal(9,6),
    @longitude    decimal(9,6),
    @altitude     smallInt
AS

DECLARE @point     geography = NULL;

BEGIN

    SET NOCOUNT ON;

    SET @point = geography::STPointFromText('POINT(' + CONVERT(varchar(15), @longitude) + ' ' + 
                                                       CONVERT(varchar(15), @latitude) + ' ' + 
                                                       CONVERT(varchar(10), @altitude) + ')', 4326)

    INSERT INTO geo_locations
    (
        location_id, 
        position_point
    )
    VALUES 
    (
        NEWID(),
        @point
    );

END

Then if you want to query for the latitude, longitude and altitude, simply use the following query format:

SELECT
    geo_locations.position_point.Lat  AS latitude,
    geo_locations.position_point.Long AS longitude,
    geo_locations.position_point.Z    AS altitude
FROM
    geo_locations;

这篇关于SQL 2008地理&几何 - 使用哪个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！