mssqlspatial functions

Topics: User Forum
Jul 18, 2009 at 5:09 PM

ST.Point appears to be the correct sp to use to create a lonlat point. (I think I am using the proper terminology).   I have had the geonames database for a number of years and want to convert the longitude and latitude coordinates (hopefully to make good use of the spatial indexing - sql 2008).  I've deployed the software (msscmd -deploy -server=mssql_server -db=mssql_database) and would like to take advantage of the ST.POINT.

UPDATE mygeonamedb SET thepoint_lonlat = ST.Point(longitude, latitude, 4269)

Unfortunately, ST.Point does not exist.  Only ST.PointN,ST.PointFromText,ST.PointFromSurface and ST.PointFromWKB appear in the list of functions.  Am I missing something?

 

 

Developer
Jul 20, 2009 at 9:32 AM

Hi thejamie, you _should_ have a scalar valued function ST.Point available. Just to clarify Sq lServer 2008 has spatial indexing - but only of native sql server geometry types - MsSqlSpatial does not have true spatial indexing (but runs on Sql Server 2005 i.e pre addition of spatial types to the sql server core) If you need spatial indexing you would probably do better using the native spatial extensions in sql server 2008.. cheers jd 

Jul 24, 2009 at 2:14 PM
Edited Jul 26, 2009 at 3:10 PM

Thanks. I think I read somewhere that there is a version out that is later than the one I downloaded and it may be the cause of my confusion.  I'll try the 2008 extensions.

This is just an observation but I found it is rather easy to kick off the query using the wrong index.  Since I'm using a single database from the GNA download and data-mining time zone history, there are many cities that are contained in the database that are names of city celebrations (3 Marabouts)and others that are in the native scribe (contain ascii characters above 128 - شنور) which I did not want to include in the search.  Unfortunately, modifying the table will cause the index to switch from the geometry index to the tables clustered index.  In the example below, the part that removes the index reference is commented out.  If you note, the same sub-filter can be applied to the lookup for the geometry point and that has solved my problem.   I suspect I could force the index, but it seems self-defeating.  Hope this information helps someone else. 

SELECT g.full_name_nd city,g.ADM1,g.cc1,g.DSG,g.dms_lat,g.dms_long,
  c.country countrystate,r.Region,r.TZName,r.tz,
  g.geog.STDistance(g.geog)/1000 [Distance in km]
FROM(
  select dms_lat,dms_long,adm1,cc1,full_name_nd,lat,long,geog,dsg from cities2009.dbo.geonames_dd_dms_date_20081231
  --where CC1=@cc1 and(DSG like 'ppl%' or DSG like 'isl%')
  --and not ascii(LEFT(full_name_nd,1))>128 and not ascii(LEFT(full_name_nd,1))=40
  )g
inner join Countries c on c.abbr=g.cc1 collate SQL_Latin1_General_CP1_CI_AS
inner join Regions r on r.ADM1=g.ADM1 collate SQL_Latin1_General_CP1_CI_AS and r.CC1=g.cc1 collate SQL_Latin1_General_CP1_CI_AS
WHERE g.full_name_nd = @city and g.cc1=@cc1
and g.geog.STDistance(g.geog)<10
ORDER BY g.geog.STDistance(g.geog)

Also, of note, now that I have both the projects's 2005 and native 2008 procedures, functions, etc on the database, it seems difficult to figure out which indexes are 2008 based and which are not.