locked
Understanding Spatial Data with example RRS feed

  • Question

  • Hi

    Can anyone help me out with detail understanding of spatial data type in sql 2012/2014 with good examples.I went through different articles over net but no good example cld find.


    srm_2010

    Monday, August 15, 2016 7:36 AM

Answers

  • Hi srm_2010,

    SQL Server supports two spatial data types: the geometry data type and the geography data type.

    Geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system.

    You can refer to the following example to create table and insert data:
    CREATE TABLE SpatialTable
        ( id int IDENTITY (1,1),
        GeogCol1 geography,
        GeogCol2 AS GeogCol1.STAsText() );
    GO
    
    INSERT INTO SpatialTable (GeogCol1)
    VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326));
    
    INSERT INTO SpatialTable (GeogCol1)
    VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));
    GO

    In this scenario, we created a table with an identity column and a geography column, GeogCol1. We can use a third column renders the geography column into its Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, and we need the STAsText() method.

    For more examples, you can review this article.

    Geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system, you can get example about geometry from: https://msdn.microsoft.com/en-us/library/cc280487(v=sql.110).aspx .

    Regards,
    Teige
    Tuesday, August 16, 2016 9:33 AM

All replies

  • The end objective of spatial data mining is to find patterns in data with respect to geography. So far, data mining and Geographic Information Systems (GIS) have existed as two separate technologies, each with its own methods, traditions, and approaches to visualization and data analysis.

    https://en.wikipedia.org/wiki/Examples_of_data_mining

    https://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_sam_concepts.htm


    Please click Mark As Answer if my post helped.



    • Edited by AV111 Monday, August 15, 2016 8:16 AM
    • Proposed as answer by Teige Gao Monday, August 15, 2016 9:33 AM
    • Unproposed as answer by Teige Gao Tuesday, August 16, 2016 8:35 AM
    Monday, August 15, 2016 8:12 AM
  • No I am afraid ,I am not looking to what you have sent.I want on geospatial system info that is used as geometry and geography data type,example to use it  in sql server 2014 database engine.

    srm_2010

    Tuesday, August 16, 2016 7:22 AM
  • Hi srm_2010,

    SQL Server supports two spatial data types: the geometry data type and the geography data type.

    Geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system.

    You can refer to the following example to create table and insert data:
    CREATE TABLE SpatialTable
        ( id int IDENTITY (1,1),
        GeogCol1 geography,
        GeogCol2 AS GeogCol1.STAsText() );
    GO
    
    INSERT INTO SpatialTable (GeogCol1)
    VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326));
    
    INSERT INTO SpatialTable (GeogCol1)
    VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));
    GO

    In this scenario, we created a table with an identity column and a geography column, GeogCol1. We can use a third column renders the geography column into its Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, and we need the STAsText() method.

    For more examples, you can review this article.

    Geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system, you can get example about geometry from: https://msdn.microsoft.com/en-us/library/cc280487(v=sql.110).aspx .

    Regards,
    Teige
    Tuesday, August 16, 2016 9:33 AM