locked
Geometry in varbinary RRS feed

  • Question

  • Hi,

    I have a table with a column name Geometry (datatype varbinary (max)) and it contains geom of certain Area, table also have other columns e.g. Area (values A, B, C), Zone (values 1, 2, 3).

    Another table contain two columns: Latitude (float) and Longitude (float). Value of Latitude and Longitude could be within area of Geometry column of previous table.

    I need to prepare statement to find area based on lookup of Latitude and Longitude in Geometry column. Since all these 3 involved columns are not stored as geometry date type how could I perform the lookup.

    Any help is highly appreciated as I am new to SQL spatial.

    Regards,

    Ash

    Monday, April 1, 2019 9:07 PM

All replies

  • Hi AshwaniSrivastava,

     

    Geometric information is stored in the database as a binary data type. You can use STAsText() method to convert them to information that contains latitude and longitude. For more details, please refer to https://gis.stackexchange.com/questions/19854/what-is-the-format-of-geometry-data-type-of-sqlserver-2008

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, April 2, 2019 2:20 AM
  • Hi Dedmon Dai,

    I am getting below error when using STAsText() or STAsBinary:

    Msg 4121, Level 16, State 1, Line 30
    Cannot find either column "Geometry" or the user-defined function or aggregate "Webmap_Geometry.STAsText", or the name is ambiguous.

    May be reason being column "Geometry" is datatype varbinary and not sql spatial geometry type?

    Tuesday, April 2, 2019 3:06 AM
  • You can try to convert your binary data type to a geometric data type using the methods in the link below:https://gis.stackexchange.com/questions/160629/how-to-convert-from-sql-server-geometry-blob-to-something-else

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, April 2, 2019 5:57 AM
  • Hi Dedmon Dai,

    I was getting Geom data from GeoMedia as GDO format in varbinary and I checked with Hexagon so it was old way of implementation and to convert to SQL spatial is a challenge. However now GeoMedia can export data as SQL Spatial type, and I have got "Area" data in Geometry type.

    However, I am still looking out how to query data stored as float type as Latitude and Longitude to see if it is within any Area as coming from GeoMedia.

    I am converting Lat & Long to Geometry as follows:

    select Geometry::STGeomFromText('POINT(-43.4890671 172.563354)', 0) 

    and then check if output of above query is in within Area coming from GeoMedia table

    SELECT   *
    FROM     Geomedia_Table 
    where Geometry_SPA.STContains(0x00000000010C580630C099BE45C044C2F7FE06926540) =1

    No luck, any suggestion is welcome.

    Wednesday, April 3, 2019 9:37 PM
  • Hi Dedmon Dai,

    I have identified further that I have got Area data in NZTM (EPSG:2193) format and lat/long data in WGS84 (EPSG:4326).

    So I need to bring them to a point where either of them converted to match other one.

    Thanks for your help.



    Thursday, April 4, 2019 12:21 AM
  • Anyone else could give a solution?
    Friday, April 5, 2019 7:04 AM