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

Question

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.

Answer 1

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