sql-serversql-server-2005geospatialspatial-data

Need to move SQL Server 2005 Table storing Lat;Lng as varchar to Spatial?

I have read some articles about using spatial optimized tables. Actually I use stored latitude and longitude as varchar comma-separated (lat;lng).

Could you suggest the best way to perform this change and enumerate the advantages. It's really necessary for a large project or only move to SQL Server 2008?

thanks.

Solution

  • I'd add two new persisted computed colunns to your table as illustrated in the demo below.

    create table Demo (
    LatLng varchar(100),
    Lat as CAST(LEFT(LatLng, charindex(';',LatLng)-1) as float) PERSISTED,
    Lng as CAST(SUBSTRING(LatLng, charindex(';',LatLng)+1, LEN(LatLng)-charindex(';',LatLng)) as float) PERSISTED
)

insert into Demo
    (LatLng)
    values
    ('33.0000;15.222222')

select *
    from Demo

drop table Demo