none
Dados tipo Geography RRS feed

  • Pergunta

  • Sou iniciante no SQL Server e tenho essa duvida:

    Existe algum meio de extrair minha Lat e Long do campo Geogrphy?

    ele se encontra assim no meu db:

    0x110F0000010C22DBF9FE0E4A53C10812144FBBA844C1

    quarta-feira, 13 de março de 2013 13:15

Respostas

  • Estranho. Obtive o resultado com o script abaixo:

    O seu campo é do tipo GEOGRAPHY?

    CREATE TABLE [dbo].[Landmark] (
        [ID]                INT IDENTITY(1, 1),
        [LandmarkName]      VARCHAR(100),
        [Location]          VARCHAR(50),
        [Latitude]          FLOAT,
        [Longitude]         FLOAT
    )
    GO
    
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694)
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
    GO
    
    SELECT * FROM LANDMARK
    
    
    ALTER TABLE [dbo].[Landmark]
    ADD [GeoLocation] GEOGRAPHY
    GO
    
    UPDATE [dbo].[Landmark]
    SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + 
                        CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
    GO
    
    SELECT * FROM LANDMARK
    
    Select
    	[GeoLocation].Lat,
    	[GeoLocation].Long
    From
    	dbo.[Landmark];
    	
    	

    quarta-feira, 13 de março de 2013 19:00

Todas as Respostas

  • Select
    [GeoLocation].Lat,
    [GeoLocation].Long
    From
    dbo.[tabela];

    http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/5cc4cdf3-d882-4342-9ff4-525e17fd8a3f/

    Espero que te ajude.

    quarta-feira, 13 de março de 2013 17:20
  • Obrigado pela ajuda!

    Executando essa query, recebo o seguinte erro:

    Could not find property or field 'Lat' for type 'Microsoft.SqlServer.Types.SqlGeometry' in assembly 'Microsoft.SqlServer.Types'.


    quarta-feira, 13 de março de 2013 17:49
  • Qual sql server vc está usando?

    Só vai funcionar se for sql2008

    Eu executei no Sql2008-R2.

    quarta-feira, 13 de março de 2013 18:29
  • É a 2008 R2.
    quarta-feira, 13 de março de 2013 18:47
  • Estranho. Obtive o resultado com o script abaixo:

    O seu campo é do tipo GEOGRAPHY?

    CREATE TABLE [dbo].[Landmark] (
        [ID]                INT IDENTITY(1, 1),
        [LandmarkName]      VARCHAR(100),
        [Location]          VARCHAR(50),
        [Latitude]          FLOAT,
        [Longitude]         FLOAT
    )
    GO
    
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694)
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 )
    INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
    VALUES        ( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
    GO
    
    SELECT * FROM LANDMARK
    
    
    ALTER TABLE [dbo].[Landmark]
    ADD [GeoLocation] GEOGRAPHY
    GO
    
    UPDATE [dbo].[Landmark]
    SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + 
                        CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
    GO
    
    SELECT * FROM LANDMARK
    
    Select
    	[GeoLocation].Lat,
    	[GeoLocation].Long
    From
    	dbo.[Landmark];
    	
    	

    quarta-feira, 13 de março de 2013 19:00