2

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.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
user325558
  • 1,413
  • 5
  • 22
  • 35
  • I already enumerated some options (computed columns, user-defined functions) for your situation when you asked the question here: [MSSQL lat;lng varchar split procedure to use as Lat and Lng for speed Searching](http://stackoverflow.com/questions/3873619/mssql-latlng-varchar-split-procedure-to-use-as-lat-and-lng-for-speed-searching/3873892#3873892). – Joe Stefanelli Oct 06 '10 at 16:08
  • The advantages gained by storing your lat/long values will depend on how you USE these values. Can you please elaborate on that? – George Mastros Oct 06 '10 at 16:22
  • These values are used in Haversine distance to calcule geopoints. Joe could you sugest the best way for a large database that preserve performance. – user325558 Oct 06 '10 at 17:33

1 Answers1

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
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Great joe, thks. This table is sqlserver2005 not optimized for geometry searching. Could you explain about move this solution to optimized geometry http://mssqlspatial.codeplex.com/wikipage?title=Getting%20Started&referringTitle=Tutorials – user325558 Oct 06 '10 at 23:33
  • I'm sorry, but that's well beyond the scope of a simple comment section here. I'd suggest that you continue to Google for some tutorials and examples. Then come back to SO with specific questions on anything that's still not clear for you. – Joe Stefanelli Oct 07 '10 at 01:49